CodeZine(コードジン)

特集ページ一覧

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

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

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2008/12/18 14:00

ダウンロード SourceCode (3.5 KB)

目次

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のイメージ
SQLのイメージ

 補足ですが、between述語は、内部的にX between Y and Zを、Y <= X and X <= Zに書き換えていますので、Y > Zの場合は、between述語の値はTrueにならなくなります。


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

バックナンバー

連載:分析関数の衝撃

もっと読む

著者プロフィール

あなたにオススメ

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