【SQL】カウントして一番多かった値を、複数個の場合は複数個返したい

Question

SQLに不慣れで使いだしたばかりなので初歩的なものかと思いますが、よろしくお願いします。

前提・実現したいこと

下記のようなテーブル（AABB）があります。

AA	BB
a	1
a	2
a	3
a	1
a	2
b	1
b	3
b	2
b	3
b	4

これをSQLでどうにかして、

AA	BB	max
a	1	2
a	2	2
b	3	2

というような結果を出したいです。

つまり、AAごとにBBが何個あるのかカウントし、一番多かったBBの値とその個数を、複数個ある場合は複数個返したいです。

試したこと

select AA, max(a.count) as max 
from (
　select AA, BB, count(BB)as count 
　from AABB
　group by AA, BB 
) a 
group by a.AA order by AA;

上記のSQL文でMAXやCOUNTを使ってAAごとに何個ある、というものまでは出来たのですが、
GROUP BYで行っているため、AAごとに実行しようとすると複数個あった場合でも1つになってしまいます。

AA	max
a	2
b	3

また、BBの表示もできず…selectに入れてもBBごとにmaxが表示されるだけで、私でも違うと分かりました。

複数個ならWHERE句を使うというのも見かけましたが、どう使えばうまくいくのか分からず
エラーばかり出て迷走しております…

初歩的かと思いますが、教えてくださいませ。
よろしくお願いします。

Comments

[Orlofsky]

同じSQLでもデータベースやそのバージョンによって方言が大きいですから、どのデータベースを使うのかを質問のタグで示したり、バージョンも明記した方が適切なコメントが付き易いです。

[Orlofsky]

SQLの動作確認のために、テーブル定義はCREATE TABLEに、テーブル中のデータはINSERTに https://teratail.com/help/question-tips#questionTips3-7 の [コード] で修正されては？

[nkrmn_a]

ご指摘ありがとうございます。
hanaDBというデータベースを使っているのですが、タグに出て来ず、つけていませんでした。
あまり詳しくないので、何に近いのかなども分かっておらず…
質問文に追記しておきます。

[nkrmn_a]

申し訳ないですが、初心者中の初心者のため修正をどうやってすればいいのか分かりません…。
今後のために勉強しておきます。

[Orlofsky]

SAP HANA https://ja.wikipedia.org/wiki/SAP_HANA でしょうか？
ここのページの右下の [返信の必要なお問い合わせはこちら] からタグ追加を依頼してください。

[sazi]

単にデータベース名がhanaとかhanaDBとかじゃないんですよね？
hanaDBというDBMSが見つかりません。
そのDBの説明のURLを教えてください。

[sazi]

これかな？
(SAP HANA)
https://help.sap.com/viewer/7c78579ce9b14a669c1f3295b0d8ca16/Cloud/en-US/20ff532c751910148657c32fe3431a9f.html

[nkrmn_a]

Orlofskyさん、saziさん
そうです、こちらのSAP HANAを使用しております。
問い合わせもしてみます！ありがとうございます。

Answer 1

SAP HANAプラットフォーム向けSAP HANA SQLリファレンスガイドによるとwithも使えるみたいなので、以下の様にしてみました。
CTEを使用した方が高速である可能性があります。

with step1 as (
  select AA, BB, count(BB)as cnt 
  from AABB
  group by AA, BB 
) , step2 as (
  select AA, BB, max(cnt) as max_cnt
  from step1
  group by AA, BB
)
select step1.*
from step1 inner join step2 
     on   step1.AA=step2.AA 
      and step1.BB=step2.BB 
      and step1.cnt=step2.max_cnt

今回は使用していませんが、分析関数(window関数)も使えるみたいなので、リファレンスの読み込みをお薦めします。

追記

質問者さんから回答されている内容のSQLは、以下と同じです。
GROUP BYの項目が同じであるなら、ネストさせる必要はありません。

select AA, BB, count(*) from
group by AA,BB
order by AA, BB

Comments

[nkrmn_a]

回答してくださりありがとうございます。
自己解決してしまいましたが、withという関数（？）を初めて知りました。
とても便利そうですね。教えていただいてありがとうございます。今後使っていこうと思います。

Answer 2

mysqlバージョン
yambejpさんのを修正

select AA,BB,count(*) as cnt from tbl as t1
group by AA,BB
having cnt=(
select count(*) as cnt from tbl as t2
where t2.AA=t1.AA
group by AA,BB
order by cnt desc limit 1
);

Comments

[nkrmn_a]

