Mastering SQL Analytic Functions: Windows, PARTITION, ROWS vs RANGE Explained

Analytic functions are SQL functions that include the OVER clause; they are applied to each row while sliding a window over the result set according to the ordering rules defined by PARTITION BY, ORDER BY and the WINDOW clause.

Key Concepts

Current row : the row for which the function is currently evaluated. The window is defined by the PARTITION BY, ORDER BY and optional WINDOW clause, and the function computes a value based on all rows in that window.

Logical vs. physical windows : a logical window is derived from the query (e.g., RANGE), while a physical window refers to actual rows (e.g., ROWS). The window range is determined jointly by PARTITION BY, ORDER BY and the WINDOW clause.

PARTITION, ORDER BY and WINDOW

PARTITION BY

splits the result set into groups before ordering; if omitted, all rows belong to a single partition. ORDER BY defines the order of rows inside each partition; it can be a column or an expression.

The WINDOW clause (optional) further restricts the rows considered for each calculation.

ROWS vs. RANGE

ROWS

works on physical row counts. When the ordering key contains duplicate values, the window for a given row may be nondeterministic, leading to unstable results. RANGE works on logical value ranges. Duplicate ordering keys share the same window, guaranteeing stable results. For numeric or date ordering keys, the range expression must be compatible (e.g., UNBOUNDED PRECEDING, CURRENT ROW, or numeric offsets).

Window Clause Variations

If neither WINDOW nor ORDER BY is present, the window includes all rows of the partition (default is RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING).

If ORDER BY is present but no WINDOW, the window spans from the first row of the partition to the current row ( RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

If a WINDOW is specified, an ORDER BY is required. The window can be defined with BETWEEN start AND end or with a single offset (e.g., ROWS 1 PRECEDING meaning ROWS BETWEEN 1 PRECEDING AND CURRENT ROW). 0 PRECEDING and 0 FOLLOWING are equivalent to CURRENT ROW.

Invalid specifications (e.g., ROWS 1 FOLLOWING without a proper ordering direction) cause errors.

Function Types

Functions that can be used as analytic functions must be written with OVER. Some functions, like SUM, work both as aggregate and analytic; others, like FIRST_VALUE, are analytic only. Ranking functions (e.g., ROW_NUMBER) usually cannot have a WINDOW clause and operate on the whole partition.

Advantages and Disadvantages

Advantages : enable complex cumulative, moving, and intermediate calculations without sub‑queries or joins; reduce table scans; Oracle can optimise them for better performance.

Disadvantages : often require sorting; multiple analytic functions may trigger multiple sorts, increasing memory usage; ROWS windows with non‑unique ordering keys can produce nondeterministic results.

Example

WITH data AS (
  SELECT 1 AS id, 'a' AS name FROM dual UNION ALL
  SELECT 2 AS id, 'b' AS name FROM dual UNION ALL
  SELECT 3 AS id, 'c' AS name FROM dual
)
SELECT
  id,
  name,
  SUM(id) OVER (ORDER BY id RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_range,
  SUM(id) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS sum_rows
FROM data;

In this query, sum_range yields stable cumulative sums because RANGE treats duplicate ordering values as a single logical window, while sum_rows may vary if the ordering key is not unique.