#はじめに
SQLで「あるグループごとに上位〇件ずつデータを取得して比較したい！」
という際にwindow関数を使うとサクッと抽出することが出来ます。
サンプルデータ
select * from book;
id | name | title | num
-------+--------------+--------------------------+--------
1 | 江戸川 乱歩 | 赤いカブトムシ | 20
2 | 江戸川 乱歩 | 宇宙怪人 | 10
～
id | name | title | num
-------+--------------+--------------------------+--------
11 | 芥川 竜之介 | アグニの神 | 40
12 | 芥川 竜之介 | 犬と笛 | 10
21 | 小川 未明 | 赤い蝋燭と人魚 | 100
22 | 小川 未明 | 赤土へくる子供たち | 90
numの大きいものから上位5件ずつ取得する
nameごとにnumの大きいものから上位5件ずつ取得します。
row_number()関数を使ってnumの降順に番号を付ける。
row_number()の代わりにrank()関数を使うとランキングの順位（同順位は同じ値）で取得できる。
select
*
from
(
select
name
,title
,num
,row_number() over (partition by name order by num desc) as rank
from
book
)
where
rank <= 5
- window関数
※ row_number()、rank()、avgなど表につけたい関数を指定する
⇒ 集約関数と異なり行自体はグループ化せずにカラムを付加するイメージ
※ over … ウィンドウ関数を使う宣言のようなもの（ウィンドウ関数の処理定義）
※ partition by … どの単位でまとめるか、グループ化する値の指定（上記例では作者(name)）
※ order by … ウィンドウ関数内の処理される行の並びを指定
＜取得結果＞
name | title | num | rank
------------+--------------------+-----+------
小川 未明 | 赤い蝋燭と人魚 | 100 | 1
小川 未明 | 赤土へくる子供たち | 90 | 2
小川 未明 | 新しい町 | 80 | 3
小川 未明 | 秋のお約束 | 70 | 4
小川 未明 | 朝の公園 | 60 | 5
芥川 竜之介 | 杜子春 | 100 | 1
芥川 竜之介 | 魔術 | 90 | 2
芥川 竜之介 | 蜘蛛の糸 | 80 | 3
～
件数の多いものから上位5件ずつ取得する
nameごとに件数の多いものから上位5件ずつ取得します。
同じ要領でcountを作り、countの降順に番号を振る。
select
*
from
(
select
name
,title
,count(*)
,row_number() over (partition by name order by count(*) desc) as rank
from
book
group by
name
,title
)
where
rank <= 5
＜取得結果＞
name | title | count | rank
-------------+--------------------+-------+------
江戸川 乱歩 | 怪奇四十面相 | 8 | 1
江戸川 乱歩 | おれは二十面相だ | 6 | 2
江戸川 乱歩 | 黄金豹 | 4 | 3
江戸川 乱歩 | 宇宙怪人 | 3 | 4
江戸川 乱歩 | 赤いカブトムシ | 2 | 5
小川 未明 | 秋のお約束 | 6 | 1
小川 未明 | 秋が きました | 5 | 2
小川 未明 | 明るき世界へ | 4 | 3
～
#おわりに
「あるグループごとに上位〇件ずつ取得する」SQLを、window関数を使わずに書くと
複雑になるので、知っておくと便利ではないかと思います。
また、これ以外にも関数が用意されていますので、簡易な集計・分析などする際は
使ってみては如何でしょうか。
（最近は多めにデータを取ってきてBIツールで可視化するケースの方が多いですが...）