Window Functions
Use window functions to compute averages, sort, rank, or count items, calculate sums, and find minimum or maximum values across windows.
Last updated
Was this helpful?
Use window functions to compute averages, sort, rank, or count items, calculate sums, and find minimum or maximum values across windows.
Last updated
Was this helpful?
This is an overview of the window functions feature.
Pinot's window function (windowedCall
) has the following syntax definition:
windowedCall
refers to the actual windowed operation.
The following query shows the complete components of the window function. Note that the PARTITION BY
,ORDER BY
, and the FRAME
clauses are all optional.
If a PARTITION BY
clause is specified, the intermediate results will be grouped into different partitions based on the values of the columns appearing in the PARTITION BY
clause.
If the PARTITION BY
clause isn’t specified, the whole result will be regarded as one big partition, i.e. there is only one partition in the result set.
If an ORDER BY
clause is specified, all the rows within the same partition will be sorted based on the values of the columns appearing in the window ORDER BY
clause. The ORDER BY
clause decides the order in which the rows within a partition are to be processed.
If no ORDER BY
clause is specified while a PARTITION BY
clause is specified, the order of the rows is undefined. To order the output, use a global ORDER BY
clause in the query.
RANGE
type window frames currently cannot be used with offset PRECEDING
/ offset FOLLOWING
The following window frame clauses are currently supported:
RANGE frame_start
where frame_start
can be UNBOUNDED PRECEDING
or CURRENT ROW
(frame_end
will default to CURRENT ROW
)
ROWS frame_start
where frame_start
can be UNBOUNDED PRECEDING
, offset PRECEDING
, or CURRENT ROW
(frame_end
will default to CURRENT ROW
)
RANGE BETWEEN frame_start AND frame_end
; frame_start
can be either UNBOUNDED PRECEDING
or CURRENT ROW
and frame_end
can be either CURRENT ROW
or UNBOUNDED FOLLOWING
ROWS BETWEEN frame_start AND frame_end
; frame_start
/ frame_end
can be one of:
UNBOUNDED PRECEDING
(frame_start
only)
offset PRECEDING
where offset
is an integer literal
CURRENT ROW
offset FOLLOWING
where offset
is an integer literal
UNBOUNDED FOLLOWING
(frame_end
only)
In RANGE
mode, a frame_start
of CURRENT ROW
means the frame starts with the current row's first peer row (a row that the window's ORDER BY
clause sorts as equivalent to the current row), while a frame_end
of CURRENT ROW
means the frame ends with the current row's last peer row. In ROWS
mode, CURRENT ROW
simply means the current row.
If no ORDER BY
clause is specified, the window frame will always be RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
and cannot be modified. When an ORDER BY
clause is present, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
if no explicit window frame is defined in the query.
If there is no FRAME
, no PARTITION BY
, and no ORDER BY
clause specified in the OVER clause (empty OVER
), the whole result set is regarded as one partition, and there's one frame in the window.
Inside the over clause, there are three optional components: PARTITION BY
clause, ORDER BY
clause, and FRAME
clause.
Window functions are commonly used to do the following:
Supported window functions are listed in the following table.
Returns the average of the values for a numeric column in the defined window.
AVG(playerScore)
Double.NEGATIVE_INFINITY
BOOL_AND
Returns false
if even a single value in the window is false
, null
if a single value in the window is null
, and true
if all the values in the window are true
.
null
BOOL_OR
Returns true
if even a single value in the window is true
, null
if a single value in the window is null
, and false
if all the values in the window are false
.
null
Returns the number of values in the window
COUNT(*)
0
Returns the minimum value of a numeric column as Double
MIN(playerScore)
null
Returns the maximum value of a numeric column as Double
MAX(playerScore)
null
Returns the sum of the values for a numeric column as Double
SUM(playerScore)
null
The LEAD
function provides access to a subsequent row within the same result set, without the need for a self-join.
LEAD(column_name, offset, default_value)
The LAG
function provides access to a previous row within the same result set, without the need for a self-join.
LAG(column_name, offset, default_value)
The FIRST_VALUE
function returns the value from the first row in the window.
FIRST_VALUE(salary)
The LAST_VALUE
function returns the value from the last row in the window
LAST_VALUE(salary)
Returns the number of the current row within its partition, counting from 1.
ROW_NUMBER()
RANK
Returns the rank of the current row, with gaps - i.e., the row_number
of the first row in its peer group.
RANK()
DENSE_RANK
Returns the rank of the current row, without gaps.
DENSE_RANK()
Note that no window frame clause can be specified for ROW_NUMBER
, RANK
, and DENSE_RANK
window functions since they're applied on the entire partition by definition. Similarly, no window frame clause can be specified for LAG
and LEAD
since the row offset
is an input to those functions themselves.
Calculate the rolling sum transaction amount ordered by the payment date for each customer ID (note, the default frame here is UNBOUNDED PRECEDING
and CURRENT ROW
).
1
2023-02-14 23:22:38.996577
5.99
5.99
1
2023-02-15 16:31:19.996577
0.99
6.98
1
2023-02-15 19:37:12.996577
9.99
16.97
1
2023-02-16 13:47:23.996577
4.99
21.96
2
2023-02-17 19:23:24.996577
2.99
2.99
2
2023-02-17 19:23:24.996577
0.99
3.98
3
2023-02-16 00:02:31.996577
8.99
8.99
3
2023-02-16 13:47:36.996577
6.99
15.98
3
2023-02-17 03:43:41.996577
6.99
22.97
4
2023-02-15 07:59:54.996577
4.99
4.99
4
2023-02-16 06:37:06.996577
0.99
5.98
Calculate the least (use MIN()
) or most expensive (use MAX()
) transaction made by each customer comparing all transactions made by the customer (default frame here is UNBOUNDED PRECEDING
and UNBOUNDED FOLLOWING
). The following query shows how to find the least expensive transaction.
1
2023-02-14 23:22:38.996577
5.99
1
2023-02-15 16:31:19.996577
0.99
1
2023-02-15 19:37:12.996577
9.99
2
2023-04-30 04:34:36.996577
4.99
2
2023-04-30 12:16:09.996577
10.99
3
2023-03-23 05:38:40.996577
2.99
3
2023-04-07 08:51:51.996577
3.99
3
3 | 2023-04-08 11:15:37.996577
4.99
Calculate a customer’s average transaction amount for all transactions they’ve made (default frame here is UNBOUNDED PRECEDING
and UNBOUNDED FOLLOWING
).
1
2023-02-14 23:22:38.996577
5.99
1
2023-02-15 16:31:19.996577
0.99
1
2023-02-15 19:37:12.996577
9.99
2
2023-04-30 04:34:36.996577
4.99
2
2023-04-30 12:16:09.996577
10.99
3
2023-03-23 05:38:40.996577
2.99
3
2023-04-07 08:51:51.996577
3.99
3
2023-04-08 11:15:37.996577
4.99
Use ROW_NUMBER()
to rank team members by their year-to-date sales (default frame here is UNBOUNDED PRECEDING
and UNBOUNDED FOLLOWING
).
1
Joe
Smith
2
Alice
Davis
3
James
Jones
4
Dane
Scott
Count the number of transactions made by each customer (default frame here is UNBOUNDED PRECEDING
and UNBOUNDED FOLLOWING
).
1
2023-02-14 23:22:38.99657
10.99
2
1
2023-02-15 16:31:19.996577
8.99
2
2
2023-04-30 04:34:36.996577
23.50
3
2
2023-04-07 08:51:51.996577
12.35
3
2
2023-04-08 11:15:37.996577
8.29
3
windowFunction
refers to the window function used, see supported .
window
is the window definition / windowing mechanism, see supported .
You can jump to the section to see more concrete use cases of window functions in Pinot.
The OVER
clause applies a specified supported to compute values over a group of rows and return a single result for each row. The OVER
clause specifies how the rows are arranged and how the aggregation is done on those rows.