SQL窗口函数实现排除NULL和重复值的滚动计数方案

方案1：使用支持COUNT(DISTINCT)的窗口函数（最简洁） #

你的业务规则已经保证了不会出现a,b,a这类跨不同值的重复场景，直接用窗口内去重计数即可实现需求：

SELECT 
  *,
  COUNT(DISTINCT val) OVER (
    PARTITION BY id 
    ORDER BY timestamp DESC
  ) AS count
FROM your_table

这个写法和你尝试的初始版本差异很小，仅增加了DISTINCT关键字，就能自动排除已经出现过的非NULLval值，完全匹配你给出的预期结果。

方案2：兼容不支持COUNT(DISTINCT)窗口函数的数据库 #

如果你使用的数据库不支持在窗口函数中使用DISTINCT，可以用标记累加的方式实现：

WITH val_mark AS (
  SELECT 
    *,
    -- 仅当当前val非空，且不等于上一个出现的非空val时标记为1，否则为0
    CASE 
      WHEN val IS NULL THEN 0
      WHEN val = LAG(val) IGNORE NULLS OVER (PARTITION BY id ORDER BY timestamp DESC) THEN 0
      ELSE 1
    END AS inc_flag
  FROM your_table
)
SELECT 
  id, timestamp, val,
  SUM(inc_flag) OVER (PARTITION BY id ORDER BY timestamp DESC) AS count
FROM val_mark

补充说明 #

如果你的数据库不支持LAG函数的IGNORE NULLS参数，可以先给连续同值（中间夹NULL也算同组）的val打分组标记，再对分组号做滚动计数，逻辑和上述写法一致。

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