Grouping by every hour for every weekday
I have a table orders
id | price | items_count | created_at
------+---------+---------------+------------------------
1 | 16.50 | 3 | 2015-07-21 12:52:00.824
2 | 50.00 | 1 | 2015-07-21 12:54:42.658
3 | 12.00 | 10 | 2015-07-22 07:21:47.808
. | . | . | .
. | . | . | .
. | . | . | .
etc.
Now I want to get the number of orders and the sum of their prices from the last month, grouped by weekdays (as numbers from 0 to 6) and hours (as numbers from 0 to 23).
What I managed to do:
SELECT EXTRACT('dow' FROM to_timezone(created_at, 'CEST', 0)) AS DAY,
(EXTRACT('hour' FROM to_timezone(created_at, 'CEST', 0)) + 0)::INT % 24 AS HOUR,
COUNT(DISTINCT id) AS orders_count,
SUM(price) AS price_total
FROM orders
WHERE created_at BETWEEN '2015-06-30 22:00:00.000000'
AND '2015-07-31 21:59:59.999999'
GROUP BY extract('dow' from to_timezone(created_at, 'CEST', 0)),
EXTRACT('hour' from to_timezone(created_at, 'CEST', 0))
ORDER BY 1 ASC, EXTRACT('hour' from to_timezone(created_at, 'CEST', 0));
And this is what I get - the current result :
day | hour | orders_count | price_total
-----+------+--------------+-------------
0 | 7 | 11 | 298.00
0 | 9 | 8 | 64.00
1 | 8 | 1 | 12.50
1 | 12 | 3 | 69.00
2 | 10 | 2 | 112.00
2 | 13 | 1 | 100.00
2 | 14 | 13 | 2163.70
2 | 21 | 4 | 357.00
etc.
Now I want to include all hours in the day and put 0
in columns orders_count
and price_total
if there were no orders at the given hour. So my desired output should look like this:
day | hour | orders_count | price_total
-----+------+--------------+-------------
0 | 0 | 0 | 0.00
0 | 1 | 0 | 0.00
0 | 2 | 0 | 0.00
0 | 3 | 0 | 0.00
0 | 4 | 0 | 0.00
0 | 5 | 0 | 0.00
0 | 6 | 0 | 0.00
0 | 7 | 11 | 298.00
0 | 8 | 0 | 0.00
0 | 9 | 8 | 64.00
0 | 10 | 0 | 0.00
etc. - I need 7 days [0,6]
and for each day 24 hours [0,23]
s 0
in cumulative columns.
I came up with a use generate_series
to get all hours in a day:
SELECT EXTRACT(hour from generate_series)
FROM generate_series('2015-07-01 00:00'::timestamp, '2015-07-01 23:00', '1 hour');
My problem is that I don't know how to combine my current result with all the clocks . What type JOIN
should I use? And how to insert 0
into the previously mentioned columns if there are no orders for the given row?
source to share
- Enter the Cartesian product of days and hours s
CROSS JOIN
. -
LEFT JOIN
to aggregated amounts for(day, hour)
- handy with a suggestionUSING
to dump columns into one instance.
SELECT day, hour
, COALESCE(orders_count, 0) AS orders_count
, COALESCE(price_total, 0.0) AS price_total
FROM generate_series(0,6) day
CROSS JOIN generate_series(0,23) hour
LEFT JOIN (
SELECT EXTRACT('dow' FROM created_at AT TIME ZONE 'CEST')::int AS day
, EXTRACT('hour' FROM created_at AT TIME ZONE 'CEST')::int AS hour
, count(DISTINCT id) AS orders_count
, sum(price) AS price_total
FROM orders
WHERE created_at BETWEEN '2015-06-30 22:00:00.000000'
AND '2015-07-31 21:59:59.999999'
GROUP BY 1, 2
) o USING (day, hour)
ORDER BY 1, 2;
Also cleaned up your query a bit.
Using standard SQL construct AT TIME ZONE
instead of Postgres function to_timezone()
. Details:
Removed redundant modulo operator . % 24
Use COALESCE()
to replace NULL
with 0
or 0.0
.
source to share