sql - 每个工作日按每小时分组

Question

我有一张表订单

  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
   .  |   .     |      .        |             .
   .  |   .     |      .        |             .
   .  |   .     |      .        |             .

等等……

现在我想获取上个月的订单数量和价格总和，按工作日分组(数字从 0 到 6 ) 和小时(从 0 到 23 的数字)。

我设法做到的是:

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));

这是我得到的 - 当前结果:

 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

等等……

现在我想包括一天中的所有时间，并将0 放入orders_count 和price_total 列中，如果给定时间内没有订单。所以我的期望的结果应该是这样的:

 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

等等 - 我需要 7 天 [0,6] 和每天 24 小时 [0,23] 和 0在聚合列中。

---

我想出了一个想法，使用 generate_series 获取一天中的所有时间:

SELECT EXTRACT(hour from generate_series)
FROM generate_series('2015-07-01 00:00'::timestamp, '2015-07-01 23:00', '1 hour');

我的问题是我不知道如何将我的当前结果与所有时间结合起来。我应该使用哪种JOIN？当给定行没有订单时，如何在前面提到的列中插入 0？

Answer 1

1. 形成一个Cartesian product使用 CROSS JOIN 的天数和小时数。
2. LEFT JOIN 到每个 (天，小时) 的聚合总和 - 使用 USING 子句方便地将列折叠到一个实例中。

Joins in the manual.

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;

还稍微清理了您的查询。
使用标准 SQL 构造 AT TIME ZONE 而不是 Postgres 函数 to_timezone()。详情:

• Ignoring timezones altogether in Rails and PostgreSQL

删除了冗余的模运算符 % 24 。

使用COALESCE()将 NULL 替换为 0 或 0.0。