sql - 计算 bool 列，并根据 bool 列平均另一列

Question

CREATE TABLE test (
    calculate_time int4 NULL,
    status bool NULL
);

INSERT INTO test (calculate_time,status) VALUES 
(10,true)
,(15,true)
,(20,true)
,(20,true)
,(5,false)
,(10,false)
,(15,false)
,(100,NULL)
,(200,NULL)
,(300,NULL)
;

对于此查询，它平均所有 calculated_time 值。有没有一种方法可以告诉它只有状态 = true 的平均状态？我尝试添加一个 where 子句，但会使失败和暂停的结果为 0。

select 
    avg(calculate_time) as cal_time,
    count(case when status = true then 1 end) as completed,
    count(case when status = false then 1 end) as failed,
    count(case when status is null then 1 end) as suspended
from test;

Answer 1

您似乎了解条件聚合的概念。您也可以使用 CASE 表达式来计算平均值，就像您对选择中的其他项所做的那样:

select 
    avg(case when status then calculate_time end) as cal_time,
    count(case when status then 1 end) as completed,
    count(case when not status then 1 end) as failed,
    count(case when status is null then 1 end) as suspended
from test;

之所以可行，是因为 AVG 函数与大多数其他聚合函数一样，会忽略 NULL 值。因此，status 不正确的记录，它们的 calculate_time 值将被有效地忽略，并且不会影响总体平均值。

其他注意事项:您可以直接在 Postgres 查询中使用 bool 值，而无需将它们与 true 进行比较。也就是说，以下两个 CASE 表达式是等价的，第二个表达式不太简洁:

avg(case when status = true then calculate_time end) as cal_time,
avg(case when status then calculate_time end) as cal_time,