分析関数の衝撃

MySQLで分析関数を模倣4（完結編）

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

山岸賢治[著]

2008/12/18 14:00

ポスト

ダウンロード SourceCode (3.5 KB)

3. Rows指定のSum関数（preceding指定とfollowing指定）

　次に、Rows指定のSum関数（preceding指定とfollowing指定）と同じ結果を求めるSQLについてです。まずは、テーブルのデータと、出力結果を考えます。

IDTable

ID	Seq	Val
AA	1	100
AA	2	100
AA	3	500
AA	4	200
AA	5	200
AA	6	50
BB	1	200
BB	2	400
BB	3	800
BB	4	900
CC	1	100
CC	2	800
CC	3	700
DD	1	400
EE	1	50
FF	1	10
FF	3	20
FF	5	40
FF	6	80

　同じIDで、Seqの昇順で、1行前から2行後までのValの合計を求めます。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。

分析関数を使ったSQL

select ID,Seq,Val,
sum(Val) over(partition by ID order by Seq
              rows between 1 preceding
                       and 2 following) as sumVal
  from IDTable
order by ID,Seq;

出力結果

ID	Seq	Val	sumVal
AA	1	100	700 (100+100+500)
AA	2	100	900 (100+100+500+200)
AA	3	500	1000 (100+500+200+200)
AA	4	200	950 (500+200+200+50)
AA	5	200	450 (200+200+50)
AA	6	50	250 (200+50)
BB	1	200	1400 (200+400+800)
BB	2	400	2300 (200+400+800+900)
BB	3	800	2100 (400+800+900)
BB	4	900	1700 (800+900)
CC	1	100	1600 (100+800+700)
CC	2	800	1600 (100+800+700)
CC	3	700	1500 (800+700)
DD	1	400	400
EE	1	50	50
FF	1	10	70 (10+20+40)
FF	3	20	150 (10+20+40+80)
FF	5	40	140 (20+40+80)
FF	6	80	120 (40+80)

　答えは下記となります。

相関サブクエリを使うSQL

select ID,Seq,Val,
(select sum(b.Val)
   from IDTable b
  where b.ID=a.ID
    and exists(select 1 from IDTable c
                where c.ID=a.ID
               having count(case when c.Seq between b.Seq and a.Seq
                                 then 1 end) <= 1+1
                  and count(case when c.Seq between a.Seq and b.Seq
                                 then 1 end) <= 2+1)) as sumVal
  from IDTable a
order by ID,Seq;

　前問で使った考え方を応用しつつ、数学の場合分けと呼ばれる手法を使います。同じIDで、Seqの昇順で、1行前から2行後までのValの合計を求めますので、

表別名がbの行のSeq <= 表別名がaの行のSeq の場合は、間にある行数（両端の表別名がbの行と表別名がaの行を含む）が2以下ならSum関数の集計対象

表別名がbの行のSeq >= 表別名がaの行のSeq の場合は、間にある行数（両端の表別名がbの行と表別名がaの行を含む）が3以下ならSum関数の集計対象

と考えてSQLを作成しています。

　SQLのロジックの検証パターンとしては、

表別名がbの行のSeq < 表別名がaの行のSeq の場合でbのSeqが最小
表別名がbの行のSeq < 表別名がaの行のSeq の場合でbのSeqが最大
表別名がbの行のSeq = 表別名がaの行のSeq の場合
表別名がbの行のSeq > 表別名がaの行のSeq の場合でbのSeqが最小
表別名がbの行のSeq > 表別名がaの行のSeq の場合でbのSeqが最大

で十分でしょう。SQLのイメージは下記です。

SQLのイメージ

　作成したSQLを少し変形してみましょう。MySQLでは、when句が1つしかない検索case式は、IF関数で変形することができますので、下記に変形してみます。

検索case式をIF関数を使って変形

select ID,Seq,Val,
(select sum(b.Val)
   from IDTable b
  where b.ID=a.ID
    and exists(select 1 from IDTable c
                where c.ID=a.ID
               having sum(if(c.Seq between b.Seq and a.Seq,1,0))
                      <= 1+1
                  and sum(if(c.Seq between a.Seq and b.Seq,1,0))
                      <= 2+1)) as sumVal
  from IDTable a
order by ID,Seq;

　さらに論理値を使って変形してみます。

論理値を使って変形

select ID,Seq,Val,
(select sum(b.Val)
   from IDTable b
  where b.ID=a.ID
    and exists(select 1 from IDTable c
                where c.ID=a.ID
               having sum(c.Seq between b.Seq and a.Seq)
                      <= 1+1
                  and sum(c.Seq between a.Seq and b.Seq)
                      <= 2+1)) as sumVal
  from IDTable a
order by ID,Seq;

　コンパクトなSQLに変形することができました。場合によっては、このような変形を行うのもよいでしょう。

次のページ
4. Rows指定のSum関数（following指定とfollowing指定）