超便利！！ MySQL 8 window関数でグループ連番をふる

MySQL 8で（やっと）実装された、window関数が超便利です！！ postgreSQLとほぼ同じことができます。

window関数とは

結果レコードを部分的に切り出した領域に集約関数をかけることができる、ものすごい便利な関数です。MySQL8.0で（ようやく）実装されました。単純なところでは、例えばWindow関数がないMySQLのバージョンでとある規則に乗っ取った連番を振りたいなんていう要求があった場合どうします？

その答えが、以前こちらに掲載したものになるのですが、これがwindow関数で行うとすっごく簡単にかけちゃいます。

hit.hateblo.jp

あ、これは、MySQL 8.0以前のバージョンでの連番手法ですね。

MySQL 8 での連番の振り方

では、MySQL8 + window関数で、どのようにグループ連番をつけるのか、やってみたいと思います今回試すテーブルは前回と同様です。

CREATE TABLE `login` (
  `UserId` bigint(20) NOT NULL,
  `loginAt` datetime NOT NULL,
  PRIMARY KEY (`UserId`,`loginAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;

条件も前回と同じでやってみます。

ユーザーIDは、1,2,3とあり、それぞれログインの間隔が異なるものとします。 loginAtはログインした日付がはいっており、useridと、loginAtのセットで、primaryキーだとします。つまり、同じユーザーidで複数、ログイン日付が入っていると想像してください

このテーブルに対して、useridでグループ化し、window関数でそれぞれに連番をふってみます

参考までに。。

MySQL 8.0 以外でのグループ連番付与SQL

SET @no:=0;
SET @userId:=null;

CREATE TABLE sum_login
SELECT
    *,
    IF(@userId != userId, @no:=1, @no:=@no+1) AS no,
    @userId:=userId AS grp_userid
FROM
  login
ORDER BY
  userId,loginAt
;

では、本題のMySQL 8での連番付与！！

MySQL 8.0 window関数を利用したグループ連番

SELECT
  *,
  ROW_NUMBER() OVER (PARTITION BY userId ORDER BY userid,loginAt) AS no
FROM
  login
;

え？これだけ？ってくらいシンプルですよね！！素敵！！！！

window関数window関数って連呼してしまいましたが、実際のところ、window関数って、

ここの部分のことを言います

ROW_NUMBER() OVER (PARTITION BY userId ORDER BY userid,loginAt)

SQLとしては、ちょっと変わった書き方なのですが、要領を掴めば、もうこれなしでいられなくなりますw

ROW_NUMBER()

行番号を振る関数ですが、基本的に、OVER PARTITIONとセットだと覚えてください。単体では利用できません。

他の関数

使ったことはないですが、以下の集計関数が利用できます（直訳ごめん）

CUME_DIST()  累積分布値
DENSE_RANK()    隙間のない、そのパーティション内の現在の行のランク
FIRST_VALUE()   ウィンドウ枠の1行目からの引数の値
LAG()   行内の引数の値は、パーティション内の現在の行を遅らせる
LAST_VALUE()    ウィンドウ枠の最後の行からの引数の値
LEAD()  パーティション内の現在の行を先頭にする行からの引数の値
NTH_VALUE() ウィンドウ枠のN番目の行からの引数の値
NTILE() そのパーティション内の現在の行のバケット番号。
PERCENT_RANK()  パーセントランク値
RANK()  ギャップを持つ、そのパーティション内の現在の行のランク
ROW_NUMBER()    パーティション内の現在の行の数

PARTITION BY

GROUP BYだと思ってください。GROUP BYがレコード全体の集約関数で、window関数は区間での集約関数ですね。

ORDER BY

ORDER BYなのですが、ここ大事です。区間内でソートしたデータに対しての集計を行うので、どうあるべきかの順番を頭の中でイメージすることが大切です

ちょっと駆け足で進めましたが、window関数を使いこなすことでSQLの可読性がすごく良くなります。ぜひ活用してみてください

f:id:hit10231023:20180309104332j:plain

Database JUNKY

MySQL,MariaDBを中心としたブログです