『Window関数』で『select＆集計』を楽にする

MySQL8からWindow関数が使えるようになったので、その基本的な使い方と利用用途を整理してみました。
只、全て整理しても書くの大変で内容もボヤケてしまうので、これは使えるかも？という観点で少しずつ範囲を広げてみようと思います。

Window関数とは？

PostgresqやOracleでは前から備わっていたようですが、ザクっと「selectするときに集計結果も取得できる」 ものと一先ず理解しています。
逆に言えば、selectと集計を分けて連結すれば求めることはできますが、何といっても記述が圧倒的に短く「楽ちん」できるところがメリットだと思います。

サンプルテーブルイメージ

SALES_NO	PRODUCT_ID	QUANTITY	SALES_DATE
1	P000005	59	2020/09/17
2	P000007	12	2020/08/23
3	P000006	43	2020/06/02
4	P000004	97	2020/05/31
5	P000001	29	2020/07/05

select連番（rownum）を求める

MySQL5では、Oracleのようなselect結果の連番（rownum）が面倒で・・・
set @rownum=0;
select (@rownum := @rownum + 1) as row_num　・・・
と変数をインクリメントする必要がありましたが、Window関数を使えばこれを簡単に求められるようになります。

select
row_number() over() as rownum
, PRODUCT_ID, QUANTITY
from t_sales
where SALES_DATE between ‘2020/04/01’ and ‘2020/04/10’
order by SALES_DATE, PRODUCT_ID

『over() 』と『row_number() 』について

「over()」は、select結果に対する「束ねる範囲と束ね順序」を記述し、「row_number()」は、それに対する「振る舞う処理」を表していると理解しています。
「over()」には、パラメータを指定できますが、この例では指定していないので全選択範囲が対象となっており、結果「rownum」と同じような効果となっています。

『束ね範囲 』ごとに連番を振る

次の例では、少し深堀して
「束ねる範囲（over partition byに ）」⇒「SALES_DATE と PRODUCT_ID 」
「束ね順序（over order byに） 」⇒「 QUANTITY」の
「振る舞う処理」⇒ 「row_number()」 を求めてみます。

select
row_number()
over(partition by s.SALES_DATE, s.PRODUCT_ID order by s.QUANTITY ) as ItemOrder
,s.SALES_NO, s.PRODUCT_ID, s.QUANTITY, s.SALES_DATE
from t_sales s
where SALES_DATE between ‘2020/04/01’ and ‘2020/04/10’
order by s.SALES_DATE, s.PRODUCT_ID, ItemOrde

上記結果の通り「SALES_DATEとPRODUCT_ID」毎に「ItemOrder」が連番で振られ「 QUANTITY」で「束ね順序」毎にソートされています。
売上日、プロダクトごとに個数でソートしたりブレーク連番を振りたい場合に利用できます。

『束ね範囲』ごとに集計する

Window関数の最大のメリットは 「束ね範囲を集計」できるというところもあるので、更にこのＳＱＬに「束ね範囲」に対する「振る舞う集計処理」を追加します。

select
row_number() over(partition by s.SALES_DATE, s.PRODUCT_ID order by s.QUANTITY, s.PRODUCT_ID ) as ItemOrder
, s.SALES_NO, s.PRODUCT_ID, s.QUANTITY, s.SALES_DATE
, sum(quantity) over(partition by s.SALES_DATE, s.PRODUCT_ID ) as totals
, round((quantity / sum(quantity)
over(partition by s.SALES_DATE, s.PRODUCT_ID ) * 100), 1) as rate
from
t_sales s
order by s.SALES_DATE, s.PRODUCT_ID , ItemOrder

上記の通り「totals」で「束ねた範囲」の集計を出力し「rate」で「束ね範囲」の「売上数量の割合」が出力できました。
※注：ここではrateは簡潔に求めているので丸め誤差があります。

「group by」と「select」を組合わせて求めるのと比較すると随分簡潔に求めることができ、selectした「束ね範囲」毎の「合計」や「率」などを求める時は、大いに楽になると思います。