如何按门店统计每月distinct客户到访数？现有SQL方案遇阻

需要统计dim.d_place_of_business（即tp.places）中每个门店每月的独立客户到访数量，尝试了三种SQL写法都没成功，不清楚问题出在哪。

原SQL代码：

SELECT
    DISTINCT tp.place,
    tp.brand,
    SUM (CASE WHEN kr.calendar_key BETWEEN '2024-01-01' AND '2024-01-31' THEN 1 END) as 1st,
    SUM (CASE WHEN kr.calendar_key BETWEEN '2024-02-01' AND '2024-02-29' THEN DISTINCT kr.customer_key END) as 2nd
    SUM (CASE WHEN kr.calendar_key BETWEEN '2024-03-01' AND '2024-03-31' THEN COUNT(DISTINCT kr.customer_key) END) as 3rd,
FROM
    orders.f_receipts kr
    INNER JOIN dim.d_place_of_business tp ON tp.business_key = kr.business_key
WHERE
    ...

期望输出格式：

Place   Jan   Feb   March
nr1     1150  900   1300
nr2     800   990   700
etc.

原SQL的问题分析 #

• 语法错误：THEN DISTINCT kr.customer_key和THEN COUNT(DISTINCT kr.customer_key)都是非法写法，聚合函数和DISTINCT不能放在CASE语句的THEN分支里。
• 逻辑错误：第一个SUM(CASE...)统计的是当月所有到访记录数，不是独立客户数，会重复计算同一客户的多次到访。
• 分组错误：用SELECT DISTINCT代替GROUP BY，无法正确完成聚合统计，应该按tp.place和tp.brand分组。

正确的SQL写法 #

方法1：先分组统计再转宽表（更清晰，适合多月份扩展）

WITH monthly_cust AS (
    SELECT
        tp.place,
        tp.brand,
        DATE_TRUNC('month', kr.calendar_key) AS visit_month,
        COUNT(DISTINCT kr.customer_key) AS distinct_customers
    FROM orders.f_receipts kr
    INNER JOIN dim.d_place_of_business tp 
        ON tp.business_key = kr.business_key
    WHERE kr.calendar_key BETWEEN '2024-01-01' AND '2024-03-31'
        -- 补充其他过滤条件
    GROUP BY tp.place, tp.brand, DATE_TRUNC('month', kr.calendar_key)
)
SELECT
    place,
    brand,
    MAX(CASE WHEN visit_month = '2024-01-01' THEN distinct_customers END) AS Jan,
    MAX(CASE WHEN visit_month = '2024-02-01' THEN distinct_customers END) AS Feb,
    MAX(CASE WHEN visit_month = '2024-03-01' THEN distinct_customers END) AS March
FROM monthly_cust
GROUP BY place, brand
ORDER BY place;

方法2：直接用COUNT(DISTINCT CASE...)（更简洁）

SELECT
    tp.place,
    tp.brand,
    COUNT(DISTINCT CASE WHEN kr.calendar_key BETWEEN '2024-01-01' AND '2024-01-31' THEN kr.customer_key END) AS Jan,
    COUNT(DISTINCT CASE WHEN kr.calendar_key BETWEEN '2024-02-01' AND '2024-02-29' THEN kr.customer_key END) AS Feb,
    COUNT(DISTINCT CASE WHEN kr.calendar_key BETWEEN '2024-03-01' AND '2024-03-31' THEN kr.customer_key END) AS March
FROM orders.f_receipts kr
INNER JOIN dim.d_place_of_business tp 
    ON tp.business_key = kr.business_key
WHERE kr.calendar_key BETWEEN '2024-01-01' AND '2024-03-31'
    -- 补充其他过滤条件
GROUP BY tp.place, tp.brand
ORDER BY tp.place;

说明 #

• 两种方法都能实现需求，方法1适合后续扩展更多月份，只需在CTE和主查询中添加对应月份的CASE即可；方法2更紧凑，适合少量月份的统计。
• 若使用MySQL等方言，需调整DATE_TRUNC写法，比如用DATE_FORMAT(kr.calendar_key, '%Y-%m-01')代替。

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