Published at Jul 11, 2024

Understanding WINDOW in PostgreSQL (With Examples)

Written by Team Timescale

In PostgreSQL or TimescaleDB, you often need to perform complex calculations across rows of a result set. The WINDOW clause in PostgreSQL allows you to define named windows for use with window functions. This is particularly useful for improving the readability and maintainability of complex queries that involve multiple window functions with the same window definition.

PostgreSQL WINDOW Syntax

The basic syntax of the WINDOW clause is as follows:

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

A window definition can include PARTITION BY, ORDER BY, and frame clauses.

Example table

Let's consider a table iot_data with the following structure and 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'); Copy

Practical examples

Let's dive into some examples to see the WINDOW clause in action.

Example 1: Using a basic WINDOW clause

Suppose we want to calculate the average temperature and the difference between each temperature and the average for each device. We can define a named window and reuse it in our window functions:

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); Copy

Result:

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  Copy

In this query, temp_window is defined to partition the data by device_id. This allows the AVG function to calculate the average temperature for each device and reuse this window definition.

Example 2: Using WINDOW clause with ORDER BY

Suppose we also want to calculate the cumulative sum of the temperature readings for each device ordered by recorded_at. We can extend our window definition to include ordering with 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); Copy

Result:

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  Copy

In this query, temp_window is defined to partition the data by device_id and order it by recorded_at. The SUM function then calculates the cumulative sum of temperatures for each device in the order of their recorded timestamps.

Next Steps

The WINDOW clause is a powerful feature in PostgreSQL and TimescaleDB that allows you to define reusable window specifications for window functions. Whether you're calculating averages, cumulative sums, or other window functions, understanding and utilizing the WINDOW clause will enhance your ability to analyze and manage data. 

To learn more about window functions and how you can leverage them for data processing in PostgreSQL or TimescaleDB, check out this article.