在PostgreSQL 13.7中统计时序数据状态未变更的连续天数

我有一个包含date、item、status三列的时序表，样例数据如下：

需要按item分组，获取每个item的最新日期、当前状态，以及当前状态连续未变更的天数，期望输出如下：

我尝试了以下SQL语句，能获取最新日期、item和当前状态，但无法正确统计当前状态的连续天数：

WITH CTE AS (
  SELECT 
    item, 
    date, 
    status, 
    LAG(status) OVER (PARTITION BY item ORDER BY date) AS prev_status, 
    ROW_NUMBER() OVER (PARTITION BY item ORDER BY date DESC) AS rn
  FROM 
    schema.table
)
SELECT 
  MAX(date) AS latest_date, 
  item, 
  status AS current_status, 
  SUM(CASE WHEN prev_status = status THEN 0 ELSE 1 END) 
    OVER (PARTITION BY item ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS number_of_days
FROM 
  CTE 
WHERE 
  rn = 1 
GROUP BY
item, status, prev_status, date
ORDER BY 
  item

解决方案 #

要正确统计每个item当前状态的连续天数，核心是先识别每个item的状态变更节点，再计算最新状态段的天数，具体实现如下：

WITH status_groups AS (
  SELECT
    item,
    date,
    status,
    -- 标记状态变更点：当前状态与前一天不同时，生成新分组ID
    SUM(CASE WHEN LAG(status) OVER (PARTITION BY item ORDER BY date) = status THEN 0 ELSE 1 END) 
      OVER (PARTITION BY item ORDER BY date) AS group_id
  FROM schema.table
),
latest_groups AS (
  SELECT
    item,
    MAX(date) AS latest_date,
    status AS current_status,
    group_id
  FROM status_groups
  GROUP BY item, status, group_id
)
SELECT
  lg.latest_date,
  lg.item,
  lg.current_status,
  -- 计算当前状态的连续天数：最新日期减分组内最小日期加1
  (lg.latest_date - MIN(sg.date) + INTERVAL '1 day')::INT AS number_of_days_on_current
FROM latest_groups lg
JOIN status_groups sg ON lg.item = sg.item AND lg.group_id = sg.group_id
GROUP BY lg.item, lg.latest_date, lg.current_status
ORDER BY lg.item;

逻辑说明 #

1. 状态分组：用LAG()函数获取每个item前一天的状态，当状态发生变化时累加生成group_id，相同连续状态的记录会被分到同一组。
2. 锁定最新状态组：按item、状态和组ID分组，提取每个item最新的状态、日期及对应的组ID。
3. 计算连续天数：关联状态分组表找到当前组的最小日期，用最新日期减去最小日期再加1天（适配连续日期的计数逻辑），转换为整数得到连续天数。

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