3. 最大値の合計と最小値の合計
次に、最大値の合計と最小値の合計を求めるSQLについてです。まずは、テーブルのデータと、出力結果を考えます。
FirstLast
| 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と同じ結果を取得します。
分析関数でのFirst指定とLast指定
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式の使いどころです。このことをふまえて、答えは下記となります。
インラインビューを使うSQL
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のイメージは下記です。
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 |
