Window functions
Use window functions to compute averages, sort, rank, or count items, calculate sums, and find minimum or maximum values.
Use window functions to do the following:
Window functions overview
Window functions consist of a supported function and an .
To learn more about syntax, see , and read about the .
To see Windows functions you can use in Pinot, see .
Window function query syntax
For information about the Window function query syntax, see .
Example query layout
The following query shows the complete components of the window function. Note, PARTITION BY and ORDER BY are optional.
Partition by clause
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.
Order by clause
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. If output ordering is desired a global ORDER BY clause should be used in the query.
{RANGE|ROWS} frame_start OR
{RANGE|ROWS} BETWEEN frame_start AND frame_end; frame_start and frame_end can be any of:
UNBOUNDED PRECEDING: expression PRECEDING. May only be allowed in ROWS mode [depends on DB, some support some don’t]
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.
The OVER clause applies a specified 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.
Inside the over clause, there are three optional components, i.e. PARTITION BY clause, ORDER BY clause, and FRAME clause.
Supported Pinot window functions
Function
Description
Example
Default Value When No Record Selected
Examples of windows functions
Sum transactions by customer ID
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).
customer_id
payment_date
amount
sum
Find the minimum or maximum transaction by customer ID
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.
customer_id
payment_date
amount
min
Find the average transaction amount by customer ID
Calculate a customer’s average transaction amount for all transactions they’ve made (default frame here is UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING).
customer_id
payment_date
amount
avg
Rank year-to-date sales for a sales team
Use ROW_NUMBER() to rank team members by their year-to-date sales (default frame here is UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING).
Row
FirstName
LastName
Total sales YTD
Count the number of transactions by customer ID
Count the number of transactions made by each customer (default frame here is UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING).
customer_id
payment_date
amount
count