sql - 在postgresql中按时间范围按数据分组

问题描述

我想GROUP by按时间范围数据。我有start_dateand的例子，我想要和之间的end_date单独范围，start_date并从to得到总和。end_date25value125

我的桌子的简单介绍：

select * from t1
where time between start_date and end_date

表 t1 有：

time 2019-10-01 value 50
time 2019-10-01 value 50
time 2019-10-02 value 50
time 2019-10-02 value 50
time 2019-10-02 value 50
time 2019-10-02 value 50
time 2019-10-03 value 50
time 2019-10-04 value 50
time 2019-10-05 value 50
time 2019-10-05 value 50
time 2019-10-05 value 50

start_date 2019-10-01
end_date   2019-10-25

generate_series分离功能

并通过这 25

2019-10-01有价值有价值_ 100 _2019-10-02400

标签： sqlpostgresql

解决方案

Assuming start_date and end_date are variables, you might wanna try the following CTE. It will group by a sum over value by time. In case you want to replace the null values with a 0, try coalesce as pointed out by @GMB in the other answer.

WITH j AS (
 SELECT generate_series(DATE '2019-10-01', DATE '2019-10-25', '1 day') AS day)
SELECT j.day, coalesce(sum(value), 0) FROM t1
RIGHT JOIN j ON j.day = time
GROUP BY j.day ORDER BY j.day;

          day           | coalesce 
------------------------+----------
 2019-10-01 00:00:00+02 |      100
 2019-10-02 00:00:00+02 |      200
 2019-10-03 00:00:00+02 |       50
 2019-10-04 00:00:00+02 |       50
 2019-10-05 00:00:00+02 |      150
 2019-10-06 00:00:00+02 |        0
 2019-10-07 00:00:00+02 |        0
 2019-10-08 00:00:00+02 |        0
 2019-10-09 00:00:00+02 |        0
 2019-10-10 00:00:00+02 |        0
 2019-10-11 00:00:00+02 |        0
 2019-10-12 00:00:00+02 |        0
 2019-10-13 00:00:00+02 |        0
 2019-10-14 00:00:00+02 |        0
 2019-10-15 00:00:00+02 |        0
 2019-10-16 00:00:00+02 |        0
 2019-10-17 00:00:00+02 |        0
 2019-10-18 00:00:00+02 |        0
 2019-10-19 00:00:00+02 |        0
 2019-10-20 00:00:00+02 |        0
 2019-10-21 00:00:00+02 |        0
 2019-10-22 00:00:00+02 |        0
 2019-10-23 00:00:00+02 |        0
 2019-10-24 00:00:00+02 |        0
 2019-10-25 00:00:00+02 |        0
(25 rows)

EDIT (see comments below):

Changing the series with a 12 hours interval between the generated elements.

WITH j AS (
 SELECT generate_series(DATE '2019-10-01 01:30:00', 
                        DATE '2019-10-03 12:30:00', '12 hours') AS day)
SELECT j.day, coalesce(sum(value),0) FROM t1
RIGHT JOIN j ON j.day = time
GROUP BY j.day ORDER BY j.day;

          day           | coalesce 
------------------------+----------
 2019-10-01 00:00:00+02 |      100
 2019-10-01 12:00:00+02 |        0
 2019-10-02 00:00:00+02 |      200
 2019-10-02 12:00:00+02 |        0
 2019-10-03 00:00:00+02 |       50
(5 rows)

You can change the parameters inside of the generate_series function as you wish, e.g. 30 minutes, 1 hour, etc.

The same can be done with TIMESTAMP, but the dates you'll join with your table need to be identical!

WITH j AS (
 SELECT generate_series(TIMESTAMP '2019-10-01 00:00:00', 
                        TIMESTAMP '2019-10-05 12:30:00', '8 hours') AS day)
SELECT j.day, coalesce(sum(value),0) FROM t1
RIGHT JOIN j ON j.day = time
GROUP BY j.day ORDER BY j.day;

         day         | coalesce 
---------------------+----------
 2019-10-01 00:00:00 |      100
 2019-10-01 08:00:00 |        0
 2019-10-01 16:00:00 |        0
 2019-10-02 00:00:00 |      200
 2019-10-02 08:00:00 |        0
 2019-10-02 16:00:00 |        0
 2019-10-03 00:00:00 |       50
 2019-10-03 08:00:00 |        0
 2019-10-03 16:00:00 |        0
 2019-10-04 00:00:00 |       50
 2019-10-04 08:00:00 |        0
 2019-10-04 16:00:00 |        0
 2019-10-05 00:00:00 |      150
 2019-10-05 08:00:00 |        0
(14 rows)

sql - 在postgresql中按时间范围按数据分组

问题描述

解决方案

推荐阅读