如何用BigQuery SQL计算每月首尾客户数及新增客户数？

首先明确需求逻辑：

• 月初客户数(start_quantity)：统计目标月份第一天时，该时刻前2个月内有成交记录的组织数量（如2020年1月的月初客户数，需统计2019年11月、12月有成交的组织）
• 月末客户数(end_quantity)：统计目标月份最后一天时，该时刻前2个月内有成交记录的组织数量（如2020年1月的月末客户数，需统计2019年12月、2020年1月有成交的组织）
• 当月新增客户(new_clients)：首次成交记录落在目标月份的组织数量
• 流失规则：最后一笔成交距今超3个月视为流失（该规则已隐含在活跃客户统计逻辑中，即仅统计前2个月有成交的组织）

错误SQL的问题分析 #

你提供的SQL存在以下核心问题：

1. 日期生成逻辑错误：date_array生成的是每个月份往前推2个月的日期，而非需要统计的目标月份序列
2. 关联条件错误：RIGHT JOIN仅关联成交月份等于前2个月的记录，无法覆盖所有在前2个月有成交的组织
3. 过滤条件逻辑偏差：WHERE子句排除了大量符合活跃条件的成交记录
4. 未满足输出字段要求：仅统计了单一客户数字段，未生成需求的四个指标

正确SQL实现 #

WITH org_metrics AS (
  -- 预处理每个组织的首次成交月份及所有成交月份
  SELECT
    organization_id,
    MIN(DATE_TRUNC(won_time, MONTH)) AS first_deal_month,
    ARRAY_AGG(DISTINCT DATE_TRUNC(won_time, MONTH)) AS deal_months
  FROM `deals`
  GROUP BY organization_id
),

month_series AS (
  -- 生成2020-01至今的所有月份序列
  SELECT
    DATE_TRUNC(month, MONTH) AS report_month
  FROM UNNEST(GENERATE_DATE_ARRAY('2020-01-01', CURRENT_DATE(), INTERVAL 1 MONTH)) AS month
)

SELECT
  report_month AS month,
  -- 计算月初客户数：前2个月内有成交的组织
  COUNT(DISTINCT CASE WHEN EXISTS (
    SELECT 1 FROM UNNEST(om.deal_months) dm
    WHERE dm BETWEEN DATE_SUB(report_month, INTERVAL 2 MONTH) AND DATE_SUB(report_month, INTERVAL 1 MONTH)
  ) THEN om.organization_id END) AS start_quantity,
  -- 计算月末客户数：前1个月至当月有成交的组织
  COUNT(DISTINCT CASE WHEN EXISTS (
    SELECT 1 FROM UNNEST(om.deal_months) dm
    WHERE dm BETWEEN DATE_SUB(report_month, INTERVAL 1 MONTH) AND report_month
  ) THEN om.organization_id END) AS end_quantity,
  -- 计算当月新增客户：首次成交在当月的组织
  COUNT(DISTINCT CASE WHEN om.first_deal_month = report_month THEN om.organization_id END) AS new_clients
FROM month_series ms
CROSS JOIN org_metrics om
GROUP BY report_month
ORDER BY report_month;

逻辑说明 #

1. org_metrics：聚合每个组织的核心数据，包括首次成交月份和所有成交月份的数组，避免重复计算
2. month_series：生成需要统计的完整月份序列，确保每个月份都有统计结果
3. 主查询通过EXISTS判断组织是否在对应时间区间内有成交，分别计算三个核心指标，最后按月份排序输出

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