Skip to Main Content
  • Questions

  • A (perhaps) tricky "partition by" SQL question

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Rick.

Asked: February 01, 2012 - 5:16 pm UTC

Last updated: February 13, 2012 - 12:13 pm UTC

Version: 10.2.0.2

Viewed 1000+ times

You Asked

I have the following data (ID, TYPE, DAY, BASE_DAY) and would like to create the column BASE_TYPE in a single, efficient query statement.

create table t (id int, type char, day date, base_day int, base_type char);

with this data in it;
insert into t values (1,'c',to_date('12/03/2009'),0,'a')
insert into t values (1,'e',to_date('12/10/2009'),0,'a')
insert into t values (1,'a',to_date('01/20/2010'),1,'a')
insert into t values (1,'a',to_date('06/30/2010'),0,'a')
insert into t values (1,'d',to_date('07/04/2010'),0,'a')
insert into t values (1,'a',to_date('09/02/2010'),0,'a')
insert into t values (2,'d',to_date('11/11/2009'),0,'k')
insert into t values (2,'k',to_date('01/20/2010'),1,'k')
insert into t values (2,'d',to_date('02/23/2010'),0,'k')
insert into t values (2,'s',to_date('05/04/2010'),0,'k')
insert into t values (3,'c',to_date('10/03/2009'),0,'ck')
insert into t values (3,'e',to_date('11/02/2009'),0,'ck')
insert into t values (3,'c',to_date('01/20/2010'),1,'ck')
insert into t values (3,'k',to_date('01/20/2010'),1,'ck')
insert into t values (3,'a',to_date('03/30/2010'),0,'ck')
insert into t values (3,'d',to_date('04/04/2010'),0,'ck')
insert into t values (3,'s',to_date('05/23/2010'),0,'ck')

NOTE:::: The last column (base_type) is "fake". I added it manually. I would like to create it programmatically.


I tried playing with max() over (partition by id) etc but note the tricky part: ID=3 who has multiples types on the base day which I would like to concat. I am blanking on this and I apologize if it is super-easy or been addressed a million times. I will gladly accept curses if they are followed by an answer ;-) Thanks!

and Tom said...

Note: your example code doesn't run - run it and you'll see.... test cases SHOULD have been run already. Makes life easier....

here is one approach.
ops$tkyte%ORA11GR2> with connect_by_data
  2  as
  3  (select id, replace( max(sys_connect_by_path(type,',')),',','') data
  4     from (select id, type, row_number() over (partition by id order by type) rn from t where base_day = 1)
  5    start with rn = 1
  6  connect by prior id = id and prior rn = rn-1
  7    group by id
  8  )
  9  select t.id, t.type, t.day, t.base_day, connect_by_data.data
 10    from t, connect_by_data
 11   where t.id = connect_by_data.id
 12   order by t.id, t.day
 13  /

        ID T DAY          BASE_DAY DATA
---------- - ---------- ---------- --------------------
         1 c 12/03/2009          0 a
         1 e 12/10/2009          0 a
         1 a 01/20/2010          1 a
         1 a 06/30/2010          0 a
         1 d 07/04/2010          0 a
         1 a 09/02/2010          0 a
         2 d 11/11/2009          0 k
         2 k 01/20/2010          1 k
         2 d 02/23/2010          0 k
         2 s 05/04/2010          0 k
         3 c 10/03/2009          0 ck
         3 e 11/02/2009          0 ck
         3 c 01/20/2010          1 ck
         3 k 01/20/2010          1 ck
         3 a 03/30/2010          0 ck
         3 d 04/04/2010          0 ck
         3 s 05/23/2010          0 ck

17 rows selected.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

and another..

Duke Ganote, February 03, 2012 - 8:34 pm UTC

