PostgreSQL如何复制同列上一行值并按条件实现mov累加计数

存在多个被追踪的样本，这些样本在一天内的不同时段会在不同区域间移动，需要计算字段mov，计数规则如下：

• 当specimen（样本）或day（日期）发生变更时，计数重置为1
• 若period（时段）变化但specimen、day、area（区域）均保持不变，mov取值等于同列上一行的mov值
• 若specimen和day保持不变，仅area发生变化，mov取值等于同列上一行的mov值加1

预期效果参考：

1. 当period变化但specimen、day、area保持不变时，如何复制mov字段的上一行值？
2. 当specimen和day保持不变、仅area变化时，如何对mov值执行累加1操作？

x86_64-pc-linux-gnu架构下的 PostgreSQL 12.11 (Ubuntu 12.11-0ubuntu0.20.04.1)

DROP TABLE IF EXISTS mytable;
CREATE TABLE mytable(
   specimen INTEGER,
   day      INTEGER,
   period   INTEGER,
   area     INTEGER
);


INSERT INTO mytable (specimen,day,period,area)
VALUES 
(1,1,1,1),
(1,1,2,1),
(1,1,3,2),
(1,1,4,2),
(1,1,5,3),
(1,1,6,2),
(1,2,1,2),
(1,2,2,1),
(1,2,3,2),
(1,2,4,3),
(1,2,5,2),
(1,2,6,1),
(2,1,1,3),
(2,1,2,3),
(2,1,3,1);

SELECT
*,
CASE
    WHEN previous_specimen Is NULL THEN 1
    WHEN specimen != previous_specimen THEN 1
    WHEN specimen = previous_specimen AND day != previous_day THEN 1
    -- WHEN specimen = previous_specimen AND day = previous_day AND area = previous_area THEN LAG(mov,1) OVER (ORDER BY specimen,day,period,area) -- **repeat previous value**
    -- WHEN specimen = previous_specimen AND day = previous_day AND area != previous_area THEN LAG(mov,1) OVER (ORDER BY specimen,day,period,area) + 1 -- **add 1 to previous value**    
     ELSE NULL
END AS mov

FROM (
SELECT
*,
LAG(specimen,1) OVER (ORDER BY specimen,day,period,area) previous_specimen,
LAG(day,1) OVER (ORDER BY specimen,day,period,area) previous_day,
LAG(area,1) OVER (ORDER BY specimen,day,period,area) previous_area
FROM mytable
) t1;

原有写法无法生效的核心原因是窗口函数不能嵌套引用自身正在计算的mov字段，无需使用递归，通过标记区域变更次数+分组累计求和即可实现需求，逻辑完全匹配规则：

• 按specimen、day做分区，天然实现样本/日期变更时计数重置
• 按period排序保证时间顺序正确
• 逐行判断当前行相对上一行是否发生区域变更，发生则记1，未发生则记0，分区首行默认记1
• 对分区内的变更标记做累计求和，即可得到最终mov值：同区域跨时段时标记为0，累计值不变；换区域时标记为1，累计值加1

可直接运行的代码如下：

SELECT 
  specimen,
  day,
  period,
  area,
  SUM(area_change_flag) OVER (
    PARTITION BY specimen, day 
    ORDER BY period 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS mov
FROM (
  SELECT
    *,
    CASE
      WHEN LAG(area) OVER (PARTITION BY specimen, day ORDER BY period) IS NULL THEN 1
      WHEN area != LAG(area) OVER (PARTITION BY specimen, day ORDER BY period) THEN 1
      ELSE 0
    END AS area_change_flag
  FROM mytable
) t
ORDER BY specimen, day, period;

运行结果与示例预期完全一致：

该写法完全兼容PostgreSQL 12版本，执行效率高于递归写法。

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