Window functions

Reference for window functions in HotSQL. Names, signatures, and examples match the engine exactly.

cume_dist

Relative rank of the current row: (number of rows preceding or peer with the current row) / (total rows).

cume_dist()

-- Example usage of the cume_dist window function:
SELECT salary,
    cume_dist() OVER (ORDER BY salary) AS cume_dist
FROM employees;

+--------+-----------+
| salary | cume_dist |
+--------+-----------+
| 30000  | 0.33      |
| 50000  | 0.67      |
| 70000  | 1.00      |
+--------+-----------+

dense_rank

Rank of the current row within its partition, without gaps. Rows with equal ORDER BY values share a rank and the next rank is consecutive.

dense_rank()

Related: rank

first_value

Value of expression from the first row of the window frame.

first_value(expression)

Arguments

• expression: Expression to evaluate.

Related: last_value, nth_value

lag

Value of expression from a row offset positions before the current row in the partition (offset defaults to 1). Returns default (or NULL) when out of range.

lag(expression [, offset [, default]])

Arguments

• expression: Expression to evaluate on the offset row.
• offset: Number of rows back. Default 1.
• default: Value returned when the offset is out of range.

Related: lead

last_value

Value of expression from the last row of the window frame.

last_value(expression)

Arguments

• expression: Expression to evaluate.

Related: first_value, nth_value

lead

Value of expression from a row offset positions after the current row in the partition (offset defaults to 1). Returns default (or NULL) when out of range.

lead(expression [, offset [, default]])

Arguments

• expression: Expression to evaluate on the offset row.
• offset: Number of rows forward. Default 1.
• default: Value returned when the offset is out of range.

Related: lag

nth_value

Value of expression from the nth row (1-based) of the window frame.

nth_value(expression, n)

Arguments

• expression: Expression to evaluate.
• n: 1-based row position within the frame.

Related: first_value, last_value

ntile

Integer ranging from 1 to the argument value, dividing the partition as equally as possible

ntile(expression)

Arguments

• expression: An integer describing the number groups the partition should be split into

-- Example usage of the ntile window function:
SELECT employee_id,
    salary,
    ntile(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

+-------------+--------+----------+
| employee_id | salary | quartile |
+-------------+--------+----------+
| 1           | 90000  | 1        |
| 2           | 85000  | 1        |
| 3           | 80000  | 2        |
| 4           | 70000  | 2        |
| 5           | 60000  | 3        |
| 6           | 50000  | 3        |
| 7           | 40000  | 4        |
| 8           | 30000  | 4        |
+-------------+--------+----------+

percent_rank

Relative rank of the current row: (rank - 1) / (total partition rows - 1).

percent_rank()

Related: rank, cume_dist

rank

Rank of the current row within its partition, with gaps. Rows with equal ORDER BY values share a rank; the next rank skips ahead.

rank()

Related: dense_rank, percent_rank

row_number

Number of the current row within its partition, counting from 1.

row_number()

-- Example usage of the row_number window function:
SELECT department,
  salary,
  row_number() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

+-------------+--------+---------+
| department  | salary | row_num |
+-------------+--------+---------+
| Sales       | 70000  | 1       |
| Sales       | 50000  | 2       |
| Sales       | 50000  | 3       |
| Sales       | 30000  | 4       |
| Engineering | 90000  | 1       |
| Engineering | 80000  | 2       |
+-------------+--------+---------+