代码之家 › 专栏 › 技术社区 › Sreehari

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

postgresql sql

Sreehari · 技术社区 · 7 年前

我一直在努力,但到目前为止还没有成功。

我的数据如下。我正在使用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);

3 回复 | 直到 7 年前

Gordon Linoff 7 年前

你需要生成小时数。以下是一种使用 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;

KIKO Software 7 年前

我还能回答吗?测试,测试,哦,是的,它工作。好吧,我认为你的问题在于你的比较是错误的: date_trunc('day', create_date)='2013-05-09' 应该是: date_trunc('day', create_date)='2013-05-09 00:00: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 00:00:00' 
GROUP BY date_trunc('hour',create_date);

但这不会返回零计数,但其他人已经解决了这个问题。

JohnHC 7 年前

使用数字CTE(Postgresql示例):

with Nums(NN) as
(
values(0)
union all
select NN+1
where NN <23
)
select NN as the_hour, count(order_id) as orders
from Nums
left join order_table
on date_part('hour',create_date) = NN
group by NN