CodeZine(コードジン)

特集ページ一覧

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

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

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

ダウンロード SourceCode (3.5 KB)

目次

4. Rows指定のSum関数(following指定とfollowing指定)

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

分析関数を使ったSQL
select ID,Seq,Val,
sum(Val) over(partition by ID order by Seq
              rows between 2 following
                       and 3 following) as sumVal
  from IDTable
order by ID,Seq;
出力結果
ID Seq Val sumVal
AA 1 100 700 (500+200)
AA 2 100 400 (200+200)
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)
                      between 2+1 and 3+1)) as sumVal
  from IDTable a
order by ID,Seq;

 同じIDで、Seqの昇順で、2行後から3行後までのValの合計を求めますので、「表別名がbの行のSeq >= 表別名がaの行のSeq」かつ「間にある行数(両端の表別名がbの行と表別名がaの行を含む)が3以上4以下」なら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) between 2+1 and 3+1) as sumVal
  from IDTable a
order by ID,Seq;

 さらに、必要条件を取り出してチューニングしてもいいでしょう。

相関サブクエリを使うSQL3
select ID,Seq,Val,
(select sum(b.Val)
   from IDTable b
  where b.ID=a.ID
    and a.Seq < b.Seq -- 必要条件
    and (select count(case when c.Seq between a.Seq
                                          and b.Seq
                           then 1 end)
           from IDTable c
          where c.ID=a.ID) between 2+1 and 3+1) as sumVal
  from IDTable a
order by ID,Seq;

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

SQLのイメージ
SQLのイメージ

 3問目と同様に、検索case式をIF関数を使って変形したり、論理値を使って変形したりしてもよいでしょう。


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

バックナンバー

連載:分析関数の衝撃

もっと読む

著者プロフィール

あなたにオススメ

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