求助：Snowflake中计算特定值两次出现间隔行数的最简SQL方法

要计算列中值为1的行到下一个值为1的行之间的行数，最简方法是结合**窗口函数LEAD()**或FIRST_VALUE()配合FILTER子句使用，以下是具体实现：

场景1：仅针对值为1的行计算间隔 #

如果只需要统计每个值为1的行到下一个1之间的行数（即中间0的数量），可以用LEAD()获取下一个1的行号，再计算差值：

WITH ordered_data AS (
    -- 先给数据分配行号，确保顺序稳定（替换id为你的实际排序列，比如时间戳）
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM your_table
)
SELECT
    id,
    value,
    -- 下一个1的行号 - 当前行号 -1 = 中间的行数
    LEAD(rn) OVER (ORDER BY rn) FILTER (WHERE value = 1) - rn - 1 AS interval_rows
FROM ordered_data
WHERE value = 1;

场景2：给所有行标记到下一个1的行数 #

如果需要给每一行都显示到最近的下一个1的行数（包括值为0的行），可以用FIRST_VALUE()向前查找下一个1的位置：

WITH ordered_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM your_table
),
next_one_position AS (
    SELECT
        *,
        -- 从当前行开始，找到第一个值为1的行的行号
        FIRST_VALUE(rn) OVER (
            ORDER BY rn 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) FILTER (WHERE value = 1) AS next_rn
    FROM ordered_data
)
SELECT
    id,
    value,
    CASE 
        WHEN next_rn IS NULL THEN NULL -- 最后一个1之后无后续1
        ELSE next_rn - rn - CASE WHEN value = 1 THEN 0 ELSE 1 END
    END AS interval_rows
FROM next_one_position;

关键说明 #

• 必须确保数据有明确的排序逻辑：Snowflake表本身是无序的，所以一定要用ROW_NUMBER()配合实际排序列（如主键、时间戳）生成行号，否则结果会随机。
• FILTER (WHERE value = 1) 是核心：它让窗口函数只关注值为1的行，精准获取下一个1的位置，避免无效计算。

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