如何在BigQuery中编写SQL计算每月新增客户数？

需求说明 #

统计每月新增客户数，计算逻辑为：截至当前月的累计去重Buyer_Username数量 - 截至上月的累计去重Buyer_Username数量

第一步：提取年月信息 #

从包含Created_Time（Datetime类型）的Main_table中提取年月信息，SQL如下：

SELECT
  FORMAT_DATE('%Y', Created_Time) AS Year,
  EXTRACT(MONTH FROM Created_Time) AS Month_Number,
  FORMAT_DATE('%B', Created_Time) AS Month,
  Buyer_Username
FROM Main_table

示例数据 #

预期结果 #

计算逻辑 #

2022年6月：截至6月的累计去重用户数(2) - 上月累计(0) = 2
2022年7月：截至7月的累计去重用户数(4) - 截至6月的累计(2) = 2
2022年8月：截至8月的累计去重用户数(5) - 截至7月的累计(4) = 1
2022年9月：截至9月的累计去重用户数(7) - 截至8月的累计(5) = 2
2022年10月：截至10月的累计去重用户数(7) - 截至9月的累计(7) = 0
2022年11月：截至11月的累计去重用户数(8) - 截至10月的累计(7) = 1
2022年12月：截至12月的累计去重用户数(9) - 截至11月的累计(8) = 1
2023年1月：截至1月的累计去重用户数(10) - 截至2022年12月的累计(9) = 1

原SQL问题分析 #

你尝试的SQL存在两个核心问题：

1. BigQuery的窗口函数不支持COUNT(DISTINCT)作为窗口聚合函数；
2. PARTITION BY Year, Month会将每个月的数据单独分区，无法实现跨月份的累计统计，LAG函数的用法也不符合累计去重的逻辑。

正确实现SQL #

方法一：基于用户首次注册月份统计（推荐，性能更优） #

该方法先找出每个用户的首次出现月份，再按月份统计首次出现的用户数，同时补全所有存在数据的月份以显示新增为0的情况：

WITH user_first_month AS (
  -- 找出每个用户的首次出现年月
  SELECT
    Buyer_Username,
    FORMAT_DATE('%Y', MIN(Created_Time)) AS first_year,
    EXTRACT(MONTH FROM MIN(Created_Time)) AS first_month_number,
    FORMAT_DATE('%B', MIN(Created_Time)) AS first_month
  FROM Main_table
  GROUP BY Buyer_Username
),
all_months AS (
  -- 生成所有存在数据的月份列表，确保没有新增的月份也能显示
  SELECT DISTINCT
    FORMAT_DATE('%Y', Created_Time) AS Year,
    EXTRACT(MONTH FROM Created_Time) AS Month_Number,
    FORMAT_DATE('%B', Created_Time) AS Month
  FROM Main_table
)
SELECT
  am.Year,
  am.Month_Number,
  am.Month,
  COUNT(ufm.Buyer_Username) AS New_Customers
FROM all_months am
LEFT JOIN user_first_month ufm
  ON am.Year = ufm.first_year
  AND am.Month_Number = ufm.first_month_number
GROUP BY am.Year, am.Month_Number, am.Month
ORDER BY am.Year, am.Month_Number

方法二：基于累计用户数差值统计 #

该方法先计算每个月的累计去重用户数，再通过LAG函数获取上月累计数，相减得到新增：

WITH monthly_users AS (
  -- 提取年月并去重每个月的用户
  SELECT DISTINCT
    FORMAT_DATE('%Y', Created_Time) AS Year,
    EXTRACT(MONTH FROM Created_Time) AS Month_Number,
    FORMAT_DATE('%B', Created_Time) AS Month,
    Buyer_Username
  FROM Main_table
),
all_months AS (
  -- 生成所有存在数据的月份
  SELECT DISTINCT
    Year,
    Month_Number,
    Month
  FROM monthly_users
),
cumulative_users AS (
  -- 计算截至每个月的累计去重用户数
  SELECT
    am.Year,
    am.Month_Number,
    am.Month,
    (SELECT COUNT(DISTINCT Buyer_Username) 
     FROM monthly_users mu
     WHERE PARSE_DATE('%Y-%m', CONCAT(mu.Year, '-', mu.Month_Number)) 
           <= PARSE_DATE('%Y-%m', CONCAT(am.Year, '-', am.Month_Number))) AS total_cumulative
  FROM all_months am
)
SELECT
  Year,
  Month_Number,
  Month,
  total_cumulative - COALESCE(LAG(total_cumulative) OVER (ORDER BY Year, Month_Number), 0) AS New_Customers
FROM cumulative_users
ORDER BY Year, Month_Number

说明 #

• 方法一通过用户首次出现月份直接统计，避免了重复计算，性能更优；
• 方法二通过累计数差值实现，逻辑更贴近你最初的思路，但需要注意月份的排序和空值处理（用COALESCE处理第一个月的上月累计为0的情况）。

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