在BigQuery中基于连续相同值行生成条件列的实现方案

现有数据表table1结构如下（已按date排序）：

id        date        value
aaaaa     2021-01-01  true
aaaaa     2021-01-02  true
aaaaa     2021-01-03  false
aaaaa     2021-01-04  false
aaaaa     2021-01-05  false
aaaaa     2021-01-06  true
...
aaaaa     2021-12-31  false
aaaab     2021-01-01  true
...
zzzzz     2021-12-31  false

字段说明：

• id：字符串类型
• date：日期范围为2021-01-01至2021-12-31
• value：布尔类型，取值为true或false

需要生成仅包含id和passed两列的新数据表table2，规则：

• id与table1中的id一致
• passed为布尔类型：按date排序后，若某个id存在连续3行value为false，则passed为false，否则为true

理想的table2结构：

id      passed
aaaaa   false
aaaab   true
...
zzzzz   true

用户尝试的SQL语句报错（提示无法识别窗口别名i.date），且未满足需求：

SELECT id,
       value,
       COUNT(*) AS cnt
  FROM (SELECT t.*,
               ROW_NUMBER() OVER i.date AS time1,
               ROW_NUMBER() OVER (PARTITION BY i.id, i.value ORDER BY i.date) AS time2
          FROM table1 t
       ) t
GROUP BY id, value, (time1 - time2)

1. 原语句错误分析 #

原语句窗口函数语法错误：ROW_NUMBER() OVER i.date不符合SQL规范，窗口子句需用PARTITION BY和ORDER BY明确声明；同时逻辑仅统计连续相同值的行数，未针对连续3个false做判断，也未聚合出每个id的最终passed结果。

2. 标准实现方案 #

通过窗口函数标记连续false的分组，统计分组行数后判断每个id是否符合条件：

WITH consecutive_false AS (
    SELECT 
        id,
        value,
        -- 为连续false分组：遇到true时分组号递增，连续false会归为同一组
        SUM(CASE WHEN value = true THEN 1 ELSE 0 END) OVER (PARTITION BY id ORDER BY date) AS group_id
    FROM table1
),
false_group_counts AS (
    SELECT 
        id,
        COUNT(*) AS false_consecutive_count
    FROM consecutive_false
    WHERE value = false
    GROUP BY id, group_id
)
SELECT 
    DISTINCT t1.id,
    -- 存在连续3个及以上false则passed为false，否则为true
    CASE WHEN EXISTS (
        SELECT 1 
        FROM false_group_counts fgc 
        WHERE fgc.id = t1.id AND fgc.false_consecutive_count >=3
    ) THEN false ELSE true END AS passed
FROM table1 t1
ORDER BY t1.id;

3. 逻辑说明 #

1. consecutive_false CTE：对每个id按日期排序，每遇到value=true就递增分组号，让连续的false归为同一个分组。
2. false_group_counts CTE：统计每个id下，每个连续false分组的行数。
3. 最终查询：判断每个id是否存在行数≥3的false分组，用DISTINCT确保每个id只输出一行。

4. 简化版（支持LAG/LEAD的数据库） #

如果数据库支持LAG函数，可直接检查某行与前两行是否均为false：

SELECT 
    DISTINCT id,
    CASE WHEN EXISTS (
        SELECT 1 
        FROM table1 t2
        WHERE t2.id = t1.id
        AND t2.value = false
        AND LAG(t2.value,1) OVER (PARTITION BY t2.id ORDER BY t2.date) = false
        AND LAG(t2.value,2) OVER (PARTITION BY t2.id ORDER BY t2.date) = false
    ) THEN false ELSE true END AS passed
FROM table1 t1
ORDER BY id;

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