sql查询分别计算每小时的人数

Question

我有一个包含这些字段的表:

id | person_id | start_time | end_time | status

我想获取特定日期每个小时存在的人数，如下面的伪代码:

select count(person) from table where dow of end_time=day and end_time >= hour and start_time < hour+1 for hour in working hours of organization and day is a certain day of week

如果有可能有一个由一天中的几个小时组成的临时表可能是以下解决方案:

select t.h, count(s.id)
from
session s cross join (temperoray table with one column of hours in a day as t)
where
s.start_time < (t.h + 1) and s.end_time > t.h
group by
t.h

但我不知道可以根据需要创建临时表的命令。

我找到了这个 question这与我想要的非常相似，但它的所有解决方案都基于 group by 我认为这对我的情况没有任何意义，因为每个组的部分都有共同的项目，例如，一个人可以第 11 小时计为人，第 12 和 13 小时计为人。

我希望我能找到一种方法来得到这样一张 table :

hour     |number of persons
10       |2
11       |0
12       |3
13       |1
...

请注意，有可能有一天人数为零。

示例:

  id  | status |            start_time            |             end_time             | branch_id | person_id | session_type 
------+--------+----------------------------------+----------------------------------+-----------+-----------+--------------
 2675 | FI     | 2018-04-23 10:30:50.939693+04:30 | 2018-04-23 12:31:39.340692+04:30 |         1 |      1085 | IN
 2676 | FI     | 2018-04-23 11:47:06.683374+04:30 | 2018-04-23 13:23:52.659714+04:30 |         1 |      2722 | IN
 2677 | FI     | 2018-04-23 11:47:59.341765+04:30 | 2018-04-23 13:25:46.339266+04:30 |         1 |      2721 | IN
 2678 | FI     | 2018-04-23 11:58:34.854222+04:30 | 2018-04-23 13:25:55.08795+04:30  |         1 |      2723 | IN
 2679 | FI     | 2018-04-23 12:27:58.817234+04:30 | 2018-04-23 13:12:28.278699+04:30 |         1 |      2724 | IN
 2680 | FI     | 2018-04-23 12:30:36.552407+04:30 | 2018-04-23 12:30:54.088159+04:30 |         1 |      2725 | IN
 2681 | FI     | 2018-04-23 14:55:50.886725+04:30 | 2018-04-23 16:08:27.076629+04:30 |         1 |        25 | IN
 2682 | FI     | 2018-04-23 15:06:30.443347+04:30 | 2018-04-23 15:52:20.128546+04:30 |         1 |      2653 | IN
 2683 | FI     | 2018-04-23 15:21:57.979387+04:30 | 2018-04-23 16:16:09.289267+04:30 |         1 |      2580 | IN
 2684 | FI     | 2018-04-23 15:26:18.057999+04:30 | 2018-04-23 16:02:44.704133+04:30 |         1 |      2726 | IN
 2685 | FI     | 2018-04-23 16:50:10.2957+04:30   | 2018-04-23 17:23:01.732404+04:30 |         1 |      2727 | IN
 2686 | FI     | 2018-04-23 16:52:28.474299+04:30 | 2018-04-23 17:23:51.013318+04:30 |         1 |      2728 | IN
 2687 | FI     | 2018-04-23 16:58:05.796563+04:30 | 2018-04-23 17:33:03.259335+04:30 |         1 |      1646 | IN
 2688 | FI     | 2018-04-23 17:50:02.738009+04:30 | 2018-04-23 18:43:27.152203+04:30 |         1 |      2729 | IN
 2689 | FI     | 2018-04-23 18:47:12.19468+04:30  | 2018-04-23 19:25:46.606731+04:30 |         1 |      2730 | IN
 2690 | FI     | 2018-04-23 19:18:32.922065+04:30 | 2018-04-23 20:11:26.703693+04:30 |         1 |      2408 | IN
 2691 | FI     | 2018-04-23 19:18:53.133712+04:30 | 2018-04-23 19:56:47.702305+04:30 |         1 |      2409 | IN
 2692 | FI     | 2018-04-23 19:21:00.348889+04:30 | 2018-04-23 20:24:25.882451+04:30 |         1 |      2731 | IN
 2693 | FI     | 2018-04-23 19:30:05.908247+04:30 | 2018-04-23 20:12:36.627888+04:30 |         1 |      2591 | IN
 2694 | FI     | 2018-04-23 19:36:02.700379+04:30 | 2018-04-23 20:13:35.146002+04:30 |         1 |      2732 | IN
 2695 | FI     | 2018-04-23 19:50:15.13214+04:30  | 2018-04-23 20:09:37.168147+04:30 |         1 |      2491 | IN
 2696 | FI     | 2018-04-23 19:51:54.754169+04:30 | 2018-04-23 20:09:59.029376+04:30 |         1 |      2733 | IN
 2697 | FI     | 2018-04-23 19:53:13.529475+04:30 | 2018-04-23 20:09:49.229139+04:30 |         1 |      2734 | IN
 2698 | FI     | 2018-04-23 19:59:27.70488+04:30  | 2018-04-23 20:21:47.862433+04:30 |         1 |      1762 | IN
 2699 | FI     | 2018-04-23 19:59:57.86605+04:30  | 2018-04-23 20:22:05.171377+04:30 |         1 |      1761 | IN
 2700 | FI     | 2018-04-23 20:24:21.212784+04:30 | 2018-04-23 20:47:31.854373+04:30 |         1 |      2735 | IN
 2701 | FI     | 2018-04-23 21:58:57.308547+04:30 | 2018-04-23 22:43:20.075321+04:30 |         1 |      1705 | IN
 2702 | FI     | 2018-04-23 21:59:44.974384+04:30 | 2018-04-23 22:43:45.946989+04:30 |         1 |      1704 | IN
 2703 | FI     | 2018-04-23 22:10:20.991216+04:30 | 2018-04-23 22:40:51.16409+04:30  |         1 |      2711 | IN

