CodeZine(コードジン)

特集ページ一覧

MySQLで分析関数を模倣4(完結編)

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

  • LINEで送る
  • このエントリーをはてなブックマークに追加
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のイメージ
SQLのイメージ

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

最後に

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

参考資料

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


  • LINEで送る
  • このエントリーをはてなブックマークに追加

バックナンバー

連載:分析関数の衝撃

もっと読む

著者プロフィール

あなたにオススメ

All contents copyright © 2005-2021 Shoeisha Co., Ltd. All rights reserved. ver.1.5