如何用BigQuery查询连续5天Value为100的客户？

示例数据集 #

BigQuery查询语句 #

根据数据特点提供两种方案：

方案1：假设每个客户每天都有记录

如果数据中每个客户的日期连续无缺失，用这个方案更简洁：

WITH consecutive_groups AS (
  SELECT
    Customer,
    Date,
    Value,
    -- 遇到非100的记录时组ID递增，将连续的100归为同一组
    SUM(CASE WHEN Value = 100 THEN 0 ELSE 1 END) OVER (PARTITION BY Customer ORDER BY Date) AS group_id
  FROM
    `your-project.your-dataset.your-table` -- 替换为你的表路径
),
group_stats AS (
  SELECT
    Customer,
    COUNT(*) AS consecutive_days
  FROM
    consecutive_groups
  WHERE
    Value = 100
  GROUP BY
    Customer, group_id
)
SELECT DISTINCT Customer
FROM group_stats
WHERE consecutive_days >= 5;

方案2：严格检查日期连续性（支持日期缺失场景）

如果数据中可能存在客户某天无记录的情况，用这个方案确保是实际连续的日期：

WITH daily_check AS (
  SELECT
    Customer,
    Date,
    Value,
    -- 判断当前记录是否是连续100序列的起点
    CASE 
      WHEN Value = 100 
        AND (LAG(Value) OVER (PARTITION BY Customer ORDER BY Date) != 100 
             OR DATE_DIFF(Date, LAG(Date) OVER (PARTITION BY Customer ORDER BY Date), DAY) != 1)
      THEN 1 
      ELSE 0 
    END AS group_start
  FROM
    `your-project.your-dataset.your-table` -- 替换为你的表路径
),
consecutive_groups AS (
  SELECT
    Customer,
    Date,
    Value,
    SUM(group_start) OVER (PARTITION BY Customer ORDER BY Date) AS group_id
  FROM
    daily_check
  WHERE
    Value = 100
),
group_stats AS (
  SELECT
    Customer,
    COUNT(*) AS consecutive_days
  FROM
    consecutive_groups
  GROUP BY
    Customer, group_id
)
SELECT DISTINCT Customer
FROM group_stats
WHERE consecutive_days >= 5;

逻辑说明 #

1. 分组标记：通过窗口函数为连续的Value=100记录分配相同组ID，遇到中断（非100或日期不连续）时组ID递增。
2. 统计连续天数：按客户和组ID分组，统计每组的记录数即连续天数。
3. 筛选目标客户：去重后保留连续天数≥5的客户，最终得到a、c、d这类符合要求的结果。

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