了解 PostgreSQL 中的 WINDOW（附带示例）

免费试用

立即开始增强您的 PostgreSQL。

Bright yellow geometric shapes over a black backgroud.

作者：Timescale 团队

在 PostgreSQL 或 TimescaleDB 中，您经常需要对结果集的行执行复杂的计算。PostgreSQL 中的 WINDOW 子句允许您定义用于 窗口函数 的命名窗口。这对于提高涉及具有相同窗口定义的多个窗口函数的复杂查询的可读性和可维护性特别有用。

PostgreSQL WINDOW 语法

WINDOW 子句的基本语法如下

SELECT column1, column2, ...
FROM table
WINDOW window_name AS (window_definition)

窗口定义可以包含 PARTITION BY、ORDER BY 和框架子句。

示例表

让我们考虑一个具有以下结构和数据的表 iot_data

CREATE TABLE iot_data (     id SERIAL PRIMARY KEY,     device_id VARCHAR(50),     temperature NUMERIC,     humidity NUMERIC,     wind_speed NUMERIC,     recorded_at TIMESTAMP );

INSERT INTO iot_data (device_id, temperature, humidity, wind_speed, recorded_at) VALUES
('device_1', 22.5, 55.0, 12.5, '2023-01-15 08:00:00'),
('device_2', 21.0, 60.0, 10.0, '2023-01-15 08:05:00'),
('device_1', 23.0, 57.0, 11.0, '2023-01-15 08:10:00'),
('device_2', 19.5, 62.0, 8.0, '2023-01-15 08:15:00'),
('device_1', 20.0, 59.0, 9.5, '2023-01-15 08:20:00'),
('device_3', 24.0, 54.0, 13.0, '2023-01-15 08:25:00'),
('device_2', 18.5, 63.0, 7.5, '2023-01-15 08:30:00'),
('device_3', 22.0, 55.5, 12.0, '2023-01-15 08:35:00'),
('device_1', 21.5, 58.0, 10.5, '2023-01-15 08:40:00'),
('device_3', 23.5, 53.0, 13.5, '2023-01-15 08:45:00');

实际示例

让我们深入研究一些示例，以查看 WINDOW 子句的实际应用。

示例 1：使用基本的 WINDOW 子句

假设我们要计算每个设备的平均温度以及每个温度与平均值之间的差值。我们可以定义一个命名窗口并在我们的窗口函数中重复使用它

SELECT
    device_id,
    temperature,
    AVG(temperature) OVER temp_window AS avg_temp,
    temperature - AVG(temperature) OVER temp_window AS temp_diff
FROM
    iot_data
WINDOW temp_window AS (PARTITION BY device_id);

结果

device_id | temperature | avg_temp | temp_diff 
-----------+-------------+----------+-----------
 device_1  |        22.5 |    21.75 |      0.75 
 device_1  |        23.0 |    21.75 |      1.25 
 device_1  |        20.0 |    21.75 |     -1.75 
 device_1  |        21.5 |    21.75 |     -0.25 
 device_2  |        21.0 |    19.67 |      1.33 
 device_2  |        19.5 |    19.67 |     -0.17 
 device_2  |        18.5 |    19.67 |     -1.17 
 device_3  |        24.0 |    23.17 |      0.83 
 device_3  |        22.0 |    23.17 |     -1.17 
 device_3  |        23.5 |    23.17 |      0.33 

在此查询中，temp_window 被定义为按 device_id 对数据进行分区。这允许 AVG 函数计算每个设备的平均温度并重用此窗口定义。

示例 2：将 WINDOW 子句与 ORDER BY 一起使用

假设我们还想计算每个设备的温度读数的累积总和，并按 recorded_at 排序。我们可以扩展窗口定义以包含排序，使用 ORDER BY

SELECT
    device_id,
    temperature,
    recorded_at,
    SUM(temperature) OVER temp_window AS cumulative_temp
FROM
    iot_data
WINDOW temp_window AS (PARTITION BY device_id ORDER BY recorded_at);

结果

device_id | temperature |     recorded_at      | cumulative_temp 
-----------+-------------+---------------------+-----------------
 device_1  |        22.5 | 2023-01-15 08:00:00 |            22.5 
 device_1  |        23.0 | 2023-01-15 08:10:00 |            45.5 
 device_1  |        20.0 | 2023-01-15 08:20:00 |            65.5 
 device_1  |        21.5 | 2023-01-15 08:40:00 |            87.0 
 device_2  |        21.0 | 2023-01-15 08:05:00 |            21.0 
 device_2  |        19.5 | 2023-01-15 08:15:00 |            40.5 
 device_2  |        18.5 | 2023-01-15 08:30:00 |            59.0 
 device_3  |        24.0 | 2023-01-15 08:25:00 |            24.0 
 device_3  |        22.0 | 2023-01-15 08:35:00 |            46.0 
 device_3  |        23.5 | 2023-01-15 08:45:00 |            69.5 

在此查询中，temp_window 被定义为按 device_id 对数据进行分区，并按 recorded_at 对其进行排序。SUM 函数然后按照记录时间戳的顺序计算每个设备的温度累积总和。

后续步骤

WINDOW 子句是 PostgreSQL 和 TimescaleDB 中的一个强大功能，允许您为窗口函数定义可重用的窗口规范。无论您是计算平均值、累积总和还是其他窗口函数，理解和利用 WINDOW 子句都将增强您分析和管理数据的能力。 

要详细了解窗口函数以及如何在 PostgreSQL 或 TimescaleDB 中利用它们进行数据处理，请查看这篇文章。