PostgreSQL中针对含bool字段的表计算相对误差的简便方法

当然有简便的实现方式！针对你给出的表结构和误差计算需求，我推荐两种高效的写法，先来看最简洁的条件聚合方案：

方案一：条件聚合（推荐） #

这种方法通过聚合函数配合CASE WHEN语句，直接在单表查询中提取每个id对应的False和True行的score值，然后计算误差，写法紧凑且性能优异：

SELECT 
    id,
    -- 按照公式计算误差：(False行score - True行score)/True行score
    (MAX(CASE WHEN bool = FALSE THEN score END) - MAX(CASE WHEN bool = TRUE THEN score END)) 
    / MAX(CASE WHEN bool = TRUE THEN score END) AS err
FROM your_table_name  -- 替换成你的实际表名
GROUP BY id;

代码解释： #

• CASE WHEN bool = FALSE THEN score END会筛选出当前id下bool为False的score值，用MAX()（或MIN()，因为每个id只有一行对应值）提取这个唯一值
• 同理得到bool为True的score值
• 按照你指定的公式计算误差err
• 最后按id分组，得到每个id的误差结果

方案二：自连接 #

如果更习惯用连接的思路，也可以通过自连接将同一个id的False行和True行关联起来，再计算误差：

SELECT 
    f.id,
    (f.score - t.score)/t.score AS err
FROM your_table_name f
INNER JOIN your_table_name t 
    ON f.id = t.id 
    AND f.bool = FALSE 
    AND t.bool = TRUE;

代码解释： #

• 将表分别作为f（False行）和t（True行）两个实例
• 通过id关联，同时筛选出f的bool为False，t的bool为True的行
• 直接用关联后的两个score值计算误差

结果验证 #

运行上述任意一种查询，都会得到你期望的结果：

id | err
1 | -0.0625
2 | 0.063829...

两种方案都能准确满足需求，其中条件聚合在表数据量较大时通常性能更优，因为只需要扫描一次表。

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