SQL查询：如何为符合条件的分组结果添加全表中位数列？

我有一个SQL查询，用于筛选出平均quantity大于全表quantity中位数的用户邮箱及对应平均quantity：

SELECT
    AVG(quantity) AS avg_quantity,
    email   
FROM table 
GROUP BY email
HAVING AVG(quantity) > (SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY quantity) FROM table)

查询结果如下：

123.12    person1@domain1.com
0.5       person2@domain2.com

我希望在上述结果中新增一列，每行都显示HAVING子句中计算的全表中位数（假设该中位数为0.01），得到如下三列结果：

123.12    person1@domain1.com    0.01
0.5       person2@domain2.com    0.01

我尝试用笛卡尔积的方式实现：

WITH (
    SELECT
        AVG(quantity) AS avg_quantity,
        email   
    FROM table 
    GROUP BY email
    HAVING AVG(quantity) > (SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY quantity) FROM table)
) AS tmp
SELECT tmp.*, PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY table.quantity)  from tmp, table 

但出现错误：

ERROR: column "tmp. avg_quantity" must appear in the GROUP BY clause or be used in an aggregate function

请问如何实现上述预期的三列输出？

解决方案 #

方法1：用CTE预计算中位数和用户平均数据 #

先通过CTE分别计算全表中位数、符合条件的用户平均数据，再将两者关联（因为中位数是单值，关联后每行都会显示该值）：

WITH median_cte AS (
    -- 预计算全表quantity的中位数
    SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY quantity) AS global_median
    FROM table
),
user_avg_data AS (
    -- 筛选出平均quantity大于中位数的用户数据
    SELECT
        AVG(quantity) AS avg_quantity,
        email   
    FROM table 
    GROUP BY email
    HAVING AVG(quantity) > (SELECT global_median FROM median_cte)
)
-- 关联两个CTE，输出结果
SELECT uad.*, mc.global_median
FROM user_avg_data uad, median_cte mc;

方法2：直接在SELECT中嵌入中位数子查询 #

这种方式更简洁，直接在SELECT列中加入计算中位数的子查询，因为该子查询返回单值，会自动填充到每一行：

SELECT
    AVG(quantity) AS avg_quantity,
    email,
    -- 直接计算全表中位数作为新增列
    (SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY quantity) FROM table) AS global_median
FROM table 
GROUP BY email
HAVING AVG(quantity) > (SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY quantity) FROM table)

错误原因说明 #

你之前的代码报错是因为：在SELECT中使用了PERCENTILE_DISC聚合函数，但没有对应GROUP BY子句，而tmp表的列（avg_quantity、email）既不在GROUP BY里，也没有被聚合函数包裹，违反了SQL的聚合查询规则。正确的做法是先把中位数计算为单值，再和用户平均数据关联，避免不必要的聚合冲突。

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