SQL自连接聚合表时SUM+CASE WHEN结果错误的解决问询

现有披萨积分交易表pizza_transactions，结构包含customer_id、amount、date_time、note、previous_balance、previous_voucher_balance、condition1，需生成按用户聚合的统计报表，每用户一行，包含以下字段：

• 符合条件的累计购买金额（note含Pizza credits bought且condition1为confirmed）
• 符合条件的单笔平均购买金额
• 首次购买日期（note含Pizza credits bought的最早交易时间）
• 末次购买日期（note含Pizza credits bought的最晚交易时间）
• 用户最后一次交易的余额（previous_balance）
• 用户最后一次交易的券余额（previous_voucher_balance）

此前尝试自连接方式时遇到两个问题：

1. 第一次查询因自连接导致交易行重复关联，total_bought的SUM计算结果错误
2. 第二次查询因语法错误（join语句后多了分号），触发ERROR: column "u.customer_id" must appear in the GROUP BY clause or be used in an aggregate function报错

正确SQL写法 #

方法一：先聚合统计，再关联最后交易数据 #

先对用户交易做聚合统计，再左连接筛选出的用户最后一次交易数据，避免行重复导致的统计错误：

-- 先计算用户交易统计指标
WITH user_stats AS (
    SELECT
        customer_id,
        SUM(CASE WHEN note LIKE '%Pizza credits bought%' AND condition1 = 'confirmed' THEN amount ELSE 0 END) AS total_bought,
        AVG(CASE WHEN note LIKE '%Pizza credits bought%' AND condition1 = 'confirmed' THEN amount END) AS avg_bought,
        MIN(CASE WHEN note LIKE '%Pizza credits bought%' THEN date_time END) AS first_purchased_date,
        MAX(CASE WHEN note LIKE '%Pizza credits bought%' THEN date_time END) AS last_purchased_date
    FROM pizza_transactions
    GROUP BY customer_id
),
-- 筛选用户最后一次交易的余额数据
last_transaction AS (
    SELECT
        customer_id,
        previous_balance,
        previous_voucher_balance
    FROM (
        SELECT
            customer_id,
            previous_balance,
            previous_voucher_balance,
            ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY date_time DESC) AS rn
        FROM pizza_transactions
    ) t
    WHERE rn = 1
)
-- 关联统计结果和最后交易数据
SELECT
    us.customer_id,
    us.total_bought,
    us.avg_bought,
    us.first_purchased_date,
    us.last_purchased_date,
    lt.previous_balance AS last_balance,
    lt.previous_voucher_balance AS last_voucher_balance
FROM user_stats us
LEFT JOIN last_transaction lt ON us.customer_id = lt.customer_id;

方法二：单查询结合窗口函数 #

利用窗口函数直接在聚合查询中获取最后一次交易的余额，无需额外关联：

SELECT DISTINCT
    customer_id,
    SUM(CASE WHEN note LIKE '%Pizza credits bought%' AND condition1 = 'confirmed' THEN amount ELSE 0 END) OVER (PARTITION BY customer_id) AS total_bought,
    AVG(CASE WHEN note LIKE '%Pizza credits bought%' AND condition1 = 'confirmed' THEN amount END) OVER (PARTITION BY customer_id) AS avg_bought,
    MIN(CASE WHEN note LIKE '%Pizza credits bought%' THEN date_time END) OVER (PARTITION BY customer_id) AS first_purchased_date,
    MAX(CASE WHEN note LIKE '%Pizza credits bought%' THEN date_time END) OVER (PARTITION BY customer_id) AS last_purchased_date,
    FIRST_VALUE(previous_balance) OVER (PARTITION BY customer_id ORDER BY date_time DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_balance,
    FIRST_VALUE(previous_voucher_balance) OVER (PARTITION BY customer_id ORDER BY date_time DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_voucher_balance
FROM pizza_transactions;

结果说明 #

执行上述SQL后，将得到符合需求的用户聚合统计：

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