MySQL8 SQL Window関数とCTE表でデータクレンジングと名寄せを実現する
とあるデータのクレンジング、名寄せ作業をSQLだけで完結する上で、よくわからなくなってしまったので、メモします。
例えば、こんな定義とデータがあったとして。
定義
CREATE TABLE `artist_master` ( `Id` bigint(20) unsigned NOT NULL, `CreatedAt` datetime DEFAULT CURRENT_TIMESTAMP, `Valid` tinyint(4) NOT NULL DEFAULT '1', `Name` varchar(128) NOT NULL, `VideoCount` bigint(20) unsigned NOT NULL DEFAULT '0', `Ext` varchar(50) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;
データ
mysql> SELECT * FROM artist_master ; ----------------- +-----------------+---------------------+-------+----------------+------------+--------------------------------------+ | Id | CreatedAt | Valid | Name | VideoCount | Ext | +-----------------+---------------------+-------+----------------+------------+--------------------------------------+ | 38095832004676 | 2018-10-26 06:50:23 | 1 | Chris Alan Lee | 0 | NULL | | 333655795382791 | 2018-10-26 06:50:21 | 1 | Chris Alan Lee | 0 | NULL | | 354380886937504 | 2018-10-26 06:50:25 | 1 | Chris Alan Lee | 0 | NULL | | 391755868626561 | 2018-10-26 06:50:24 | 1 | Chris Alan Lee | 0 | NULL | | 504480813693733 | 2018-10-26 06:50:22 | 1 | Chris Alan Lee | 0 | NULL | | 577030850315619 | 2018-10-26 06:50:24 | 1 | Chris Alan Lee | 0 | https://www.imdb.com/name/nm1327414/ | | 629430777071848 | 2018-10-26 06:50:20 | 1 | Chris Alan Lee | 0 | NULL | | 643255722139392 | 2018-10-26 06:50:20 | 1 | Chris Alan Lee | 0 | NULL | +-----------------+---------------------+-------+----------------+------------+--------------------------------------+
やりたいこと
上記のデータを踏まえ、
577030850315619のValid値を1のまま保守して、その他のValid値を0にしたい場合、どうすればいいんだっけ？というものを考えてまして・・・いわゆる、データークレンジングとか名寄せの話になるのですが（汗）それをSQLのみで実現するにはどうしたらいいってお話です
残す（Valid=1のまま）にするルールを決める
このようにルールを決めました
- VideoCountが多いもの
- Extが入っているもの
- CreatedAtが古いもの
上から順番に優先順位が高く、同順の場合、次の候補で比較するとか、ちょっと自分で何いっているのかわからなくなりましたが、残すプライオリティをSQLで決めたかったんです（笑）
SQL
とりあえず、ナンバリングしよう
特に、規則がある数字が無いデータなので、まずはナンバリングしましょうってことで！ WINDOW関数を使ってみることにしました。
優先順位を決めるSQL
SELECT a.Id, a.CreatedAt, a.Valid, a.VideoCount, a.Ext, ROW_NUMBER() OVER (PARTITION BY a.Name ORDER BY a.VideoCount DESC, a.Ext DESC, a.CreatedAt ) AS Seq FROM artist_master a ;
結果
まあ、予想通りの結果かなと。。
+-----------------+---------------------+-------+------------+--------------------------------------+-----+ | Id | CreatedAt | Valid | VideoCount | Ext | Seq | +-----------------+---------------------+-------+------------+--------------------------------------+-----+ | 577030850315619 | 2018-10-26 06:50:24 | 1 | 0 | https://www.imdb.com/name/nm1327414/ | 1 | | 629430777071848 | 2018-10-26 06:50:20 | 1 | 0 | NULL | 2 | | 643255722139392 | 2018-10-26 06:50:20 | 1 | 0 | NULL | 3 | | 333655795382791 | 2018-10-26 06:50:21 | 1 | 0 | NULL | 4 | | 504480813693733 | 2018-10-26 06:50:22 | 1 | 0 | NULL | 5 | | 38095832004676 | 2018-10-26 06:50:23 | 1 | 0 | NULL | 6 | | 391755868626561 | 2018-10-26 06:50:24 | 1 | 0 | NULL | 7 | | 354380886937504 | 2018-10-26 06:50:25 | 1 | 0 | NULL | 8 | +-----------------+---------------------+-------+------------+--------------------------------------+-----+
最初の一行だけValidを1のままにして、その他のレコードのValidは、0にする
あぁ、これ、中間テーブルも使わないとダメかなと思って作成したSQLがこちらです。
WITH tmp_artist AS ( SELECT a.Id, a.CreatedAt, a.Valid, a.VideoCount, a.Ext, ROW_NUMBER() OVER (PARTITION BY a.Name ORDER BY a.VideoCount DESC, a.Ext DESC, a.CreatedAt ) AS Seq FROM artist_master a ) UPDATE artist_master a INNER JOIN tmp_artist b ON a.Id = b.Id SET a.Valid = 0 WHERE b.Seq > 1 ;
先ほどの、WINDOW関数で残すSeqは1になっているはずなので、更新かけるのは、Seq != 1のデータです。ここは、CTEとUPDATE JOINをうまい感じで使い（どこもうまい感じではないか。。） 上記のようなSQLで更新をかけてみました。
結果
うん、いい感じ！！！
mysql> SELECT * FROM artist_master ; +-----------------+---------------------+-------+----------------+------------+--------------------------------------+ | Id | CreatedAt | Valid | Name | VideoCount | Ext | +-----------------+---------------------+-------+----------------+------------+--------------------------------------+ | 38095832004676 | 2018-10-26 06:50:23 | 0 | Chris Alan Lee | 0 | NULL | | 333655795382791 | 2018-10-26 06:50:21 | 0 | Chris Alan Lee | 0 | NULL | | 354380886937504 | 2018-10-26 06:50:25 | 0 | Chris Alan Lee | 0 | NULL | | 391755868626561 | 2018-10-26 06:50:24 | 0 | Chris Alan Lee | 0 | NULL | | 504480813693733 | 2018-10-26 06:50:22 | 0 | Chris Alan Lee | 0 | NULL | | 577030850315619 | 2018-10-26 06:50:24 | 1 | Chris Alan Lee | 0 | https://www.imdb.com/name/nm1327414/ | | 629430777071848 | 2018-10-26 06:50:20 | 0 | Chris Alan Lee | 0 | NULL | | 643255722139392 | 2018-10-26 06:50:20 | 0 | Chris Alan Lee | 0 | NULL | +-----------------+---------------------+-------+----------------+------------+--------------------------------------+
うまくいきました！！ おそらく、こんなところで悩んでいるの自分だけかもしれませんが！（他の方はプログラムかけるし。。）
あくまで、SQLだけで解決する手法って意味で書きました。
追記：
全然関係ない話ですが、クレンジングに引っかかった、広告が笑いました。
