MySQL查询获取列的模态平均值？

发布于 2024-10-30 03:27:58 字数 381 浏览 1 评论 0原文

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

我有一个像这样的表：

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

我有数千行，想要获得模态平均值。

即 1200px = 300 个用户，1100px = 125 个用户，500 像素 = 12 个用户

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

原文

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....

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

十秒萌定你 2024-11-06 03:27:58

获取原始计数

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

获取模态平均值（如果最高计数存在关联，这将显示多个值）

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 技巧，即在通过聚合子查询时使用变量来存储最大计数。操作摘要

O(n)：扫描一次表并构建计数 (T1)
O(n)：扫描派生表 T1 并将最高计数保留在变量 @r 中 (T2)
O(n)：扫描派生表T2 和过滤器仅针对计数最高的高度

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

回复收藏 0 原文

~没有更多了~