Oracle Database – Analytic Functions

Analytic functions — window functions, in ANSI terms — are the feature that most changes how you write SQL. Oracle shipped them in 8i, long before most other databases, so Oracle codebases use them heavily and you will not get far in one without reading them fluently.

The one-sentence version: an aggregate collapses rows, an analytic keeps every row and adds a computed column.

-- Aggregate: 1 row per customer
SELECT customer_id, sum(total) FROM orders GROUP BY customer_id;

-- Analytic: every order row, each carrying its customer's total
SELECT id, customer_id, total,
       sum(total) OVER (PARTITION BY customer_id) AS customer_total
FROM   orders;

That second query answers "what fraction of this customer's spend was this order?" in one pass. The pre-analytic equivalent is a self-join to a grouped subquery — two scans and a lot more text.

Anatomy of OVER

function(args) OVER (
    PARTITION BY expr, ...      -- restart per group. Omit = one window over all rows.
    ORDER BY     expr, ...      -- order within the partition. Some functions require it.
    ROWS | RANGE BETWEEN ... AND ...   -- the frame: which rows the function can see
)

Ranking

SELECT customer_id, id, total,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn,
       RANK()       OVER (PARTITION BY customer_id ORDER BY total DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS drnk,
       NTILE(4)     OVER (ORDER BY total)                              AS quartile,
       PERCENT_RANK() OVER (ORDER BY total)                            AS pct
FROM   orders;

The three ranking functions differ only in how they treat ties:

total	`ROW_NUMBER`	`RANK`	`DENSE_RANK`
500	1	1	1
300	2	2	2
300	3	2	2
100	4	4	3

ROW_NUMBER is arbitrary among ties, so add a tiebreaker to the ORDER BY if the result has to be reproducible.

Top-N per group

The single most useful pattern in the whole feature. Note the analytic has to be computed in an inner query — you cannot filter on an analytic in the same WHERE, because analytics run after WHERE.

SELECT * FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM   orders o
)
WHERE rn <= 3;    -- three most recent orders per customer

OUTER APPLY answers the same question, and which one is faster depends entirely on the data — the previous post has the comparison. In short: this version reads the whole child table once, so it wins when you want top-N for every group; APPLY wins when the outer row source is small and there is an index on (fk, sort_column) to stop early on.

Deduplication

Same shape, and it beats DISTINCT whenever "duplicate" means "same key, keep the newest":

DELETE FROM customers WHERE rowid IN (
  SELECT rid FROM (
    SELECT rowid AS rid,
           ROW_NUMBER() OVER (PARTITION BY lower(email) ORDER BY created_at DESC) AS rn
    FROM   customers
  ) WHERE rn > 1
);

ROWID is Oracle's physical row address and is the fastest possible way to identify a row for deletion. It is stable within a transaction, which is all this needs.

LAG and LEAD

Look at the previous or next row without a self-join. Month-over-month change, gaps between events, state transitions:

SELECT mth, revenue,
       LAG(revenue) OVER (ORDER BY mth)                  AS prev_month,
       revenue - LAG(revenue) OVER (ORDER BY mth)        AS delta,
       ROUND((revenue / LAG(revenue) OVER (ORDER BY mth) - 1) * 100, 1) AS pct_change,
       LEAD(mth) OVER (ORDER BY mth)                     AS next_month
FROM   monthly_revenue
ORDER  BY mth;

The first row has no previous row, so LAG returns NULL and the delta is null too. Supply a default as the third argument when that matters:

LAG(revenue, 1, 0) OVER (ORDER BY mth)    -- offset 1, default 0

Running totals, and the frame clause

