Window Functions

Window functions exist within many SQL dialects. QuestDB is consistent with expected function.

What is a Window Function?#

A window function performs a calculation across a set of rows that are related to the current row. This set of related rows is called a "window", defined by an `OVER` clause that follows the window function.

In practical terms, window functions are used when you need to perform a calculation that depends on a group of rows, but you want to retain the individual rows in the result set. This is different from aggregate functions like `sum` or `avg`, which perform calculations on a group of rows and return a single result.

The underlying mechanism of a window function involves three components:

• Partitioning: The `PARTITION BY` clause divides the result set into partitions (groups of rows) upon which the window function is applied. If no partition is defined, the function treats all rows of the query result set as a single partition.

• Ordering: The `ORDER BY` clause within the `OVER` clause determines the order of the rows in each partition.

• Frame Specification: This defines the set of rows included in the window, relative to the current row. For example, `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` includes all rows from the start of the partition to the current row.

Use cases for window functions are vast.

They are often used in analytics for tasks such as:

• Calculating running totals or averages
• Finding the maximum or minimum value in a sequence or partition
• Ranking items within a specific category or partition
• Calculating moving averages or cumulative sums

Window functions are tough to grok.

An analogy before we get to building:

Imagine a group of cars in a race. Each car has a number, a name, and a finish time. If you wanted to know the average finish time, you could use an aggregate function like `avg` to calculate it. But this would only give you a single result: the average time. You wouldn't know anything about individual cars' times.

Now, let's say you want to know how each car's time compares to the average. Enter window functions. A window function allows you to calculate the average finish time (the window), but for each car (row) individually.

For example, you could use a window function to calculate the average finish time for all cars, but then apply this average to each car to see if they were faster or slower than the average. The `OVER` clause in a window function is like saying, "for each car, compare their time to the average time of all cars."

So, in essence, window functions allow you to perform calculations that consider more than just the individual row or the entire table, but a 'window' of related rows. This 'window' could be all rows with the same value in a certain column, like all cars of the same engine size, or it could be a range of rows based on some order, like the three cars who finished before and after a certain car.

This makes window functions incredibly powerful for complex calculations and analyses.

Building Window Functions#

At the peak of its complexity, a window function can appear as such:

functionName OVER (
PARTITION BY columnName [, columnName ...]
ORDER BY columnName [ASC | DESC] [, columnName [ASC | DESC] ...]
RANGE | ROWS (
UNBOUNDED PRECEDING | offset PRECEDING | CURRENT ROW
| BETWEEN (UNBOUNDED PRECEDING | offset PRECEDING | CURRENT ROW)
AND (offset PRECEDING | CURRENT ROW)
)
EXCLUDE CURRENT ROW | EXCLUDE NO OTHERS
)

The broad scope of possible choices can be overwhelming. But once the options become clear, assembling valuable and performant queries is quick work.

We will break down the above into:

Base Function#

This reference page demonstrates 5 base functions:

We can assemble our window functions into "blocks" so that it is easier to understand. Each block is then explained in its own small section.

The base function is the first block.

It contextualizes the "way we look through our window":

avg(price) ... (
...
)
...
)

Next we define `OVER`. It is the key to assembling valuable and performant window functions:

avg(price) OVER (
...
)
...
)

Within `OVER`, we will define `PARTITION BY` and `ORDER BY`, as well as provide our "Frame" clause, which details our `RANGE` or `ROWS`. This is the heart and shape of our window:

avg(price) OVER (
PARTITION BY columnName [, columnName ...]
ORDER BY columnName [ASC | DESC] [, columnName [ASC | DESC] ...]
RANGE | ROWS (
UNBOUNDED PRECEDING | offset PRECEDING | CURRENT ROW
| BETWEEN (UNBOUNDED PRECEDING | offset PRECEDING | CURRENT ROW)
AND (offset PRECEDING | CURRENT ROW)
)
...
)

Finally, our exclusion clauses indicate what to omit. It's a bit like sculpting the final details into the window:

avg(price) OVER (
PARTITION BY columnName [, columnName ...]
ORDER BY columnName [ASC | DESC] [, columnName [ASC | DESC] ...]
RANGE | ROWS (
UNBOUNDED PRECEDING | offset PRECEDING | CURRENT ROW
| BETWEEN (UNBOUNDED PRECEDING | offset PRECEDING | CURRENT ROW)
AND (offset PRECEDING | CURRENT ROW)
)
EXCLUDE CURRENT ROW | EXCLUDE NO OTHERS
)

OVER Clause - PARTITION & ORDER#

The `OVER` clause defines how data is grouped and processed. When you set the function ahead of `OVER`, it's a bit like a "for each" operation. It is framed as: "perform this function OVER related rows based on the following terms".

It can be used with `PARTITION BY` and `ORDER BY` to set unique parameters and organize the rows. For performance reasons, if `ORDER BY` is set within an `OVER` clause, it should match the base query's `ORDER BY`.

"Frame" Clause - RANGE or ROWS#

Window functions use a "frame" to define the subset of data the function operates on. Two modes are available for defining this frame: `RANGE` and `ROWS`.

