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
这些列可以通过
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屋!