Statistics - Daily Mode from MySQL table with numbers and date
I need some help writing a MySQL query that grabs the mode (most frequently occurring number) each day
Data set looks something like this
开发者_如何学Go datetime number 2010-01-01 00:35:00 500 2010-01-01 01:15:10 500 2010-01-02 01:25:10 1500 2010-01-02 01:35:10 50 2010-01-03 12:35:50 100 2010-01-05 05:25:10 2500
(etc)
The following should give you the mode per day:
SELECT x.* FROM
(
SELECT datetime, number, COUNT(*) AS Ct
FROM table
GROUP BY datetime, number
) x
INNER JOIN
(
SELECT datetime, MAX(Ct) AS MaxCt
FROM (
SELECT datetime, number, COUNT(*) AS Ct
FROM table
GROUP BY datetime, number
) y
GROUP BY datetime
) z
ON z.MaxCt = x.Ct
AND z.datetime = x.datetime
n.b. it doesn't do anything fancy where you have multiple numbers with the same modal frequency -- you'll get multiple rows for that date.
You can use GROUP BY and LIMIT to accomplish this for a given day:
SELECT number, count(number) AS qty
FROM table
GROUP BY number
ORDER BY qty DESC
LIMIT 1;
精彩评论