PostgreSQLの分析関数の衝撃1
（モードとメジアン）

PostgreSQLの基本的なwindow関数の使用例

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) a);

　これをwindow関数で書き換えるのですが、このような複雑なselect文を書き換える際にはselect文の評価順序を理解していることが重要です。select文は、

1. from句
2. where句
3. group by句
4. having句
5. select句
6. order by句

の順に評価されます。window関数が使用できるのは5番目のselect句と6番目のorder by句なので、window関数の結果をwhere句で使うにはインラインビューを使う必要があります。

　考え方としては、まず、Graduates（卒業生テーブル）に対する、以下のselect文を考えます。

Graduates（卒業生テーブル）

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の最大値を、window関数の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

　少し分かりにくいかもしれませんが、window関数のmax関数の引数に、集合関数のcount関数を使用してます。

　後は、window関数の結果を、where句で使うには、インラインビューを使う必要があることをふまえて、下記のSQL文ができあがります。

window関数で書き換えたSQL

select income,cnt
  from (select income,count(*) as cnt,
        max(count(*)) over() as maxCnt
        from Graduates
        group by income) a
where cnt = maxCnt;

出力結果

income	cnt
20,000	3
10,000	3

　SQLのイメージは、下記となります。group by incomeなのでincomeで赤線を引いてます。

SQLのイメージ