MySQL查询获取列的模态平均值? [英] MySQL query to get the modal averages of a column?

问题描述

我一直在记录控制面板用户的窗口高度.

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

我有一张这样的桌子:

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 = 125个用户，500像素= 12个用户

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

我正在寻找一个MySQL查询，我可以直接进入PhpMyAdmin....

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

推荐答案

获取原始计数

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

这使用了MySQL的技巧，即使用变量来存储通过聚合子查询时的最大计数.操作摘要

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):扫描表一次并建立计数(T1)
• O(n):扫描派生表T1并将最高计数保留在变量@r(T2)
• O(n):扫描派生表T2并仅过滤计数最高的高度

这篇关于MySQL查询获取列的模态平均值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！