トップページに戻る    前のmodel句のサンプルへ

model句23 MySQLのgroup_concat(distinctオプション付き)もどき

SQLパズル

groupConcatDis
ID  Val
--  ---
 1  A
 1  A
 2  D
 2  E
 2  F
 3  GG
 3  HH
 3  HH
 4  JJ
 5  a
 5  a
 5  b
 5  b
 5  c
 5  c

MySQLのgroup_concat(distinctオプション付き)を使った下記の結果をmodel句を使って取得する。

select ID,group_concat(distinct Val order by Val) as concatVal
  from groupConcatDis
group by ID;

出力結果
ID  concatVal
--  ---------
 1  A
 2  D,E,F
 3  GG,HH
 4  JJ
 5  a,b,c


データ作成スクリプト

create table groupConcatDis(ID,Val) as
select 1,'A'  from dual union all
select 1,'A'  from dual union all
select 2,'D'  from dual union all
select 2,'E'  from dual union all
select 2,'F'  from dual union all
select 3,'GG' from dual union all
select 3,'HH' from dual union all
select 3,'HH' from dual union all
select 4,'JJ' from dual union all
select 5,'a'  from dual union all
select 5,'a'  from dual union all
select 5,'b'  from dual union all
select 5,'b'  from dual union all
select 5,'c'  from dual union all
select 5,'c'  from dual;


SQL

--■■■添字の0番に集める方法■■■
select ID,substr(concatVal,2) as concatVal
  from groupConcatDis
 model return updated rows
 partition by(ID)
 dimension by(dense_rank() over(partition by ID order by Val) as soeji,
              Row_Number() over(partition by ID,Val order by 1) as rn)
 measures(Val,cast(null as varchar2(20)) as concatVal)
 rules iterate(100) UNTIL (presentV(Val[ITERATION_NUMBER+2,1],1,0) = 0)
 (concatVal[0,0] = concatVal[0,0] || ',' || Val[ITERATION_NUMBER+1,1])
order by ID;

--■■■添字の1番に集める方法■■■
select ID,substr(concatVal,2) as concatVal
  from groupConcatDis
 model return updated rows
 partition by(ID)
 dimension by(dense_rank() over(partition by ID order by Val) as soeji,
              Row_Number() over(partition by ID,Val order by 1) as rn)
 measures(Val,cast(null as varchar2(20)) as concatVal)
 rules iterate(100) UNTIL (presentV(Val[ITERATION_NUMBER+2,1],1,0) = 0)
 (concatVal[1,1] = concatVal[1,1] || ',' || Val[ITERATION_NUMBER+1,1])
order by ID;


解説

dense_rank関数とRow_Number関数でdimensionを設定しています。

model句22 MySQLのgroup_concatもどき
10-291 累計(重複値あり)を求めるwmsys.wm_concatもどき

同時に別の集合関数、たとえば、count(*)も求めたいのであれば、
rules句に、count(*)[any,any]を追加すればいいです。