MySQL query to get the modal averages of a column?

问题:

I've been recording the window height of users of my control panel.

I have a table like so:

user_id(INT) | window_height(INT)
--------------------------------
123          | 1200
124          | 1200
125          | 1100
126          | 1200

I have thousands of rows and would like to get the Modal averages.

ie 1200px = 300users, 1100px = 125users, 500px = 12users

I'm looking for a MySQL query i can just bang into PhpMyAdmin....

回答1:

To get raw counts

select window_height, count(*) totalusers
from tbl
group by window_height
order by totalusers desc  # or by window_height

To get the modal average (this will show multiple values if there are ties for the highest count)

select window_height, totalusers
from (
    select @r := if(totalusers>@r,totalusers,@r) maxcount, window_height, totalusers
    from (select @r:=0) initvars, (
        select window_height, count(*) totalusers
        from tbl
        group by window_height
    ) X ) Y
where totalusers = @r

This uses a MySQL trick of using a variable to store the max count as it goes through the aggregated subquery. Summary of operations

• O(n): scan table once and build the counts (T1)
• O(n): scan the derived table T1 and keep the highest count in the variable @r (T2)
• O(n): scan the derived table T2 and filter only for the heights with the highest count