分析関数の衝撃

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

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

2008/12/18 14:00

ポスト

2. Rows指定のSum関数（preceding指定）

　次に、Rows指定のSum関数（preceding指定）と同じ結果を求めるSQLについてです。前問の、Sum関数でのRange指定の分析関数の代用は簡単でしたが、Rows指定の分析関数の代用は複雑になります。まずは、テーブルのデータと、出力結果を考えます。

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の昇順で、2行前から自行までのValの合計を求めます。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。

分析関数を使ったSQL

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

出力結果

ID	Seq	Val	sumVal
AA	1	100	100
AA	2	100	200 (100+100)
AA	3	500	700 (100+100+500)
AA	4	200	800 (100+500+200)
AA	5	200	900 (500+200+200)
AA	6	50	450 (200+200+50)
BB	1	200	200
BB	2	400	600 (200+400)
BB	3	800	1400 (200+400+800)
BB	4	900	2100 (400+800+900)
CC	1	100	100
CC	2	800	900 (100+800)
CC	3	700	1600 (100+800+700)
DD	1	400	400
EE	1	50	50
FF	1	10	10
FF	3	20	30 (10+20)
FF	5	40	70 (10+20+40)
FF	6	80	140 (20+40+80)

　Seqの昇順で2行前から自行までなので、自行までの行の数が1以上3以下ならSum関数の集計対象と考えて、答えは下記となります。

相関サブクエリを使うSQL

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

　SQLのイメージは下記です。

SQLのイメージ