📣
TiDB Cloud Premium is now in public preview. Unlimited growth, instant elasticity, advanced security for enterprise workloads. Try it out →
​
Sign InStart for Free
AI
Developer
Best Practices
API
​
Sign InStart for Free

Window Functions

Overview

Window functions perform calculations across a set of related rows while returning one result per input row. Unlike aggregate functions, window functions don't collapse rows into a single output.

Key characteristics:

  • Operate on a "window" of rows related to the current row
  • Return one value per input row (no grouping/collapsing)
  • Can access values from other rows in the window
  • Support partitioning and ordering for flexible calculations

Note on SQL examples in this documentation:

  • ✅ Complete SQL statements have been validated against TiDB Cloud Lake
  • ⚠️ Syntax examples show window frame patterns (not complete statements)
  • 📋 All examples use standard SQL syntax supported by TiDB Cloud Lake
  • 🔍 Examples marked as "Complete example" are fully executable

Window Function Categories

TiDB Cloud Lake supports two main categories of window functions:

1. Dedicated Window Functions

These functions are specifically designed for window operations.

Ranking Functions:

FunctionDescriptionTies HandlingExample Output
ROW_NUMBERSequential numberingAlways unique1, 2, 3, 4, 5
RANKRanking with gapsSame rank, gaps after1, 2, 2, 4, 5
DENSE_RANKRanking without gapsSame rank, no gaps1, 2, 2, 3, 4

Distribution Functions:

FunctionDescriptionRangeExample Output
PERCENT_RANKRelative rank as percentage0.0 to 1.00.0, 0.25, 0.5, 0.75, 1.0
CUME_DISTCumulative distribution0.0 to 1.00.2, 0.4, 0.6, 0.8, 1.0
NTILEDivide into N buckets1 to N1, 1, 2, 2, 3, 3

Value Access Functions:

FunctionDescriptionUse Case
FIRST_VALUEFirst value in windowGet highest/earliest value
LAST_VALUELast value in windowGet lowest/latest value
NTH_VALUENth value in windowGet specific positioned value
LAGPrevious row valueCompare with previous
LEADNext row valueCompare with next

Aliases:

FunctionAlias For
FIRSTFIRST_VALUE
LASTLAST_VALUE

2. Aggregate Functions Used as Window Functions

These are standard aggregate functions that can be used with the OVER clause to perform window operations.

FunctionDescriptionWindow Frame SupportExample
SUMCalculates sum over window✓SUM(sales) OVER (PARTITION BY region ORDER BY date)
AVGCalculates average over window✓AVG(score) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
COUNTCounts rows over window✓COUNT(*) OVER (PARTITION BY department)
MINReturns minimum value in window✓MIN(price) OVER (PARTITION BY category)
MAXReturns maximum value in window✓MAX(price) OVER (PARTITION BY category)
ARRAY_AGGCollects values into arrayARRAY_AGG(product) OVER (PARTITION BY category)
STDDEV_POPPopulation standard deviation✓STDDEV_POP(value) OVER (PARTITION BY group)
STDDEV_SAMPSample standard deviation✓STDDEV_SAMP(value) OVER (PARTITION BY group)
MEDIANMedian value✓MEDIAN(response_time) OVER (PARTITION BY server)

Conditional Variants

FunctionDescriptionWindow Frame SupportExample
COUNT_IFConditional count✓COUNT_IF(status = 'complete') OVER (PARTITION BY dept)
SUM_IFConditional sum✓SUM_IF(amount, status = 'paid') OVER (PARTITION BY customer)
AVG_IFConditional average✓AVG_IF(score, passed = true) OVER (PARTITION BY class)
MIN_IFConditional minimum✓MIN_IF(temp, location = 'outside') OVER (PARTITION BY day)
MAX_IFConditional maximum✓MAX_IF(speed, vehicle = 'car') OVER (PARTITION BY test)

Basic Syntax

All window functions follow this pattern:

FUNCTION() OVER (
    [ PARTITION BY column ]
    [ ORDER BY column ]
    [ window_frame ]
)
  • PARTITION BY: Divides data into groups
  • ORDER BY: Sorts rows within each partition
  • window_frame: Defines which rows to include (optional)

Window Frame Specification

The window frame defines which rows are included in the calculation for each row. TiDB Cloud Lake supports two types of window frames:

1. ROWS BETWEEN

Defines a window frame using physical row counts.

Syntax:

ROWS BETWEEN frame_start AND frame_end

Examples:

  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW - Running total
  • ROWS BETWEEN 2 PRECEDING AND CURRENT ROW - 3-day moving average
  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING - Centered window

For detailed examples and usage, see ROWS BETWEEN.

2. RANGE BETWEEN

Defines a window frame using logical value ranges.

Syntax:

RANGE BETWEEN frame_start AND frame_end

Examples:

  • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW - Cumulative by value
  • RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW - 7-day window

For detailed examples and usage, see RANGE BETWEEN.

Common Use Cases

  • Ranking: Create leaderboards and top-N lists
  • Analytics: Calculate running totals, moving averages, percentiles
  • Comparison: Compare current vs previous/next values
  • Grouping: Divide data into buckets without losing detail

For detailed syntax and examples, see individual function documentation above.

Was this page helpful?