はじめに
本稿では、Oracleの分析関数の中で、Ratio_to_report
関数、median
関数、First
指定およびLast
指定の分析関数、wmsys.wm_concat
関数と同じ結果を取得する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. 総合計に対する割合を求める
最初は、総合計に対する割合を求めるSQLについてです。まずは、テーブルのデータと、出力結果を考えます。
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の総合計に対する、その行のValの割合の百分率を、小数点第3位以下を切り捨てた値で求めます。たとえば、IDがFFの行のValの総合計は、10+20+40+80
で150ですので、各行のValの総合計に対する割合は、10/150*100
で6.66、20/150*100
で13.33、40/150*100
で26.66、80/150*100
で53.33となります。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。
select ID,Seq,Val, trunc(100 * Ratio_to_report(Val) over(partition by ID),2) as ratio from IDTable order by ID,Seq;
ID | Seq | Val | ratio |
AA | 1 | 100 | 8.69 |
AA | 2 | 100 | 8.69 |
AA | 3 | 500 | 43.47 |
AA | 4 | 200 | 17.39 |
AA | 5 | 200 | 17.39 |
AA | 6 | 50 | 4.34 |
BB | 1 | 200 | 8.69 |
BB | 2 | 400 | 17.39 |
BB | 3 | 800 | 34.78 |
BB | 4 | 900 | 39.13 |
CC | 1 | 100 | 6.25 |
CC | 2 | 800 | 50 |
CC | 3 | 700 | 43.75 |
DD | 1 | 400 | 100 |
EE | 1 | 50 | 100 |
FF | 1 | 10 | 6.66 |
FF | 3 | 20 | 13.33 |
FF | 5 | 40 | 26.66 |
FF | 6 | 80 | 53.33 |
IDごとのValの総合計さえ求めれば後は計算すればいい、と考えて、答えは下記となります。
select ID,Seq,Val, (select truncate(100*a.Val / sum(b.Val),2) from IDTable b where b.ID = a.ID) as ratio from IDTable a order by ID,Seq;
SQLのイメージは下記です。
なお、MySQLで小数点第3位以下を切り捨てる時はtruncate(数値,2)
を使い、Oracleで小数点第3位以下を切り捨てる時はtrunc(数値,2)
を使い、PostgreSQLで小数点第3位以下を切り捨てる時はtrunc(数値,2)
を使います。