如何计算PostgreSQL中ltree字段所属系列的最后连续最大值？

直接查询指定series_id的连续最大值 #

针对单个指定的series_id，可以通过以下SQL直接计算last_contiguous_max：

WITH parsed AS (
  SELECT
    (subpath(id, -1)::text)::int AS num
  FROM wbs_numbers
  WHERE series_id = 1 -- 替换为你需要查询的series_id
),
continuous_check AS (
  SELECT
    num,
    num - ROW_NUMBER() OVER (ORDER BY num) AS gap_group
  FROM parsed
)
SELECT COALESCE(MAX(num) FILTER (WHERE gap_group = 0), 0) AS last_contiguous_max
FROM continuous_check;

逻辑说明 #

1. parsed CTE：从ltree类型的id中提取最后一段节点，转换为文本后再转为整数类型的num。
2. continuous_check CTE：通过num减去其在有序序列中的行号，标记连续数字组——从1开始的连续序列，这个差值始终为0；出现间隙时，差值会发生变化。
3. 最终筛选出差值为0的最大num，即为从起始点开始的最长连续序列的最大值。

批量计算所有series_id的连续最大值 #

如果需要一次性计算所有series_id对应的last_contiguous_max，可以使用以下SQL：

WITH parsed AS (
  SELECT
    series_id,
    (subpath(id, -1)::text)::int AS num
  FROM wbs_numbers
),
continuous_check AS (
  SELECT
    series_id,
    num,
    num - ROW_NUMBER() OVER (PARTITION BY series_id ORDER BY num) AS gap_group
  FROM parsed
)
SELECT
  series_id,
  COALESCE(MAX(num) FILTER (WHERE gap_group = 0), 0) AS last_contiguous_max
FROM continuous_check
GROUP BY series_id;

维护缓存表优化查询性能 #

由于wbs_numbers的记录只会新增、不会删除或修改，连续岛只会扩展不会缩小，我们可以创建一张缓存表存储计算结果，避免重复计算：

1. 创建缓存表 #

CREATE TABLE series_contiguous (
  series_id INT PRIMARY KEY,
  last_contiguous_max INT NOT NULL DEFAULT 0
);

2. 初始化缓存数据 #

首次运行以下SQL，将现有数据的计算结果写入缓存表：

INSERT INTO series_contiguous (series_id, last_contiguous_max)
WITH parsed AS (
  SELECT
    series_id,
    (subpath(id, -1)::text)::int AS num
  FROM wbs_numbers
),
continuous_check AS (
  SELECT
    series_id,
    num,
    num - ROW_NUMBER() OVER (PARTITION BY series_id ORDER BY num) AS gap_group
  FROM parsed
)
SELECT
  series_id,
  COALESCE(MAX(num) FILTER (WHERE gap_group = 0), 0)
FROM continuous_check
GROUP BY series_id
ON CONFLICT (series_id) DO UPDATE SET last_contiguous_max = EXCLUDED.last_contiguous_max;

3. 创建触发器自动更新缓存 #

当wbs_numbers插入新记录时，自动更新缓存表：

触发器函数

CREATE OR REPLACE FUNCTION update_contiguous_max()
RETURNS TRIGGER AS $$
DECLARE
  current_max INT;
BEGIN
  SELECT last_contiguous_max INTO current_max
  FROM series_contiguous
  WHERE series_id = NEW.series_id;

  -- 若该series_id无缓存记录，初始化
  IF current_max IS NULL THEN
    INSERT INTO series_contiguous (series_id, last_contiguous_max)
    VALUES (NEW.series_id, (subpath(NEW.id, -1)::text)::int);
  ELSE
    -- 新插入的num是当前连续最大值+1，说明连续岛扩展，更新缓存
    IF (subpath(NEW.id, -1)::text)::int = current_max + 1 THEN
      UPDATE series_contiguous
      SET last_contiguous_max = current_max + 1
      WHERE series_id = NEW.series_id;
    END IF;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

创建触发器

CREATE TRIGGER after_wbs_insert
AFTER INSERT ON wbs_numbers
FOR EACH ROW
EXECUTE FUNCTION update_contiguous_max();

4. 查询缓存数据 #

后续查询直接从缓存表获取，性能大幅提升：

SELECT last_contiguous_max FROM series_contiguous WHERE series_id = 1;

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