[Mysql] グループ化した中で 最頻値・２番目に多いデータを取得したい

Question

mysqlバージョン: v5.7

やりたいこと

# テーブル作成
CREATE TABLE `trump` (
  `id` int(11) NOT NULL,
  `card_type` varchar(40) NOT NULL COMMENT 'カード種別',
  `created_at` datetime DEFAULT NULL COMMENT '作成日時'
);

# インサート
INSERT INTO `trump` (`id`, `card_type`, `created_at`) VALUES 
　　(1, "K", "2020-10-01 11:00:00"),
　　(1, "K", "2020-10-01 12:00:00"),
　　(1, "K", "2020-10-01 13:00:00"),
　　(1, "Q", "2020-10-01 13:00:00"),
　　(1, "Q", "2020-10-01 14:00:00"),
　　(2, "K", "2020-10-01 11:00:00"),
　　(2, "K", "2020-10-01 12:00:00"),
　　(2, "Q", "2020-10-01 11:00:00"),
　　(2, "Q", "2020-10-01 13:00:00"),
　　(3, "JOKER", "2020-10-01 11:00:00"),
　　(3, "JOKER", "2020-10-01 12:00:00");

# データ取得
select * from trump;
+----+-----------+---------------------+
| id | card_type | created_at          |
+----+-----------+---------------------+
|  1 | K         | 2020-10-01 11:00:00 |
|  1 | K         | 2020-10-01 12:00:00 |
|  1 | K         | 2020-10-01 13:00:00 |
|  1 | Q         | 2020-10-01 13:00:00 |
|  1 | Q         | 2020-10-01 14:00:00 |
|  2 | K         | 2020-10-01 11:00:00 |
|  2 | K         | 2020-10-01 12:00:00 |
|  2 | Q         | 2020-10-01 11:00:00 |
|  2 | Q         | 2020-10-01 13:00:00 |
|  3 | JOKER     | 2020-10-01 11:00:00 |
|  3 | JOKER     | 2020-10-01 12:00:00 |
+----+-----------+---------------------+

上記のようなテーブルがあるとします。
以下の条件に合ったデータを id でグループ化して mysql で取得したいと思っています。

■ 取得したい内容

1. id

2. 同一 id の中で 最も多い card_type 
　　【エイリアス】 card_type_1
　　
3. { 同一 id の中で 最も多い card_type } の中で 最新 の created_at 
　　【エイリアス】 card_type_1

4. 同一 id の中で 2番目 に多い card_type
　　【エイリアス】 card_type_2

5. { 同一 id の中で 2番目 に多い card_type } の中で 最新 の created_at
　　【エイリアス】 card_type_2

※ 同率一位の場合、 created_at で比較し、新しい方が 1位 とする。

■ 結果として欲しいレコード

結果としては以下のレコードが欲しいです。

id	card_type_1	created_at_1	card_type_2	created_at_2
1	K	2020-10-01 13:00:00	Q	2020-10-01 14:00:00
2	Q	2020-10-01 13:00:00	K	2020-10-01 12:00:00
3	JOKER	2020-10-01 12:00:00	null	null

いろいろ調べたり試してみたのですが、そもそもこれが実現できることなのかもわからず、困っています。
もし、一回で取得するのが難しければ、phpと処理を分けるつもりなのですが、
できればsqlで一回で取得したいです。

どのようなクエリを書けば良いのか、ご教示いただきたいです。よろしくお願いします。

Comments

[firegrape]

無茶苦茶いい問題ですね。
教科書の応用問題で出そう。
今、考えています。
たぶん、sqlで一発でいけますよ。
ちょっと今から予定があるので、
後で答え合わせしよう。

[kirby318]

ありがとうございます。とても心強いお言葉...！
私も引き続き考えます。お忙しい中ありがとうございます。

[sazi]

MySQLのバージョンは何ですか？

[Orlofsky]

テーブル定義やテーブル中のデータをCREATE TABLEとINSERTで https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正できた方が動作確認しやすいので適切なコメントが付き易いかと。
MySQL8.0ですと、Windows関数が使えます。https://hit.hateblo.jp/entry/MYSQL/8.0/WINDOW

[kirby318]

mysql のバージョンは、 `5.7` です。
記載しておらず申し訳ありません。追記いたしました。

[kirby318]

>Orlofsky様
ありがとうございます。
サンプルテーブル・レコードのクエリを追記させていただきました。

[Orlofsky]

わたしは修正してください、と書きました。
無断に情報がダブるのは良くないです。

[kirby318]

>Orlofsky様
大変失礼しました。おっしゃっている意味を考えて修正したのですが、認識合っていますでしょうか。。
違っていれば申し訳ありません。

Answer 1

MySQL5.7だと、Window関数もWithも使えないので、非常に冗長になります。

select id 
     , max(case when rank=1 then card_type end)   as card_type_1
     , max(case when rank=1 then created_at end)  as created_at_1
     , max(case when rank=2 then card_type end)   as card_type_2
     , max(case when rank=2 then created_at end)  as created_at_2
