【SQL書き方】GROUP BY したあとに、特定のカラム内でCOUNTする方法

Question

閲覧いただきありがとうございます。
SQL文の書き方について、詰まってしまったので、ご教授いただければ幸いです。

現状

• itemsテーブル

id	session_id	item_id	competition_id
1	1	2	1
2	1	3	1
2	1	2	1
2	1	2	1
2	1	5	2
3	1	7	2
4	1	4	2
5	1	5	2

実現したいこと

competition_idでグルーピングしてから、
item_id内で同じ数字をカウントしていき、最も多かった数字とその個数を抽出。

例えば、
competition_idが1の場合は、item_idは2が最多で、その個数は3
competition_idが2の場合は、item_idは5が最多で、その個数は2
competition_idが3の場合は、・・・
competition_idが4の場合は、・・・

試したこと

SELECT item_id,competition_id FROM items GROUP BY competition_id;

結果
2|1
7|2
など、competition_idで場合分けされていることがわかった。

SELECT temp.item_id FROM (SELECT item_id,count(*) cnt2 FROM items GROUP BY item_id) temp WHERE temp.cnt2 = (SELECT max(cnt) FROM (SELECT competition_id,count(*) cnt FROM items GROUP BY item_id ) num );

結果
28
62
item_idごとの合計の最大値をとったitem_idは28と62ということがわかったが、competition_idでグループ分けしてからの方法がわからなかった。

環境

macOS BigSur
ruby 2.7.0
Rails 6.1.1
sqlite

Answer 1

こんな感じでいかがでしょうか。

sqlite> create table items (id int, session_id int, item_id int, competition_id int);
sqlite> insert into items values (1, 1, 2, 1), (2, 1, 3, 1), (2, 1, 2, 1), (2, 1, 2, 1), (2, 1, 5, 2), (3, 1, 7, 2), (4, 1, 4, 2), (5, 1, 5, 2);
sqlite> .mode column
sqlite> select * from items;
id          session_id  item_id     competition_id
----------  ----------  ----------  --------------
1           1           2           1             
2           1           3           1             
2           1           2           1             
2           1           2           1             
2           1           5           2             
3           1           7           2             
4           1           4           2             
5           1           5           2             
sqlite> select competition_id, item_id, count(*) as count from items group by competition_id, item_id;
competition_id  item_id     count     
--------------  ----------  ----------
1               2           3         
1               3           1         
2               4           1         
2               5           2         
2               7           1         
sqlite> select *, rank() over (partition by competition_id order by count desc) as rank from (select competition_id, item_id, count(*) as count from items group by competition_id, item_id) as t;
competition_id  item_id     count       rank      
--------------  ----------  ----------  ----------
1               2           3           1         
1               3           1           2         
2               5           2           1         
2               4           1           2         
2               7           1           2         
sqlite> select * from (select *, rank() over (partition by competition_id order by count desc) as rank from (select competition_id, item_id, count(*) as count from items group by competition_id, item_id) as t) as tt where rank = 1;
competition_id  item_id     count       rank      
--------------  ----------  ----------  ----------
1               2           3           1         
2               5           2           1         

清書した SQL

select
  *
from (
  select
    *,
    rank() over (partition by competition_id order by count desc) as rank
  from (
    select
      competition_id,
      item_id,
      count(*) as count
    from items
    group by competition_id, item_id
  ) as t
) as tt
where rank = 1;

Comments

[katahik]

hoshi-takanori 様
ご回答ありがとうございました。
無事、欲しいデータを抽出することができました。

恥ずかしながら、分析関数というものを知りませんでした。
また、GROUP BYで複数のカラムを指定できることも知りませんでした。
勉強になりました。

ありがとうございました。

Answer 2

SQLiteのバージョンによるだろうけど、SQLite3版 分析関数(ウインドウ関数) コードサンプルが使えるかも？