pgAdmin(PostgreSQL)查询分组求和后每月最大营收行方案

问题背景 #

在pgAdmin中操作vip_sales单表，需统计小型零售店每年、每个月总营收最高对应的周几记录。初始分组汇总SQL可正常返回按年、月、周几聚合的营收排序结果：

SELECT year, month, day_of_week, SUM(total_revenue)
FROM vip_sales
GROUP BY year, month, day_of_week
ORDER BY year, month, SUM DESC

需要基于该结果进一步筛选，仅保留每年每个月营收最高的单条（或并列最高）周几记录。

此前尝试的报错原因 #

• 直接编写MAX(SUM(total_revenue))嵌套聚合触发ERROR: aggregate function calls cannot be nested（SQL state: 42803）：PostgreSQL不支持聚合函数直接嵌套，多层聚合必须分层通过子查询/CTE实现。
• FROM子句使用子查询未设置别名触发ERROR: subquery in FROM must have an alias（SQL state: 42601）：PostgreSQL语法强制要求，所有出现在FROM位置的子查询必须指定唯一别名。
• 子查询设置别名后仍按year, month, day_of_week分组取最大值：分组粒度保留到周几维度，等价于对每个周几的自身值取最大值，自然会返回所有周几记录，无法实现按月筛选最高值的逻辑。

可直接运行的实现方案 #

方案1：窗口函数实现（推荐，写法简洁） #

用RANK()或ROW_NUMBER()窗口函数，按年、月分区，按聚合后的营收降序排序，筛选排名为1的记录即可：

-- 先聚合得到每个年-月-周几的总营收
WITH dw_revenue AS (
    SELECT
        year,
        month,
        day_of_week,
        SUM(total_revenue) AS revenue_sum
    FROM vip_sales
    GROUP BY year, month, day_of_week
)
SELECT year, month, day_of_week, revenue_sum
FROM (
    SELECT
        *,
        -- 按年、月分组，营收从高到低排名
        RANK() OVER (PARTITION BY year, month ORDER BY revenue_sum DESC) AS revenue_rank
    FROM dw_revenue
) ranked_revenue
WHERE revenue_rank = 1
ORDER BY year, month;

参数说明：如果同月存在多个周几营收并列第一的场景，RANK()会返回所有并列第一的记录；如果要求每个月必须仅返回1条记录，可将RANK()替换为ROW_NUMBER()，同时可在排序规则中补充条件，比如ORDER BY revenue_sum DESC, day_of_week，固定取周几序号更小的那条作为结果。

方案2：关联子查询实现（兼容所有PostgreSQL版本） #

先计算出每个年、月对应的最高营收值，再关联回聚合结果匹配对应周几：

SELECT
    agg.year,
    agg.month,
    agg.day_of_week,
    agg.revenue_sum
FROM (
    -- 第一层：聚合得到年-月-周几维度的总营收
    SELECT
        year,
        month,
        day_of_week,
        SUM(total_revenue) AS revenue_sum
    FROM vip_sales
    GROUP BY year, month, day_of_week
) agg
JOIN (
    -- 第二层：计算每个年-月对应的最高营收
    SELECT
        year,
        month,
        MAX(revenue_sum) AS max_revenue
    FROM (
        SELECT
            year,
            month,
            day_of_week,
            SUM(total_revenue) AS revenue_sum
        FROM vip_sales
        GROUP BY year, month, day_of_week
    ) dw_agg
    GROUP BY year, month
) max_agg
ON agg.year = max_agg.year
AND agg.month = max_agg.month
AND agg.revenue_sum = max_agg.max_revenue
ORDER BY agg.year, agg.month;

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