mysql_如何用mysql计算每组的中位数

本文介绍了一种在MySQL中计算每个组中位数的方法。由于MySQL缺乏内置的中位数函数,作者通过创建辅助列、使用变量跟踪行计数及组别等步骤,最终实现了对各组数据中位数的有效计算。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

有一天,我不得不计算MySQL中每个组的中位数。 事实证明这并不像听起来那么简单,主要是因为在MySQL中不存在median()函数。 经过一些反复试验和一些有用的提示,我想到了。 尤其对于那些不熟悉MySQL的人来说,这里是我自己写的解决方案。

这是我们的例子。 两组候选人(男性和女性)的一系列数据点。 我们要计算每组的中位高度。

这里写图片描述

我们先从简单的开始,通过选择两列并按组和高度排序。
SELECT 
    gender, height
FROM
    heights
ORDER BY gender , height ASC
返回以下结果:

这里写图片描述

接下来,我们需要计算每个组的行数,并将其作为另一列添加到表格中。
SELECT 
    gender,
    height,
    (SELECT 
        COUNT(*)
        FROM
            heights
        WHERE
            a.gender = gender) AS total_of_group
FROM
    (SELECT 
        gender, height
    FROM
        heights
    ORDER BY gender , height) AS a
结果:

这里写图片描述

接下来会变得稍微复杂一些,但这是真正重要的部分。 我们添加了两个变量row_number和median_group。 行数应该每行加1,直到我们到达下一个组,然后再从1开始。我们用median_group帮助变量来实现这个。 在每个结果中,median_group变量设置为行组名(这里是性别)。 案例选择检测是否有组名的变化,并相应地重置计数器。
SET @ROW_NUMBER:=0; 
SET @median_group:='';

SELECT 
    @ROW_NUMBER:=CASE
        WHEN @median_group = gender THEN @ROW_NUMBER + 1
        ELSE 1
    END AS count_of_group,
    @median_group:=gender AS median_group,
    gender,
    height,
    (SELECT 
            COUNT(*)
        FROM
            heights
        WHERE
            a.gender = gender) AS total_of_group
FROM
    (SELECT 
        gender, height
    FROM
        heights
    ORDER BY gender , height) AS a
结果:

这里写图片描述

现在我们实际计算中位数。

通过将组的计数除以2 (total_of_group / 2.0)并且进行相同的加1操作(total_of_group / 2.0 + 1),我们正在为我们的选择定义一个搜索范围,并将其应用于count_of_group。 这意味着我们只选择在这个范围之间的那些行,有效地选择每个组运行计数的中间值。

我们还将平均值应用于选择(AVG(高度)),因为每个组可能并不总是只有一行被选中。
SET @ROW_NUMBER:=0; 
SET @median_group:='';

SELECT 
    median_group, AVG(height) AS median
FROM
    (SELECT 
        @ROW_NUMBER:=CASE
                WHEN @median_group = gender THEN @ROW_NUMBER + 1
                ELSE 1
            END AS count_of_group,
            @median_group:=gender AS median_group,
            gender,
            height,
            (SELECT 
                    COUNT(*)
                FROM
                    heights
                WHERE
                    a.gender = gender) AS total_of_group
    FROM
        (SELECT 
        gender, height
    FROM
        heights
    ORDER BY gender , height) AS a) AS b
WHERE
    count_of_group BETWEEN total_of_group / 2.0 AND total_of_group / 2.0 + 1
GROUP BY median_group
最后的结果:

这里写图片描述

在SQL中计算每组数据的中位数并不直接内置,因为不是所有数据库系统都提供这个功能。不过,你可以通过一些间接的方式来获取接近于中位数的结果。以下是几种常见的方法: 1. **分组并排序**: - 对于数值型字段,你可以先按组对数据进行分组,然后对每个组内的值进行排序。之后,如果组的数量是奇数,中间的那个值就是中位数;如果是偶数,通常取中间两个数的平均值作为中位数。 ```sql SELECT group_column, AVG(value_column) as median FROM ( SELECT group_column, value_column, ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY value_column) as row_num FROM your_table ) subquery WHERE row_num IN ((SELECT COUNT(*) FROM your_table WHERE group_column = t.group_column) / 2, (SELECT COUNT(*) FROM your_table WHERE group_column = t.group_column) / 2 + 1) GROUP BY group_column; ``` 2. **使用窗口函数**: - 如果你的数据库支持窗口函数(如MySQL 8.0+、PostgreSQL等),可以使用`PERCENTILE_CONT`或`PERCENTILE_DISC`等函数。 ```sql WITH ranked_data AS ( SELECT group_column, value_column, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value_column) OVER (PARTITION BY group_column) as median FROM your_table ) SELECT * FROM ranked_data; ``` 3. **存储过程或自定义函数**: - 如果数据库不支持这些内置函数,可能需要编写自定义存储过程或者创建用户定义函数(UDF)来进行计算。 请注意,以上示例假设`value_column`是你想要计算中位数的列,并且`group_column`是用来分组的列。在实际应用中,你需要将`your_table`, `group_column`, 和 `value_column` 替换为你实际的数据表名和字段名。同时,不同数据库的具体语法可能会有差异。如果你的数据库不支持上述方法,你可能需要查询具体的数据库文档寻找替代方案。
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值