• ベストアンサー
  • 困ってます

【MySQL5.7】IDごとの最頻値

お世話になります。MySQLでidごとの最頻値を抽出しているのですが、 select id, hantei,count(*) as ModeCount from member a group by id,hantei having count(*) >= all(select count(*) from member b where b.id=a.id group by hantei asc) order by id,hantei レコード数は5000件ほどなのですが現在の方法では10.485sと時間がかかりすぎて困っています。MySQLでは最頻値を出すのにサブクエリを使うしかないのでしょうか? こうすればもっと速くなるよって方法があれば教えてください。お願いします。

共感・応援の気持ちを伝えよう!

  • MySQL
  • 回答数3
  • 閲覧数435
  • ありがとう数2

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.3

Time | Value 4:35:49 | 122 4:35:49 | 122 4:35:50 | 121 4:35:50 | 121 4:35:50 | 111 4:35:51 | 122 4:35:51 | 111 4:35:51 | 111 4:35:51 | 132 4:35:51 | 132 を Time | Value 4:35:49 | 122 4:35:50 | 121 4:35:51 | 132 INNER JOINを使って100万件で21.485 sec SELECT MaxCountSub.`Time`, CountSub.`Value` FROM (SELECT myTable.`Time`, myTable.`Value`, Count(myTable.`Value`) AS CountOfValue FROM myTable GROUP BY myTable.`Time`, myTable.`Value`) As CountSub INNER JOIN (SELECT dT.`Time`, Max(CountOfValue) As MaxCountOfValue FROM (SELECT myTable.`Time`, myTable.`Value`, Count(myTable.`Value`) AS CountOfValue FROM myTable GROUP BY myTable.`Time`, myTable.`Value`) As dT GROUP BY dT.`Time`) As MaxCountSub ON CountSub.`Time` = MaxCountSub.`Time` AND CountSub.CountOfValue = MaxCountSub.MaxCountOfValue 5000件でどうなるかちょっと自信ない手元にあったデータにコピペしたクエリーを走らさせただけだからだから

参考URL:
https://stackoverflow.com/questions/34845076/sql-get-the-most-frequent-value-for-each-group

共感・感謝の気持ちを伝えよう!

質問者からのお礼

アドバイスありがとうございます。turu575さんのお礼にも書きましたが、自分の言葉足らずで最終的にどういう形にしたいかの説明が足りていませんでした。 1.idごとのhanteiの最頻値(値は4つしかない)を出す。 2.idごとのhantei最頻値をカラムhantei1・hantei2・hantei3・hantei4に分ける。 3.最頻値は● それ以外は〇にして、視覚的に分かりやすくしたかった。 最終的にしたかった形は↓こんな形です。 ---------------------------------------------------------------------------- id | hantei1 | hantei2 | hantei3 | hantei4 ---------------------------------------------------------------------------- 1 | 〇 | ● | 〇 | 〇 ---------------------------------------------------------------------------- 2 | 〇 | ● | ● | 〇 まず、「最頻値を出さないといけない」ことばかり考えてしまいました。 その結果、「遅い!」「サブクエリ使わない方法ないか?」って迷子になっていました。 冷静に考えると最終的にやりたい形にするには1.は必要なく、 カラムhanteiを最初からreplaceでhantei1~4に分け CASE WHEN SUM(A.hantei2) > SUM(A.hantei1) THEN '〇' WHEN SUM(A.hantei3) > SUM(A.hantei1) THEN '〇' WHEN SUM(A.hantei4) > SUM(A.hantei1) THEN '〇' else '● ' END hantei1, とすることで実現できました。お二方とも見当違いな質問に親身なアドバイスありがとうございました。

その他の回答 (2)

  • 回答No.2

先の回答撤回します。質問内容を理解していなかった。回答文もスペースが消えていたりおかしかった・・・ 一つの項目の最頻値なら100万件でも0.9sから1.2sなのに

共感・感謝の気持ちを伝えよう!

質問者からのお礼

最終的にどういう形にしたいのか自分の説明文が足りていませんでした。 グループごとの最頻値を出すのにサブクエリ使わずに(遅いので)集計する方法がないか?って知恵をお借りしたかったのです。結果、紛らわしい質問内容になってしまいすみませんでした。

  • 回答No.1

