主页 / dba / 问题 / 343914
Accepted
Antti Keskinen
Asked: 2024-11-28 23:23:44 +0800 CST

在 PostgreSQL 中如何使用窗口函数获取值的每小时最小值？

  • 772

假设我们有以下数据，每秒测量一次温度。数据跨度为几年，因此有相当多的行。这是来自现场设备的测量数据。该表还有其他列，但它们与问题无关。

时间 温度_摄氏度
2024-11-01 00:00:00+00 20.1
2024-11-01 00:00:01+00 21.2
2024-11-01 00:00:02+00 21.6
2024-11-01 00:00:03+00 20.2
... ...
2026-12-31 23:59:57+00 25.4
2026-12-31 23:59:58+00 25.2
2026-12-31 23:59:59+00 25.6

我知道我可以使用一个GROUP BY子句来获取特定开始和结束时间之间一小时窗口内的平均值，如下所示：

  SELECT to_char("time", 'YYYY-MM-DD HH'), AVG("Temperature_deg_in_C")
    FROM "measurements"
   WHERE "time" BETWEEN '2024-11-01' AND '2024-12-01'
GROUP BY to_char("time", 'YYYY-MM-DD HH')

但是，这种语法感觉像是一种黑客行为，并且它不能完全适应例如 10 秒或其他更复杂的窗口框架（或分区）。

此外，我想具体了解如何使用 PostgreSQL 手册中概述的、、、、和类似工具正确且高效地完成OVER此操作。WINDOWPARTITION BYRANGEROWS

更新：窗口函数不能用在 Postgres 上实现这个目标，因为它们不会像我想象的那样创建“存储桶”或“框架”。GROUP BY是实现这一目标的方法。

最终目标是找到一种解决方案，其中窗口长度和测量单位（分钟、小时、天）以及观察期的开始和结束时间是灵活的，但查询主体本身保持不变。也就是说，一个简单易懂的参数化查询。

postgresql

2 个回答

  1. Best Answer

    在此查询中：

    SELECT to_char("time", 'YYYY-MM-DD HH'), AVG("Temperature_deg_in_C")
FROM "measurements"
WHERE "time" BETWEEN '2024-11-01' AND '2024-12-01'
GROUP BY 1

    ...您正在将时间戳转换为 bin 标识符，这些标识符对于组中的每一行都是相同的，因此它们最终位于同一个 bin 中。通过更改计算方式，您可以使用不限于 1 分钟、1 小时或 1 天的时间段。

    例如，您可以使用：代替 to_char()

    FLOOR( EXTRACT( epoch FROM "time" ) / bin_length )

    这会将 postgres 时间戳转换为 UNIX 时间戳（以秒为单位），然后除以 bin_length（也以秒为单位）。然后，取底结果为一个整数，该整数是此行的 bin 标识符。使用此表达式，bin 将从 UNIX 时间戳零开始，并以 bin_length 的倍数重复。

    您还可以使用类似以下的方法：

    FLOOR( EXTRACT( epoch FROM ( "time" - start_time ) / bin_length )

    这会从查询参数 start_time 中减去“time”，因此第一个 bin 将从“start_time”开始，并且 bins 将以 bin_length 的倍数重复。

    GROUP BY 的结果将包含 bin 编号。然后您可以反向执行相同的操作以转换回时间戳。例如：

    SELECT start_time + '1 second'::INTERVAL * bin_id, m_avg FROM (
SELECT 
FLOOR( EXTRACT( epoch FROM ( "time" - start_time ) / bin_length ) AS bin_id,
avg( measurement ) m_avg,
...
GROUP BY bin_id
)

    或者，你可以使用 bin 左边缘的时间戳作为 bin 键，通过将 to_char() 替换为类似以下内容：

    start_time + '1 second'::INTERVAL * FLOOR( 
    EXTRACT( epoch FROM ( "time" - start_time ) / bin_length ))
    • 0

  2. PLPgSQL使用windows函数查询：

    SELECT 
    to_char(time, 'YYYY-MM-DD HH'), 

    ROUND(
        AVG(temperature_deg_in_c) OVER (
           PARTITION BY to_char("time", 'YYYY-MM-DD HH')
        ), 
    2) AS AVG_TEMP

FROM measurements

WHERE time BETWEEN '2024-11-01' AND '2024-12-01';

    使用 windows 函数的替代方法：

    SELECT to_char(time, 'YYYY-MM-DD HH')
    ROUND(
        SUM(temperature_deg_in_c) OVER (PARTITION BY to_char("time", 'YYYY-MM-DD HH'))/
        FLOOR(COUNT(temperature_deg_in_c) OVER (PARTITION BY to_char("time", 'YYYY-MM-DD HH')))
    ) AS COUNT_TEMP
    
FROM measurements
WHERE time BETWEEN '2024-11-01' AND '2024-12-01';

    db<>小提琴

    • -1

相关问题