分析関数の衝撃

MySQLで分析関数を模倣5（応用編）

MySQLで、Oracleの分析関数と同じ結果を取得する5

山岸賢治[著]

2009/03/19 14:00

ポスト

ダウンロード SourceCode (4.9 KB)

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;

出力結果