使用BigQuery SQL计算Active记录间Column A的去重计数

现有如下结构的表格：

需要计算每两条"Active"记录之间Column A的去重记录数，新增Column C展示该计数，期望输出示例：

请问能否用分析函数实现？曾尝试FIRST_VALUE函数，但它只会关联到首次出现的Active，无法得到正确结果。

可以用分析函数实现，核心思路是先给每条记录标记所属的"Active分组"，再基于分组计算去重计数。

步骤1：标记分组 #

用SUM() OVER()窗口函数，以Column B的顺序为依据，每遇到"Active"就累加分组ID，把两条Active之间的记录归为同一组：

SELECT 
    ColumnA,
    ColumnB,
    SUM(CASE WHEN ColumnA = 'Active' THEN 1 ELSE 0 END) OVER (ORDER BY ColumnB) AS group_id
FROM your_table;

步骤2：计算分组内的去重计数 #

基于分组ID，先统计每个分组的去重值，再关联回原数据生成Column C：

WITH grouped_data AS (
    SELECT 
        ColumnA,
        ColumnB,
        SUM(CASE WHEN ColumnA = 'Active' THEN 1 ELSE 0 END) OVER (ORDER BY ColumnB) AS group_id
    FROM your_table
),
group_distinct_counts AS (
    SELECT 
        group_id,
        COUNT(DISTINCT ColumnA) - 1 AS distinct_count -- 减去分组内的"Active"本身
    FROM grouped_data
    GROUP BY group_id
)
SELECT 
    gd.ColumnA,
    gd.ColumnB,
    CASE 
        WHEN gd.ColumnA = 'Active' THEN 
            -- 第一条Active显示x，后续Active显示前一组的计数
            CASE WHEN gd.group_id = 1 THEN 'x' ELSE gdc.distinct_count END
        -- 最后一组无后续Active的非Active记录显示y
        WHEN gd.group_id = (SELECT MAX(group_id) FROM grouped_data) THEN 'y'
        ELSE gdc.distinct_count 
    END AS ColumnC
FROM grouped_data gd
JOIN group_distinct_counts gdc ON gd.group_id = gdc.group_id
ORDER BY gd.ColumnB;

逻辑说明 #

1. 分组标记：通过累加"Active"出现的次数，将数据划分为[第一个Active, 第二个Active)、[第二个Active, 第三个Active)这类区间组。
2. 去重计数：对每个分组统计ColumnA的去重值，减去"Active"本身，得到两条Active之间的其他去重记录数。
3. 结果映射：根据记录类型和分组位置设置Column C的值，第一条Active显示x，后续Active显示前一组计数，最后一组无后续Active的记录显示y，其余记录显示所在组的计数。

如果数据库支持COUNT(DISTINCT)在窗口函数中使用，可简化为：

SELECT 
    ColumnA,
    ColumnB,
    CASE 
        WHEN ColumnA = 'Active' THEN 
            CASE WHEN SUM(CASE WHEN ColumnA = 'Active' THEN 1 ELSE 0 END) OVER (ORDER BY ColumnB) = 1 THEN 'x' 
                 ELSE LAG(COUNT(DISTINCT ColumnA) OVER (PARTITION BY group_id) - 1) OVER (ORDER BY ColumnB) END
        WHEN group_id = (SELECT MAX(group_id) FROM t) THEN 'y'
        ELSE COUNT(DISTINCT ColumnA) OVER (PARTITION BY group_id) - 1
    END AS ColumnC
FROM (
    SELECT 
        ColumnA,
        ColumnB,
        SUM(CASE WHEN ColumnA = 'Active' THEN 1 ELSE 0 END) OVER (ORDER BY ColumnB) AS group_id
    FROM your_table
) t
ORDER BY ColumnB;

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