【SQL】GROUP BYしたときにSELECTできるのは3種類だけやで

# SQL「はぁ？何やこのようわからんクエリは...」

```sql
SELECT user_id, MAX(login_date), device
FROM login_history
GROUP BY user_id
;
```

SQL「多分、[ユーザーごとに、最後にログインしたときのデバイスを知りたい]んやろうけど...」
SQL「さてはGROUP BYのこと分かってへんな」
SQL「GROUP BYしたときにSELECTできるんは、3種類だけなんや」

```sql
SELECT COUNT(*), MAX(login_date)
FROM login_history
GROUP BY user_id
;
```

SQL「まずは一番カンタンなやつやな。集計関数や。」
SQL「カウントやったらグループに集まったレコードの数を数える。MAXやったら最大値を求める。」
SQL「なんもおかしいことあらへんな。」
SQL「次や。」

```sql
SELECT user_id, COUNT(*)
FROM login_history
GROUP BY user_id
;
```

SQL「GROUP BYで使ったカラムはSELECTできるで」
SQL「だって、グループの中で値は1つしかありえへんからな。」
SQL「ワシは天才やから、こんなんお茶の子さいさいやで。」
SQL「次はちょっと変なやつやけど覚えといてもええ。」

```sql
SELECT "定数", COUNT(*)
FROM login_history
GROUP BY user_id
;
```

SQL「定数も、別にグループ関係なしに値は一定やからな。」
SQL「SELECTされたら返したるわ。難しないしな。」

SQL「もうわかったと思うけど...」
SQL「これ以外のカラムはそのままSELECTすなよ！」

```sql
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つめいくで」

### サブクエリを使うパターン

```sql
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関数を使うパターン

```sql
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

```sql
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を使うパターン

```sql
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できるのは以下のみであると思っておいた方が安全です。

1. GROUP BY句で指定したカラム
1. 集計関数(を通したカラム)
1. 定数

※ 当然CASE式や各種関数は使えますが、SELECTで式・関数を書ける人はもうこのあたり十分理解があると思いますので割愛します。

また、mysqlではsql_modeによっては直接カラムをSELECTしても結果を返すことが可能ですが、どの値を返すかは未定義であるため結果が保証されません。