Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/pingcap/tidb/llms.txt

Use this file to discover all available pages before exploring further.

Window functions compute values across a set of table rows related to the current row — similar to aggregate functions, but without collapsing the result into a single row. TiDB supports the full SQL window function syntax including the OVER clause, PARTITION BY, ORDER BY, and frame specifications.

​
Syntax

function_name([arguments]) OVER (
    [PARTITION BY partition_expression, ...]
    [ORDER BY sort_expression [ASC | DESC], ...]
    [frame_clause]
)
ClauseDescription
PARTITION BYDivides rows into independent groups (like GROUP BY, but rows are not collapsed)
ORDER BYDefines the row order within each partition
frame_clauseFurther restricts which rows within the partition are included in the calculation

​
Ranking functions

Ranking functions assign ordinal positions to rows within a partition.

​
Sample data

CREATE TABLE sales (
    rep    VARCHAR(50),
    region VARCHAR(50),
    amount DECIMAL(10,2)
);

INSERT INTO sales VALUES
    ('Alice', 'West',  9200.00),
    ('Bob',   'West',  8100.00),
    ('Carol', 'East', 11500.00),
    ('Dave',  'East',  9800.00),
    ('Eve',   'East',  9800.00);

​
ROW_NUMBER()

Assigns a unique sequential integer to each row within a partition. 
SELECT rep, region, amount,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_num
FROM sales;
repregionamountrow_num
CarolEast11500.001
DaveEast9800.002
EveEast9800.003
AliceWest9200.001
BobWest8100.002

​
RANK()

Like ROW_NUMBER(), but rows with equal values receive the same rank and the next rank is skipped. 
SELECT rep, region, amount,
       RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rnk
FROM sales;
repregionamountrnk
CarolEast11500.001
DaveEast9800.002
EveEast9800.002
AliceWest9200.001
BobWest8100.002
Note that Dave and Eve both receive rank 2, and rank 3 is skipped.

​
DENSE_RANK()

Like RANK(), but does not skip ranks after ties. 
SELECT rep, region, amount,
       DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS dense_rnk
FROM sales;

​
NTILE(n)

Divides rows into n approximately equal buckets. 
SELECT rep, amount,
       NTILE(3) OVER (ORDER BY amount DESC) AS bucket
FROM sales;

​
Value functions

Value functions return a value from a specific row relative to the current row.

​
LAG() and LEAD()

LAG(expr, offset, default) returns a value from a preceding row; LEAD() looks ahead. 
-- Month-over-month revenue comparison
SELECT month, revenue,
       LAG(revenue, 1)  OVER (ORDER BY month) AS prev_month,
       LEAD(revenue, 1) OVER (ORDER BY month) AS next_month,
       revenue - LAG(revenue, 1) OVER (ORDER BY month) AS delta
FROM monthly_revenue;

​
FIRST_VALUE() and LAST_VALUE()

Return the first or last value in the window frame. 
SELECT rep, region, amount,
       FIRST_VALUE(amount) OVER (PARTITION BY region ORDER BY amount DESC) AS top_sale,
       LAST_VALUE(amount)  OVER (
           PARTITION BY region
           ORDER BY amount DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS bottom_sale
FROM sales;
LAST_VALUE() uses the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) unless you explicitly extend it with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

​
Aggregate window functions

Standard aggregate functions (SUM, AVG, COUNT, MIN, MAX) can be used as window functions by adding an OVER clause.

​
Running total

SELECT rep, amount,
       SUM(amount) OVER (ORDER BY rep ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales;
repamountrunning_total
Alice9200.009200.00
Bob8100.0017300.00
Carol11500.0028800.00
Dave9800.0038600.00
Eve9800.0048400.00

​
Moving average (3-row window)

SELECT rep, amount,
       AVG(amount) OVER (
           ORDER BY rep
           ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
       ) AS moving_avg
FROM sales;

​
Partition aggregate

SELECT rep, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       amount / SUM(amount) OVER (PARTITION BY region) AS share
FROM sales;

​
Frame clauses

A frame restricts which rows within the partition are included in the window calculation.
SyntaxDescription
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWAll rows from the start of the partition to the current row
ROWS BETWEEN 2 PRECEDING AND CURRENT ROWThe current row and the two preceding rows
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWINGA sliding 3-row window centered on the current row
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGAll rows in the partition
ROWS counts physical rows; RANGE counts rows with values within a range of the current row’s ORDER BY value. Use ROWS when working with numeric offsets; use RANGE when working with ordered values and ties matter.

​
Common use cases

-- Top 2 sales reps per region
SELECT rep, region, amount FROM (
    SELECT rep, region, amount,
           RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rnk
    FROM sales
) ranked
WHERE rnk <= 2;

SELECT month, revenue,
       ROUND(
           (revenue - LAG(revenue) OVER (ORDER BY month))
           / LAG(revenue) OVER (ORDER BY month) * 100,
           2
       ) AS pct_change
FROM monthly_revenue;

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn
    FROM orders
) paged
WHERE rn BETWEEN 21 AND 40;

Build docs developers (and LLMs) love

Get started for freeTalk to us