Netezza中按组计算中位数：直接用MEDIAN函数是否正确？

你的现有代码正确性 #

Netezza原生支持MEDIAN()聚合函数，你写的这段代码逻辑完全没问题：

select
 count(*) as total_count, 
median(height) as median_height, 
median(weight) as median_weight 
from football_players
group by country_of_origin;

它能按country_of_origin分组，正确统计每组的总人数，以及身高、体重的中位数，既然在Netezza中运行成功，结果是可信的。

用NTILE()实现分组中位数（兼容无MEDIAN函数的SQL引擎） #

如果要在不支持MEDIAN()的SQL服务器（比如MySQL 8.0以前、SQLite等）中实现，可借助NTILE()函数间接计算，核心思路是把每个分组内的数据分成2个桶，再根据数据行数的奇偶性提取中位数。具体实现如下：

步骤1：对分组内数据排序并分桶 #

先给每个国家的球员按身高、体重分别排序，用NTILE(2)把每组数据分成两个大小尽量相等的桶：

WITH ranked_players AS (
    SELECT
        country_of_origin,
        height,
        weight,
        NTILE(2) OVER (PARTITION BY country_of_origin ORDER BY height) AS height_tile,
        NTILE(2) OVER (PARTITION BY country_of_origin ORDER BY weight) AS weight_tile,
        COUNT(*) OVER (PARTITION BY country_of_origin) AS group_count
    FROM football_players
)

步骤2：根据分组行数奇偶性计算中位数 #

• 若分组行数为奇数：中间行同时属于两个桶，取第二个桶的最小值即可
• 若分组行数为偶数：中位数是中间两个数的平均值，需取第一个桶的最大值和第二个桶的最小值再求平均

最终查询代码：

WITH ranked_players AS (
    SELECT
        country_of_origin,
        height,
        weight,
        NTILE(2) OVER (PARTITION BY country_of_origin ORDER BY height) AS height_tile,
        NTILE(2) OVER (PARTITION BY country_of_origin ORDER BY weight) AS weight_tile,
        COUNT(*) OVER (PARTITION BY country_of_origin) AS group_count
    FROM football_players
)
SELECT
    country_of_origin,
    MAX(group_count) AS total_count,
    -- 计算身高中位数
    CASE
        WHEN MAX(group_count) % 2 = 1 THEN MIN(CASE WHEN height_tile = 2 THEN height END)
        ELSE (MAX(CASE WHEN height_tile = 1 THEN height END) + MIN(CASE WHEN height_tile = 2 THEN height END)) / 2.0
    END AS median_height,
    -- 计算体重中位数
    CASE
        WHEN MAX(group_count) % 2 = 1 THEN MIN(CASE WHEN weight_tile = 2 THEN weight END)
        ELSE (MAX(CASE WHEN weight_tile = 1 THEN weight END) + MIN(CASE WHEN weight_tile = 2 THEN weight END)) / 2.0
    END AS median_weight
FROM ranked_players
GROUP BY country_of_origin;

这段代码兼容大多数不支持MEDIAN()的SQL引擎，逻辑和原生MEDIAN()函数一致，能正确输出分组后的中位数。

内容的提问来源于stack exchange，提问作者wulasa