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 Databend
• ⚠️ Syntax examples show window frame patterns (not complete statements)
• 📋 All examples use standard SQL syntax supported by Databend
• 🔍 Examples marked as "Complete example" are fully executable

Window Function Categories

Databend supports two main categories of window functions:

1. Dedicated Window Functions

These functions are specifically designed for window operations.

Ranking Functions:

Function	Description	Ties Handling	Example Output
ROW_NUMBER	Sequential numbering	Always unique	1, 2, 3, 4, 5
RANK	Ranking with gaps	Same rank, gaps after	1, 2, 2, 4, 5
DENSE_RANK	Ranking without gaps	Same rank, no gaps	1, 2, 2, 3, 4

Distribution Functions:

Function	Description	Range	Example Output
PERCENT_RANK	Relative rank as percentage	0.0 to 1.0	0.0, 0.25, 0.5, 0.75, 1.0
CUME_DIST	Cumulative distribution	0.0 to 1.0	0.2, 0.4, 0.6, 0.8, 1.0
NTILE	Divide into N buckets	1 to N	1, 1, 2, 2, 3, 3

Value Access Functions:

Function	Description	Use Case
FIRST_VALUE	First value in window	Get highest/earliest value
LAST_VALUE	Last value in window	Get lowest/latest value
NTH_VALUE	Nth value in window	Get specific positioned value
LAG	Previous row value	Compare with previous
LEAD	Next row value	Compare with next

Aliases:

Function	Alias For
FIRST	FIRST_VALUE
LAST	LAST_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.

Function	Description	Window Frame Support	Example
SUM	Calculates sum over window	✓	SUM(sales) OVER (PARTITION BY region ORDER BY date)
AVG	Calculates average over window	✓	AVG(score) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
COUNT	Counts rows over window	✓	COUNT(*) OVER (PARTITION BY department)
MIN	Returns minimum value in window	✓	MIN(price) OVER (PARTITION BY category)
MAX	Returns maximum value in window	✓	MAX(price) OVER (PARTITION BY category)
ARRAY_AGG	Collects values into array		ARRAY_AGG(product) OVER (PARTITION BY category)
STDDEV_POP	Population standard deviation	✓	STDDEV_POP(value) OVER (PARTITION BY group)
STDDEV_SAMP	Sample standard deviation	✓	STDDEV_SAMP(value) OVER (PARTITION BY group)
MEDIAN	Median value	✓	MEDIAN(response_time) OVER (PARTITION BY server)

Conditional Variants

Function	Description	Window Frame Support	Example
COUNT_IF	Conditional count	✓	COUNT_IF(status = 'complete') OVER (PARTITION BY dept)
SUM_IF	Conditional sum	✓	SUM_IF(amount, status = 'paid') OVER (PARTITION BY customer)
AVG_IF	Conditional average	✓	AVG_IF(score, passed = true) OVER (PARTITION BY class)
MIN_IF	Conditional minimum	✓	MIN_IF(temp, location = 'outside') OVER (PARTITION BY day)
MAX_IF	Conditional 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. Databend 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.