PostgreSQL中计算用户每月消费总额中位数的SQL报错求助

嘿，我来帮你解决这个问题！你的SQL报错+逻辑偏离目标，核心问题是没按日历月做分区排序，而且最终分组的维度也错了。咱们一步步修正：

原SQL的主要问题 #

• 窗口函数ROW_NUMBER()没加PARTITION BY calendar_month，导致是把所有月份的消费总额混在一起排序，而不是每个月单独算排名，这完全背离了“每个月算中位数”的需求。
• 最后按user_id分组，但咱们要的是每个月的中位数，不是每个用户的统计值，分组方向错了。

修正后的可行SQL #

假设你的transactions表有user_id、created_at、amount这几个核心字段，正确的中位数计算SQL如下：

WITH monthly_user_totals AS (
    -- 先算出每个用户在每个月的总消费
    SELECT 
        EXTRACT(MONTH FROM created_at) AS calendar_month,
        user_id,
        SUM(amount) AS total_per_user
    FROM transactions
    GROUP BY calendar_month, user_id
),
ranked_monthly_totals AS (
    -- 对每个月内的用户消费额做升序排名，同时统计当月有消费的用户总数
    SELECT 
        calendar_month,
        total_per_user,
        ROW_NUMBER() OVER (PARTITION BY calendar_month ORDER BY total_per_user ASC) AS asc_rank,
        COUNT(*) OVER (PARTITION BY calendar_month) AS total_users
    FROM monthly_user_totals
)
-- 最后计算每个月的中位数（兼容奇数/偶数用户数的情况）
SELECT 
    calendar_month,
    CASE
        -- 当月用户数是奇数：直接取中间位置的数值
        WHEN total_users % 2 = 1 THEN MAX(CASE WHEN asc_rank = (total_users + 1)/2 THEN total_per_user END)
        -- 当月用户数是偶数：取中间两个数的平均值
        ELSE AVG(CASE WHEN asc_rank IN (total_users/2, total_users/2 + 1) THEN total_per_user END)
    END AS median_total_per_user
FROM ranked_monthly_totals
GROUP BY calendar_month
ORDER BY calendar_month;

代码逻辑拆解 #

1. monthly_user_totals：这一步是基础聚合，把每个用户每个月的所有交易金额加总，得到每个用户当月的消费总额。
2. ranked_monthly_totals：
   • PARTITION BY calendar_month是关键，确保每个月的排名都是独立计算的，不会和其他月份混在一起。
   • COUNT(*) OVER (PARTITION BY calendar_month)用来统计当月有消费的用户数量，方便后续判断中位数的位置。
3. 最终查询：
   • 针对奇数和偶数的用户数分别处理：奇数用户数取中间那一行的数值，偶数用户数取中间两行的平均值，这是中位数的标准计算方式。

原SQL报错的额外原因 #

除了逻辑偏差，你的原SQL里子查询total_amount包含calendar_month字段，但外层查询没有引用它，同时按user_id排序也没有实际意义，这些语法/逻辑问题都会导致报错。

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