RANGE Mode#

`RANGE` mode defines the window frame based on a range of values in the `ORDER BY` column. This is useful when the data has a continuous or time-based nature.

For example, to calculate a moving average of prices over time, you might use `RANGE` mode with `ORDER BY` timestamp:

SELECT symbol, price, timestamp,
avg(price) OVER (
PARTITION BY symbol
ORDER BY timestamp
RANGE BETWEEN '1' HOUR PRECEDING AND CURRENT ROW)
as moving_avg

This calculates the average price for each symbol, for the current row and all rows with a timestamp within the preceding hour.

ROWS Mode#

`ROWS` mode defines the window frame based on a specific number of rows. This is useful when you want to consider a fixed number of rows, regardless of their values.

For example, to calculate a moving average of the last `N` prices, you might use `ROWS` mode:

SELECT symbol, price, timestamp,
avg(price) OVER (PARTITION BY symbol ORDER BY timestamp ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as moving_avg

This calculates the average price for each symbol, for the current row and the three preceding rows.

Common Syntax#

Both `RANGE` and `ROWS` modes share similar syntax for defining the frame:

• `UNBOUNDED PRECEDING`: The window starts at the first row of the partition
• `value PRECEDING` or `offset PRECEDING`: The window starts at a specified value or number of rows before the current row
• `CURRENT ROW`: The window starts or ends at the current row
• `BETWEEN (UNBOUNDED PRECEDING | value PRECEDING | CURRENT ROW) AND (value PRECEDING | CURRENT ROW)`: The window starts and ends at specified points relative to the current row

The choice between `RANGE` and `ROWS` depends on the nature of your data and the specific requirements of your calculation.

Default Frame Definition#

When the frame clause is not specified, the default frame is `RANGE UNBOUNDED PRECEDING`, which includes all rows from the start of the partition to the current row.

• If `ORDER BY` is not present, the frame includes the entire partition, as all rows are considered equal.

• If `ORDER BY` is present, the frame includes all rows from the start of the partition to the current row. Note that `UNBOUNDED FOLLOWING` is only allowed when the frame start is `UNBOUNDED PRECEDING`, which means the frame includes the entire partition.

Exclusion Option#

The `OVER` clause can also include an exclusion option, which determines whether certain rows are excluded from the frame:

• `EXCLUDE CURRENT ROW`: Excludes the current row in `ROWS` mode and all rows with the same `ORDER BY` value in `RANGE` mode. This is equivalent to setting the frame end to `1 PRECEDING`.
• `EXCLUDE NO OTHERS`: Includes all rows in the frame. This is the default if no exclusion option is specified.

Time Units#

The time units that can be used in window functions are:

• `day`
• `hour`
• `minute`
• `second`
• `millisecond`
• `microsecond`

Plural forms of these time units are also accepted.

avg#

In the context of window functions, `avg(value)` calculates the average of `value` over the set of rows defined by the window frame.

Arguments:

• `value`: The column of numeric values to calculate the average of.

Return value:

• The average of `value` for the rows in the window frame.

Description

When used as a window function, `avg()` operates on a "window" of rows defined by the `OVER` clause. The rows in this window are determined by the `PARTITION BY`, `ORDER BY`, and frame specification components of the `OVER` clause.

The `avg()` function respects the frame clause, meaning it only includes rows within the specified frame in the calculation. The result is a separate average for each row, based on the corresponding window of rows.

Note that the order of rows in the result set is not guaranteed to be the same with each execution of the query. To ensure a consistent order, use an `ORDER BY` clause outside of the `OVER` clause.

Examples:

Examples below use `trades` table:

symbol SYMBOL,
price DOUBLE,
amount DOUBLE,
timestamp TIMESTAMP
) TIMESTAMP (timestamp)
PARTITION BY DAY WAL;
INSERT INTO trades(symbol, price, amount, timestamp)
VALUES
('ETH-USD', 2615.54, 0.00044, '2022-03-08 18:03:57'),
('BTC-USD', 39269.98, 0.001, '2022-03-08 18:03:57'),
('BTC-USD', 39265.31, 0.000127, '2022-03-08 18:03:58'),
('BTC-USD', 39265.31, 0.000245, '2022-03-08 18:03:58'),
('BTC-USD', 39265.31, 0.000073, '2022-03-08 18:03:58'),
('BTC-USD', 39263.28, 0.00392897, '2022-03-08 18:03:58'),
('ETH-USD', 2615.35, 0.02245868, '2022-03-08 18:03:58'),
('ETH-USD', 2615.36, 0.03244613, '2022-03-08 18:03:58'),
('BTC-USD', 39265.27, 0.00006847, '2022-03-08 18:03:58'),
('BTC-USD', 39262.42, 0.00046562, '2022-03-08 18:03:58');

Moving average price over latest 4 rows#

