こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

-広告-

解決済みの質問

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

投稿日時 - 2017-11-16 13:39:58

QNo.9398022

困ってます

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

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

投稿日時 - 2017-11-17 15:46:06

お礼

アドバイスありがとうございます。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,

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

投稿日時 - 2017-11-17 23:31:30

ANo.3

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

-広告-
-広告-

回答(3)

ANo.2

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

投稿日時 - 2017-11-17 11:02:54

お礼

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

投稿日時 - 2017-11-17 22:29:23

ANo.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ページ・・で検索

投稿日時 - 2017-11-17 06:05:35

-広告-
-広告-
-広告-
-広告-