新しいSQL語彙を知ったので書きます!
・OVER句
・ROW_NUMBER()
まずはOVER句!
区間ごとに分割したり、並び替えたりできるやーつ
GROUP BYと何が違うんかな?
(((((( や っ て み よ う )))))
こんなテーブル(niku29)があったとして
id |
class_id |
name |
1
|
1
|
カルビ |
1
|
2
|
ホルモン |
2
|
1
|
せせり |
3
|
1
|
豚バラ |
※idとclass_idのテーブル用意して結合するとかだと思うんですがここでは触れないでくださいw
牛(id=1)、鳥(id=2)、豚(id=3)の各種類の数を知りたい場合、
GROUP BYを使うと
SELECT
id,count(class_id)
FROM
niku29
GROUP BY id;
こんな感じ。すなお。
OVER句を使うと、
SELECT
id,count(class_id) OVER (PARTITION BY id)
FROM
niku29;
こんな感じ?
しかし、これだとid=1について2行同じレコードでてきちゃいます。
そこで、ROW_NUMBER()を使い、idごとに行数振り、
1行目だけ取ってくるやり方で見やすくします。
とりあえず、
SELECT
row_number() OVER (PARTITION BY id) AS row_num,
id,
count(class_id) OVER (PARTITION BY id)
FROM
niku29;
で行を表示してみる。
row_num |
id |
count |
1
|
1
|
2
|
2
|
1
|
2
|
1
|
2
|
1
|
1
|
3
|
1
|
イイカンジ、もうちょい。
んで、1行目だけ取ってきて
SELECT
row_number() OVER (PARTITION BY id) AS row_num,
id,
count(class_id) OVER (PARTITION BY id)
FROM
niku29
WHERE row_num = 1;
ERROR: 列"row_num"は存在しません
え、あるやん。
そこで、
SELECT
row_number() OVER (PARTITION BY id) AS row_num,
id,
count(class_id) OVER (PARTITION BY id)
FROM
niku29
WHERE row_number() OVER (PARTITION BY id) = 1;
ERROR: WHERE句ではウィンドウ関数を使用できません
使えないらしい・・・。
ウィンドウ関数 → row_number()、rank()とか….
すんごいネストするけどこれならできる!
SELECT
id,count
FROM
(SELECT
row_number() OVER (PARTITION BY id) AS row_num,
id,
count(class_id) OVER (PARTITION BY id) AS count
FROM
niku29) niku
WHERE row_num = 1;
うーん、しかし例が悪かった。
もっとOVER,ROW_NUMBER()が役立つ時がきっとある!
まとめ
・WHERE句にWindow関数(ROW_NUMBERとか)は使えない。