如何计算特定列中连续零的平均值？求千万级数据通用方案

问题背景 #

你提供的数据集如下（按a、b分组）：

需求是按a、b分组，计算列c中连续零组的平均长度：公式为零的总数 ÷ 连续零的组数。示例里零总数是10，连续零组共3组（前5个零、第7个零、最后4个零），最终结果约为3.33。

千万级数据的通用解决方案 #

针对千万级规模的数据，必须选择可并行、高性能的处理方式，这里推荐用支持窗口函数的SQL引擎（比如Spark SQL、BigQuery、PostgreSQL、Snowflake等）——这类引擎能利用分布式计算能力轻松处理大数据量，窗口函数更是解决连续分组场景的最优工具。

实现思路（分步拆解）

1. 标记连续零的分组：用窗口函数给每个连续的零序列分配唯一ID。逻辑是：当当前行c=0且前一行c≠0时，开启一个新组；如果当前行c≠0，则不参与零组标记。
2. 统计每组零的数量：按a、b和零组ID分组，统计每组的零个数。
3. 计算平均长度：按a、b分组，用总零数除以零组的数量，得到最终的平均值。

完整SQL代码

WITH zero_groups AS (
    SELECT
        a,
        b,
        c,
        -- 生成连续零的组ID：每次遇到非零后的第一个零，组ID加1
        SUM(CASE 
                WHEN c = 0 AND LAG(c, 1, 1) OVER (PARTITION BY a, b ORDER BY d) != 0 THEN 1
                WHEN c != 0 THEN 0
                ELSE 0 
            END) OVER (PARTITION BY a, b ORDER BY d) AS zero_group_id
    FROM your_table
),
zero_group_stats AS (
    SELECT
        a,
        b,
        zero_group_id,
        COUNT(*) AS zero_count
    FROM zero_groups
    WHERE c = 0
    GROUP BY a, b, zero_group_id
)
SELECT
    a,
    b,
    ROUND(SUM(zero_count) / COUNT(zero_group_id), 2) AS h
FROM zero_group_stats
GROUP BY a, b;

方案亮点

• 高效性：窗口函数和分组聚合都是SQL引擎深度优化的操作，支持分布式并行计算，处理千万级数据毫无压力。
• 通用性：几乎所有现代SQL引擎都支持这套语法，不用依赖特定语言或工具。
• 准确性：严格按照“连续零为一组”的规则分组，不会出现统计误差。

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