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

▲ ページTOPに戻る

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;
出力結果
 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指定に対応する黄緑線と青線をイメージしてます。

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;

▲ ページTOPに戻る

Left Curve
図でイメージする
Oracle DatabaseのSQL全集
Right Curve