SELECT order_date, total,
       SUM(total) OVER (ORDER BY order_date
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       AVG(total) OVER (ORDER BY order_date
                        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)         AS moving_avg_7,
       SUM(total) OVER ()                                                 AS grand_total
FROM   orders
ORDER  BY order_date;

ROWS vs RANGE — the gotcha

This is the subtlety that produces wrong numbers rather than errors. ROWS counts physical rows. RANGE counts value ranges, so all rows with the same ORDER BY value are one unit.

And the default frame, when you write ORDER BY and no frame clause at all, is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. So with duplicate dates:

-- Two orders on 2024-03-01, for 100 and 200.
-- Default RANGE: both rows show a running total of 300 — the whole day is "current".
SELECT order_date, total,
       SUM(total) OVER (ORDER BY order_date) AS rt_range,
       SUM(total) OVER (ORDER BY order_date
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rt_rows
FROM   orders;

Which one you want depends on the question — a genuine daily running total probably should include the whole day — but you should be choosing, not inheriting. Write the frame explicitly.

RANGE also accepts intervals, which is how you do a true time window rather than a row-count window:

-- Revenue in the trailing 30 days, regardless of how many orders that is
SUM(total) OVER (ORDER BY order_date
                 RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW)

FIRST_VALUE and LAST_VALUE

LAST_VALUE is the other classic trap. With the default frame ending at CURRENT ROW, "the last value" is the current row — which is useless and looks like a bug in Oracle:

SELECT customer_id, order_date, total,
       FIRST_VALUE(total) OVER (PARTITION BY customer_id ORDER BY order_date) AS first_order,
       -- WRONG: frame ends at the current row, so this equals `total`
       LAST_VALUE(total)  OVER (PARTITION BY customer_id ORDER BY order_date) AS last_wrong,
       -- RIGHT: extend the frame to the end of the partition
       LAST_VALUE(total)  OVER (PARTITION BY customer_id ORDER BY order_date
                                ROWS BETWEEN UNBOUNDED PRECEDING
                                         AND UNBOUNDED FOLLOWING)             AS last_order
FROM   orders;

Oracle also has a compact alternative that avoids the whole question — the KEEP (DENSE_RANK …) aggregate, which works in a normal GROUP BY:

SELECT customer_id,
       MIN(total) KEEP (DENSE_RANK FIRST ORDER BY order_date) AS first_order_total,
       MAX(total) KEEP (DENSE_RANK LAST  ORDER BY order_date) AS last_order_total,
       COUNT(*)                                              AS orders
FROM   orders
GROUP  BY customer_id;

Read it as "the total belonging to the row that sorts first by order_date". The MIN/MAX only breaks ties. This is very common in Oracle SQL and reads as gibberish until someone explains it once.

Gaps and islands

A pattern worth having in your pocket: group consecutive runs of something. The trick is that position minus rank is constant within a consecutive run.

-- Streaks of consecutive days on which a customer ordered
WITH d AS (
  SELECT DISTINCT customer_id, trunc(order_date) AS day FROM orders
), g AS (
  SELECT customer_id, day,
         day - ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY day) AS grp
  FROM   d
)
SELECT customer_id, min(day) AS streak_start, max(day) AS streak_end, count(*) AS days
FROM   g
GROUP  BY customer_id, grp
HAVING count(*) >= 3
ORDER  BY customer_id, streak_start;

Reusing a window

Repeating a long OVER clause invites typos. Name it once:

SELECT customer_id, order_date, total,
       SUM(total) OVER w AS running_total,
       AVG(total) OVER w AS running_avg,
       COUNT(*)   OVER w AS orders_so_far
FROM   orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

The WINDOW clause arrived in 21c. Before that, a WITH subquery is the usual workaround.

Where they can and cannot go

Analytics are evaluated after WHERE, GROUP BY and HAVING, and before ORDER BY. So:

You can use one in SELECT and ORDER BY.
You cannot use one in WHERE, GROUP BY or HAVING — wrap the query and filter outside.
You can nest an aggregate inside an analytic: RANK() OVER (ORDER BY sum(total) DESC) alongside a GROUP BY customer_id. The analytic then operates on the grouped rows, which is exactly what you want for "rank customers by revenue".

SELECT customer_id, sum(total) AS revenue,
       RANK() OVER (ORDER BY sum(total) DESC) AS revenue_rank
FROM   orders
GROUP  BY customer_id
ORDER  BY revenue_rank
FETCH  FIRST 10 ROWS ONLY;

PL/SQL — Oracle's procedural language, and the reason a lot of business logic in enterprise systems lives inside the database rather than in front of it.