sql - 查询以检索每小时计数，如果没有则为零

Question

所有的方法都在尝试，但至今没有成功。

我通过以下方式获取数据。我正在使用 postGreSQL

order_id |      create_date
    -----+--------------------
    6000 | 2013-05-09 11:53:04
    6001 | 2013-05-09 12:58:00
    6002 | 2013-05-09 13:01:08
    6003 | 2013-05-09 13:01:32
    6004 | 2013-05-09 14:05:06
    6005 | 2013-05-09 14:06:25
    6006 | 2013-05-09 14:59:58
    6007 | 2013-05-09 19:00:07

我需要一个查询来生成 24 小时内每小时的订单数。如果一小时内没有订单，查询输出默认为零。下面应该是输出格式。

    orders |      hour
    -----+--------------------
    0    | 00:00
    0    | 01:00
    0    | 02:00
    0    | 03:00
    0    | 04:00
    0    | 05:00
    0    | 06:00
    0    | 07:00
    0    | 08:00
    0    | 09:00
    0    | 10:00
    1    | 11:00
    1    | 12:00
    2    | 13:00
    3    | 14:00
    0    | 15:00
    0    | 16:00
    0    | 17:00
    0    | 18:00
    1    | 19:00
    0    | 20:00
    0    | 21:00
    0    | 22:00
    0    | 23:00

可以吗？以下是我当前的查询。当然，它没有按照我想要的方式提供输出。

select count(order_id) as orders, date_trunc('hour', create_date) as hr from order_table where date_trunc('day', create_date)='2013-05-09' GROUP BY date_trunc('hour', create_date);

Answer 1

您需要生成小时数。这是使用 generate_series() 的一种方法:

select '00:00'::time + g.h * interval '1 hour',
       count(order_id) as orders
from generate_series(0, 23, 1) g(h) left join
     order_table ot
     on extract(hour from create_date) = g.h and
        date_trunc('day', create_date) = '2013-05-09'
group by g.h
order by g.h;

或者:

select g.dte, count(order_id) as orders
from generate_series('2013-05-09'::timestamp, '2013-05-09 23:00:00'::timestamp, interval '1 hour') g(dte) left join
     order_table ot
     on g.dte = date_trunc('hour', create_date) 
group by g.dte
order by g.dte;