Presto 0.52 Documentation

2.8. Window Functions

2.8. Window Functions

Window functions perform calculations across rows of the query result. They run after the HAVING clause but before the ORDER BY clause. Invoking a window function requires special syntax using the OVER clause to specify the window. A window has three components:

For example, the following query ranks orders for each clerk by price:

SELECT orderid, clerk, totalprice,
       RANK() OVER (PARTITION BY clerk
                    ORDER BY totalprice DESC) AS rnk
FROM orders
ORDER BY clerk, rnk

Ranking Functions

cume_dist() → bigint

Returns the cumulative distribution of a value in a group of values. The result is the number of rows preceding or peer with the row in the window ordering of the window partition divided by the total number of rows in the window partition. Thus, any tie values in the ordering will evaluate to the same distribution value.

dense_rank() → bigint

Returns the rank of a value in in a group of values. This is similar to rank(), except that tie values do not produce gaps in the sequence.

percent_rank() → bigint

Returns the percentage ranking of a value in group of values. The result is (r - 1) / (n - 1) where r is the rank() of the row and n is the total number of rows in the window partition.

rank() → bigint

Returns the rank of a value in a group of values. The rank is one plus the number of rows preceding the row that are not peer with the row. Thus, tie values in the ordering will produce gaps in the sequence. The ranking is performed for each window partition.

row_number() → bigint

Returns a unique, sequential number for each row, starting with one, according to the ordering of rows within the window partition.