PostgreSQL视图补全segments表缺失segmentNo行的实现问询

完全可以实现这个需求。PostgreSQL的视图本质是基于SQL查询的虚拟表，支持复杂的查询逻辑生成动态结果，刚好能用来补全缺失数据并展示。

具体视图创建SQL #

CREATE OR REPLACE VIEW segments_completed AS
WITH file_max_segments AS (
    -- 获取每个fileId对应的最大segmentNo
    SELECT fileId, MAX(segmentNo) AS max_segment
    FROM segments
    GROUP BY fileId
),
all_segments AS (
    -- 为每个fileId生成从0到max_segment的完整segmentNo序列
    SELECT 
        fs.fileId,
        generate_series(0, fs.max_segment) AS segmentNo
    FROM file_max_segments fs
)
-- 左连接原表，补全缺失行的字段为NULL
SELECT 
    asg.segmentNo,
    asg.fileId,
    s.c1,
    s.c2,
    s.c3
FROM all_segments asg
LEFT JOIN segments s 
    ON asg.fileId = s.fileId 
    AND asg.segmentNo = s.segmentNo
ORDER BY asg.fileId, asg.segmentNo;

逻辑说明 #

1. file_max_segments CTE：按fileId分组统计每个文件的最大segmentNo，确定需要补全的序列范围。
2. all_segments CTE：利用PostgreSQL的generate_series函数，为每个fileId生成从0到其最大segmentNo的所有整数，得到完整的segmentNo列表。
3. 左连接原表：将完整序列与原segments表关联，存在匹配的行保留c1/c2/c3的值，无匹配的行这些字段自动为NULL，最后按fileId和segmentNo排序得到目标结果。

这个视图每次被查询时都会动态执行上述逻辑，生成补全后的数据集，完全满足需求。

内容的提问来源于stack exchange，提问作者A. Geisler