将分组结果作为单行返回的 SQL 查询 [英] SQL query to return a grouped result as a single row

问题描述

如果我有这样的工作表:

If I have a jobs table like:

|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  |

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

I want a query that will group them by date and count the occurrence of each status and the total status for that date.

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 个可能的单词中的任何一个，但每个日期可能不是每个状态中的一个.我还想要每天的所有状态.所以想要的结果看起来像:

I would like to now condense this down to a single row per created_at unique date with some sort of multi column layout for each status. One constraint is that status is any one of 5 possible words but each date might not have one of every status. Also I would like a total of all statuses for each day. So desired results would look like:

|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

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

the columns could be built dynamically from something like

SELECT DISTINCT status FROM jobs;

对于不包含任何此类状态的任何一天的结果为空.我不是 SQL 专家，但我试图在数据库视图中执行此操作，这样我就不必在 Rails 中执行多个查询.

with a null result for any day that doesn't contain any of that type of status. I am no SQL expert but am trying to do this in a DB view so that I don't have to bog down doing multiple queries in Rails.

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

I am using Postresql but would like to try to keep it straight SQL. I have tried to understand aggregate function enough to use some other tools but not succeeding.

推荐答案

以下内容应该适用于任何 RDBMS:

The following should work in any 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 ... 表达式即可.

The query uses conditional aggregation so as to pivot grouped data. It assumes that status values are known before-hand. If you have additional cases of status values, just add the corresponding sum(case ... expression.

在这里演示

这篇关于将分组结果作为单行返回的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！