使用窗口函数按日期范围过滤：BigQuery客户统计问题

我有一个仅包含Date、Customer两列的销售记录表，同一日期可能存在重复客户（客户可多次购买）。需要生成包含以下列的统计表格：

• Date
• 当日购买的不同客户数量
• 自Date起过去7天内购买的不同客户数量（客户多次出现仅计数一次）

尝试用BigQuery窗口函数实现时执行以下SQL报错：

SELECT 
  DISTINCT Date,
  COUNT(DISTINCT Customer) AS test_day,
  COUNT(DISTINCT Customer) OVER (ORDER BY Date RANGE BETWEEN INTERVAL 6 DAY PRECEDING  AND CURRENT ROW) AS test_week

from table
GROUP BY 1

报错信息：

ORDER BY key must be numeric in a RANGE-based window with OFFSET
PRECEDING or OFFSET FOLLOWING boundaries, but has type DATE AND Window
ORDER BY is not allowed if DISTINCT is specified

示例测试数据：

SELECT DATE('2024-08-08') AS date, 'customer1' AS customer UNION ALL
SELECT DATE('2024-08-07'), 'customer1' UNION ALL
SELECT DATE('2024-08-06'), 'customer2' UNION ALL
SELECT DATE('2024-08-05'), 'customer3' UNION ALL
SELECT DATE('2024-08-04'), 'customer4' UNION ALL
SELECT DATE('2024-08-03'), 'customer5' UNION ALL
SELECT DATE('2024-08-02'), 'customer6' UNION ALL
SELECT DATE('2024-08-01'), 'customer7' UNION ALL
SELECT DATE('2024-08-01'), 'customer8' UNION ALL
SELECT DATE('2024-08-01'), 'customer8'

另外，完成基础统计后，还需添加带过滤条件的统计列（如当日及近7天法国籍客户数），希望通过窗口函数实现而非逐字段编写。

核心问题分析 #

你的SQL报错有两个关键原因：

1. BigQuery中使用RANGE BETWEEN INTERVAL时，窗口排序键必须是数值类型，不能直接用DATE类型；
2. BigQuery窗口函数不支持COUNT(DISTINCT)语法。

基础统计实现 #

要实现需求，先对数据去重（每个日期-客户仅保留一条记录），再通过数值化日期+数组聚合去重的方式实现滚动统计：

WITH deduplicated_data AS (
  -- 去重：消除同一日期同一客户的重复购买记录
  SELECT DISTINCT Date, Customer
  FROM your_table_name
),
daily_unique AS (
  -- 计算每日去重客户数
  SELECT
    Date,
    COUNT(Customer) AS daily_unique_customers
  FROM deduplicated_data
  GROUP BY Date
)
SELECT
  d.Date,
  d.daily_unique_customers,
  -- 计算过去7天去重客户数：用数组聚合窗口内客户，再去重计数
  ARRAY_LENGTH(ARRAY(
    SELECT DISTINCT cust
    FROM UNNEST(ARRAY_AGG(Customer) OVER (
      ORDER BY UNIX_DATE(Date)
      RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
    )) cust
  )) AS rolling_7d_unique_customers
FROM deduplicated_data
RIGHT JOIN daily_unique d ON deduplicated_data.Date = d.Date
GROUP BY d.Date, d.daily_unique_customers
ORDER BY d.Date DESC;

关键说明：

• UNIX_DATE(Date)将日期转换为数值（自1970-01-01以来的天数），满足RANGE窗口对数值类型的要求；
• ARRAY_AGG收集窗口内所有客户，再通过UNNEST+SELECT DISTINCT去重，最后用ARRAY_LENGTH得到去重后的客户数量。

带过滤条件的扩展实现 #

如果需要添加过滤条件（如法国籍客户），可以通过条件聚合在窗口函数中实现，无需重复编写逻辑：

假设存在customers表包含Customer和Country字段，修改后的SQL如下：

WITH deduplicated_data AS (
  SELECT DISTINCT 
    s.Date, 
    s.Customer,
    c.Country
  FROM your_sales_table s
  LEFT JOIN customers c ON s.Customer = c.Customer
),
daily_stats AS (
  -- 计算当日总去重客户数、法国籍去重客户数
  SELECT
    Date,
    COUNT(Customer) AS daily_total_unique,
    COUNT(CASE WHEN Country = 'France' THEN Customer END) AS daily_france_unique
  FROM deduplicated_data
  GROUP BY Date
)
SELECT
  d.Date,
  d.daily_total_unique,
  d.daily_france_unique,
  -- 过去7天总去重客户数
  ARRAY_LENGTH(ARRAY(
    SELECT DISTINCT cust
    FROM UNNEST(ARRAY_AGG(Customer) OVER (
      ORDER BY UNIX_DATE(Date)
      RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
    )) cust
  )) AS rolling_7d_total_unique,
  -- 过去7天法国籍去重客户数：仅聚合符合条件的客户再去重计数
  ARRAY_LENGTH(ARRAY(
    SELECT DISTINCT cust
    FROM UNNEST(ARRAY_AGG(CASE WHEN Country = 'France' THEN Customer END) OVER (
      ORDER BY UNIX_DATE(Date)
      RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
    )) cust
    WHERE cust IS NOT NULL
  )) AS rolling_7d_france_unique
FROM deduplicated_data
RIGHT JOIN daily_stats d ON deduplicated_data.Date = d.Date
GROUP BY d.Date, d.daily_total_unique, d.daily_france_unique
ORDER BY d.Date DESC;

测试结果验证 #

用你提供的示例数据（假设所有客户非法国籍），基础统计的结果如下：

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