SELECT symbol, price, amount, timestamp,
avg(price) OVER (PARTITION BY symbol
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
symbolpriceamounttimestampavg
ETH-USD2615.540.000442022-03-08T18:03:57.609765Z2615.54
BTC-USD39269.980.0012022-03-08T18:03:57.710419Z39269.98
BTC-USD39265.310.0001272022-03-08T18:03:58.357448Z39267.645
BTC-USD39265.310.0002452022-03-08T18:03:58.357448Z39266.8666
BTC-USD39265.310.0000732022-03-08T18:03:58.357448Z39266.4775
BTC-USD39263.280.003928972022-03-08T18:03:58.357448Z39264.8025
ETH-USD2615.350.022458682022-03-08T18:03:58.612275Z2615.445
ETH-USD2615.360.032446132022-03-08T18:03:58.612275Z2615.4166
BTC-USD39265.270.000068472022-03-08T18:03:58.660121Z39264.7925
BTC-USD39262.420.000465622022-03-08T18:03:58.660121Z39264.07

Moving average price over preceding X rows#

If frame is specified only on preceding rows, `avg()` returns null until at least one non-null value enters the frame.

SELECT symbol, price, amount, timestamp,
avg(price) OVER (PARTITION BY symbol
ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING)
symbolpriceamounttimestampavg
ETH-USD2615.540.000442022-03-08T18:03:57.609765Znull
BTC-USD39269.980.0012022-03-08T18:03:57.710419Znull
BTC-USD39265.310.0001272022-03-08T18:03:58.357448Znull
BTC-USD39265.310.0002452022-03-08T18:03:58.357448Znull
BTC-USD39265.310.0000732022-03-08T18:03:58.357448Znull
BTC-USD39263.280.003928972022-03-08T18:03:58.357448Z39269.98
ETH-USD2615.350.022458682022-03-08T18:03:58.612275Znull
ETH-USD2615.360.032446132022-03-08T18:03:58.612275Znull
BTC-USD39265.270.000068472022-03-08T18:03:58.660121Z39267.6450
BTC-USD39262.420.000465622022-03-08T18:03:58.660121Z39266.8666

Moving average price over values in the latest second#

SELECT symbol, price, amount, timestamp,
avg(price) OVER (PARTITION BY symbol
ORDER BY timestamp
RANGE BETWEEN '1' SECOND PRECEDING AND CURRENT ROW)
ORDER BY timestamp
symbolpriceamounttimestampavg
ETH-USD2615.540.000442022-03-08T18:03:57.609765Z2615.54
BTC-USD39269.980.0012022-03-08T18:03:57.710419Z39269.98
BTC-USD39265.310.0001272022-03-08T18:03:58.357448Z39267.645
BTC-USD39265.310.0002452022-03-08T18:03:58.357448Z39266.8666
BTC-USD39265.310.0000732022-03-08T18:03:58.357448Z39266.4775
BTC-USD39263.280.003928972022-03-08T18:03:58.357448Z39265.838
ETH-USD2615.350.022458682022-03-08T18:03:58.612275Z2615.35
ETH-USD2615.360.032446132022-03-08T18:03:58.612275Z2615.355
BTC-USD39265.270.000068472022-03-08T18:03:58.660121Z39265.7433
BTC-USD39262.420.000465622022-03-08T18:03:58.660121Z39265.2685

Moving average price over values in the latest second, descending designated timestamp order#

SELECT symbol, price, amount, timestamp,
avg(price) OVER (PARTITION BY symbol
ORDER BY timestamp DESC
RANGE BETWEEN '1' SECOND PRECEDING AND CURRENT ROW)
ORDER BY timestamp DESC
symbolpriceamounttimestampavg
BTC-USD39262.420.000465622022-03-08T18:03:58.660121Z39262.42
BTC-USD39265.270.000068472022-03-08T18:03:58.660121Z39263.845
ETH-USD2615.360.032446132022-03-08T18:03:58.612275Z2615.36
ETH-USD2615.350.022458682022-03-08T18:03:58.612275Z2615.355
BTC-USD39263.280.003928972022-03-08T18:03:58.357448Z39263.6566
BTC-USD39265.310.0000732022-03-08T18:03:58.357448Z39264.07
BTC-USD39265.310.0002452022-03-08T18:03:58.357448Z39264.318
BTC-USD39265.310.0001272022-03-08T18:03:58.357448Z39264.4833
BTC-USD39269.980.0012022-03-08T18:03:57.710419Z39265.2685
ETH-USD2615.540.000442022-03-08T18:03:57.609765Z2615.54

Moving average over default frame#

Default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which spans whole partition in absence of ORDER BY clause.

SELECT symbol, price, amount, timestamp,
avg(price) OVER (PARTITION BY symbol)
ORDER BY timestamp
symbolpriceamounttimestampavg
ETH-USD2615.540.000442022-03-08T18:03:57.609765Z2615.4166
BTC-USD39269.980.0012022-03-08T18:03:57.710419Z39265.2685
BTC-USD39265.310.0001272022-03-08T18:03:58.357448Z39265.2685
BTC-USD39265.310.0002452022-03-08T18:03:58.357448Z39265.2685
BTC-USD39265.310.0000732022-03-08T18:03:58.357448Z39265.2685
BTC-USD39263.280.003928972022-03-08T18:03:58.357448Z39265.2685
ETH-USD2615.350.022458682022-03-08T18:03:58.612275Z2615.4166
ETH-USD2615.360.032446132022-03-08T18:03:58.612275Z2615.4166
BTC-USD39265.270.000068472022-03-08T18:03:58.660121Z39265.2685
BTC-USD39262.420.000465622022-03-08T18:03:58.660121Z39265.2685

Moving average over default ordered frame#

SELECT symbol, price, amount, timestamp,
avg(price) OVER (PARTITION BY symbol ORDER BY timestamp)
ORDER BY timestamp
symbolpriceamounttimestampavg
ETH-USD2615.540.000442022-03-08T18:03:57.609765Z2615.54
BTC-USD39269.980.0012022-03-08T18:03:57.710419Z39269.98
BTC-USD39265.310.0001272022-03-08T18:03:58.357448Z39267.645
BTC-USD39265.310.0002452022-03-08T18:03:58.357448Z39266.8666
BTC-USD39265.310.0000732022-03-08T18:03:58.357448Z39266.4775
BTC-USD39263.280.003928972022-03-08T18:03:58.357448Z39265.838
ETH-USD2615.350.022458682022-03-08T18:03:58.612275Z2615.445
ETH-USD2615.360.032446132022-03-08T18:03:58.612275Z2615.4166
BTC-USD39265.270.000068472022-03-08T18:03:58.660121Z39265.7433
BTC-USD39262.420.000465622022-03-08T18:03:58.660121Z39265.2685

Moving average over whole result set#

SELECT symbol, price, amount, timestamp,
avg(price) OVER ()
ORDER BY timestamp
symbolpriceamounttimestampavg
ETH-USD2615.540.000442022-03-08T18:03:57.609765Z28270.3130
BTC-USD39269.980.0012022-03-08T18:03:57.710419Z28270.3130
BTC-USD39265.310.0001272022-03-08T18:03:58.357448Z28270.3130
BTC-USD39265.310.0002452022-03-08T18:03:58.357448Z28270.3130
BTC-USD39265.310.0000732022-03-08T18:03:58.357448Z28270.3130
BTC-USD39263.280.003928972022-03-08T18:03:58.357448Z28270.3130
ETH-USD2615.350.022458682022-03-08T18:03:58.612275Z28270.3130
ETH-USD2615.360.032446132022-03-08T18:03:58.612275Z28270.3130
BTC-USD39265.270.000068472022-03-08T18:03:58.660121Z28270.3130
BTC-USD39262.420.000465622022-03-08T18:03:58.660121Z28270.3130

first_value#

In the context of window functions, `first_value(value)` calculates the first `value` in the set of rows defined by the window frame.

Arguments:

• `value`: Any numeric value.

Return value:

• The first occurrence of `value` (including null) for the rows in the window frame.

Description

`first_value()` operates on a "window" of rows defined by the `OVER` clause. The rows in this window are determined by the `PARTITION BY`, `ORDER BY`, and frame specification components of the `OVER` clause.

The `first_value()` function respects the frame clause, meaning it only includes rows within the specified frame in the calculation. The result is a separate value for each row, based on the corresponding window of rows.

Note that the order of rows in the result set is not guaranteed to be the same with each execution of the query. To ensure a consistent order, use an `ORDER BY` clause outside of the `OVER` clause.

Examples:

Examples below use `trades` table defined above.

First price over latest 4 rows#

SELECT
symbol,
price,
amount,
timestamp,
first_value(price) OVER (
PARTITION BY symbol
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
)
FROM
symbolpriceamounttimestampfirst_value
ETH-USD2615.540.000442022-03-08T18:03:57.000000Z2615.54
BTC-USD39269.980.0012022-03-08T18:03:57.000000Z39269.98
BTC-USD39265.310.0001272022-03-08T18:03:58.000000Z39269.98
BTC-USD39265.310.0002452022-03-08T18:03:58.000000Z39269.98
BTC-USD39265.310.0000732022-03-08T18:03:58.000000Z39269.98
BTC-USD39263.280.003928972022-03-08T18:03:58.000000Z39265.31
ETH-USD2615.350.022458682022-03-08T18:03:58.000000Z2615.54
ETH-USD2615.360.032446132022-03-08T18:03:58.000000Z2615.54
BTC-USD39265.270.000068472022-03-08T18:03:58.000000Z39265.31
BTC-USD39262.420.000465622022-03-08T18:03:58.000000Z39265.31

First price over preceding rows except 4 latest#

If frame is specified only on preceding rows, `first_value()` returns null until at least one non-null value enters the frame.

SELECT
symbol,
price,
amount,
timestamp,
first_value(price) OVER (
PARTITION BY symbol
ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING
)
FROM
symbolpriceamounttimestampfirst_value
ETH-USD2615.540.000442022-03-08T18:03:57.000000Znull
BTC-USD39269.980.0012022-03-08T18:03:57.000000Znull
BTC-USD39265.310.0001272022-03-08T18:03:58.000000Znull
BTC-USD39265.310.0002452022-03-08T18:03:58.000000Znull
BTC-USD39265.310.0000732022-03-08T18:03:58.000000Znull
BTC-USD39263.280.003928972022-03-08T18:03:58.000000Z39269.98
ETH-USD2615.350.022458682022-03-08T18:03:58.000000Znull
ETH-USD2615.360.032446132022-03-08T18:03:58.000000Znull
BTC-USD39265.270.000068472022-03-08T18:03:58.000000Z39269.98
BTC-USD39262.420.000465622022-03-08T18:03:58.000000Z39269.98

First value of price in the latest second#

SELECT symbol, price, amount, timestamp,
first_value(price) OVER (PARTITION BY symbol
ORDER BY timestamp
RANGE BETWEEN '1' SECOND PRECEDING AND CURRENT ROW)
ORDER BY timestamp
symbolpriceamounttimestampfirst_value
ETH-USD2615.540.000442022-03-08T18:03:57.000000Z2615.54
BTC-USD39269.980.0012022-03-08T18:03:57.000000Z39269.98
BTC-USD39265.310.0001272022-03-08T18:03:58.000000Z39269.98
BTC-USD39265.310.0002452022-03-08T18:03:58.000000Z39269.98
BTC-USD39265.310.0000732022-03-08T18:03:58.000000Z39269.98
BTC-USD39263.280.003928972022-03-08T18:03:58.000000Z39269.98
ETH-USD2615.350.022458682022-03-08T18:03:58.000000Z2615.54
ETH-USD2615.360.032446132022-03-08T18:03:58.000000Z2615.54
BTC-USD39265.270.000068472022-03-08T18:03:58.000000Z39269.98
BTC-USD39262.420.000465622022-03-08T18:03:58.000000Z39269.98

First value of price in the latest second, descending designated timestamp order#

SELECT
symbol,
price,
amount,
timestamp,
first_value(price) OVER (
PARTITION BY symbol
ORDER BY timestamp DESC
RANGE BETWEEN '1' SECOND PRECEDING AND CURRENT ROW
)
FROM
ORDER BY
timestamp DESC
symbolpriceamounttimestampfirst_value
BTC-USD39262.420.000465622022-03-08T18:03:58.000000Z39262.42
BTC-USD39265.270.000068472022-03-08T18:03:58.000000Z39262.42
ETH-USD2615.360.032446132022-03-08T18:03:58.000000Z2615.36
ETH-USD2615.350.022458682022-03-08T18:03:58.000000Z2615.36
BTC-USD39263.280.003928972022-03-08T18:03:58.000000Z39262.42
BTC-USD39265.310.0000732022-03-08T18:03:58.000000Z39262.42
BTC-USD39265.310.0002452022-03-08T18:03:58.000000Z39262.42
BTC-USD39265.310.0001272022-03-08T18:03:58.000000Z39262.42
BTC-USD39269.980.0012022-03-08T18:03:57.000000Z39262.42
ETH-USD2615.540.000442022-03-08T18:03:57.000000Z2615.36

First value of price in default frame#

Default frame is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`, which spans the whole partition in absence of an `ORDER BY` clause.

SELECT
symbol,
price,
amount,
timestamp,
first_value(price) OVER (
PARTITION BY symbol
)
FROM
ORDER BY
timestamp
symbolpriceamounttimestampfirst_value
ETH-USD2615.540.000442022-03-08T18:03:57.000000Z2615.54
BTC-USD39269.980.0012022-03-08T18:03:57.000000Z39269.98
BTC-USD39265.310.0001272022-03-08T18:03:58.000000Z39269.98
BTC-USD39265.310.0002452022-03-08T18:03:58.000000Z39269.98
BTC-USD39265.310.0000732022-03-08T18:03:58.000000Z39269.98
BTC-USD39263.280.003928972022-03-08T18:03:58.000000Z39269.98
ETH-USD2615.350.022458682022-03-08T18:03:58.000000Z2615.54
ETH-USD2615.360.032446132022-03-08T18:03:58.000000Z2615.54
BTC-USD39265.270.000068472022-03-08T18:03:58.000000Z39269.98
BTC-USD39262.420.000465622022-03-08T18:03:58.000000Z39269.98

First value of price in default ordered frame#

SELECT
symbol,
price,
amount,
timestamp,
first_value(price) OVER (
PARTITION BY symbol
ORDER BY timestamp
)
FROM
ORDER BY
timestamp
symbolpriceamounttimestampfirst_value
ETH-USD2615.540.000442022-03-08T18:03:57.000000Z2615.54
BTC-USD39269.980.0012022-03-08T18:03:57.000000Z39269.98
BTC-USD39265.310.0001272022-03-08T18:03:58.000000Z39269.98
BTC-USD39265.310.0002452022-03-08T18:03:58.000000Z39269.98
BTC-USD39265.310.0000732022-03-08T18:03:58.000000Z39269.98
BTC-USD39263.280.003928972022-03-08T18:03:58.000000Z39269.98
ETH-USD2615.350.022458682022-03-08T18:03:58.000000Z2615.54
ETH-USD2615.360.032446132022-03-08T18:03:58.000000Z2615.54
BTC-USD39265.270.000068472022-03-08T18:03:58.000000Z39269.98
BTC-USD39262.420.000465622022-03-08T18:03:58.000000Z39269.98

First value of price in whole result set#

SELECT
symbol,
price,
amount,
timestamp,
first_value(price) OVER ()
FROM
ORDER BY
timestamp
symbolpriceamounttimestampfirst_value
ETH-USD2615.540.000442022-03-08T18:03:57.000000Z2615.54
BTC-USD39269.980.0012022-03-08T18:03:57.000000Z2615.54
BTC-USD39265.310.0001272022-03-08T18:03:58.000000Z2615.54
BTC-USD39265.310.0002452022-03-08T18:03:58.000000Z2615.54
BTC-USD39265.310.0000732022-03-08T18:03:58.000000Z2615.54
BTC-USD39263.280.003928972022-03-08T18:03:58.000000Z2615.54
ETH-USD2615.350.022458682022-03-08T18:03:58.000000Z2615.54
ETH-USD2615.360.032446132022-03-08T18:03:58.000000Z2615.54
BTC-USD39265.270.000068472022-03-08T18:03:58.000000Z2615.54
BTC-USD39262.420.000465622022-03-08T18:03:58.000000Z2615.54

rank#

In the context of window functions, `rank()` assigns a unique rank to each row within the window frame, with the same rank assigned to rows with the same values. Rows with equal values receive the same rank, and a gap appears in the sequence for the next distinct value; that is, the `row_number` of the first row in its peer group.

Arguments:

• `rank()` does not require arguments.

Return value:

• The rank of each row within the window frame. Return value type is `long`.

Description

When used as a window function, `rank()` operates on a "window" of rows defined by the `OVER` clause. The rows in this window are determined by the `PARTITION BY` and `ORDER BY` components of the `OVER` clause.

The `rank()` function assigns a unique rank to each row within its window, with the same rank assigned to rows with the same values in the `ORDER BY` clause of the `OVER` clause. It ignores the frame clause, meaning it considers all rows in each partition, regardless of the frame specification.

Note that the order of rows in the result set is not guaranteed to be the same with each execution of the query. To ensure a consistent order, use an `ORDER BY` clause outside of the `OVER` clause.

Examples:

For a given table `housing`:

CREATE TABLE housing (
id INT,
price DOUBLE,
rating INT,
location STRING,
date_sold TIMESTAMP
);
INSERT INTO housing(id, price, rating, location, date_sold)
VALUES
(2, 246.3393, 1, 'alcatraz_ave', '2021-02-01 00:00:00'),
(10, 69.2601, 5, 'alcatraz_ave', '2021-02-01 04:00:00'),
(15, 616.2569, 3, 'westbrae', '2021-02-01 08:00:00'),
(3, 112.7856, 5, 'south_side', '2021-02-01 12:00:00'),
(17, 993.3345, 1, 'south_side', '2021-02-01 16:00:00'),
(8, 937.4274, 1, 'berkeley_hills', '2021-02-01 20:00:00'),
(4, 207.7797, 1, 'alcatraz_ave', '2021-02-02 00:00:00'),
(17, 352.3193, 3, 'downtown', '2021-02-02 04:00:00'),
(3, 140.0437, 1, 'westbrae', '2021-02-02 08:00:00'),
(15, 971.7142, 1, 'westbrae', '2021-02-02 12:00:00');

The following query uses `rank()` to display output based on the rating:

SELECT
location,
price,
date_sold,
rating,
rank() OVER (
ORDER BY rating ASC
) AS rank
FROM
housing
ORDER BY
rank
locationpricedate_soldratingrank
westbrae971.71422021-02-02T12:00:00.000000Z11
westbrae140.04372021-02-02T08:00:00.000000Z11
alcatraz_ave207.77972021-02-02T00:00:00.000000Z11
berkeley_hills937.42742021-02-01T20:00:00.000000Z11
south_side993.33452021-02-01T16:00:00.000000Z11
alcatraz_ave246.33932021-02-01T00:00:00.000000Z11
downtown352.31932021-02-02T04:00:00.000000Z37
westbrae616.25692021-02-01T08:00:00.000000Z37
south_side112.78562021-02-01T12:00:00.000000Z59
alcatraz_ave69.26012021-02-01T04:00:00.000000Z59

row_number#

In the context of window functions, `row_number()` assigns a unique row number to each row within the window frame. For each partition, the row number starts with one and increments by one.

Arguments:

• `row_number()` does not require arguments.

Return value:

• The row number of each row within the window frame. Return value type is `long`.

Description

When used as a window function, `row_number()` operates on a "window" of rows defined by the `OVER` clause. The rows in this window are determined by the `PARTITION BY` and `ORDER BY` components of the `OVER` clause.

The `row_number()` function assigns a unique row number to each row within its window, starting at one for the first row in each partition and incrementing by one for each subsequent row. It ignores the frame clause, meaning it considers all rows in each partition, regardless of the frame specification.

Note that the order of rows in the result set is not guaranteed to be the same with each execution of the query. To ensure a consistent order, use an `ORDER BY` clause outside of the `OVER` clause.

Examples:

Given a table `trades`, the queries below use `row_number()` with a `WHERE` clause to filter trading records added within one day.

The following query assigns row numbers and orders output based on them:

SELECT
symbol,
price,
amount,
row_number() OVER () AS row_num
WHERE timestamp > DATEADD('d', -1, NOW())
ORDER BY row_num ASC;
-- The ORDER BY clause arranges the output based on the assigned row_num.
symbolpriceamountrow_num
BTC-USD20633.470.175692981
ETH-USD1560.041.32892
ETH-USD1560.040.33
ETH-USD15601.404267864
BTC-USD20633.480.001790925

The following query groups the table based on `symbol` and assigns row numbers to each group based on `price`:

SELECT
symbol,
price,
amount,
row_number() OVER (PARTITION BY symbol ORDER BY price) AS row_num
WHERE timestamp > DATEADD('d', -1, NOW())
ORDER BY row_num ASC;
-- The ORDER BY clause arranges the output based on the assigned row_num.
symbolpriceamountrow_num
BTC-USD1479.410.109046331
ETH-USD200000.11
BTC-USD1479.450.022
ETH-USD200000.0002492

sum#

In the context of window functions, `sum(value)` calculates the sum of `value` in the set of rows defined by the window frame.

Arguments:

• `value`: Any numeric value.

Return value:

• The sum of `value` for the rows in the window frame.

Description

When used as a window function, `sum()` operates on a "window" of rows defined by the `OVER` clause. The rows in this window are determined by the `PARTITION BY`, `ORDER BY`, and frame specification components of the `OVER` clause.

The `sum()` function respects the frame clause, meaning it only includes rows within the specified frame in the calculation. The result is a separate value for each row, based on the corresponding window of rows.

Note that the order of rows in the result set is not guaranteed to be the same with each execution of the query. To ensure a consistent order, use an `ORDER BY` clause outside of the `OVER` clause.

Examples:

Examples below use `trades` table defined above.

Moving price sum over latest 4 rows#

SELECT
symbol,
price,
amount,
timestamp,
sum(price) OVER (
PARTITION BY symbol
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
)
FROM
symbolpriceamounttimestampsum
ETH-USD2615.540.000442022-03-08T18:03:57.000000Z2615.54
BTC-USD39269.980.0012022-03-08T18:03:57.000000Z39269.98
BTC-USD39265.310.0001272022-03-08T18:03:58.000000Z78535.29
BTC-USD39265.310.0002452022-03-08T18:03:58.000000Z117800.6
BTC-USD39265.310.0000732022-03-08T18:03:58.000000Z157065.91
BTC-USD39263.280.003928972022-03-08T18:03:58.000000Z157059.21
ETH-USD2615.350.022458682022-03-08T18:03:58.000000Z5230.89
ETH-USD2615.360.032446132022-03-08T18:03:58.000000Z7846.25
BTC-USD39265.270.000068472022-03-08T18:03:58.000000Z157059.17
BTC-USD39262.420.000465622022-03-08T18:03:58.000000Z157056.28

Moving price sum over preceding rows except 4 latest#

If frame is specified only on preceding rows, `sum()` returns null until at least one non-null value enters the frame.

SELECT
symbol,
price,
amount,
timestamp,
sum(price) OVER (
PARTITION BY symbol
ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING
)
FROM
symbolpriceamounttimestampsum
ETH-USD2615.540.000442022-03-08T18:03:57.000000Znull
BTC-USD39269.980.0012022-03-08T18:03:57.000000Znull
BTC-USD39265.310.0001272022-03-08T18:03:58.000000Znull
BTC-USD39265.310.0002452022-03-08T18:03:58.000000Znull
BTC-USD39265.310.0000732022-03-08T18:03:58.000000Znull
BTC-USD39263.280.003928972022-03-08T18:03:58.000000Z39269.98
ETH-USD2615.350.022458682022-03-08T18:03:58.000000Znull
ETH-USD2615.360.032446132022-03-08T18:03:58.000000Znull
BTC-USD39265.270.000068472022-03-08T18:03:58.000000Z78535.29
BTC-USD39262.420.000465622022-03-08T18:03:58.000000Z117800.6

Moving price sum over the latest second#

SELECT
symbol,
price,
amount,
timestamp,
sum(price) OVER (
PARTITION BY symbol
ORDER BY timestamp
RANGE BETWEEN '1' SECOND PRECEDING AND CURRENT ROW
)
FROM
ORDER BY
timestamp
symbolpriceamounttimestampsum
ETH-USD2615.540.000442022-03-08T18:03:57.000000Z2615.54
BTC-USD39269.980.0012022-03-08T18:03:57.000000Z39269.98
BTC-USD39265.310.0001272022-03-08T18:03:58.000000Z78535.29
BTC-USD39265.310.0002452022-03-08T18:03:58.000000Z117800.6
BTC-USD39265.310.0000732022-03-08T18:03:58.000000Z157065.91
BTC-USD39263.280.003928972022-03-08T18:03:58.000000Z196329.19
ETH-USD2615.350.022458682022-03-08T18:03:58.000000Z5230.89
ETH-USD2615.360.032446132022-03-08T18:03:58.000000Z7846.25
BTC-USD39265.270.000068472022-03-08T18:03:58.000000Z235594.46
BTC-USD39262.420.000465622022-03-08T18:03:58.000000Z274856.88

Moving price sum over the latest second, descending designated timestamp order#

SELECT
symbol,
price,
amount,
timestamp,
sum(price) OVER (
PARTITION BY symbol
ORDER BY timestamp DESC
RANGE BETWEEN '1' SECOND PRECEDING AND CURRENT ROW
)
FROM
ORDER BY
timestamp DESC
symbolpriceamounttimestampsum
BTC-USD39262.420.000465622022-03-08T18:03:58.000000Z39262.42
BTC-USD39265.270.000068472022-03-08T18:03:58.000000Z78527.69
ETH-USD2615.360.032446132022-03-08T18:03:58.000000Z2615.36
ETH-USD2615.350.022458682022-03-08T18:03:58.000000Z5230.71
BTC-USD39263.280.003928972022-03-08T18:03:58.000000Z117790.97
BTC-USD39265.310.0000732022-03-08T18:03:58.000000Z157056.28
BTC-USD39265.310.0002452022-03-08T18:03:58.000000Z196321.59
BTC-USD39265.310.0001272022-03-08T18:03:58.000000Z235586.9
BTC-USD39269.980.0012022-03-08T18:03:57.000000Z274856.88
ETH-USD2615.540.000442022-03-08T18:03:57.000000Z7846.25

Moving price sum over default frame#

Default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which spans whole partition in absence of ORDER BY clause.

SELECT
symbol,
price,
amount,
timestamp,
sum(price) OVER (
PARTITION BY symbol
)
FROM
ORDER BY
timestamp
symbolpriceamounttimestampsum
ETH-USD2615.540.000442022-03-08T18:03:57.000000Z7846.25
BTC-USD39269.980.0012022-03-08T18:03:57.000000Z274856.88
BTC-USD39265.310.0001272022-03-08T18:03:58.000000Z274856.88
BTC-USD39265.310.0002452022-03-08T18:03:58.000000Z274856.88
BTC-USD39265.310.0000732022-03-08T18:03:58.000000Z274856.88
BTC-USD39263.280.003928972022-03-08T18:03:58.000000Z274856.88
ETH-USD2615.350.022458682022-03-08T18:03:58.000000Z7846.25
ETH-USD2615.360.032446132022-03-08T18:03:58.000000Z7846.25
BTC-USD39265.270.000068472022-03-08T18:03:58.000000Z274856.88
BTC-USD39262.420.000465622022-03-08T18:03:58.000000Z274856.88

Moving price sum over default ordered frame#

SELECT
symbol,
price,
amount,
timestamp,
sum(price) OVER (
PARTITION BY symbol
ORDER BY timestamp
)
FROM
ORDER BY
timestamp
symbolpriceamounttimestampsum
ETH-USD2615.540.000442022-03-08T18:03:57.000000Z2615.54
BTC-USD39269.980.0012022-03-08T18:03:57.000000Z39269.98
BTC-USD39265.310.0001272022-03-08T18:03:58.000000Z78535.29
BTC-USD39265.310.0002452022-03-08T18:03:58.000000Z117800.6
BTC-USD39265.310.0000732022-03-08T18:03:58.000000Z157065.91
BTC-USD39263.280.003928972022-03-08T18:03:58.000000Z196329.19
ETH-USD2615.350.022458682022-03-08T18:03:58.000000Z5230.89
ETH-USD2615.360.032446132022-03-08T18:03:58.000000Z7846.25
BTC-USD39265.270.000068472022-03-08T18:03:58.000000Z235594.46
BTC-USD39262.420.000465622022-03-08T18:03:58.000000Z274856.88

Moving price sum over whole result set#

SELECT
symbol,
price,
amount,
timestamp,
sum(price) OVER ()
FROM
ORDER BY
timestamp
symbolpriceamounttimestampsum
ETH-USD2615.540.000442022-03-08T18:03:57.000000Z282703.13
BTC-USD39269.980.0012022-03-08T18:03:57.000000Z282703.13
BTC-USD39265.310.0001272022-03-08T18:03:58.000000Z282703.13
BTC-USD39265.310.0002452022-03-08T18:03:58.000000Z282703.13
BTC-USD39265.310.0000732022-03-08T18:03:58.000000Z282703.13
BTC-USD39263.280.003928972022-03-08T18:03:58.000000Z282703.13
ETH-USD2615.350.022458682022-03-08T18:03:58.000000Z282703.13
ETH-USD2615.360.032446132022-03-08T18:03:58.000000Z282703.13
BTC-USD39265.270.000068472022-03-08T18:03:58.000000Z282703.13
BTC-USD39262.420.000465622022-03-08T18:03:58.000000Z282703.13

โญ Something missing? Page not helpful? Please suggest an edit on GitHub.