from (
  select *
       , greatest(
           main_rank
         , case when main_rank>=2 and sub_rank>=2 then 3 else sub_rank end
        ) Rank
  from (
    select  id, card_type, cnt, created_at
        , ( 
            select count(*) + 1
            from (
              select id, card_type, count(*) as cnt
              from trump
              group by id, card_type
            ) s
            where id = base.id and cnt > base.cnt
          ) as main_rank
        , ( 
            select count(*) + 1
            from (
              select id, card_type, count(*) as cnt, max(created_at) as created_at
              from trump
              group by id, card_type
            ) s
            where id = base.id and cnt = base.cnt and created_at > base.created_at
          ) as sub_rank
    from (
      select id, card_type, count(*) as cnt, max(created_at) as created_at
      from trump
      group by id, card_type
    ) base
  ) step1
) step2
where rank <= 2
group by id
order by id

MySQL8.0だと、Window関数が使えるので、記述もシンプルでパフォーマンスも向上します。

select id 
     , max(case when rank=1 then card_type end)   as card_type_1
     , max(case when rank=1 then created_at end)  as created_at_1
     , max(case when rank=2 then card_type end)   as card_type_2
     , max(case when rank=2 then created_at end)  as created_at_2
from (
  select *, rank() over(partition by id order by cnt desc, created_at desc) as rank
  from (
      select id, card_type, count(*) as cnt, max(created_at) as created_at
      from trump
      group by id, card_type
  ) step1
) step2
where rank <= 2
group by id
order by id

Comments

[kirby318]

8.0 を使用したことがなかったのですが、
Window関数は元の行はそのままで複数の行から計算した値を結果に含められるんですね。
この OVER (PARTITION BY id) の部分で指定した指定した式値で
FROMやWHEREによる結果のセットを分割されて、それ元に計算されるんですね。
記述量も全然違いますね。すごいです・・・！
8.0での勉強も、自宅でしてみたいと思います。
本当にありがとうございました。大変助かり、勉強になりました！！

[sazi]

１番目のSQLは同率２位に対応できていませんでしたので、修正しました。
尚、順位の幅を広げるとしても１桁まで(9位)までです。
適宜、rankの編集部分を変更して下さい。

[kirby318]

修正いただき、ありがとうございます。

>select *, main_rank * 10 + sub_rank as rank

こちらの部分を修正されたということですよね。
今試しに修正いただいたsqlを実行してみたのですが、全てnullで返ってきてしまいました...

確かに修正前のsqlだと、以下のような同率２位があった場合、
「created_at_2」で最新の作成日時（=2020-10-01 20:00:00）が返ってきてはいたのですが、
「card_type_2」が、「created_at_2」に紐づくカード種別ではなく、
もう一方（"Q"）の同率２位のカード種別になってしまっていました。

これが、今回の修正されたsqlで解消された、ということですよね？

+----+-----------+------------------------------+
| id | card_type | created_at |
+----+-----------+------------------------------+
| 2 | K | 2020-10-01 11:00:00 |
| 2 | K | 2020-10-01 14:00:00 |
| 2 | K | 2020-10-01 11:00:00 |
| 2 | Q | 2020-10-01 10:00:00 |
| 2 | Q | 2020-10-01 11:00:00 |
| 2 | J | 2020-10-01 09:00:00 |
| 2 | J | 2020-10-01 20:00:00 |
+----+-----------+------------------------------+

[sazi]

連絡を焦って、安易な修正になってました。手打ちで確認も取っていませんでしたし。
ちょっと見直してみます。
更に冗長になりそうですが。。。

[sazi]

修正しました。
rank 算出の所で工夫したので、あまり冗長にはならなくて済みました。
ただ、汎用的ではなく、あくまで上位２位まで限定ですが。

[kirby318]

select id
, max(case when rank=11 then card_type end) as card_type_1
, max(case when rank=11 then created_at end) as created_at_1
, max(case when rank=21 then card_type end) as card_type_2
, max(case when rank=21 then created_at end) as created_at_2
from (
select *, main_rank * 10 + sub_rank as rank
from (
select id, card_type, cnt, created_at
, (
select count(*) + 1
from (
select id, card_type, count(*) as cnt
from trump
group by id, card_type
) s
where id = base.id and cnt > base.cnt
) as main_rank
, (
select count(*) + 1
from (
select id, card_type, count(*) as cnt, max(created_at) as created_at
from trump
group by id, card_type
) s
where id = base.id and cnt = base.cnt and created_at > base.created_at
) as sub_rank
from (
select id, card_type, count(*) as cnt, max(created_at) as created_at
from trump
group by id, card_type
) base
) step1
) step2
where rank <= 21
group by id
order by id;

こうでしょうか？？

[kirby318]

すみません再度修正いただいたコメント確認せずに私もコメントしてしまいました・・・！

期待通りの結果が返ってきました！！再度みていただき本当にありがとうございます。

-----------------------------
greatest(
main_rank
, case when main_rank>=2 and sub_rank>=2 then 3 else sub_rank end
) Rank
-----------------------------

ここを修正されたんですね。
これなら sub_rank の中での 1位と2位が正しく算出されていますね。
本当にとても勉強になりました。
最初のやり方でも長くはなるけどできることも理解できました。

本当にありがとうございました！！

[sazi]

気付いて修正されているかな？とも思ったんですが、気になったのでコメントしました。
ふと気になって気付いたから良かったのですが、バグにならずに一安心しました。

[kirby318]

気づきましたが、リファクタリングというかさらに工夫して短く書くことが全然思いつかなかったので、とっても助かりましたし、勉強になりました。
軽くテストをして実装すすめて、最後テストする際に全パターン確認したいと思います。
本当に本当にありがとうございました。