SQL’s Most Underused Power Feature
Analytic functions let you perform calculations across related rows without collapsing the result set like GROUP BY. Available since Oracle 8i, yet many developers still write self-joins or PL/SQL loops for tasks a single analytic function solves in one pass.
ROW_NUMBER: Ranking and Deduplication
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;LAG and LEAD: Comparing Adjacent Rows
SELECT month_date, revenue,
LAG(revenue) OVER (ORDER BY month_date) AS prev_month,
revenue - LAG(revenue) OVER (ORDER BY month_date) AS 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;Running Totals and Moving Averages
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
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, almost always faster than self-joins or correlated subqueries. When you find yourself joining a table to itself or writing a subquery referencing the outer query’s current row, consider an analytic function instead.