PostgreSQL如何按小时获取订单

Question

我有一个看起来像的表

Order
OrderId
OrderStatus
OrderTime

我还有另一张桌子

OrderId
OrderLineId
OrderLineAmount
OrderLineCost

我试着在两次约会之间得到一个平均每小时的量。也可以是一天的开始和结束，或者一周的开始和终止。例如，如果在上午9点和上午9点30分下两个订单，每个订单50英镑。然后我试图得到：

20/07/2018 09:00:00 - 20/07/2018 10:00:00 
2 
100

这有道理吗？

我目前有这个，但没有完全按照我需要的方式工作

SELECT 
COUNT(*) as "number of orders",
sum(ol.quantity * ol.amount) as "line total",
to_timestamp(floor((extract('epoch' from "ordertimestamp") / 600 )) * 600) AT TIME ZONE 'UTC' as interval_alias
FROM "Order" as o
inner join "OrderLine" as ol on o.orderid = ol.orderid
GROUP BY interval_alias;

任何帮助都会很棒，谢谢

Answer 1

开始：

create table ord (
orderid int,
orderstatus int,
ordertime timestamp
);
insert into ord values (1, 1, '2018-07-20 09:10:00');
insert into ord values (2, 1, '2018-07-20 09:40:00');
insert into ord values (3, 1, '2018-07-20 10:15:00');
create table orderline (
orderid int,
orderlineid int,
orderlineamount int,
orderlinecost int
);
insert into orderline values (1, 100, 50, 40);
insert into orderline values (2, 100, 25, 40);
insert into orderline values (2, 100, 25, 40);
insert into orderline values (3, 10, 1234, 40);  
select
date_trunc('hour', o.ordertime), 
count(distinct o.orderid), 
sum(l.orderlineamount)
from ord o
join orderline l on l.orderid = o.orderid
group by date_trunc('hour', o.ordertime)
order by date_trunc('hour', o.ordertime);

结果：

date_trunc                     count                 sum                   
---------------------------------------------------------------------------
2018-07-20 09:00:00.0          2                     100                   
2018-07-20 10:00:00.0          1                     1234