Analytic Functions Every Oracle Developer Should Know

SQL’s Most Underused Power Feature

Analytic functions (also called window functions) let you perform calculations across a set of related rows without collapsing the result set the way GROUP BY does. They have been available since Oracle 8i, yet many developers still write self-joins, correlated subqueries, or PL/SQL loops for tasks that a single analytic function can solve in one pass through the data.

ROW_NUMBER: Ranking and Deduplication

-- Get the most recent order per customer
SELECT customer_id, order_id, order_date, total
  FROM (
    SELECT customer_id, order_id, order_date, total,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY order_date DESC
           ) AS rn
      FROM orders
  )
 WHERE rn = 1;

ROW_NUMBER is the workhorse analytic function. Use it for top-N queries per group, deduplicating data by choosing the best row per key, and pagination.

LAG and LEAD: Comparing Adjacent Rows

-- Calculate month over month revenue change
SELECT month_date,
       revenue,
       LAG(revenue) OVER (ORDER BY month_date) AS prev_month_revenue,
       revenue - LAG(revenue) OVER (ORDER BY month_date) AS revenue_change,
       ROUND(
           (revenue - LAG(revenue) OVER (ORDER BY month_date))
           / NULLIF(LAG(revenue) OVER (ORDER BY month_date), 0) * 100, 1
       ) AS pct_change
  FROM monthly_revenue;

LAG looks at the previous row and LEAD looks at the next row within the window. These eliminate the need for self-joins when comparing sequential values.

Running Totals and Moving Averages

-- Running total and 3-month moving average
SELECT month_date,
       revenue,
       SUM(revenue) OVER (
           ORDER BY month_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total,
       ROUND(AVG(revenue) OVER (
           ORDER BY month_date
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 2) AS moving_avg_3mo
  FROM monthly_revenue;

RATIO_TO_REPORT: Percentage of Total

-- Each department's percentage of total salary budget
SELECT department_name,
       total_salary,
       ROUND(RATIO_TO_REPORT(total_salary) OVER () * 100, 1) AS pct_of_total
  FROM (
    SELECT d.department_name, SUM(e.salary) AS total_salary
      FROM employees e
      JOIN departments d ON e.department_id = d.department_id
     GROUP BY d.department_name
  )
 ORDER BY pct_of_total DESC;

Performance Benefits

Analytic functions process data in a single pass, which is almost always faster than equivalent approaches using self-joins, correlated subqueries, or PL/SQL loops. When you find yourself joining a table to itself to compare rows or writing a subquery that references the outer query’s current row, consider whether an analytic function can do the same job more efficiently.