求按月统计消费超20美元的客户数量的正确SQL查询语句

错误查询问题分析 #

Q1 问题 #

where amount>20 过滤的是单笔消费超过20美元的支付记录，后续按月份统计customer_id会重复计算同一个客户当月多笔超20的情况，而且统计的不是“客户当月总消费超20”的数量，完全偏离需求逻辑。

Q2 问题 #

group by 1 按月份分组后，having sum(amount)>20 判断的是整个月份的总消费超过20美元，不是单个客户的月消费总额；同时count(customer_id)统计的是当月所有支付记录关联的客户数（含重复），和需求的“月消费总额超20的客户数量”不匹配。

正确查询语句 #

先按客户+月份维度计算单个客户的月消费总额，筛选出总额超20的记录，再按月份统计符合条件的客户数量：

SELECT
    MONTH(payment_ts) AS "Mon",
    COUNT(DISTINCT customer_id) AS "Total"
FROM (
    SELECT
        customer_id,
        MONTH(payment_ts) AS month,
        SUM(amount) AS monthly_total
    FROM payment
    GROUP BY customer_id, MONTH(payment_ts)
    HAVING SUM(amount) > 20
) AS customer_monthly_summary
GROUP BY month;

另一种基于窗口函数的写法（适用于支持窗口函数的数据库）：

SELECT
    MONTH(payment_ts) AS "Mon",
    COUNT(DISTINCT customer_id) AS "Total"
FROM (
    SELECT
        customer_id,
        payment_ts,
        SUM(amount) OVER (PARTITION BY customer_id, MONTH(payment_ts)) AS monthly_total
    FROM payment
) AS customer_monthly_data
WHERE monthly_total > 20
GROUP BY MONTH(payment_ts);

核心逻辑：先锁定“单个客户单月总消费超20”的有效客户-月份组合，再按月份统计这类客户的数量，用COUNT(DISTINCT)确保每个客户在对应月份只被统计一次。

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