Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

MySQLで分析関数を模倣1(前編)

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

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

ダウンロード SourceCode (2.6 KB)

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

目次

はじめに

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

対象読者

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

 本稿では、相関サブクエリを多用しますので、『相関サブクエリで行と行を比較する』を先に読んでおくと理解が進むと思います。

必要な環境

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

  • SQLServer
  • DB2

1. IDごとのValの合計を求める

 最初は、IDごとにValの合計を求める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ごとのValの合計を求めます。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。

分析関数を使ったSQL
select ID,Seq,Val,
sum(Val) over(partition by ID) as sumVal
  from IDTable
order by ID,Seq;
出力結果
ID Seq Val sumVal
AA 1 100 1150
AA 2 100 1150
AA 3 500 1150
AA 4 200 1150
AA 5 200 1150
AA 6 50 1150
BB 1 200 2300
BB 2 400 2300
BB 3 800 2300
BB 4 900 2300
CC 1 100 1600
CC 2 800 1600
CC 3 700 1600
DD 1 400 400
EE 1 50 50
FF 1 10 150
FF 3 20 150
FF 5 40 150
FF 6 80 150

 partition by IDという指定をして、IDごとのValの合計を求めていますので、答えは相関サブクエリを使った下記となります。

相関サブクエリを使う方法
select ID,Seq,Val,
(select sum(b.Val)
   from IDTable b
  where b.ID = a.ID) as sumVal
  from IDTable a
order by ID,Seq;

 相関サブクエリでwhere b.ID = a.IDを指定して、外側のselect文の結果と同じIDを条件として、sum関数でValの合計を求めてます。結果としてIDごとのValの合計が求まります。別解として、内部結合を使う方法もあります。

内部結合を使う方法
select a.ID,a.Seq,a.Val,b.sumVal
  from IDTable a,
       (select ID,
        sum(Val) as sumVal
          from IDTable
        group by ID) b
 where b.ID = a.ID
order by a.ID,a.Seq;

 インラインビューでIDごとのValの合計を求めておいて、IDの一致を結合条件として内部結合させています。

 SQLのイメージは下記となります。

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

 なお、内部結合を使う方法であれば、例えば、IDごとのValの最大値と行数も欲しい場合は、下記のように列を追加するだけでよいです。

select a.ID,a.Seq,a.Val,b.sumVal,b.maxVal,b.cnt
  from IDTable a,
       (select ID,
        sum(Val) as sumVal,
        max(Val) as maxVal,
        count(*) as cnt
          from IDTable
        group by ID) b
 where b.ID = a.ID
order by a.ID,a.Seq;

出力結果
ID Seq Val sumVal maxVal cnt
AA 1 100 1150 500 6
AA 2 100 1150 500 6
AA 3 500 1150 500 6
AA 4 200 1150 500 6
AA 5 200 1150 500 6
AA 6 50 1150 500 6
BB 1 200 2300 900 4
BB 2 400 2300 900 4
BB 3 800 2300 900 4
BB 4 900 2300 900 4
CC 1 100 1600 800 3
CC 2 800 1600 800 3
CC 3 700 1600 800 3
DD 1 400 400 400 1
EE 1 50 50 50 1
FF 1 10 150 80 4
FF 3 20 150 80 4
FF 5 40 150 80 4
FF 6 80 150 80 4

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

著者プロフィール

バックナンバー

連載:分析関数の衝撃

もっと読む

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