Cloud

Overview

Window functions is a group of SQL functions, that operate on a partition or “window” of a result set, returning values for every row within that window. Redpanda SQL supports the following window functions and clauses:

Window functions

Function Name Description

count()

Counts all the rows or those specified by the given expression

avg()

Calculates the average (arithmetic mean) of a set of numeric values within a window

sum()

Calculates and returns the sum of values from the input column or expression values

min()

Computes the minimum value of an expression across a set of rows

max()

Computes the maximum value of an expression across a set of rows

bool_and()

Evaluates whether all values within a specified window of rows are true

bool_or()

Evaluates whether at least one value within a specified window of rows is true

Ranking functions

Function Name Description

row_number()

Returns the current row index within its partition (beginning with 1)

rank()

Calculates and returns the rank of a value within a specified group of values

dense_rank()

Assigns ranks within a partition without gaps (ties receive the same rank and subsequent ranks are not skipped)

ntile()

Divides an ordered data set into a specified number of approximately equal groups

Distribution functions

Function Name Description

cume_dist()

Calculates the cumulative distribution of a value within a set of values

percent_rank()

Calculates and returns the percent rank of a value within a specified group of values

Value functions

Function Name Description

first_value()

Returns the first value in an ordered set of values within a specified partition

last_value()

Returns the last value in an ordered set of values within a specified partition

nth_value()

Returns a value from the nth row in an ordered partition of a result set

lag()

Returns the values for a row located at a defined offset, either above or below the current row within the partition

lead()

Returns the values for a row located at a defined offset, either above or below the current row within the partition

Window clause

Clause Name Description

OVER

Defines the window specification and is mandatory for window functions

WINDOW

Optional clause that defines one or more named window specifications

Important notes

There are a few essential things to remember when using window functions in Redpanda SQL:

  • Verify that you can effectively use window functions alongside the PARTITION BY, ORDER BY and FRAME clauses as part of your window specification

  • Ensure the window specification chaining is supported by executing the following command: SELECT SUM(i0) OVER w2 FROM tb1 WINDOW w1 AS (PARTITION BY i1), w2 AS (w1 ROWS CURRENT ROW)

  • The FRAME clause of the window specification is restricted to the ROWS clause and does not include frame exclusion