如何在SQL中通过添加子查询按client_id汇总所有月度平均值

实现按client_id汇总平均值的三种可行方案 #

方案1：窗口函数实现（保留原有月度明细行） #

适用于需要在原有月度明细结果的基础上，每一行额外新增对应client全时段平均MRR的场景，无需调整原有分组逻辑：

SELECT 
    to_char(cd.date, 'YYYYMM') AS date_format_m,
    cd.client_id,
    csd.owner_email,
    AVG(mrr) AS monthly_avg_mrr,
    -- 新增按client_id聚合的全时段平均值
    AVG(AVG(mrr)) OVER (PARTITION BY cd.client_id) AS client_total_avg_mrr
FROM client_data AS cd
LEFT JOIN client_static_data AS csd ON cd.client_id = csd.client_id
WHERE cd.date > '2021-09-01' AND mrr > 0
GROUP BY date_format_m, cd.client_id, csd.owner_email

说明：窗口函数中嵌套聚合AVG(AVG(mrr))，内层AVG是月度维度的聚合结果，外层AVG是对同client下所有月度平均值再次求平均，完全匹配需求。

方案2：ROLLUP分组实现（同时返回明细+汇总行） #

适用于需要额外输出每个client单独的汇总行的场景，汇总行和原有月度明细行同时返回：

SELECT 
    to_char(cd.date, 'YYYYMM') AS date_format_m,
    cd.client_id,
    csd.owner_email,
    AVG(mrr) AS avg_mrr,
    -- 新增行类型标识区分明细和汇总
    CASE 
        WHEN to_char(cd.date, 'YYYYMM') IS NOT NULL THEN '月度明细'
        ELSE 'client全时段汇总' 
    END AS row_type
FROM client_data AS cd
LEFT JOIN client_static_data AS csd ON cd.client_id = csd.client_id
WHERE cd.date > '2021-09-01' AND mrr > 0
GROUP BY ROLLUP((cd.client_id, csd.owner_email), date_format_m)

方案3：子查询正确写法 #

如果需要用子查询实现，可通过CTE先计算月度明细结果，再关联聚合得到汇总值：

WITH monthly_avg_result AS (
    -- 原有的月度平均值计算逻辑
    SELECT 
        to_char(cd.date, 'YYYYMM') AS date_format_m,
        cd.client_id,
        csd.owner_email,
        AVG(mrr) AS monthly_avg_mrr
    FROM client_data AS cd
    LEFT JOIN client_static_data AS csd ON cd.client_id = csd.client_id
    WHERE cd.date > '2021-09-01' AND mrr > 0
    GROUP BY date_format_m, cd.client_id, csd.owner_email
)
SELECT 
    t1.*,
    (SELECT AVG(monthly_avg_mrr) FROM monthly_avg_result t2 WHERE t2.client_id = t1.client_id) AS client_total_avg_mrr
FROM monthly_avg_result t1

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