かなり前に試した方法・・極値関数を使用 select A.`品名` as `最頻値`,A.cnt as `頻度`from (select`品名`,count(*) as cnt, (select max(X.cnt) from (select count(*) as cnt from uriage group by `品名`) X) as maxCnt fromuriage group by `品名`) Awhere cnt = maxCnt ネタ元URLは長いので・・・データ集計・分析のためのSQL入門 129ページ・・で検索

共感・感謝の気持ちを伝えよう!

関連するQ&A

  • Mysql サブクエリの使い方

    table_A ---+------ id | count ---+------ 1 | 5 ---+------ 2 | 6 ---+------ 3 | 7 ---+------ table_B ---+----- id | sub_count ---+----- 1 | 2 ---+------ 2 | 2 ---+------ 3 | 5 ---+------ 1 | 3 ---+------ 2 | 4 ---+------ このようなテーブルで table_Aのcountと、table_Bのidでまとめたsub_countの合計が 一致しないidだけを抽出するために、 SELECT table_A.id FROM (SELECT sum(table_B.sub_count) FROM table_B GROUP BY table_B.id) AS B, table_A, table_B WHERE table_A.id = table_B.id AND table_A.count != sum(table_B.sub_cout) と書いてみましたが、うまくいきません。 ERROR 1111 (HY000): Invalid use of group function 何が悪いのでしょうか? mysqlも投稿も初心者です。 よろしくお願いします。

    • ベストアンサー
    • MySQL
  • in (サブクエリ) わかんない

    table hh; +------+ | id | +------+ | 1 | | 2 | | 3 | | 2 | | 1 | | 1 | +------+ select id,count(*) as cou from hh group by id having count(*) in (1,2,3); こいつはOK select id,count(*) as cou from hh group by id having count(*) in (select count(*) from hh group by id); こいつはダメ 違いはなんですか、教えて下さい

  • POSTGRESでMYSQLと同じ結果を得る方法

    create table test (a varchar(10), b varchar(10)); insert into test values ('a','a'); insert into test values ('b','b'); insert into test values ('b','c'); insert into test values ('c','d'); select count(*), a, b from test group by a; 上記を実行するとMYSQLでは +----------+------+------+ | count(*) | a | b | +----------+------+------+ | 1 | a | a | | 2 | b | b | | 1 | c | d | +----------+------+------+ という結果になる。 POSTGRESでは以下のエラーになります。 ERROR: column "test.b" must appear in the GROUP BY clause or be used in an aggr egate function select count(*), a, b from test group by a, b; とすると結果が変わってしまいます。 MYSQLと同じ結果をPOSTGRESで得るいい方法はありますか?

  • group by のソート

    mysql Client API version 3.23.49 PHP/4.4.5 で動作させようと思っています。 table a   b 1   2 3   4 2   4 7   1 2   6 1   6 上のデータから以下のような結果を得たいのですが table a   b  idcount1 1   6    2 3   4    1 2   6    2 7   1    1 グループで集計をとり、その上でbに6がある場合は6その他は bの値にNULLもしくは別の値が入るという形でいいのですが・・・ できません。 SELECT a, b, count( * ) AS idcount1 FROM table GROUP BY a するとはじめに読みこんだ値がbには入ってしまいます。 結果 table a   b  idcount1 1   2    2 3   4    1 2   4    2 7   1    1 SELECT a, b, count( * ) AS idcount1 FROM table where b=6 GROUP BY a するとbの値ははじきますし、カウントしている意味がなくなります。 結果 table a   b  idcount1 1   6    1 2   6    1 GROUP BY 処理前のソートを考え、from table後に oder by b=6 asc を使ったがエラー 末尾に oder by b=6 asc を使うと最初に試した値をソートするだけでした。 宜しくお願いいたします。

    • ベストアンサー
    • MySQL
  • SQLのcount()とgroup by

    以下のようなテーブルがあったとき、 tbl1 id | score 1 | 100 1 | 200 1 | 300 2 | 200 2 | 100 3 | 300 3 | 400 3 | 500 3 | 600 idでまとめて個数を出すとすると、 SELECT count(*) FROM tbl1 group by id とすれば count(*) 3 2 4 と結果が返ってきますが、この結果にidを足して id | count(*) 1 | 3 2 | 2 3 | 4 というようなSQLはどうやって書けばいいのでしょうか? MySQLでは、 SELECT id, count(*) FROM tbl1 group by id と書けるのですが、これは標準的なSQLではNGなそうなので、気になって質問しました。 よろしくお願いします。

  • MySQL: 複数テーブルのcount

    複数テーブルの総行数(count値)を取得したいのですが、下記だとテーブルA,B,Cの結果が バラバラに出力されてしまいます。 【入力条件】 mysql> select COUNT(*) AS 列ID from テーブルA      union all      select COUNT(*) AS 列ID from テーブルB      union all      select COUNT(*) AS 列ID from テーブルC; 【出力結果】       +-----+       | 列_ID |       +-----+       |  1  | ←テーブルAの合計:行数       |  2  | ←テーブルBの合計:行数       |  3  | ←テーブルCの合計:行数       +-----+ 総行数:6を一発で取得するには、どのようにしたらよろしいのでしょうか? 大変恐縮ですが、ご教授よろしくお願いいたいます。 <<MySQLのverは5,5です。

    • ベストアンサー
    • MySQL
  • テーブルの結合について

    以下の二つを実行すると、2 の方がかなり速いのですが理由が分かりません。 分かる方教えてもらっていいですか? DBはMYSQLでInoDBです。 ちなみに Aは1万件、B は10万件ほど 1 のSQL SELECT * FROM A LEFT JOIN (SELECT * FROM B WHERE ・・・ GROUP BY XXX) B ON A.id = B.XXX WHERE ・・・ GROUP BY A.id 2 のSQL (SELECT * FROM A ・・・ GROUP BY ID ) A LEFT JOIN (SELECT * FROM B WHERE ・・・ GROUP BY XXX) ON A.ID = B.XXX.ID

  • viewでfrom句にサブクエリが書けない

    こんにちは。MYSQLのVIEWの代替についてお教え下さい。 下記でinner joinについて教えて頂きました。 http://okwave.jp/qa/q7296711.html 環境はWin 7 MYQL5です。 t1テーブルのデータ ID,在庫 001,22 t2テーブルのデータ ID,出庫 001,17 001,3 SELECT A.z AS `在庫`,B.* FROM (SELECT SUM(stock) AS z FROM t1 WHERE id='001' GROUP BY id) A INNER JOIN t2 B ON A.id=B.id というコードで、それぞれのテーブルをグループ化してグループ化したもの同士をJOINしました。 このコードを元にVIEWを作り、そのVIEWを対象にクエリを作ろうと考えていました。 しかしながら、MYSQLのVIEWではFROM句でサブクエリは使えませんでした。 http://www.klab.jp/media/mysql/index4.html 上記のコードを書きなおして、なんとしてもVIEWで利用したいのですが、FROM句でサブクエリを使わずにVIEWを作成する方法がわかりません。 どのようにリライトすればよいか、ご助言お願い致します。

    • ベストアンサー
    • MySQL
  • 重複レコードのある項目を比較し更新する方法

    nameで重複しているレコードをDateの 最新の日のレコードのIDをTGT_IDに入れたいのですが select * from テーブル名 where name in ( select name from テーブル名 group by name having count(*) > 1 ) と重複したカラムを抽出まではできるのですが そこからどうしたらいいのか分かりません どなたかご教授をお願いします。 ID name date tgt_id 1 A 20040403 2 2 A 20040625 2 3 B 20040429 4 4 B 20040623 4

  • MySQLの文法について

    MySQL初心者です 以下の文法でおかしなところがあったら直していただきたいのですが… テーブルの内容などは関係なく,文法がおかしいか見ていただきたいです バージョンは6.0.6を使っています select c_count, count(*) as custdist from ( select c_custkey, count(o_orderkey) from customer left outer join orders on c_custkey = o_custkey and o_comment not like '%special%products%' group by c_custkey ) as c_orders (c_custkey, c_count) group by c_count order by custdist desc, c_count desc; よろしくお願いします><