PostgreSQL外连接补全空白：如何填充最新已知参数值？

在PostgreSQL数据库中有一对主从表：

• 主表samples：存储带时间戳的样本记录
• 从表sample_values：存储对应样本时间戳下部分参数的数值

当前查询 #

SELECT s.sample_id, s.sample_time, v.parameter_id, v.sample_value
FROM samples s LEFT OUTER JOIN sample_values v ON v.sample_id=s.sample_id
ORDER BY s.sample_id, v.parameter_id;

返回结果（符合预期） #

需求 #

如何编写查询语句，使结果中每个时间戳对应每个参数一行，且sample_value为该参数的最新已知值？示例结果如下：

疑问与函数说明 #

我不确定LAST_VALUE函数是否适用于此场景，它的语法及中文翻译如下：

原语法 #

LAST_VALUE ( expression )  
OVER ( 
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

中文翻译 #

LAST_VALUE(表达式)
OVER (
    [PARTITION BY 分区表达式, ... ]
    ORDER BY 排序表达式 [ASC | DESC], ...
)

解决方案 #

LAST_VALUE完全适用于这个场景，结合笛卡尔积生成全量参数-样本对，再配合窗口函数即可实现需求：

方法一（PostgreSQL 11+，支持IGNORE NULLS） #

WITH all_parameters AS (
    -- 获取所有唯一参数ID
    SELECT DISTINCT parameter_id
    FROM sample_values
),
sample_parameter_pairs AS (
    -- 生成每个样本与每个参数的组合
    SELECT s.sample_id, s.sample_time, p.parameter_id
    FROM samples s
    CROSS JOIN all_parameters p
),
filled_values AS (
    SELECT 
        spp.sample_id,
        spp.sample_time,
        spp.parameter_id,
        -- 按参数分区、时间排序，取当前行及之前最新的非空值
        LAST_VALUE(v.sample_value) OVER (
            PARTITION BY spp.parameter_id
            ORDER BY spp.sample_time
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            IGNORE NULLS
        ) AS sample_value
    FROM sample_parameter_pairs spp
    LEFT JOIN sample_values v 
        ON spp.sample_id = v.sample_id 
        AND spp.parameter_id = v.parameter_id
)
SELECT * FROM filled_values ORDER BY sample_id, parameter_id;

方法二（兼容低版本PostgreSQL） #

如果你的PostgreSQL版本低于11，不支持IGNORE NULLS，可以用分组累加的方式实现：

WITH all_parameters AS (
    SELECT DISTINCT parameter_id
    FROM sample_values
),
sample_parameter_pairs AS (
    SELECT s.sample_id, s.sample_time, p.parameter_id
    FROM samples s
    CROSS JOIN all_parameters p
),
grouped_values AS (
    SELECT 
        spp.*,
        v.sample_value,
        -- 每次遇到非空值时生成新分组
        SUM(CASE WHEN v.sample_value IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY spp.parameter_id
            ORDER BY spp.sample_time
        ) AS value_group
    FROM sample_parameter_pairs spp
    LEFT JOIN sample_values v 
        ON spp.sample_id = v.sample_id 
        AND spp.parameter_id = v.parameter_id
),
filled_values AS (
    SELECT 
        sample_id,
        sample_time,
        parameter_id,
        -- 同分组内取最新的非空值
        MAX(sample_value) OVER (
            PARTITION BY parameter_id, value_group
            ORDER BY sample_time
        ) AS sample_value
    FROM grouped_values
)
SELECT * FROM filled_values ORDER BY sample_id, parameter_id;

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