如何从column_a生成zero_count：统计非0值前的连续前置0数量

针对需求中要求的zero_count列生成逻辑，我们可以通过窗口函数分组+条件计数的方式实现，核心思路是先将数据按非0值分割为独立分组，再在每个分组内统计前置连续0的数量。

核心逻辑拆解 #

• 第一步：生成分组标识，将连续的0和紧随其后的非0值划分为同一组（每次遇到非0值时分组ID递增）
• 第二步：在每个分组内，统计当前行之前所有0的数量
• 第三步：对0值行直接返回0，非0值行返回统计得到的前置0数量

SQL实现代码（以Spark SQL/PostgreSQL为例） #

假设数据表名为data_table，id为有序主键，执行以下SQL即可得到目标结果：

WITH grouped_data AS (
    SELECT 
        id,
        column_a,
        -- 生成分组ID：每遇到非0值，分组ID+1
        SUM(CASE WHEN column_a != 0 THEN 1 ELSE 0 END) OVER (ORDER BY id) AS group_id
    FROM data_table
)
SELECT 
    id,
    column_a,
    CASE 
        WHEN column_a != 0 THEN 
            -- 统计当前分组内、当前行之前的0的数量
            COUNT(CASE WHEN column_a = 0 THEN 1 END) OVER (
                PARTITION BY group_id 
                ORDER BY id 
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            )
        ELSE 0 
    END AS zero_count
FROM grouped_data
ORDER BY id;

结果验证 #

将示例数据代入上述SQL，会得到与需求完全匹配的输出：

+---+--------+----------+
| id|column_a|zero_count|
+---+--------+----------+
|  1|       0|         0|
|  2|       0|         0|
|  3|       0|         0|
|  4|       1|         3|
|  5|       0|         0|
|  6|       0|         0|
|  7|       0|         0|
|  8|       0|         0|
|  9|       1|         4|
| 10|       0|         0|
+---+--------+----------+

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