DB2 window frame specifications: OVER, ROWS and RANGE

Three kinds of OLAP specification

IBM splits OLAP syntax into ordered specifications, numbering, and aggregation:

• RANK() / DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...) — ranking. They need an ORDER BY inside OVER. They do not take a ROWS/RANGE frame.
• ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) — unique sequence numbers. ORDER BY is how you make the numbers meaningful; without it, numbering is arbitrary. No frame clause.
• Aggregate OVER (...) — AVG, CORRELATION, COUNT, COUNT_BIG, COVARIANCE, MAX, MIN, STDDEV, SUM, VARIANCE (and related forms). These may include a window-aggregation-group-clause: ROWS or RANGE with start/end bounds.

1
2
3
4
5
6
7
8
SELECT WORKDEPT, LASTNAME, SALARY,
       RANK() OVER (PARTITION BY WORKDEPT ORDER BY SALARY DESC) AS RK,
       SUM(SALARY) OVER (
         PARTITION BY WORKDEPT
         ORDER BY LASTNAME
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS RUNNING_PAY
  FROM DSN8C10.EMP;

RANK looks at the whole partition ordered by salary. SUM looks only at the frame: here, every row from the start of the department through the current row in last-name order. Both functions return one value per input row. GROUP BY would have returned one row per department.

PARTITION BY

PARTITION BY expression-list splits the FROM/WHERE result into independent windows. Each partition is framed and ordered on its own. Omit PARTITION BY and the entire result is one partition (a grand running total, a single RANK list).

Partition expressions can be columns or expressions. Changing a partition key resets RANK and resets a running SUM. Choose the same grain you would have grouped by if you were writing GROUP BY, then keep the detail rows.

ORDER BY in the window

Window ORDER BY is not the SELECT ORDER BY. It only defines:

• The sequence used by RANK, DENSE_RANK, and ROW_NUMBER
• The sequence and peer groups used by RANGE frames
• The physical sequence used by ROWS frames

ASC and DESC apply per sort key. NULLS FIRST and NULLS LAST control where null keys sit. If the ORDER BY keys do not uniquely identify a row, ROW_NUMBER among ties is non-deterministic, and RANGE treats those ties as peers (they share CURRENT ROW for a RANGE frame).

1
2
3
4
SELECT LASTNAME, SALARY,
       ROW_NUMBER() OVER (ORDER BY SALARY DESC, LASTNAME) AS N
  FROM DSN8C10.EMP
 WHERE WORKDEPT = 'D11';

Adding LASTNAME makes the numbering stable. The SELECT still needs ORDER BY N or ORDER BY SALARY DESC if you want the displayed grid in that sequence. Windows do not replace the query ORDER BY.

Window frames: ROWS and RANGE

A frame is the subset of the partition that an aggregate sees for the current row. Syntax shapes:

1
2
3
4
5
ROWS UNBOUNDED PRECEDING
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Default frames

• No ORDER BY in OVER — the aggregate uses the whole partition (the same idea as RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING). Every row in the department sees the same SUM(SALARY) OVER (PARTITION BY WORKDEPT).
• ORDER BY present, no ROWS/RANGE — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That is a cumulative aggregate from the start of the partition through the current peer group.

ROWS

ROWS counts rows in window order, ignoring whether keys are equal. ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING is a three-row sliding window (previous, current, next), truncated at partition edges. Use ROWS for moving averages on a report sequence, or for running totals that must not jump ahead when salaries tie.

RANGE

RANGE is value-based. CURRENT ROW means “this ORDER BY value,” so all peers enter the frame together. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for ORDER BY SALARY includes every row with salary less than or equal to the current salary (in ASC). An unsigned-constant RANGE offset (RANGE 1000 PRECEDING) measures distance on a single numeric or datetime ORDER BY expression—not on a two-column sort.

1
2
3
4
5
6
7
8
9
10
11
SELECT LASTNAME, SALARY,
       SUM(SALARY) OVER (
         ORDER BY SALARY
         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS RANGE_RUN,
       SUM(SALARY) OVER (
         ORDER BY SALARY
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS ROWS_RUN
  FROM DSN8C10.EMP
 WHERE WORKDEPT = 'D11';

If two D11 employees share a salary, RANGE_RUN is the same on both rows (both peers are inside CURRENT ROW). ROWS_RUN differs: the second physical row also adds the first peer’s salary, then the next adds again. That difference is the usual interview question.

Moving windows and partition totals

Whole-partition total on every detail row:

1
SUM(SALARY) OVER (PARTITION BY WORKDEPT)

Three-row moving average in hire-date order:

1
2
3
4
5
AVG(SALARY) OVER (
  PARTITION BY WORKDEPT
  ORDER BY HIREDATE
  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)

At the first row of a partition there is no preceding row; at the last there is no following row. The frame shrinks. COUNT(*) OVER (same frame) tells you how many rows actually participated if you need to scale the average yourself.

Frames cannot reach outside the partition. ROWS 100 PRECEDING on a 5-row partition only sees those five rows. That is intentional: PARTITION BY is a hard wall.

Practical rules

• Put PARTITION BY columns first in supporting indexes when the window is selective and large.
• Make window ORDER BY unique when you need stable ROW_NUMBER or ROWS running totals.
• Prefer an explicit ROWS/RANGE clause in production SQL so the default RANGE peers do not surprise the next reader.
• Do not wrap the ordered column in a function inside OVER if you can avoid it; it can block a matching sort.
• Nested OLAP in the same SELECT is allowed; each OVER is independent. A quality-tier RANK and a running SUM can share PARTITION BY and still use different ORDER BY lists.

Explain It Like I'm Five

Imagine kids lined up by class (PARTITION BY), then by height (ORDER BY). RANK is handing out place ribbons for the whole class line. A window frame is a sliding cardboard sleeve that covers only some kids while you add up their pocket money. ROWS says “the sleeve covers three kids in the line, period.” RANGE says “the sleeve covers everyone who is the same height as this kid, plus everyone shorter, if we started from the front.” The class still has every kid in the photo. You did not squash them into one “class total” row the way GROUP BY would.

Exercises

1. Write RANK and DENSE_RANK over EMP partitioned by WORKDEPT ordered by SALARY DESC. Find two rows where RANK and DENSE_RANK disagree and explain why.
2. Compute a running SUM of SALARY with the default frame (ORDER BY SALARY only) and with an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Describe any ties.
3. Add a moving AVG of SALARY using ROWS BETWEEN 2 PRECEDING AND CURRENT ROW partitioned by WORKDEPT. What happens on the first two employees of a department?
4. Explain why SELECT ... RANK() OVER (ORDER BY SALARY) without a query ORDER BY can return ranks that look “out of order” on the screen.
5. Rewrite a GROUP BY WORKDEPT SUM(SALARY) as a window SUM so each employee row still appears, carrying the department total in an extra column.