【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では最頻値を出すのにサブクエリを使うしかないのでしょうか？
こうすればもっと速くなるよって方法があれば教えてください。お願いします。

回答No.3

turu575 · ベストアンサー

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さんのお礼にも書きましたが、自分の言葉足らずで最終的にどういう形にしたいかの説明が足りていませんでした。

１．idごとのhanteiの最頻値（値は４つしかない）を出す。
２．idごとのhantei最頻値をカラムhantei１・hantei２・hantei３・hantei４に分ける。
３．最頻値は●　それ以外は〇にして、視覚的に分かりやすくしたかった。
最終的にしたかった形は↓こんな形です。

----------------------------------------------------------------------------
id | hantei1 | hantei2 | hantei3 | hantei4
----------------------------------------------------------------------------
1 | 〇 | ● | 〇 | 〇
----------------------------------------------------------------------------
2 | 〇 | ● | ● | 〇

まず、「最頻値を出さないといけない」ことばかり考えてしまいました。
その結果、「遅い！」「サブクエリ使わない方法ないか？」って迷子になっていました。

冷静に考えると最終的にやりたい形にするには１．は必要なく、
カラム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,

とすることで実現できました。お二方とも見当違いな質問に親身なアドバイスありがとうございました。

回答No.2

turu575

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

お礼

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

回答No.1

turu575

かなり前に試した方法・・極値関数を使用
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ページ・・で検索