SQL 查询将分组结果作为单行返回

Question

如果我有一个像这样的工作表:

|id|created_at  |status    |
----------------------------
|1 |01-01-2015  |error     |
|2 |01-01-2015  |complete  |
|3 |01-01-2015  |error     |
|4 |01-02-2015  |complete  |
|5 |01-02-2015  |complete  |
|6 |01-03-2015  |error     |
|7 |01-03-2015  |on hold   |
|8 |01-03-2015  |complete  |

我想要一个按日期对它们进行分组并计算每个状态的出现次数和该日期的总状态的查询。

SELECT created_at status, count(status), created_at 
FROM jobs 
GROUP BY created_at, status;

这给了我

|created_at  |status    |count|
-------------------------------
|01-01-2015  |error     |2
|01-01-2015  |complete  |1
|01-02-2015  |complete  |2
|01-03-2015  |error     |1
|01-03-2015  |on hold   |1
|01-03-2015  |complete  |1   

我现在想将其压缩为每个 created_at 唯一日期的一行，并为每个 status 使用某种多列布局。一个约束是 status 是 5 个可能的词中的任何一个，但每个日期可能不具有每个状态之一。我也想要每天的所有状态。所以期望的结果看起来像:

|date        |total |errors|completed|on_hold|
----------------------------------------------
|01-01-2015  |3     |2     |1        |null   
|01-02-2015  |2     |null  |2        |null
|01-03-2015  |3     |1     |1        |1

列可以从类似的东西动态构建

SELECT DISTINCT status FROM jobs;

不包含任何此类状态的任何一天的结果为空。我不是 SQL 专家，但我正在尝试在数据库 View 中执行此操作，这样我就不必陷入在 Rails 中执行多个查询的困境。

我正在使用 Postresql，但我想尽量保持 SQL 的直接性。我试图充分理解聚合函数以使用其他一些工具，但没有成功。

Answer 1

以下应该适用于任何 RDBMS:

SELECT created_at, count(status) AS total,
       sum(case when status = 'error' then 1 end) as errors,
       sum(case when status = 'complete' then 1 end) as completed,
       sum(case when status = 'on hold' then 1 end) as on_hold
FROM jobs 
GROUP BY created_at;

查询使用条件聚合 以便透视 分组数据。它假定 status 值是事先已知的。如果您有其他 status 值的情况，只需添加相应的 sum(case ... 表达式即可。

Demo here