SQL「はぁ？何やこのようわからんクエリは...」
SELECT user_id, MAX(login_date), device
FROM login_history
GROUP BY user_id
;
SQL「多分、[ユーザーごとに、最後にログインしたときのデバイスを知りたい]んやろうけど...」
SQL「さてはGROUP BYのこと分かってへんな」
SQL「GROUP BYしたときにSELECTできるんは、3種類だけなんや」
SELECT COUNT(*), MAX(login_date)
FROM login_history
GROUP BY user_id
;
SQL「まずは一番カンタンなやつやな。集計関数や。」
SQL「カウントやったらグループに集まったレコードの数を数える。MAXやったら最大値を求める。」
SQL「なんもおかしいことあらへんな。」
SQL「次や。」
SELECT user_id, COUNT(*)
FROM login_history
GROUP BY user_id
;
SQL「GROUP BYで使ったカラムはSELECTできるで」
SQL「だって、グループの中で値は1つしかありえへんからな。」
SQL「ワシは天才やから、こんなんお茶の子さいさいやで。」
SQL「次はちょっと変なやつやけど覚えといてもええ。」
SELECT "定数", COUNT(*)
FROM login_history
GROUP BY user_id
;
SQL「定数も、別にグループ関係なしに値は一定やからな。」
SQL「SELECTされたら返したるわ。難しないしな。」
SQL「もうわかったと思うけど...」
SQL「これ以外のカラムはそのままSELECTすなよ！」
SELECT user_id, MAX(login_date), device
FROM login_history
GROUP BY user_id
;
SQL「こんなん書かれてもやな、」
SQL「例えばuser_idが1のグループになるdeviceは」
SQL「"PC","iOS","iOS","PC","PC"...」
SQL「っていっぱいあるんや」
SQL「deviceくださいって言われても、何欲しいんかわからんわ。」
SQL「左隣りにMAX(login_date)って書いてるけど、」
SQL「これSELECTやからな。絞り込み条件ちゃうからな。」
SQL「カラムはGROUP BYで一意に決まってる奴か、集計関数で集計した奴しかSELECTできひんねや。」
ほなどないすんねん
SQL「[ユーザーごとに、最後にログインしたときのデバイスを知りたい]んやったら、」
SQL「2通りの方法があるで」
SQL「どっちもちょっとめんどくさいけどな。」
SQL「1つめいくで」
サブクエリを使うパターン
SELECT
main.user_id, main.device
FROM
login_history AS main
INNER JOIN
(SELECT
user_id, MAX(login_date) first_date
FROM
login_history
GROUP BY user_id) AS sub ON main.user_id = sub.user_id
AND main.login_date = sub.first_date
;
SQL「サブクエリで[あるユーザーの最新ログイン日]を引っ張ってきて、」
SQL「それをもとのテーブルにINNER JOINしたら、」
SQL「ユーザー別の最新ログイン日のレコードだけが残るやろ?」
SQL「あとはそのレコードのカラムをSELECTするだけや」
window関数を使うパターン
SELECT
user_id, device
FROM
(
SELECT
user_id,
device,
RANK() OVER (
PARTITION BY user_id
ORDER BY
login_date DESC
) as date_num
FROM
login_history
) as sub
WHERE sub.date_num = 1;
SQL「window関数っちゅうのを使ったら、」
SQL「こんなふうに、結合なしで書けるんや」
SQL「一旦全部のレコードに対して、」
SQL「ユーザーごとにログイン日のランキングを付けて」
SQL「そのランキングで1位のレコードを取ってくるわけやな」
SQL「こっちやと結合せんでええから」
SQL「状況によるけど、はよ結果返せるで」
SQL「結合は気張らなあかんからな...」
SQL「特にサブクエリの結合は」
SQL「インデックスくんがおらんからキツいんや...」
SQL「ちなみに、mysqlくんは8.0からサポートしたらしいで」
SQL「古いmysqlくんやと、」
SQL「「window関数ってなんですか?」ってとぼけよるから」
SQL「気ぃつけや」
追記：window関数を使うパターン2
SELECT DISTINCT
user_id,
MAX(login_date) OVER(PARTITION BY user_id),
LAST_VALUE(device) OVER(PARTITION BY user_id ORDER BY login_date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM login_history
;
SQL「@kaki-xxxさんにコメントで教えてもらったやで」
SQL「@kaki-xxxさんありがとうな」（ありがとうございます）
SQL「これは、クエリ1本でほしいデータを取ってこれるスマートなパターンやな。」
SQL「(全然思いつかんかったで...)」
SQL「ORDER BY login_date RANGE BETWEENでlogin_dateを基準にdeviceを並び替えて...
SQL「一番新しいログイン日のdeviceをLAST_VALUEで取ってこれるな。」
SQL「スマートや。デキる大人のクエリって感じや。」
SQL「RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGってのが」
SQL「もしかするとパフォーマンス悪化につながるかもしれんから、」
SQL「パフォーマンスを気にするときはRANK()の方がいいこともあるかもしれん。」
追記：GROUP_CONCATを使うパターン
SELECT user_id,
MAX(login_date),
SUBSTRING_INDEX(GROUP_CONCAT(device ORDER BY login_date DESC SEPARATOR '###'), '###', 1)
FROM login_history
GROUP BY user_id
;
SQL「これは、@reg1st20220303さんにコメントで教えてもらったで」
SQL「@reg1st20220303さんありがとうな」（ありがとうございます）
SQL「これも、サブクエリを使わんとクエリ1本で取ってこれるスマートな方法や」
SQL「window関数を使うパターン2で教えてもらっとのとおんなじように、」
SQL「deviceをlogin_date基準で並び替えて文字列として結合させて、」
SQL「SUBSTRING_INDEXで最初の文字列(=最新のログインデバイス)を取ってきとるな。」
SQL「かっこええクエリや。」
SQL「サブクエリで愚直に書いてる例が恥ずかしく見えるで...。」
SQL「ただ、GROUP_CONCATは文字数制限があることに注意やで」
SQL「このクエリやったら最初の文字列を取得してるから文字が切れても問題ないけど」
SQL「最後を取ってくるみたいな書き方してたら上手いこといかんから」
SQL「GROUP_CONCAT使うときは文字数が溢れんかは、」
SQL「これに限らず気にしといたほうがええで。」
SQL「あと...」
SQL「GROUP_CONCATはパフォーマンス面でもボトルネックになったりするから」
SQL「大規模データ扱うときは、更に慎重にな。」
「僕はGROUP BYで指定してないカラムをSELECTできますけど」やと?
SQL「mysqlくんの優しさでエラーにしてないだけやのに...」
SQL「この、たわけ！」
SQL「mysqlくんはな...」
SQL「基本的に、sql_mode="ONLY_FULL_GROUP_BY"で、」
SQL「GROUP BYで指定してないカラムを直接SELECTできひんようにしてるんやが」
SQL「これを無効化することで、当たり障りない値を返してくれるようになるんや」
SQL「mysqlくんに感謝せえ！」
SQL「dockerコンテナで仮想mysqlくんを使ってるときは」
SQL「本物のmysqlくんと仮想mysqlくんで」
SQL「優しさが違う場合があるから、ちゃんと顔色伺っとくんやで」
結論
(特に初学者の方は)GROUP BYを使用するときにSELECTできるのは以下のみであると思っておいた方が安全です。
- GROUP BY句で指定したカラム
- 集計関数(を通したカラム)
- 定数
※ 当然CASE式や各種関数は使えますが、SELECTで式・関数を書ける人はもうこのあたり十分理解があると思いますので割愛します。
また、mysqlではsql_modeによっては直接カラムをSELECTしても結果を返すことが可能ですが、どの値を返すかは未定義であるため結果が保証されません。