sql - 如何在 postgresql 中使用 “time” 字段按天和 2 小时分组？

Question

我需要按一天分成 2 小时分组:

我使用了 EXTRACT PostgreSQL 函数。但找不到按 2 小时时间分组的方法

SELECT EXTRACT(dow from  completed_at) AS "day",
       EXTRACT(hour from  completed_at) AS "hour", count(*)
FROM orders
WHERE completed_at is not null
GROUP BY 1, 2
ORDER BY 1;

预期输出:

day       hour  count
-------- ------ ------
Sun       12am   10
Sun        2am   8
Sun        4am   0
Sun        6am   24
Sun        8am   25
Sun       10am   100
Sun       12pm   67
Sun        2pm   10
Sun        4pm   10
Sun        6pm   10
Sun        8pm   10
Sun       10pm   10

像这样，我每个工作日都需要同样的东西

Answer 1

尝试:

SELECT EXTRACT(dow from  completed_at) AS "day",
       EXTRACT(hour from  completed_at) AS "hour", count(*)
FROM orders
join generate_series(0,22,2) g on g >= extract(hour from completed_at) and g< extract(hour from completed_at) + 2
WHERE completed_at is not null
GROUP BY "day","hour"
ORDER BY 1;

就像在我的示例架构中一样:

db=# create table so (t timestamptz);
CREATE TABLE
Time: 171.144 ms
db=# insert into so select generate_series(now(),current_date + 2,' 1hour'::interval);
INSERT 0 40
Time: 71.150 ms
db=# select count(*), g
from so
join generate_series(0,22,2) g on g >= extract(hour from t) and g< extract(hour from t) + 2
group by g
order by 2,1
;
 count | g
-------+----
     1 |  0
     2 |  2
     2 |  4
     2 |  6
     3 |  8
     4 | 10
     4 | 12
     4 | 14
     4 | 16
     4 | 18
     4 | 20
     4 | 22
     2 | 24
(13 rows)

Time: 11.958 ms