2. 最頻値(モード)を求める
次に最頻値(モード)を求めるSQLです。「HAVING句の力」では、以下の最頻値(モード)を求めるSQLが提示されています。
--最頻値を求めるSQL その2:極値関数の利用 SELECT income FROM Graduates GROUP BY income HAVING COUNT(*) >= ( SELECT MAX(cnt) FROM ( SELECT COUNT(*) AS cnt FROM Graduates GROUP BY income) );
これを分析関数で書き換えるのですが、このような複雑なselect
文を書き換える際にはselect
文の評価順序を理解していることが重要です。select
文は、
- from句
- where句
- group by句
- having句
- select句
- order by句
の順に評価されます。
分析関数が使用できるのは5番目のselect
句と6番目のorder by
句なので、分析関数の結果をwhere
句で使うにはインラインビューを使う必要があります。
まず、Graduates
(卒業生テーブル)に対する、以下のselect
文を考えます。
name | income |
サンプソン | 400,000 |
マイク | 30,000 |
ホワイト | 20,000 |
アーノルド | 20,000 |
スミス | 20,000 |
ロレンス | 15,000 |
ハドソン | 15,000 |
ケント | 10,000 |
ベッカー | 10,000 |
スコット | 10,000 |
select income, count(*) as cnt from Graduates group by income;
income cnt ------- --- 400,000 1 30,000 1 20,000 3 15,000 2 10,000 3
この結果を見ると、「cnt
が最大であること」を条件とすればいいと分かりますよね。cnt
の最大値を分析関数のmax
関数を使って求めたselect
文を考えます。
select income,count(*) as cnt, max(count(*)) over() as maxCnt from Graduates group by income;
income cnt maxCnt ------- --- ------ 400,000 1 3 30,000 1 3 20,000 3 3 15,000 2 3 10,000 3 3
少し分かりにくいかもしれませんが、分析関数のmax
関数の引数に集合関数のcount
関数を使用してます。
そして、分析関数の結果をwhere
句で使うためのインラインビューを考慮すると、以下のSQL文ができあがります。
select income,cnt from (select income, count(*) as cnt, max(count(*)) over() as maxCnt from Graduates group by income) where cnt = maxCnt;
income cnt ------ --- 20,000 3 10,000 3
3. 中央値(メジアン)を求める
今度は中央値(メジアン)を求めます。「HAVING句の力」で提示されている以下のSQLを、分析関数を使って書き換えてみます。
--メジアンを求めるSQL:自己非等値結合をHAVING句で使う SELECT AVG(DISTINCT income) FROM (SELECT T1.income FROM Graduates T1, Graduates T2 GROUP BY T1.income --S1の条件 HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END) >= COUNT(*) / 2 --S2の条件 AND SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END) >= COUNT(*) / 2 );
まずは、データの具体的な例を挙げて考えます。なお、income
の順位を持つRank
列も付与して考えます。
case1 case2 income Rank income Rank ------- ---- ------- ---- 10,000 1 10,000 1 10,000 2 10,000 2 10,000 3 10,000 3 15,000 4 15,000 4 15,000 5 15,000 5 20,000 6 20,000 7 20,000 8 30,000 9 400,000 10
- case1が、データ件数が偶数の場合で、中央値は、(15000+20000) / 2 = 17500
- case2が、データ件数が奇数の場合で、中央値は、10000
手続き型の言語であれば、
- 処理1 昇順にソート
- 処理2 データ件数を求める
- 処理3 データ件数が偶数なら、(データ件数 / 2)番目と(データ件数 / 2 +1)番目の、平均が中央値
- 処理4 データ件数が奇数なら、(データ件数 / 2 + 0.5)番目が中央値
といった処理を行うと思いますが、分析関数を使ったSQLでも似たような考え方を使います。
select avg(income) from (select income, Row_Number() over(order by income) as Rank, count(*) over() as DataCount from Graduates) where (mod(DataCount,2) = 0 and Rank in(DataCount/2, DataCount/2+1)) or (mod(DataCount,2) = 1 and Rank = Ceil(DataCount/2));
インラインビューの中で、Row_Number
関数で順位、count
関数でデータ件数を求めてます。そして、データ件数が偶数か奇数かでwhere
句の条件を分岐させてます。
最後に
分析関数で書き換えたSQLを3つ挙げました。同じテーブルに2回以上アクセスするSQLのほとんどは分析関数で書き換えることが可能です。興味を持った方はどんどん分析関数を使ってみてください。
partition by
、Rows
、Range
を使う分析関数は、分析関数の衝撃の中編と後編で扱います。
参考資料
- CodeZine 『HAVING句の力』
- OTN Japan『Oracle8i データウェアハウス』
- OTN Japan『分析ファンクション』
- US-OTN『Analytic Functions』
- @IT 『SQLクリニック(8) 極めよう! 分析関数によるSQL高速化計画』
RANK
関数とDENSE_RANK
関数)について、分かりやすく解説されてます。- @IT 『SQLクリニック(9) SQL分析関数をさらに深く追求してみよう』
SUM
関数とRATIO_TO_REPORT
関数)について、分かりやすく解説されてます。- @IT 『SQLクリニック(10) まだまだあるぞ! 分析関数の究極テクニック』
LAG
関数とLEAD
関数)について、分かりやすく解説されてます。- OracleSQLパズル『select文の評価順序』
select
文の評価順序の詳細を置いてます。- OracleSQLパズル『モード(最頻値)を取得』
- OracleSQLパズル『メジアン(中央値)を取得』
- OracleSQLパズル『最小の空き番号を取得その1』
- OracleSQLパズル『最小の空き番号を取得その2』