分析関数の衝撃

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

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

山岸賢治[著]

2008/12/18 14:00

ポスト

ダウンロード SourceCode (3.5 KB)

5. Rows指定のSum関数（Unbounded指定）

　最後は、Rows指定のSum関数（Unbounded指定）と同じ結果を求める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の昇順で、2行後以降のValの合計を求めます。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。

分析関数を使ったSQL

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

出力結果

ID	Seq	Val	sumVal
AA	1	100	950 (500+200+200+50)
AA	2	100	450 (200+200+50)
AA	3	500	250 (200+50)
AA	4	200	50
AA	5	200	null
AA	6	50	null
BB	1	200	1700 (800+900)
BB	2	400	900
BB	3	800	null
BB	4	900	null
CC	1	100	700
CC	2	800	null
CC	3	700	null
DD	1	400	null
EE	1	50	null
FF	1	10	120 (40+80)
FF	3	20	80
FF	5	40	null
FF	6	80	null

　前々問および前問と似た考え方を使って答えは、下記となります。

相関サブクエリを使うSQL1

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 a.Seq
                                                and b.Seq
                                 then 1 end) >= 2+1)) as sumVal
  from IDTable a
order by ID,Seq;

　同じIDで、Seqの昇順で、2行後以降のValの合計を求めますので、表別名がbの行のSeq >= 表別名がaの行のSeq、かつ、間にある行数（両端の表別名がbの行と表別名がaの行を含む）が3以上ならSum関数の集計対象と考えてSQLを作成しています。

　上記のSQLは、having句での条件指定が単純な条件で、かつ、group by句がないので、下記に書き換え可能です。

相関サブクエリを使うSQL2

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

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

SQLのイメージ

　前問と同様に必要条件を取り出してチューニングしてもいいし、3問目と同様に、検索case式をIF関数を使って変形したり、論理値を使って変形してもいいでしょう。

最後に

　今回は、Range指定およびRows指定のSum関数と同じ結果を取得するSQLを扱いました。次回の応用編では、Ratio_to_report関数,median関数,First指定およびLast指定の分析関数,wmsys.wm_concat関数と同じ結果を取得するSQLを扱います。

参考資料

OracleSQLパズル『移動平均を求める』
本稿の「2. Rows指定のSum関数（preceding指定）」の応用例を置いてます。
ONLamp.com Stephane Faroult 『Emulating Analytic （AKA Ranking） Functions with MySQL』 Stephane Faroultt著、2007年3月
本連載の原案となった記事です。
ONLamp.com Stephane Faroult 『Emulating Analytic （AKA Ranking） Functions with MySQL: Part 2』 Stephane Faroult著、2007年4月
本連載の原案となった記事です。