回答していただきありがとうございます。
私の質問ミスのせいでご迷惑をおかけしました。お時間取らせてしまい申し訳ありません。
今後の参考させていただきます！

Answer 3

mysqlバージョン

select AA,BB,count(*) as cnt from tbl
group by AA,BB
having cnt=(
select count(*) as cnt from tbl as t1
group by AA,BB
order by cnt desc limit 1
);

Comments

[nkrmn_a]

回答していただきありがとうございます。
私の質問ミスのせいでご迷惑をおかけしました。お時間取らせてしまい申し訳ありません。

Answer 4

美しさは兎も角、一応できている様です。Postgresqlで書いていますのはご容赦ください。

select t1.AA, t1.BB, t1.CC from
  (select AA, BB, count(*) CC from tbl1
    group by AA, BB) t1,
  (select AA, max(CC) CC from
     (select AA, BB, count(*) CC from tbl1
      group by AA, BB) t3
   group by AA) t2
where t1.AA=t2.AA and t1.CC=t2.CC

Comments

[nkrmn_a]

回答していただきありがとうございます。
私の質問ミスのせいで期待通りの動作ではありませんでしたが、新しい書き方を知ることができ
とてもありがたいです。Postgresqlも使う機会がありそうなので、こちら他でも参考にさせていただきます。

Answer 5

たいへん申し訳ないのですが、一つ見落としていたカラムを発見しました。
そもそもの考え方が異なっており、AAとBBとは別のCC（仮）もセットで一意のものとなっていました。

select AA, BB, max(cnt) from
　(select AA, BB, count(*) as cnt from 
　　(select AA, CC, BB from TABLE)
　group by AA,BB
)
group by AA,BB
order by AA

早とちりで色々とミスが発覚しているので、解決済から下げさせていただきました。
ご迷惑をおかけして申し訳ありません。

（追記）

どうやらSAP hanaのSQL動作が普通のものとは異なっていたようです。

実際にSAPhanaで実行したかったSQLはポスグレなどだとほんの数行で終わるようです。
私の環境ではそうはいかなかったので、解決した方法を記載しておきます。
上に書いてあるCCも使用しました。
SAPhanaを使用している方は参考にしてください（長いです）。

WITH
　tb AS(
　　SELECT
　　　 AA, BB, count(*) AS cnt 
　　FROM (
　　　SELECT
　　　　AA, BB, CC
　　　FROM
　　　　TABLE
　　)
　　GROUP BY 
　　　AA, BB
　),
　cntTb AS(
　　SELECT
　　　AA, max(cnt) AS maxcnt 
　　FROM
　　　tb
　　GROUP BY 
　　　AA
　)

SELECT
　cntTb.AA,
　BB, 
　maxcnt
FROM
　cntTb
INNER JOIN
　tb 
　ON 　cntTb.AA = tb.AA
　AND cntTb.maxcnt = tb.cnt
ORDER BY 3 DESC, 1, 2

Comments

[sazi]

解決したSQLは以下と同義で、質問にも出てくるSQLですけど、それなのに解決したのですか？
select AA, BB, count(*) from TABLE group by AA, BB

[sazi]

ああ、count(*)ではなかったという事ですね。
count(項目名）だと 項目の値がNullの場合はカウントされません。

[nkrmn_a]

CCが入ることでAAとBBが一意のものになり、そうでないとレコード数を数えられなかったようです。
お答えいただいたのに申し訳ありません。

[sazi]

distinct とかgroup byとかが無いと(select AA, CC, BB from TABLE)を経由しても何も変わりませんよ。
count(*)に変えたからじゃないですか？

[nkrmn_a]

確認したところ、
select AA, BB, count(*) from TABLE group by AA, BB
で実行しても期待通りの動作になりませんでした。（全てAA,BBが1つずつという結果でした）
TABLEには他にも多くのカラムがあるのですが、なにか関係しているということでしょうか…。

[nkrmn_a]

大変申し訳ないのですが、SQL文を間違えておりました。
先程載せていたSQL文についてはsaziさんのおっしゃる通りでした。
次からしっかりと確認した上で載せるように心掛けます。
ご迷惑をおかけしてすみません…

[sazi]

考えられる事としては、group byにAA,BB以外を設定した、あるいはselectの項目にAA,BB以外を設定した、ですね。
selectの項目はSAP HANAが、Mysqlのようにgroup by項目が指定されていなくてもエラーとせずに忖度する仕様でなければ考えられませんので、group byの項目に不要なものを設定したのだと思います。

[sazi]

SQLをコメントに書くと見辛いので、私の回答に追記しました。