如何使用PostgreSQL选择边界值之间的有序子集

给定带有边界标记列的表，需要选择边界值（此处为99）之间的所有连续行，并标记为可疑；边界值之外的行标记为正常。具体场景是：多台设备记录测量数据，设备故障时发送值为99的错误信号，收到该信号后，后续行需标记为可疑，直到再次收到99信号（设备恢复）。

示例数据划分（按设备分组）：

• 设备A：第1-8行正常，第9-14行可疑，第15-17行正常，第18-23行可疑
• 设备B：第24行及以后正常

要求用纯SQL实现，无需游标。

with d as (
select *,
  CASE 
    WHEN v=99 THEN 1
    ELSE 0
  END as mark
FROM test
), 
d2 as (
SELECT *,
  DENSE_RANK() OVER (PARTITION BY device,mark ORDER BY t) as r
FROM d
)
SELECT *
FROM d2
ORDER BY device,t;

该方案未成功，因为DENSE_RANK按device和mark分区，无法区分同一标记（比如mark=0）在不同阶段的分组，无法准确划分两次99之间的可疑区间。

可以通过累计求和窗口函数实现，核心思路是按设备分组，累计计算到当前行为止99出现的次数，根据次数的奇偶性判断是否处于可疑区间：

• 累计次数为偶数（包括0次）：正常区间
• 累计次数为奇数：可疑区间

完整SQL代码：

SELECT
  device,
  t,
  v,
  CASE
    -- 累计99出现次数为奇数时，标记为可疑
    WHEN SUM(CASE WHEN v = 99 THEN 1 ELSE 0 END) OVER (
      PARTITION BY device 
      ORDER BY t 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) % 2 = 1 THEN '可疑'
    ELSE '正常'
  END AS status
FROM test
ORDER BY device, t;

1. 累计计数：使用SUM(...) OVER (PARTITION BY device ORDER BY t)按设备分组、按时间排序，累计计算当前行及之前所有行中99出现的次数。
2. 奇偶判断：累计次数为奇数时，说明当前行处于第一次99之后、第二次99之前的区间，也就是设备故障后的可疑阶段；偶数则处于正常阶段（包括未出现过99，或已恢复正常的区间）。
3. 边界自定义：如果需要单独标记99本身的行（比如标记为"故障触发"），可以在CASE中额外添加WHEN v=99 THEN '故障触发'的分支，按需调整即可。

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