如何编写SQL查询：分组求平均并添加全数据集平均列

需求为计算每个地点的平均销售额，同时新增一列展示整个数据集的平均销售额，两个需求单独实现均无问题，但组合时遇到阻碍。

当前数据集 #

期望结果 #

此前尝试的问题点 #

• 直接组合聚合函数与窗口函数时，GROUP BY会让窗口函数仅在分组内计算，无法得到全数据集的平均：

-- 错误写法：全局平均仅基于分组后的数据，结果不符合预期
SELECT location_id, AVG(sales) AS location_avg, AVG(sales) OVER () AS total_avg
FROM table
WHERE (date > '01/01/2023')
GROUP BY location_id;

• 用CTE关联时，错误地在主查询中重复计算，导致数据冗余且未正确获取全局平均。

解决方案1：窗口函数组合实现 #

通过分区窗口函数计算分组平均，同时用无分区的窗口函数计算全局平均，最后去重得到唯一结果：

SELECT DISTINCT
    location_id,
    AVG(sales) OVER (PARTITION BY location_id) AS Avg_Sales,
    AVG(sales) OVER () AS Dataset_Avg
FROM table
WHERE date > '01/01/2023';

• PARTITION BY location_id：按地点分组计算单地点平均销售额
• OVER ()：无分区条件，计算整个筛选后数据集的平均销售额
• DISTINCT：去除因原表多行数据产生的重复结果

解决方案2：双CTE关联实现 #

分别用CTE计算分组平均和全局平均，再通过交叉关联将全局平均附加到每个地点的结果上：

WITH LocationAvg AS (
    SELECT 
        location_id,
        AVG(sales) AS Avg_Sales
    FROM table
    WHERE date > '01/01/2023'
    GROUP BY location_id
),
GlobalAvg AS (
    SELECT AVG(sales) AS Dataset_Avg
    FROM table
    WHERE date > '01/01/2023'
)
SELECT 
    la.location_id,
    la.Avg_Sales,
    ga.Dataset_Avg
FROM LocationAvg la
CROSS JOIN GlobalAvg ga;

• LocationAvg：存储各地点的平均销售额
• GlobalAvg：存储整个数据集的平均销售额
• CROSS JOIN：将全局平均与每个地点的结果关联，确保每行都带上全局平均

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