请求SQL专家协助：计算过去6个月网站月均活跃客户数

需求明确 #

需要计算过去6个月内的月均活跃客户数，其中活跃客户定义为：在统计当月时，过去12个月内产生过Order Receipt事件（即完成下单）的客户（含未登录访客，用session_id标识为GUEST_xxx格式）。

原SQL的核心问题 #

1. 时间窗口逻辑错误：通过CROSS JOIN UNNEST将每个订单与6个月份偏移量绑定，再用event_date BETWEEN筛选的方式，实际统计的是「该偏移窗口内的下单客户数」，而非「统计当月时过去12个月有下单行为的客户数」，逻辑完全偏离需求。
2. 时区处理不严谨：手动对时间戳减10小时的写法会导致时间边界模糊，无法保证统计周期的准确性。
3. 重复关联导致统计偏差：同一个订单会被匹配到多个month_offset，即便用COUNT(DISTINCT)去重，也违背了「每个统计月独立计算活跃客户」的逻辑。

修正后的SQL方案 #

WITH 
-- 生成过去6个完整自然月的起始/结束日期（例如当前为2024-07，则生成2024-01至2024-06）
date_ranges AS (
    SELECT 
        DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL offset MONTH), MONTH) AS month_start,
        DATE_ADD(DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL offset MONTH), MONTH), INTERVAL 1 MONTH) AS month_end
    FROM UNNEST(GENERATE_ARRAY(1, 6)) AS offset
),
-- 统一整理所有下单客户的标识（区分注册客户/访客）及下单时间
order_customers AS (
    SELECT 
        IFNULL(CAST(customer_id AS STRING), CONCAT('GUEST_', CAST(session_id AS STRING))) AS customer_identifier,
        event_date AS order_time
    FROM online-retail-dbt.retail.tblEvent
    WHERE Event_name = 'Order Receipt'
)
-- 计算每个月的活跃客户数，再求平均值
SELECT 
    ROUND(AVG(active_customers), 2) AS avg_monthly_active_customers
FROM (
    SELECT 
        FORMAT_DATE('%Y-%m', dr.month_start) AS month,
        COUNT(DISTINCT oc.customer_identifier) AS active_customers
    FROM date_ranges dr
    LEFT JOIN order_customers oc
        ON oc.order_time >= TIMESTAMP(DATE_SUB(dr.month_end, INTERVAL 12 MONTH))
        AND oc.order_time < TIMESTAMP(dr.month_end)
    GROUP BY dr.month_start
    ORDER BY dr.month_start
) monthly_active;

修正说明 #

1. 明确统计周期：用DATE_TRUNC生成过去6个完整自然月的范围，避免基于当前日期的零散偏移。
2. 数据分层处理：先单独提取下单客户数据，减少原表扫描次数，提升查询效率。
3. 正确的窗口关联：通过LEFT JOIN将每个统计月与「该月结束前12个月内的下单客户」关联，严格匹配活跃客户的定义。
4. 严谨时区适配：直接基于日期转换时间戳，若需适配特定时区，可在TIMESTAMP转换时指定（例如TIMESTAMP(dr.month_end, 'Asia/Shanghai')）。

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