如何将时间序列中初始连续0值转为null，直至出现非零值？

需求说明 #

原始时间序列数据：

期望输出：将第一个非零值出现之前的所有0替换为NULL，之后的数值保持不变：

现有解法 #

你当前的实现通过累加求和判断是否出现过非零值，逻辑可行但存在局限性：

with  data as (
  select cast('2024-03-01' as date) as day, 0 as value
  union
  select '2024-04-01', 0
  union
  select '2024-05-01', 1
  union
  select '2024-06-01', 0
  union
  select '2024-07-01', 2
),
withsum as (
  SELECT day, value, sum(value) OVER (ORDER BY day) AS cum_amount from data
)
select day,
         value,
         cum_amount,
         case when cum_amount = 0 and value = 0 then null 
              when cum_amount = 0 and value != 0 then 0
              else value end as thevalue
         from withsum

该方法的问题：如果value存在负数，累加和可能在首个非零值前就不为0，导致逻辑错误；同时累加求和在数据量较大时性能成本更高。

更优实现方案 #

方案1：定位首个非零值日期（推荐） #

直接通过窗口函数找到第一个非零值的日期，再判断当前行是否在该日期之前，逻辑直观且性能更优：

WITH data AS (
  SELECT CAST('2024-03-01' AS DATE) AS day, 0 AS value
  UNION ALL
  SELECT '2024-04-01', 0
  UNION ALL
  SELECT '2024-05-01', 1
  UNION ALL
  SELECT '2024-06-01', 0
  UNION ALL
  SELECT '2024-07-01', 2
),
first_non_zero AS (
  SELECT 
    day, 
    value,
    -- 全局窗口获取第一个非零值的日期
    MIN(CASE WHEN value != 0 THEN day END) OVER () AS first_non_zero_day
  FROM data
)
SELECT 
  day,
  CASE 
    -- 日期在首个非零值之前且当前值为0时替换为NULL
    WHEN day < first_non_zero_day AND value = 0 THEN NULL
    ELSE value
  END AS value
FROM first_non_zero
ORDER BY day;

优势：

• 逻辑清晰，直接匹配需求场景
• 不受value数值大小、正负影响，鲁棒性更强
• 计算成本低，MIN()窗口函数的性能远优于累加求和

方案2：统计累计非零值数量 #

通过窗口函数统计到当前行为止的非零值数量，判断是否还未出现过非零值：

WITH data AS (
  SELECT CAST('2024-03-01' AS DATE) AS day, 0 AS value
  UNION ALL
  SELECT '2024-04-01', 0
  UNION ALL
  SELECT '2024-05-01', 1
  UNION ALL
  SELECT '2024-06-01', 0
  UNION ALL
  SELECT '2024-07-01', 2
)
SELECT 
  day,
  CASE 
    -- 累计非零值数量为0且当前值为0时替换为NULL
    WHEN SUM(CASE WHEN value != 0 THEN 1 ELSE 0 END) OVER (ORDER BY day) = 0 AND value = 0 THEN NULL
    ELSE value
  END AS value
FROM data
ORDER BY day;

优势：

• 无需额外CTE，结构更紧凑
• 同样不受value数值影响，逻辑严谨

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