質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.11%

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

解決済

回答 5

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 312

nkrmn_a

score 27

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句を使うというのも見かけましたが、どう使えばうまくいくのか分からず
エラーばかり出て迷走しております…

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • sazi

    2020/02/05 10:02

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

    キャンセル

  • キャンセル

  • nkrmn_a

    2020/02/05 13:04

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

    キャンセル

回答 5

+2

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2020/02/05 13:12

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

    キャンセル

+1

美しさは兎も角、一応できている様です。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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2020/02/05 13:15

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

    キャンセル

+1

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
);

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2020/02/05 13:21

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

    キャンセル

+1

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
);

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2020/02/05 13:26

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

    キャンセル

check解決した方法

0

たいへん申し訳ないのですが、一つ見落としていたカラムを発見しました。
そもそもの考え方が異なっており、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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2020/02/05 13:47 編集

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

    キャンセル

  • 2020/02/05 13:56

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

    キャンセル

  • 2020/02/05 14:11 編集

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.11%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる