sql - 如何在 PostgreSQL 中按周对日期进行分组？

Question

我有一个非常简单的表，它有 2 列。第一个显示时间(timestamp)，第二个显示当时车速(float8)。

|      DATE_TIME      | SPEED |
|---------------------|-------|
| 2018-11-09 00:00:00 | 256   |
| 2018-11-09 01:00:00 | 659   |
| 2018-11-09 02:00:00 | 256   |
|      other dates    | xxx   |
| 2018-11-21 21:00:00 | 651   |
| 2018-11-21 22:00:00 | 515   |
| 2018-11-21 23:00:00 | 849   |

假设我们的时间段是从 11 月 9 日到 11 月 21 日。如何按周对该时间段进行分组。事实上我想要这样的结果:

|      DATE_TIME      | AVG_SPEED |
|---------------------|-----------|
| 9-11 November       | XXX       |
| 12-18 November      | YYY       |
| 19-21 November      | ZZZ       |

我使用 PostgreSQL 10.4。

我使用这样的 SQL 语句来知道某个日期是星期几:

SELECT EXTRACT(WEEK FROM TIMESTAMP '2018-11-09 00:00:00'); 

编辑:

@tim-biegeleisen 当我将时间段从“2018-11-01”设置为“2018-11-13”时，您的 sql 语句返回 2 个结果:

其实我需要这样的结果:

2018-11-01 00:00:00 | 2018-11-04 23:00:00
2018-11-05 00:00:00 | 2018-11-11 23:00:00
2018-11-12 00:00:00 | 2018-11-13 05:00:00

正如您在日历中看到的那样，该期间有 3 周。

Answer 1

我们可以使用日历表来做到这一点。此答案假定一周从数据集中的第一个日期开始。您也可以假设其他情况来执行此操作，例如根据其他标准一周。

WITH dates AS (
    SELECT date_trunc('day', dd)::date AS dt
    FROM generate_series
    ( '2018-11-09'::timestamp 
    , '2018-11-21'::timestamp
    , '1 day'::interval) dd
),
cte AS (
    SELECT t1.dt, t2.DATE_TIME, t2.SPEED,
        EXTRACT(week from t1.dt) week
    FROM dates t1
    LEFT JOIN yourTable t2
        ON t1.dt = t2.DATE_TIME::date
)

SELECT
    MIN(dt)::text || '-' || MAX(dt) AS DATE_TIME,
    AVG(SPEED) AS AVG_SPEED
FROM cte
GROUP BY
    week
ORDER BY
    MIN(dt);

Demo