Analytic Functions, Partitions, Frames, Ranking, Running Metrics, and Gaps-and-Islands

Learning outcomes

ServiceHub needs daily dashboards that show every reading, its running total, its rank within an asset, and streaks of consecutive service dates. A developer first reaches for GROUP BY, but aggregation collapses rows that the dashboard still needs. Analytic functions solve a different problem: compute across related rows while preserving each detail row.

1. GROUP BY collapses; analytics preserve

An aggregate query returns one row per group. An analytic function computes over a window but returns a value for each input row. Oracle processes analytic functions after joins, WHERE, GROUP BY, and HAVING, but before the final query ORDER BY.

SELECT asset_id, SUM(reading_value) AS asset_totalFROM servicehub_sql_readingGROUP BY asset_id;SELECT asset_id, observed_at, reading_id, reading_value,       SUM(reading_value) OVER (PARTITION BY asset_id) AS asset_totalFROM servicehub_sql_readingORDER BY asset_id, observed_at, reading_id;

The second query repeats the partition total alongside each detail row. That is intentional; analytics augment a row set rather than replacing its grain.

2. PARTITION BY defines independent calculation groups

PARTITION BY asset_id restarts the analytic calculation for each asset. Omitting it creates one partition containing the entire result. Analytic partitions are query-processing groups; they are unrelated to table partitioning on disk.

SELECT asset_id, observed_at, reading_id, reading_value,       SUM(reading_value) OVER (         PARTITION BY asset_id         ORDER BY observed_at, reading_id         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW       ) AS running_totalFROM servicehub_sql_readingORDER BY asset_id, observed_at, reading_id;

3. Deliberately wrong: accept the default frame with tied sort values

When an analytic function has an ORDER BY and no explicit frame, current Oracle documentation defines the default as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE is logical: rows with equal sort values are peers. If two readings share the same timestamp, both can receive the same cumulative value even though a developer expected row-by-row accumulation.

SELECT asset_id, observed_at, reading_id, reading_value,       SUM(reading_value) OVER (         PARTITION BY asset_id         ORDER BY observed_at       ) AS default_running_totalFROM servicehub_sql_readingORDER BY asset_id, observed_at, reading_id;

The repair is not “always use ROWS.” Choose semantics. If you need physical row-by-row progression, specify ROWS and a unique ordering. If business logic treats equal timestamps as one peer group, RANGE or GROUPS can be the correct frame.

SUM(reading_value) OVER (  PARTITION BY asset_id  ORDER BY observed_at, reading_id  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

4. ROWS, RANGE, and GROUPS answer different window questions

SELECT asset_id, observed_at, reading_id, reading_value,       SUM(reading_value) OVER (         PARTITION BY asset_id         ORDER BY observed_at         GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW       ) AS current_and_previous_time_groupFROM servicehub_sql_readingORDER BY asset_id, observed_at, reading_id;

5. Ranking and navigation: ties are part of the contract

SELECT asset_id, reading_id, reading_value,       ROW_NUMBER() OVER (PARTITION BY asset_id ORDER BY reading_value DESC, reading_id) AS rn,       RANK()       OVER (PARTITION BY asset_id ORDER BY reading_value DESC) AS rnk,       DENSE_RANK() OVER (PARTITION BY asset_id ORDER BY reading_value DESC) AS dense_rnk,       LAG(reading_value) OVER (PARTITION BY asset_id ORDER BY observed_at, reading_id) AS prior_valueFROM servicehub_sql_readingORDER BY asset_id, reading_value DESC, reading_id;

ROW_NUMBER always assigns distinct numbers, so add a tie-breaker if the chosen row matters. RANK leaves gaps after ties; DENSE_RANK does not. The right function depends on the business meaning of “top 3,” not on which output looks neatest.

SELECT asset_id, reading_id, reading_valueFROM (  SELECT r.*,         ROW_NUMBER() OVER (           PARTITION BY asset_id           ORDER BY reading_value DESC, reading_id         ) AS rn  FROM servicehub_sql_reading r)WHERE rn <= 2ORDER BY asset_id, rn;

6. Gaps-and-islands: turn consecutive dates into a stable group key

For one service row per asset/date, subtracting a consecutive row number from the date produces the same anchor date across a run of consecutive days. Grouping by that derived anchor turns detail rows into islands.

WITH numbered AS (  SELECT asset_id, service_date,         ROW_NUMBER() OVER (           PARTITION BY asset_id           ORDER BY service_date         ) AS rn  FROM servicehub_sql_service_day), tagged AS (  SELECT asset_id, service_date,         service_date - rn AS island_key  FROM numbered)SELECT asset_id,       MIN(service_date) AS island_start,       MAX(service_date) AS island_end,       COUNT(*) AS day_countFROM taggedGROUP BY asset_id, island_keyORDER BY asset_id, island_start;

This pattern assumes one row per asset/day and calendar-day consecutiveness. If the domain means business days or timestamp gaps, define a different adjacency rule.

7. Hands-on lab

DROP TABLE servicehub_sql_service_day IF EXISTS PURGE;DROP TABLE servicehub_sql_reading IF EXISTS PURGE;CREATE TABLE servicehub_sql_reading (  reading_id NUMBER PRIMARY KEY,  asset_id NUMBER NOT NULL,  observed_at TIMESTAMP NOT NULL,  reading_value NUMBER(10,2) NOT NULL);INSERT INTO servicehub_sql_reading VALUES (1,10,TIMESTAMP '2026-08-25 08:00:00',10);INSERT INTO servicehub_sql_reading VALUES (2,10,TIMESTAMP '2026-08-25 08:00:00',15);INSERT INTO servicehub_sql_reading VALUES (3,10,TIMESTAMP '2026-08-25 09:00:00',20);INSERT INTO servicehub_sql_reading VALUES (4,20,TIMESTAMP '2026-08-25 08:30:00',7);INSERT INTO servicehub_sql_reading VALUES (5,20,TIMESTAMP '2026-08-25 09:30:00',9);CREATE TABLE servicehub_sql_service_day (  asset_id NUMBER NOT NULL,  service_date DATE NOT NULL,  CONSTRAINT sh06_service_day_pk PRIMARY KEY (asset_id, service_date));INSERT INTO servicehub_sql_service_day VALUES (10,DATE '2026-08-20');INSERT INTO servicehub_sql_service_day VALUES (10,DATE '2026-08-21');INSERT INTO servicehub_sql_service_day VALUES (10,DATE '2026-08-23');INSERT INTO servicehub_sql_service_day VALUES (10,DATE '2026-08-24');INSERT INTO servicehub_sql_service_day VALUES (10,DATE '2026-08-25');COMMIT;

DROP TABLE servicehub_sql_service_day PURGE;DROP TABLE servicehub_sql_reading PURGE;

8. Production judgment

Window functions often sort or buffer data. Measure on representative volumes and watch TEMP/PGA pressure in later performance chapters rather than inventing a universal “windows are fast” rule. Keep partition/order keys aligned with the business question, make physical frames deterministic, and separate analytic calculation order from final presentation order. No paid pack, restart, parameter, or COMPATIBLE change is required for these mandatory examples on the declared 26ai baseline.

Authoritative references

• Analytic Functions — analytic processing order, PARTITION/ORDER and ROWS/RANGE/GROUPS semantics
• SELECT — window/query syntax and final ordering
• Aggregate Functions — aggregate versus analytic context