如何在Postgres中基于相同ID的不同行进行SQL计算

在AWS的PostgreSQL数据库中创建带计算逻辑的视图时，遇到以下场景：

• 存在多条ID相同但unit、sub_unit不同的数据行
• value_1和value_2在同一ID内的取值始终一致，但部分行中这两个字段为NULL
• 需要基于ID计算sum列，且同一ID的所有行sum值必须相同

输入数据 #

尝试过的无效查询 #

由于value_1和value_2的非空值分布在不同行，直接相乘会返回NULL：

SELECT
    ID,
    unit, sub_unit,
    value_1, value_2,
    value_1 * value_2 AS sum
FROM 
    t1

期望结果 #

将同ID下的NULL字段填充为该ID对应的非空值，再计算sum，最终所有同ID行的sum一致：

可以通过两种简洁的方式实现需求：

方法1：使用窗口函数快速填充非空值 #

利用MAX()窗口函数（因同ID内取值一致，MAX会直接取到该ID对应的非空值），写法简洁高效：

SELECT
    ID,
    unit,
    sub_unit,
    MAX(value_1) OVER (PARTITION BY ID) AS value_1,
    MAX(value_2) OVER (PARTITION BY ID) AS value_2,
    MAX(value_1) OVER (PARTITION BY ID) * MAX(value_2) OVER (PARTITION BY ID) AS sum
FROM t1;

如果需要明确优先取第一个非空值，也可以用FIRST_VALUE()窗口函数：

SELECT
    ID,
    unit,
    sub_unit,
    FIRST_VALUE(value_1) OVER (PARTITION BY ID ORDER BY CASE WHEN value_1 IS NOT NULL THEN 0 ELSE 1 END) AS value_1,
    FIRST_VALUE(value_2) OVER (PARTITION BY ID ORDER BY CASE WHEN value_2 IS NOT NULL THEN 0 ELSE 1 END) AS value_2,
    FIRST_VALUE(value_1) OVER (PARTITION BY ID ORDER BY CASE WHEN value_1 IS NOT NULL THEN 0 ELSE 1 END) *
    FIRST_VALUE(value_2) OVER (PARTITION BY ID ORDER BY CASE WHEN value_2 IS NOT NULL THEN 0 ELSE 1 END) AS sum
FROM t1;

方法2：聚合ID级值后关联查询 #

先通过子查询获取每个ID对应的value_1和value_2非空值，再与原表关联：

SELECT
    t1.ID,
    t1.unit,
    t1.sub_unit,
    agg.value_1,
    agg.value_2,
    agg.value_1 * agg.value_2 AS sum
FROM t1
JOIN (
    SELECT
        ID,
        MAX(value_1) AS value_1,
        MAX(value_2) AS value_2
    FROM t1
    GROUP BY ID
) agg ON t1.ID = agg.ID;

说明 #

• 因同一ID内value_1和value_2取值一致，MAX()或MIN()均可稳定获取非空值
• 上述查询均可直接用于创建视图，只需在开头添加CREATE VIEW view_name AS

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