我今天要得到的结果如下:

hour   | number
10     | 1
11     | 4
12     | 6
13     | 4
14     | 1
15     | 4
16     | 6
17     | 4
18     | 2
19     | 11
20     | 10
21     | 2
22     | 3

Answer 1

如果我理解正确

你需要使用generate_series函数创建24小时然后左加入它。

您可以在Where 子句上添加一些条件。

按小时获取

SELECT    gs.hours, 
          Sum( 
          CASE 
                    WHEN start_time IS NOT NULL THEN 1 
                    WHEN end_time IS NOT NULL THEN 1 
                    ELSE 0 
          END ) AS "count" 
FROM      ( 
                 SELECT hours 
                 FROM   Generate_series(1,24) AS gs(hours) ) gs 
LEFT JOIN 
          ( 
                 SELECT *, 
                        Generate_series(start_time::timestamp, end_time::timestamp, '1 hours') invhour
                 FROM   t )t 
ON        gs.hours = To_char(t.invhour,'HH24')::integer 
GROUP BY  gs.hours

按小时和日期获取

SELECT    To_char(t.invhour,'yyyy-MM-dd') AS "dates", 
          gs.hours, 
          Sum( 
          CASE 
                    WHEN start_time IS NOT NULL THEN 1 
                    WHEN end_time IS NOT NULL THEN 1 
                    ELSE 0 
          END ) AS "count" 
FROM      ( 
                 SELECT hours 
                 FROM   Generate_series(1,24) AS gs(hours) ) gs 
LEFT JOIN 
          ( 
                 SELECT *, 
                        Generate_series(start_time::timestamp, end_time::timestamp, '1 hours') invhour
                 FROM   t )t 
ON        gs.hours = To_char(t.invhour,'HH24')::integer 
GROUP BY  gs.hours, 
          to_char(t.invhour,'yyyy-MM-dd')

sqlfiddle:http://sqlfiddle.com/#!17/717fa/1

generate_series