distinctオプション
重複を排除して集計
distinctオプションは、主にcount関数で使用されます。
distinctオプションを使うと、重複を排除して集計できます。
create table DistinctSample(ID,Val) as
select 111,2 from dual union all
select 111,2 from dual union all
select 111,7 from dual union all
select 222,3 from dual union all
select 222,4 from dual union all
select 222,6 from dual union all
select 333,8 from dual;
IDごとの、データ件数,重複を排除したValの数を求めてみます。
select ID,count(*) as cnt,
count(distinct Val) as distinctValCnt
from DistinctSample
group by ID
order by ID;
出力結果
ID cnt distinctValCnt
--- --- --------------
111 3 2
222 3 3
333 1 1
Keep指定
順位付けしてから、集計対象を限定
Keep指定は、count関数,max関数,min関数,sum関数,avg関数などで使用されます。
Keep指定を使うと、指定したソート条件で(dense_rankな順位で)順位付けしてから、先頭や最後といった形で、集計対象を限定することができます。
create table KeepSample(ID,SortKey,Val) as
select 111,2,10 from dual union all
select 111,2,20 from dual union all
select 111,3,40 from dual union all
select 111,7,30 from dual union all
select 222,4,90 from dual union all
select 222,6,60 from dual union all
select 222,6,70 from dual union all
select 333,5,90 from dual;
IDごとで、データ件数,SortKeyが最大なデータの件数,
Valの最大値,SortKeyが最大なデータのValの最大値,
Valの合計,SortKeyが最大なデータのValの合計を求めてみます。
select ID, count(*) as cnt1, count(*) Keep(Dense_Rank Last order by SortKey) as cnt2, max(Val) as maxVal1, max(Val) Keep(Dense_Rank Last order by SortKey) as maxVal2, sum(Val) as sumVal1, sum(Val) Keep(Dense_Rank Last order by SortKey) as sumVal2 from KeepSample group by ID order by ID;
select ID,
count(*) as cnt1,
count(*) Keep(Dense_Rank Last order by SortKey) as cnt2,
max(Val) as maxVal1,
max(Val) Keep(Dense_Rank Last order by SortKey) as maxVal2,
sum(Val) as sumVal1,
sum(Val) Keep(Dense_Rank Last order by SortKey) as sumVal2
from KeepSample
group by ID
order by ID;
出力結果 ID cnt1 cnt2 maxVal1 maxVal2 sumVal1 sumVal2 --- ---- ---- ------- ------- ------- ------- 111 4 1 40 30 100 30 222 3 2 90 70 220 130 333 1 1 90 90 90 90
出力結果
ID cnt1 cnt2 maxVal1 maxVal2 sumVal1 sumVal2
--- ---- ---- ------- ------- ------- -------
111 4 1 40 30 100 30
222 3 2 90 70 220 130
333 1 1 90 90 90 90
上記のSQLのイメージは下記になります。
group by句に対応する赤線をイメージしてから、Keep指定に対応する黄緑線と青線をイメージしてます。
ちなみに、上記のSQLと同じ結果を取得できるSQLは、下記となります。
-- 集約関数のKeep指定と同じ結果を取得できるSQL
select ID,
count(*) as cnt1,
count(case rn when 1 then 1 end) as cnt2,
max(Val) as maxVal1,
max(case rn when 1 then Val end) as maxVal2,
sum(Val) as sumVal1,
sum(case rn when 1 then Val end) as sumVal2
from (select ID,Val,
Dense_Rank() over(partition by ID
order by SortKey desc) as rn
from KeepSample)
group by ID
order by ID;