目標
ジャンル毎に平均視聴数がトップの番組を取得したい、いわば、特定のグループ（ジャンル）毎に、上位のレコード（平均視聴数がトップの番組）を取得したい。
ORDER BY句とGROUP BY 句では限界があることに気付いたので、その学習メモをするもの。
結論
分析関数を使用する。
分析関数とは、集計関数と同様な動作を行毎に実行すること。GROUP BY 句のような同じカラム値をもつ全ての行で集計を実施する集計関数とは違い、集計作業の対象を制限することが可能。この集計対象を指定するのがOVERである。
ウィンドウ関数 OVER (PARTITION BY 列リスト ORDER BY ソート用リスト)
ウィンドウ関数
RANK関数
ランキングを算出。同順位が複数出た場合は、後続の順位が飛ぶ。
ROW_NUMBER関数
一意の連番を返す。順位がでないようにランキングを出すことができる。
集約関数
具体的にどんな結果が得られるのかは省略するが、全ての集約関数も、ウィンドウ関数として使うことが可能。
PRTITION BY
GROUP BY 句との違いを示した概念図は以下の通り。GROUP BY はグループでまとめた行を表示。PARTITON BYは、グループ単位で行を表示。
ORDER BY
どの列をどのような順序をつけるのか指定。SELECTの末尾で使うORDER BYと同じで、ASC/DESCで昇順・降順を指定することができる。
ウィンドウ関数はどこで使うのか
原則として、SELECT句のみで使える。WHERE句やGROUP BY句で使うことができない。
実行順序
よって、以下の構文でジャンル毎に平均視聴数がトップの番組を取得することができると思ったが、エラーが表示された。
SELECT ジャンル、番組タイトル、RANK() OVER(PARTITION BY ジャンル ORDER BY 平均視聴数) AS program_rank FROM テーブル名 WHERE program_rank = 1
SELECT pg.genre_id, pav.program_title, RANK() OVER(PARTITION BY pg.genre_id ORDER BY pav.program_avg_view DESC) AS program_rank
FROM programs_genres AS pg
LEFT JOIN program_avg_view AS pav
ON pg.program_id = pav.id
WHERE program_rank = 1;
エラー原因は、SQLの実行順序を考えていないためである。
SQLの実行順序は概ね以下の通り。
FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
WHERE program_rank= 1 でフィルタリングをしているが、ウィンドウ関数（RANK()など）はSELECTの段階で計算されるため、WHERE句でウィンドウ関数の結果にアクセスできない。
よって、共通テーブル（CTE）を作成し、RANK()の結果を一時的に保存し、それに対してWHERE句適用する必要がある。
ちなみに、私は共通テーブルの作成はエクセルでいうピポットテーブルを何個も作成するイメージを持っている。
ゴール
よって、以下の構文でジャンル毎に平均視聴数がトップの番組を取得することができた。
WITH rank_program AS (
SELECT pg.genre_id, pav.program_title, RANK() OVER(PARTITION BY pg.genre_id ORDER BY pav.program_avg_view DESC) AS program_rank
FROM programs_genres AS pg
LEFT JOIN program_avg_view AS pav
ON pg.program_id = pav.id
)
SELECT genre_id, program_title
FROM rank_program
WHERE program_rank = 1;
また、データベス設計に関する記事も作成しているため、見て頂けたら幸甚です。