はじめに
本稿では、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についてです。まずは、テーブルのデータと、出力結果を考えます。
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の総合計に対する、その行の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と同じ結果を取得します。
分析関数を使った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の総合計さえ求めれば後は計算すればいい、と考えて、答えは下記となります。
相関サブクエリを使うSQL
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のイメージは下記です。
SQLのイメージ
なお、MySQLで小数点第3位以下を切り捨てる時はtruncate(数値,2)を使い、Oracleで小数点第3位以下を切り捨てる時はtrunc(数値,2)を使い、PostgreSQLで小数点第3位以下を切り捨てる時はtrunc(数値,2)を使います。
