PostgreSQL层级表：用祖先非NULL值填充tier列空缺

现有两张PostgreSQL表：

• 层级表：存储ltree类型的层级路径
• 结果表：包含marker、tier（存在NULL值）、hierarchy字段

需要创建视图，将每个marker对应hierarchy的tier NULL值，替换为最近的非NULL祖先层级值；若整条路径无有效非NULL值，则保留NULL。

输入表数据 #

层级表（hierarchy为ltree类型） #

| hierarchy |
|-----------|
| A         |
| A.X       |
| A.X.Y     |
| A.X.Y.Z   |
| A.B       |
| A.B.C     |
| A.B.C.D   |
| A.B.C.D.E |

结果表 #

| marker | tier | hierarchy |
|:------:|:----:|:---------:|
|    1   |   1  |     A     |
|    1   | NULL |   A.X.Y   |
|    1   |   2  |    A.X    |
|    1   | NULL | A.B.C.D.E |
|    1   |   4  |   A.B.C   |
|    1   | NULL |     A     |
|    2   | NULL |    A.B    |
|    2   | NULL |   A.B.C   |

期望输出视图 #

| marker | tier | hierarchy |
|:------:|:----:|:---------:|
|    1   |   1  |     A     |
|    1   |   2  |   A.X.Y   |
|    1   |   2  |    A.X    |
|    1   |   4  | A.B.C.D.E |
|    1   |   4  |    A.B    |
|    1   |   1  |     A     |
|    2   | NULL |    A.B    |
|    2   | NULL |   A.B.C   |

解决方案SQL #

核心思路是利用PostgreSQL的ltree函数生成每个层级的所有祖先路径，再关联结果表找到最近的非NULLtier值：

CREATE OR REPLACE VIEW filled_tier_view AS
WITH hierarchy_paths AS (
    -- 生成当前层级的所有祖先路径（含自身）
    SELECT
        r.marker,
        r.hierarchy,
        unnest(subpath(r.hierarchy, 0, nlevel(r.hierarchy) - i + 1)) AS ancestor_path,
        r.tier
    FROM
        result_table r
    CROSS JOIN generate_series(1, nlevel(r.hierarchy)) i
),
ranked_ancestors AS (
    -- 按marker分组，筛选有非NULL tier的祖先并按层级从近到远排序
    SELECT
        marker,
        hierarchy,
        rt.tier,
        ROW_NUMBER() OVER (
            PARTITION BY marker, hierarchy
            ORDER BY nlevel(ancestor_path) DESC
        ) AS rn
    FROM
        hierarchy_paths hp
    JOIN
        result_table rt ON hp.marker = rt.marker AND hp.ancestor_path = rt.hierarchy
    WHERE
        rt.tier IS NOT NULL
)
-- 关联原表填充NULL值
SELECT
    rt.marker,
    COALESCE(rt.tier, ra.tier) AS tier,
    rt.hierarchy
FROM
    result_table rt
LEFT JOIN
    ranked_ancestors ra ON rt.marker = ra.marker AND rt.hierarchy = ra.hierarchy AND ra.rn = 1
ORDER BY
    rt.marker, rt.hierarchy;

语句说明 #

1. hierarchy_paths CTE：通过subpath和generate_series生成当前层级的所有祖先路径，例如A.X.Y会生成A.X.Y、A.X、A三个路径。
2. ranked_ancestors CTE：将生成的祖先路径与结果表关联，筛选出有非NULLtier的记录，并用ROW_NUMBER()确保取到最近的祖先（层级最深的那个）。
3. 主查询：用COALESCE替换原结果表的NULL值，若整条路径无有效非NULL值，则保留NULL。

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