Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

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

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

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

ダウンロード SourceCode (3.5 KB)

 連載「分析関数の衝撃」では、Oracleの分析関数を主に扱いました。「分析関数の衝撃」の外伝となるこの連載では、 MySQLで、Oracleの分析関数と同じ結果を取得するSQLの考え方と、処理のイメージを解説します。今回は、Range指定およびRows指定のSum関数と同じ結果を取得するSQLを扱います。

目次

はじめに

 本稿では、Oracleの分析関数の中で、Range指定およびRows指定のSum関数と同じ結果を取得するSQLを扱います。

対象読者

  • MySQLやPostgreSQLで、分析関数と同じ結果を取得するSQLを書きたい方
  • OracleやDB2やSQL Serverの、分析関数の理解を深めたい方

必要な環境

 本稿で扱うSQLは、MySQL 5.1.28で動作確認しました。ソースコードはPostgreSQL 8.3とOracle 10.2.0.1.0でも動作確認しました。その他、次の環境でも応用が可能です。

  • SQL Server
  • DB2

1. Range指定のSum関数

 最初は、Range指定のSum関数と同じ結果を求める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が自分より1小さい行から2大きい行までのValの合計を求めます。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。

分析関数を使ったSQL
select ID,Seq,Val,
sum(Val) over(partition by ID order by Seq
              range between 1 preceding
                        and 2 following) as sumVal
  from IDTable
order by ID,Seq;
出力結果
ID Seq Val sumVal
AA 1 100 700 (100+100+500)
AA 2 100 900 (100+100+500+200)
AA 3 500 1000 (100+500+200+200)
AA 4 200 950 (500+200+200+50)
AA 5 200 450 (200+200+50)
AA 6 50 250 (200+50)
BB 1 200 1400 (200+400+800)
BB 2 400 2300 (200+400+800+900)
BB 3 800 2100 (400+800+900)
BB 4 900 1700 (800+900)
CC 1 100 1600 (100+800+700)
CC 2 800 1600 (100+800+700)
CC 3 700 1500 (800+700)
DD 1 400 400
EE 1 50 50
FF 1 10 30 (10+20)
FF 3 20 60 (20+40)
FF 5 40 120 (40+80)
FF 6 80 120

 相関サブクエリでの、Range指定の分析関数の代用は簡単で、下記が答えとなります。

相関サブクエリを使うSQL
select ID,Seq,Val,
(select sum(b.Val)
   from IDTable b
  where b.ID = a.ID
    and b.Seq between a.Seq-1 and a.Seq+2) as sumVal
  from IDTable a
order by ID,Seq;

 相関サブクエリのbetween述語で、「IDが同じであること」および「Seqが自分より1小さい値以上、かつ、自分より2大きい値以下であること」を条件とし、Sum関数でValの合計を求めています。SQLのイメージは下記です。

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

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

著者プロフィール

バックナンバー

連載:分析関数の衝撃

もっと読む

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