OVER句、ROW_NUMBER()

 

新しい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;

こんな感じ。すなお。

id	count
1	2
3	1
2	1

 

 

OVER句を使うと、

SELECT
id,count(class_id) OVER (PARTITION BY id)
FROM
niku29;

こんな感じ?

id	count
1	2
1	2
2	1
3	1

 

しかし、これだとid=1について２行同じレコードでてきちゃいます。

 

 

 

そこで、ROW_NUMBER()を使い、idごとに行数振り、

１行目だけ取ってくるやり方で見やすくします。

とりあえず、

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

イイカンジ、もうちょい。

 

 

んで、１行目だけ取ってきて
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;

id	count
1	2
2	1
3	1

 

 

うーん、しかし例が悪かった。

もっとOVER,ROW_NUMBER()が役立つ時がきっとある！

 

まとめ

・WHERE句にWindow関数(ROW_NUMBERとか)は使えない。