3. 最大値の合計と最小値の合計
次に、最大値の合計と最小値の合計を求めるSQLについてです。まずは、テーブルのデータと、出力結果を考えます。
ID | Seq | Val |
AA | 1 | 1 |
AA | 1 | 2 |
AA | 2 | 4 |
AA | 3 | 8 |
AA | 3 | 16 |
AA | 3 | 32 |
BB | 1 | 64 |
BB | 2 | 128 |
BB | 3 | 256 |
CC | 1 | 512 |
DD | 6 | 10 |
DD | 6 | 20 |
DD | 7 | 40 |
DD | 7 | 80 |
IDごとのSeqが最大値の行のValの合計と、IDごとのSeqが最小値の行のValの合計を求めます。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。
select ID,Seq,Val, sum(Val) Keep(Dense_Rank First order by Seq) over(partition by ID) as FirstSum, sum(Val) Keep(Dense_Rank Last order by Seq) over(partition by ID) as LastSum from FirstLast order by ID,Seq;
ID | Seq | Val | FirstSum | LastSum |
AA | 1 | 1 | 3 | 56 |
AA | 1 | 2 | 3 | 56 |
AA | 2 | 4 | 3 | 56 |
AA | 3 | 8 | 3 | 56 |
AA | 3 | 16 | 3 | 56 |
AA | 3 | 32 | 3 | 56 |
BB | 1 | 64 | 64 | 256 |
BB | 2 | 128 | 64 | 256 |
BB | 3 | 256 | 64 | 256 |
CC | 1 | 512 | 512 | 512 |
DD | 6 | 10 | 30 | 120 |
DD | 6 | 20 | 30 | 120 |
DD | 7 | 40 | 30 | 120 |
DD | 7 | 80 | 30 | 120 |
集計条件は、Seqが最大値であることと、Seqが最小値であることです。このように集計条件が2つ以上ある場合は、case
式の使いどころです。このことをふまえて、答えは下記となります。
select d.ID,d.Seq,d.Val,c.FirstSum,c.LastSum from (select a.ID, sum(case when a.Seq = b.minSeq then a.Val end) as FirstSum, sum(case when a.Seq = b.maxSeq then a.Val end) as LastSum from FirstLast a, (select ID, min(Seq) as minSeq, max(Seq) as maxSeq from FirstLast group by ID) b where a.ID=b.ID group by a.ID) c,FirstLast d where c.ID=d.ID order by d.ID,d.Seq,d.Val;
まずは、最も内側にあるクエリの実行結果について考えてみましょう。
select ID, min(Seq) as minSeq, max(Seq) as maxSeq from FirstLast group by ID;
ID | minSeq | maxSeq |
AA | 1 | 3 |
BB | 1 | 3 |
CC | 1 | 1 |
DD | 6 | 7 |
IDでグループ化して、IDごとのSeqの最大値と最小値を求めています。後に、case
式で使うことになります。
続きまして、次に内側にあるクエリの実行結果について考えてみましょう。
select a.ID, sum(case when a.Seq = b.minSeq then a.Val end) as FirstSum, sum(case when a.Seq = b.maxSeq then a.Val end) as LastSum from FirstLast a, (select ID, min(Seq) as minSeq, max(Seq) as maxSeq from FirstLast group by ID) b where a.ID=b.ID group by a.ID;
ID | FirstSum | LastSum |
AA | 3 | 56 |
BB | 64 | 256 |
CC | 512 | 512 |
DD | 30 | 120 |
表別名がaの表と、表別名がbの表を、IDが等しいことを条件として内部結合しています。表別名がbの表で、IDでグループ化して、IDごとのSeqの最大値と最小値を求めておいて、sum
関数の引数となるcase
式で使用し、集計条件として使っています。
後は、FirstLastテーブルとIDが等しいことを条件として内部結合して、欲しい出力結果としています。
SQLのイメージは下記です。
後は、FirstLastテーブルとIDが等しいことを条件として内部結合して、欲しい出力結果としています。
余談ですが、Oracleでは下記のような、集合関数でのFirst指定とLast指定というのも存在します。こちらのほうが使用頻度は高いでしょう。
First
指定とLast
指定select ID, sum(Val) Keep(Dense_Rank First order by Seq) as FirstSum, sum(Val) Keep(Dense_Rank Last order by Seq) as LastSum from FirstLast group by ID order by ID;
ID | FirstSum | LastSum |
AA | 3 | 56 |
BB | 64 | 256 |
CC | 512 | 512 |
DD | 30 | 120 |