MySQLで分析関数を模倣5（応用編）

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

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

はじめに

　本稿では、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)を使います。