如何在BigQuery中实现上一年对应月份至当年对应日期的唯一CustomerID统计（按最新日期分组且周期无重叠）

我来帮你搞定这个统计需求！先明确下核心逻辑：你需要针对当年的每一个日期（从当年1月1日到当前日期），统计去年对应月份第一天到该日期范围内的唯一customer_id数量，最终按日期分组输出结果。

实现思路拆解 #

• 生成当年的日期序列：先把当年需要统计的所有日期列出来，从1月1日到当前日期，每个日期作为一个统计周期的结束点。
• 计算每个周期的起始日期：对于每个结束日期，起始点是去年对应月份的第一天——比如2021-01-01对应的起始日期是2020-01-01，2021-02-05对应的起始日期是2020-02-01。
• 关联订单数据并统计去重客户：把生成的日期序列和你的订单表关联，筛选出每个周期内的订单，然后统计唯一客户的数量。

完整BigQuery SQL代码 #

WITH date_range AS (
    -- 生成当年1月1日到当前日期的所有日期
    SELECT date AS current_date
    FROM UNNEST(GENERATE_DATE_ARRAY(
        DATE_TRUNC(CURRENT_DATE(), YEAR),
        CURRENT_DATE(),
        INTERVAL 1 DAY
    )) AS date
),
period_boundaries AS (
    -- 为每个结束日期计算对应的周期起始点
    SELECT
        current_date AS order_date,
        DATE_TRUNC(DATE_SUB(current_date, INTERVAL 1 YEAR), MONTH) AS start_date
    FROM date_range
)
SELECT
    pb.order_date,
    COUNT(DISTINCT o.customer_id) AS count_distinct_CustomerID
FROM period_boundaries pb
LEFT JOIN `your-project.your-dataset.your-table` o
    ON o.order_date BETWEEN pb.start_date AND pb.order_date
GROUP BY pb.order_date
ORDER BY pb.order_date;

代码细节说明 #

• date_range 公共表表达式（CTE）：用GENERATE_DATE_ARRAY生成当年的日期序列，确保不会漏掉任何需要统计的日期。
• period_boundaries CTE：通过DATE_SUB减去一年，再用DATE_TRUNC截断到月份第一天，精准得到每个周期的起始日期。
• 主查询：用LEFT JOIN把周期边界和订单表关联，筛选出每个周期内的订单，然后用COUNT(DISTINCT)统计唯一客户数，最后按日期排序输出。

大数据量场景优化 #

如果你的订单数据量很大，COUNT(DISTINCT)可能会拖慢查询速度。这时候可以用BigQuery的近似去重函数APPROX_COUNT_DISTINCT，它能在保证可接受精度的前提下，大幅提升查询效率：

SELECT
    pb.order_date,
    APPROX_COUNT_DISTINCT(o.customer_id) AS count_distinct_CustomerID
FROM period_boundaries pb
LEFT JOIN `your-project.your-dataset.your-table` o
    ON o.order_date BETWEEN pb.start_date AND pb.order_date
GROUP BY pb.order_date
ORDER BY pb.order_date;

用你的样例数据验证 #

拿你提供的样例数据测试的话：

• 当order_date是2021-01-01时，周期是2020-01-01到2021-01-01，包含客户111、113，计数为2；
• 当order_date是2021-02-01时，周期是2020-02-01到2021-02-01，包含客户112、111、113、115，计数为4；
• 当order_date是2021-03-01时，周期是2020-03-01到2021-03-01，包含客户111、113、115、119，计数为4。

完全符合你的需求逻辑。

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