SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

分析関数の衝撃

MySQLで分析関数を模倣5(応用編)

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

  • X ポスト
  • このエントリーをはてなブックマークに追加

ダウンロード SourceCode (4.9 KB)

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

  • X ポスト
  • このエントリーをはてなブックマークに追加

はじめに

 本稿では、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のイメージ
SQLのイメージ

 なお、MySQLで小数点第3位以下を切り捨てる時はtruncate(数値,2)を使い、Oracleで小数点第3位以下を切り捨てる時はtrunc(数値,2)を使い、PostgreSQLで小数点第3位以下を切り捨てる時はtrunc(数値,2)を使います。

次のページ
2. メジアン(中央値)を求める

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
分析関数の衝撃連載記事一覧

もっと読む

この記事の著者

山岸 賢治(ヤマギシ ケンジ)

趣味が競技プログラミングなWebエンジニアで、OracleSQLパズルの運営者。AtCoderの最高レーティングは1204(水色)。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/3103 2009/03/19 14:00

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング