Use window functions-AnalyticDB(AnalyticDB)-阿里云帮助中心

AnalyticDB for PostgreSQL supports all window functions available in PostgreSQL. Window functions perform calculations across a set of rows related to the current row — such as ranking, cumulative distribution, and offset access to adjacent rows — without collapsing those rows into a single result.

For the full PostgreSQL window function reference, see Window Functions.

Quick examples

The following snippets cover the most common window function patterns:

-- Assign a unique row number within each department, ordered by salary
SELECT employee_id, department, salary,
  row_number() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

-- Rank employees by salary, with gaps for ties
SELECT employee_id, salary,
  rank() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

-- Access the previous row's value to calculate change
SELECT order_date, revenue,
  lag(revenue) OVER (ORDER BY order_date) AS prev_revenue,
  revenue - lag(revenue) OVER (ORDER BY order_date) AS change
FROM daily_sales;

-- Divide rows into four equal groups (quartiles)
SELECT employee_id, salary,
  ntile(4) OVER (ORDER BY salary) AS quartile
FROM employees;

General-purpose window functions

Function	Return type	Description	Example
`row_number()`	`BIGINT`	Returns the row's position within the partition, starting from 1.	`row_number() OVER (ORDER BY salary DESC)`
`rank()`	`BIGINT`	Returns the rank within the partition. Rows with equal values receive the same rank, and the next rank skips the tied positions. For example, three rows ranked 1 are followed by rank 4.	`rank() OVER (ORDER BY score DESC)`
`dense_rank()`	`BIGINT`	Returns a consecutive rank within the partition. Rows with equal values receive the same rank, but the next rank increments by 1 rather than skipping. For example, three rows ranked 1 are followed by rank 2.	`dense_rank() OVER (ORDER BY score DESC)`
`percent_rank()`	`DOUBLE PRECISION`	Returns the relative rank of the row as a value from 0 to 1. Formula: (Rank - 1) / (Total number of partition rows - 1), where Rank is the `rank()` result for the current row. Rows with equal values return the same result.	`percent_rank() OVER (ORDER BY score)`
`cume_dist()`	`DOUBLE PRECISION`	Returns the cumulative distribution of the row within the partition. Formula: (Number of partition rows before the current row + Number of partition rows with the same value as the current row) / Total number of partition rows.	`cume_dist() OVER (ORDER BY score)`
`ntile(number_buckets)`	`INTEGER`	Divides the partition into the specified number of equal buckets and returns the bucket number for each row. `number_buckets` must be a positive integer.	`ntile(4) OVER (ORDER BY salary)`
`lag(column [,offset [,default]])`	Same type as `column`	Returns the value from the row that is `offset` rows before the current row. If the offset row does not exist, returns `default`. `offset` defaults to 1; `default` defaults to null.	`lag(revenue, 1, 0) OVER (ORDER BY date)`
`lead(column [,offset [,default]])`	Same type as `column`	Returns the value from the row that is `offset` rows after the current row. If the offset row does not exist, returns `default`. `offset` defaults to 1; `default` defaults to null.	`lead(revenue, 1, 0) OVER (ORDER BY date)`
`first_value(column)`	Same type as `column`	Returns the value of the first row within the partition.	`first_value(salary) OVER (PARTITION BY department ORDER BY salary)`
`last_value(column)`	Same type as `column`	Returns the value of the last row within the partition.	`last_value(salary) OVER (PARTITION BY department ORDER BY salary)`
`nth_value(column, nth)`	Same type as `column`	Returns the value of the nth row within the partition, counting from 1. Returns null if the row does not exist. `nth` must be a positive integer. `nth_value(column, 1)` is equivalent to `first_value(column)`.	`nth_value(salary, 3) OVER (PARTITION BY department ORDER BY salary)`

Choosing between similar functions

rank() vs. dense_rank()

Both assign the same rank to rows with equal values, but differ in how they handle the next rank:

SELECT name, score,
  rank()       OVER (ORDER BY score DESC) AS rank,
  dense_rank() OVER (ORDER BY score DESC) AS dense_rank
FROM scores;

name	score	rank	dense_rank
Alice	90	1	1
Bob	90	1	1
Carol	90	1	1
Dave	80	4	2
Eve	70	5	3

Use rank() when gaps in ranking matter (for example, competition scoring). Use dense_rank() when you need contiguous rank values.

lag() vs. lead()

lag() looks backward; lead() looks forward:

SELECT order_date, revenue,
  lag(revenue,  1, 0) OVER (ORDER BY order_date) AS prev_revenue,
  lead(revenue, 1, 0) OVER (ORDER BY order_date) AS next_revenue
FROM daily_sales;

Both accept an optional offset (default: 1) and a default value returned when the offset row does not exist (default: null).