Oracle/SQL如何按分组计算组内小于等于中位数的VALUE条件平均值

实现逻辑 #

1. 先按GROUP ID分组计算每组的VALUE中位数，这里使用Oracle内置的百分位函数实现，和你示例的离散中位数计算逻辑匹配。
2. 过滤每组中小于等于对应中位数的VALUE，计算这部分数据的平均值。

实现代码 #

写法1：CTE关联实现（逻辑清晰易读） #

WITH group_median AS (
    -- 计算每个分组的中位数
    SELECT 
        "GROUP ID",
        PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY VALUE) AS group_median
    FROM your_table
    GROUP BY "GROUP ID"
)
SELECT 
    t."GROUP ID",
    AVG(t.VALUE) AS "COND AVG"
FROM your_table t
INNER JOIN group_median gm 
    ON t."GROUP ID" = gm."GROUP ID"
WHERE t.VALUE <= gm.group_median
GROUP BY t."GROUP ID"
ORDER BY t."GROUP ID";

写法2：窗口函数实现（性能更优，无需关联表） #

SELECT 
    "GROUP ID",
    AVG(CASE WHEN VALUE <= group_median THEN VALUE END) AS "COND AVG"
FROM (
    SELECT 
        "GROUP ID",
        VALUE,
        -- 窗口函数计算每行对应分组的中位数
        PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY VALUE) OVER (PARTITION BY "GROUP ID") AS group_median
    FROM your_table
) t
GROUP BY "GROUP ID"
ORDER BY "GROUP ID";

补充说明 #

• 如果你需要使用连续插值计算中位数，可以将PERCENTILE_DISC(0.5)替换为PERCENTILE_CONT(0.5)，两者区别为：PERCENTILE_DISC返回分组内实际存在的数值，PERCENTILE_CONT会在偶数个元素时返回两个中间值的插值结果，你可以根据业务需求选择。
• 注：你给出的示例中分组1的COND AVG标注为4属于笔误，实际小于等于中位数4的VALUE为2、3、4，平均值为3，上述SQL执行后会返回正确的计算结果。

内容的提问来源于stack exchange，提问作者W. Walter