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。一个约束是status5个可能的单词中的任何一个，但每个日期可能没有每个状态之一。另外，我希望每天总计所有状态。因此，所需的结果将如下所示：

|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专家，但尝试在数据库视图中执行此操作，因此不必在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 ...表达式即可。

演示在这里