sql - 如何在 SQL 查询中聚合两类值？

Question

我有一个包含间隔的表:

CREATE TABLE tbl (
    user_id: INTEGER,
    start: TIMESTAMP,
    end: TIMESTAMP,
    billable: BOOLEAN
);

我想创建一个如下所示的 View :

user_id | billable_time | unbillable_time

我可以使用这样的查询得到这两个中的一个:

SELECT user_id, sum(end - start) AS billable_time WHERE billable = TRUE GROUP BY user_id;

但是，我需要两列，不可计费时间是等效的:

SELECT user_id, sum(end - start) AS unbillable_time WHERE billable = FALSE GROUP BY user_id;

如何将这两个值放入一个查询中？

Answer 1

select user_id, sum(case billable when 1 then (end - start) end) as can_bill,
  sum(case billable when 0 then (end - start) end) as unbillable