Advanced windows: LAG, LEAD, NTILE and frames

Looking sideways: LAG and LEAD

Sometimes a row needs the value of another related row: the previous day's to compute growth, the next one to compare, etc. That is exactly what LAG and LEAD do over an ordered window:

• LAG(col) → the value of col in the previous row (per the ORDER BY).
• LEAD(col) → the value of col in the next row.

SELECT id, amount,
  LAG(amount)  OVER (ORDER BY id) AS previous_amount,
  LEAD(amount) OVER (ORDER BY id) AS next_amount
FROM sales;

In the first row there is no previous one and in the last there is no next one: there the result is NULL. You can pass a second and third argument to skip more rows or give a default value: LAG(amount, 1, 0) returns 0 instead of NULL in the first row.

A classic subtraction: amount - LAG(amount) OVER (ORDER BY id) gives you the change relative to the previous row.

Splitting into groups: NTILE(n)

NTILE(n) distributes the rows (in the window order) into n groups as equal as possible and returns which group (1..n) each row belongs to. It is ideal for quartiles (n = 4), deciles, approximate percentiles, etc.

SELECT name, price,
  NTILE(3) OVER (ORDER BY price) AS third
FROM products;

If the rows do not split exactly, the first groups get an extra row.

Window frames: ROWS BETWEEN

By default, an aggregate with ORDER BY inside OVER accumulates from the first row up to the current one. But you can define the frame (the band of rows that enters the calculation) with ROWS BETWEEN ... AND ...:

SELECT id, amount,
  AVG(amount) OVER (
    ORDER BY id
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM sales;

• N PRECEDING → N rows before the current one.
• CURRENT ROW → the current row.
• N FOLLOWING → N rows after.
• UNBOUNDED PRECEDING / UNBOUNDED FOLLOWING → up to the start / the end.

So, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW computes over the current row and the two before it: the basis of moving averages and sums.

The classic running total (SUM(...) OVER (ORDER BY id)) is actually the frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW written implicitly.

Examples

Compare each sale with the previous one (LAG)

SELECT id, amount,
  LAG(amount) OVER (ORDER BY id) AS previous,
  amount - LAG(amount) OVER (ORDER BY id) AS change
FROM sales
ORDER BY id;

2-row moving average with a window frame

SELECT id, amount,
  AVG(amount) OVER (
    ORDER BY id
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM sales
ORDER BY id;