如何用SQL计算1年滚动周期内的购买人数？

为什么别用Python循环生成SQL？ #

你之前靠循环生成12个查询再拼UNION ALL的做法，不仅写起来繁琐，还会重复扫描表12次，数据量大时效率极低，后续维护也麻烦。直接用纯SQL一次搞定才是最优解，下面针对你用到的BigQuery（从FORMAT_DATE这类函数能判断）给出两种靠谱方案：

方案一：生成统计月份序列 + 关联统计（首推） #

这种方法先自动生成过去12个月的统计月份，再关联原始表计算每个月份对应的滚动12个月去重购买人数，全程只扫描一次表：

WITH monthly_report_dates AS (
  -- 生成过去12个月的每个月第一天（作为统计月份标识）
  SELECT 
    DATE_TRUNC(date, MONTH) AS report_month
  FROM UNNEST(GENERATE_DATE_ARRAY(
    -- 起始日期：当前月份往前推11个月（比如现在是2024年5月，起始就是2023年6月）
    DATETIME_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 11 MONTH),
    -- 结束日期：当前月份
    DATE_TRUNC(CURRENT_DATE(), MONTH),
    -- 步长：1个月
    INTERVAL 1 MONTH
  )) AS date
)
SELECT
  -- 把统计月份格式化为"August 2022"这种样式
  FORMAT_DATE("%B %Y", report_month) AS report_month_name,
  -- 用近似去重计数，大数据量下比COUNT(DISTINCT)快很多，精度也足够
  APPROX_COUNT_DISTINCT(t.NB_buyer) AS rolling_12m_buyers
FROM monthly_report_dates r
-- 关联原始表，筛选每个统计月份对应的过去12个月数据
LEFT JOIN `你的项目名.数据集名.Table` t
ON DATE_TRUNC(t.date, MONTH) BETWEEN DATETIME_SUB(r.report_month, INTERVAL 11 MONTH) 
                                AND r.report_month
-- 加上你的额外筛选条件
WHERE {conditions}
GROUP BY report_month, report_month_name
-- 按月份排序，结果更清晰
ORDER BY report_month;

关键说明：

• GENERATE_DATE_ARRAY自动生成需要统计的12个月份，不用手动写12次重复逻辑
• 仅扫描一次原始表，性能比循环拼UNION ALL提升数倍
• 若数据量很小，也可以把APPROX_COUNT_DISTINCT换成COUNT(DISTINCT t.NB_buyer)

方案二：利用窗口函数（仅适合小数据量） #

如果你的数据量不大，也可以尝试窗口函数方案，但要注意多数SQL引擎不支持窗口内的COUNT(DISTINCT)，BigQuery虽支持，但大数据量下性能不如方案一：

WITH monthly_buyers AS (
  -- 先按月份聚合每个月的购买用户（去重）
  SELECT
    DATE_TRUNC(date, MONTH) AS month,
    ARRAY_AGG(DISTINCT NB_buyer) AS buyers
  FROM `你的项目名.数据集名.Table`
  WHERE {conditions}
    -- 只取需要的时间范围：过去23个月（要计算最近12个月的滚动，得包含前面11个月的数据）
    AND DATE_TRUNC(date, MONTH) >= DATETIME_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 23 MONTH)
  GROUP BY month
),
rolling_buyers AS (
  -- 用窗口函数聚合过去12个月的用户数组，再计算去重数量
  SELECT
    month,
    ARRAY_CONCAT_AGG(buyers) OVER (
      ORDER BY month 
      RANGE BETWEEN INTERVAL 11 MONTH PRECEDING AND CURRENT ROW
    ) AS rolling_12m_buyers_array
  FROM monthly_buyers
)
SELECT
  FORMAT_DATE("%B %Y", month) AS report_month_name,
  -- 计算数组中的去重元素数量
  (SELECT COUNT(DISTINCT buyer) FROM UNNEST(rolling_12m_buyers_array) AS buyer) AS rolling_12m_buyers
FROM rolling_buyers
-- 只取最近12个月的统计结果
WHERE month >= DATETIME_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 11 MONTH)
ORDER BY month;

注意：

这种方法需要先按月份聚合用户，再用窗口函数合并数组，最后统计去重数量，数据量大时会因数组过大导致性能下降，所以优先选方案一。

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