トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

10-129 オプション指定のgroup_concat関数を模倣

SQLパズル

MySQLの、オプション指定のgroup_concat関数を模倣し、
以下と同じ結果を取得する、OracleのSQLを記述する。

mysql> select ID,sum(Val) as SumVal,
    -> group_concat(distinct Str order by Str desc) as ConcatStr
    ->   from (select 1 as ID,100 as Val,'aaa' as Str
    -> union all select 1,150,'bbb'
    -> union all select 1,200,'aaa'
    -> union all select 2,300,'ccc'
    -> union all select 2,400,'ddd'
    -> union all select 2,450,'ddd'
    -> union all select 2,450,'ddd'
    -> union all select 2,550,'eee') dummy
    -> group by ID;
+----+--------+-------------+
| ID | SumVal | ConcatStr   |
+----+--------+-------------+
|  1 |    450 | bbb,aaa     |
|  2 |   2150 | eee,ddd,ccc |
+----+--------+-------------+

こちらを参考にさせていただきました


SQL

col ConcatStr for a20

--■■■階層問い合わせを使う方法■■■
with WorkView as (select 1 as ID,100 as Val,'aaa' as Str from dual
union all select 1,150,'bbb' from dual
union all select 1,200,'aaa' from dual
union all select 2,300,'ccc' from dual
union all select 2,400,'ddd' from dual
union all select 2,450,'ddd' from dual
union all select 2,450,'ddd' from dual
union all select 2,550,'eee' from dual)
select ID,SumVal,SubStr(sys_connect_by_path(Str,','),2) as ConcatStr
from (select ID,Str,
      sum(Val) over(partition by ID) as SumVal,
      count(distinct Str) over(partition by ID) as MaxLevel,
      Dense_Rank() over(partition by ID order by Str desc) as Rank1,
      Row_Number() over(partition by ID,Str order by 1) as Rank2
        from WorkView)
where Level = MaxLevel
Start With 1 = all(Rank1,Rank2)
connect by prior ID = ID
       and prior Rank1 = Rank1 - 1
       and Rank2 = 1;

--■■■model句を使う方法(10g以降)■■■
with WorkView as (select 1 as ID,100 as Val,'aaa' as Str from dual
union all select 1,150,'bbb' from dual
union all select 1,200,'aaa' from dual
union all select 2,300,'ccc' from dual
union all select 2,400,'ddd' from dual
union all select 2,450,'ddd' from dual
union all select 2,450,'ddd' from dual
union all select 2,550,'eee' from dual)
select ID,SumVal,max(ConcatStr) as ConcatStr
from (select ID,SumVal,ConcatStr
        from WorkView
       model
       partition by (ID)
       dimension by (Row_Number() over(partition by ID order by Val) as rn)
       measures(Val,9999 as SumVal,cast(Str as varchar2(200)) as ConcatStr)
       rules(
       ConcatStr[any] order by rn=
       case when cv(rn) = 1 then ConcatStr[cv()]
            when instr(',' || ConcatStr[cv()-1] || ',',',' || ConcatStr[cv()] || ',') > 0
            then ConcatStr[cv()-1]
            else ConcatStr[cv()-1] || ',' || ConcatStr[cv()] end,
       SumVal[any] = sum(Val)[any]))
group by ID,SumVal;


解説

where Level = MaxLevel
の部分は、
10gならば、Connect_by_IsLeafを使ってもいいでしょう