The Limitation of GROUP BY#

Standard aggregate functions (SUM, MAX, AVG) coupled with GROUP BY are incredibly useful, but they have a massive limitation: They destroy the detail rows.

If you want to show an employee's salary alongside the average salary of their department, you cannot do it with a simple GROUP BY. You would have to write a subquery to calculate the department average, and then JOIN it back to the main employee table. This is slow and verbose.

Analytical Functions (Window Functions) solve this. They perform aggregates across a set of rows related to the current row, but they do not collapse the result set. You get the aggregate value, and you keep the detail row!

The Anatomy of OVER()#

An analytical function is defined by the OVER() clause. It dictates the "Window" of data the function operates on.

SELECT 
  emp_name,
  dept_id,
  salary,
  -- Calculate average salary PER department, without losing the emp_name!
  AVG(salary) OVER(PARTITION BY dept_id) as dept_avg_salary
FROM employees;

The OVER() clause has three main optional components:

1. PARTITION BY: Divides the result set into groups (like GROUP BY).
2. ORDER BY: Defines the logical order of rows within the partition.
3. ROWS BETWEEN: Defines a sliding window frame relative to the current row.

Ranking Functions (ROW_NUMBER vs RANK)#

These are the most commonly used analytical functions for deduplication and Top-N reporting.

ROW_NUMBER()

Assigns a unique, sequential integer to each row within the partition.

WITH ranked_emps AS (
  SELECT 
    emp_name, dept_id, salary,
    ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) as rnk
  FROM employees
)
SELECT * FROM ranked_emps WHERE rnk = 1;

RANK() vs DENSE_RANK()

What if two employees have the exact same salary?

• ROW_NUMBER() will arbitrarily assign one as 1 and the other as 2.
• RANK() will assign both as 1, but skip the next number (the next employee gets 3).
• DENSE_RANK() will assign both as 1, and NOT skip (the next employee gets 2).

Time Travel (LEAD and LAG)#

LEAD and LAG allow you to look at data from subsequent or preceding rows without writing expensive, complex Self-Joins. This is vital for calculating Year-Over-Year growth or day-to-day variances.

SELECT 
  sale_date,
  daily_revenue,
  -- Get the revenue from the previous day
  LAG(daily_revenue, 1) OVER(ORDER BY sale_date) as prev_day_revenue,
  -- Calculate the variance
  daily_revenue - LAG(daily_revenue, 1) OVER(ORDER BY sale_date) as variance
FROM daily_sales;

• LAG(column, offset) looks backward.
• LEAD(column, offset) looks forward.

Defining Windows (ROWS BETWEEN)#

When you include an ORDER BY in your OVER() clause, Oracle automatically applies a default window frame: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This is what creates a Running Total.

SELECT 
  sale_date,
  amount,
  SUM(amount) OVER(ORDER BY sale_date) as running_total
FROM sales;

Sliding Windows (Moving Averages)

You can manually override the window frame to calculate things like a 7-day Moving Average.

SELECT 
  sale_date,
  amount,
  AVG(amount) OVER(
    ORDER BY sale_date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_7day_avg
FROM sales;

Common Gotchas#

Key Takeaways#