按时间分组，每天24小时

Given the following declarations where there is a registration of an arbitrary event for a user (USER) at a given time (TIME) in a table DATA:

给定以下声明，其中在表数据中的给定时间(时间)为用户(用户)注册了任意事件:

CREATE TABLE DATA
(
  "USER" Varchar(20),
  "TIME" Time
);


INSERT INTO DATA ("USER", "TIME") VALUES ('Martin', '14:58:00.000');
INSERT INTO DATA ("USER", "TIME") VALUES ('Martin', '15:02:11.000');
INSERT INTO DATA ("USER", "TIME") VALUES ('Martin', '15:48:44.000');
INSERT INTO DATA ("USER", "TIME") VALUES ('Marion', '08:45:01.000');
INSERT INTO DATA ("USER", "TIME") VALUES ('Marion', '15:01:01.000');
INSERT INTO DATA ("USER", "TIME") VALUES ('Marion', '15:03:48.000');

It's trivial to find the number of events per user at a given one-hour period:

在给定的一小时内找到每个用户的事件数是很简单的:

select 
    "USER", 
    extract(hour from "TIME") as "Hour",
    count(*)
from 
    DATA
group by
    "USER", 
    extract(hour from "TIME")
;

The result is, of course:

结果当然是:

USER  |Hour|COUNT
------+----+-----
Marion|   8|    1
Marion|  15|    2
Martin|  14|    1
Martin|  15|    2

But how do I get the number of events per user across an entire day in one hour intervals? Like this:

但是，如何在一小时的间隔内得到一天中每个用户的事件数呢?是这样的:

USER  |Hour|COUNT
------+----+-----
Marion|   0|    0
Marion|   1|    0
...
Marion|   7|    0
Marion|   8|    1
Marion|   9|    0
...
Marion|  14|    0
Marion|  15|    2
Marion|  16|    0
...
Marion|  22|    0
Marion|  23|    0
Martin|   0|    0
Martin|   1|    0
...
Martin|  13|    0
Martin|  14|    1
Martin|  15|    2
Martin|  16|    0
...
Martin|  22|    0
Martin|  23|    0

BTW I won't have write access to any database involved.

顺便说一下，我不会写任何涉及到的数据库。

2 个解决方案

#1

You would want to do something like this, create a (temp) table HOURS with the numbers 0 to 23, then do an outer join to get all the HOUR values regardless of whether they are in DATA

您可能想做这样的事情，创建一个(temp)表小时，其中的数字为0到23，然后执行外部连接以获取所有的小时值，而不管它们是否在数据中

select 
    "USER", 
    extract(hour from "TIME") as "Hour",
    SUM(CASE WHEN Data.HOUR is NOT NULL 1 ELSE 0 END)
from 
    DATA
    right outer join HOURS on extract(hour from "TIME") = HOURS.hour
group by
    "USER", 
    extract(hour from "TIME")
;

If you can't create a table, you can do ugly things like

如果你不能创建一个表，你可以做一些丑陋的事情，比如

(SELECT 1 as hour UNION 2 ... UNION 23) as HOURS

though there might be better way to do this depending your dialect

虽然可能有更好的方法根据你的方言

#2

@spinning_plate's answer is good for a vertical answer (both ways -- temp table, or unions)

@spinning_plate的答案对于垂直的答案是好的(两种方法——临时表或联合)

You might also like a horizontal result -- i.e. with headers: user | 0000 | 0100 | 0200 | ... | 2200 | 2300

您可能还希望得到一个水平结果——例如，使用header: user | 0000 | 0100 | 0200 |…| 2200 | 2300

If so, then some options...

如果是的话，那么一些选择……

with SQL-Server 2005+ or Oracle 11g+, look into PIVOT / UNPIVOT

使用SQL-Server 2005+或Oracle 11g+，请查看PIVOT / UNPIVOT

with Oracle <11g, look into DECODE (e.g.:

使用Oracle <11g，请查看DECODE(例如:

SELECT
   ...
   SUM(DECODE(extract(hour from "TIME"),1,1,0)) as 0100,
   SUM(DECODE(extract(hour from "TIME"),13,1,0)) as 1300,
...

#1