WITH sample_data AS (
SELECT 0 as ID, '' as TYPE, sysdate as DAY_dt
, 1 as BASE_DAY, '' as base_type1
FROM DUAL WHERE 1=0 union all
select 1,'c',to_date('12/03/2009','MM/DD/YYYY'),0,'a' from dual union all
select 1,'e',to_date('12/10/2009','MM/DD/YYYY'),0,'a' from dual union all
select 1,'a',to_date('01/20/2010','MM/DD/YYYY'),1,'a' from dual union all
select 1,'a',to_date('06/30/2010','MM/DD/YYYY'),0,'a' from dual union all
select 1,'d',to_date('07/04/2010','MM/DD/YYYY'),0,'a' from dual union all
select 1,'a',to_date('09/02/2010','MM/DD/YYYY'),0,'a' from dual union all
select 2,'d',to_date('11/11/2009','MM/DD/YYYY'),0,'k' from dual union all
select 2,'k',to_date('01/20/2010','MM/DD/YYYY'),1,'k' from dual union all
select 2,'d',to_date('02/23/2010','MM/DD/YYYY'),0,'k' from dual union all
select 2,'s',to_date('05/04/2010','MM/DD/YYYY'),0,'k' from dual union all
select 3,'c',to_date('10/03/2009','MM/DD/YYYY'),0,'ck' from dual union all
select 3,'e',to_date('11/02/2009','MM/DD/YYYY'),0,'ck' from dual union all
select 3,'c',to_date('01/20/2010','MM/DD/YYYY'),1,'ck' from dual union all
select 3,'k',to_date('01/20/2010','MM/DD/YYYY'),1,'ck' from dual union all
select 3,'a',to_date('03/30/2010','MM/DD/YYYY'),0,'ck' from dual union all
select 3,'d',to_date('04/04/2010','MM/DD/YYYY'),0,'ck' from dual union all
select 3,'s',to_date('05/23/2010','MM/DD/YYYY'),0,'ck' from dual
)
SELECT ID, type, day_dt, base_day, base_type
FROM sample_data
MODEL
PARTITION BY (ID)
DIMENSION BY (row_number() over (partition by ID order by type) rn)
MEASURES( type, base_day, base_type1, day_dt
, cast(null as varchar2(5)) as base_builder
, cast(null as varchar2(5)) as base_type
)
RULES (
base_builder[1] = case base_day[cv()] when 1 then type[cv()] end
,base_builder[rn] = base_builder[cv()-1]||
case base_day[cv()] when 1 then type[cv()] end
,base_type[any] = max(base_builder) OVER (partition by ID)
)
ORDER BY id, day_dt
/

ID T DAY_DT BASE_DAY BASE_
----- - ----------- ---------- -----
1 c 2009-DEC-03 0 a
1 e 2009-DEC-10 0 a
1 a 2010-JAN-20 1 a
1 a 2010-JUN-30 0 a
1 d 2010-JUL-04 0 a
1 a 2010-SEP-02 0 a
2 d 2009-NOV-11 0 k
2 k 2010-JAN-20 1 k
2 d 2010-FEB-23 0 k
2 s 2010-MAY-04 0 k
3 c 2009-OCT-03 0 ck
3 e 2009-NOV-02 0 ck
3 c 2010-JAN-20 1 ck
3 k 2010-JAN-20 1 ck
3 a 2010-MAR-30 0 ck
3 d 2010-APR-04 0 ck
3 s 2010-MAY-23 0 ck

Custom string aggregator

Matthew McPeak, February 06, 2012 - 3:19 pm UTC

Sooner or later, I'm going to have to learn the model clause. It hurts my poor head.

But, if you have a custom string aggregate function (I'm pretty sure Tom has the code for one on his site here), then I think the answer is just:

select t.*, replace(cust_comma_list(case when base_day = 1 then type else null end) over ( partition by id order by type rows between unbounded preceding and unbounded following ),',','') basetype_calc from t

Assuming you name your custom string aggregator "cust_comma_list". LISTAGG is good in 11i, but doesn't do everything the old ODCI aggregates could do for some reason?

Matt


LISTAGG

Duke Ganote, February 13, 2012 - 12:09 pm UTC

As Matthew McPeak suggested, could be done with LISTAGG:

WITH sample_data AS (
SELECT 0 as ID, '' as TYPE, sysdate as DAY_dt
, 1 as BASE_DAY, '' as base_type1
FROM DUAL WHERE 1=0 union all
select 1,'c',date'2009-12-03',0,'a' from dual union all
select 1,'e',date'2009-12-10',0,'a' from dual union all
select 1,'a',date'2010-01-20',1,'a' from dual union all
select 1,'a',date'2010-06-30',0,'a' from dual union all
select 1,'d',date'2010-07-04',0,'a' from dual union all
select 1,'a',date'2010-09-02',0,'a' from dual union all
select 2,'d',date'2009-11-11',0,'k' from dual union all
select 2,'k',date'2010-01-20',1,'k' from dual union all
select 2,'d',date'2010-02-23',0,'k' from dual union all
select 2,'s',date'2010-05-04',0,'k' from dual union all
select 3,'c',date'2009-10-03',0,'ck' from dual union all
select 3,'e',date'2009-11-02',0,'ck' from dual union all
select 3,'c',date'2010-01-20',1,'ck' from dual union all
select 3,'k',date'2010-01-20',1,'ck' from dual union all
select 3,'a',date'2010-03-30',0,'ck' from dual union all
select 3,'d',date'2010-04-04',0,'ck' from dual union all
select 3,'s',date'2010-05-23',0,'ck' from dual
)
select ID
, LISTAGG(case when base_day = 1 then type else null end)
within group (order by type) AS basetype_calc
from sample_data t
group by ID;

ID BASETYPE_CALC
----- --------------------
1 a
2 k
3 ck
Tom Kyte
February 13, 2012 - 12:13 pm UTC

right, but not in version 10.2.0.2 - which the original poster is using....
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.