OLAP and analytic functions in DB2

What an OLAP specification is

An OLAP specification computes a value for the current row using other rows in a window. The window is defined by OVER(...). Unlike AVG in a GROUP BY query, the detail rows stay visible. You still see every employee; you also see their rank.

IBM groups three forms:

• Ordered OLAP — RANK() and DENSE_RANK() with a required window ORDER BY.
• Numbering — ROW_NUMBER() with an optional window ORDER BY.
• Aggregation specification — AVG, SUM, COUNT, and friends with OVER, optionally with a ROWS/RANGE frame (covered on the window-frame page).

Restrictions that surprise people coming from other SQL dialects:

• Not in WHERE, HAVING, or GROUP BY.
• Not as an argument of an aggregate function.
• Partition and sort expressions must unambiguously reference columns of the subselect result. They must not include a scalar fullselect, XMLQUERY, XMLEXISTS, or a non-deterministic / external-action function.

The OVER clause

1
2
3
RANK() OVER (PARTITION BY workdept ORDER BY salary DESC)
DENSE_RANK() OVER (PARTITION BY workdept ORDER BY salary DESC)
ROW_NUMBER() OVER (PARTITION BY workdept ORDER BY salary DESC)

• PARTITION BY partition-expression, ... — optional. Split rows into independent windows. Omit it and the whole result is one partition.
• ORDER BY sort-key-expression [ASC | DESC] [NULLS FIRST | NULLS LAST] — required for RANK and DENSE_RANK; optional for ROW_NUMBER. This orders rows inside the window, not the final query output.

PARTITION BY

PARTITION BY is not tablespace partitioning. It is “restart the ranking for each department.”

1
2
3
SELECT WORKDEPT, EMPNO, LASTNAME, SALARY,
       RANK() OVER (PARTITION BY WORKDEPT ORDER BY SALARY DESC) AS RK
  FROM DSN8C10.EMP;

Each WORKDEPT value gets its own sequence starting at 1. Two employees in A00 and D11 can both be rank 1. Multiple partition expressions are allowed (PARTITION BY WORKDEPT, JOB) — think of the combination as the window key.

Each column in a partitioning-expression must unambiguously reference a column of the subselect that contains the OLAP specification.

RANK

RANK assigns the ordinal rank of a row within its window. Rows that are not distinct on the ORDER BY keys share the same rank. The next distinct value skips ahead so that rank equals “1 + count of rows with a strictly better ordering key.”

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

DENSE_RANK

DENSE_RANK also ties equal keys, but the next group gets the next integer with no gap. Use DENSE_RANK when you want “medal classes” (gold / silver / bronze) without holes. Use RANK when the number should mean “how many people are strictly ahead of me, plus one.”

ROW_NUMBER

ROW_NUMBER assigns 1, 2, 3, … with no shared numbers. If you omit ORDER BY in the window, numbers are assigned in an arbitrary order as rows come back — fine for “give me any unique sequence,” dangerous for “page 2 of a report.” Always specify ORDER BY when the number must be reproducible.

1
2
3
SELECT EMPNO, LASTNAME,
       ROW_NUMBER() OVER (ORDER BY LASTNAME, EMPNO) AS RN
  FROM DSN8C10.EMP;

Add EMPNO (or another unique key) at the end of the window ORDER BY so ties on LASTNAME still produce a stable sequence.

RANK vs DENSE_RANK vs ROW_NUMBER

Two people at 90000: RANK and DENSE_RANK both show 1. RANK then jumps to 3 for 80000. DENSE_RANK uses 2. ROW_NUMBER never ties.

Top-N per partition

Because you cannot put RANK in WHERE, nest the query:

1
2
3
4
5
6
7
8
9
SELECT WORKDEPT, EMPNO, LASTNAME, SALARY, RK
  FROM (
        SELECT WORKDEPT, EMPNO, LASTNAME, SALARY,
               RANK() OVER (PARTITION BY WORKDEPT
                            ORDER BY SALARY DESC) AS RK
          FROM DSN8C10.EMP
       ) AS R
 WHERE RK <= 3
 ORDER BY WORKDEPT, RK, EMPNO;

A CTE (WITH) is the same idea and often clearer in production SQL. FETCH FIRST n ROWS ONLY applies to the whole result, not per partition — that is why RANK/ROW_NUMBER plus an outer filter is the pattern for “top 3 per dept.”

Window ORDER BY versus query ORDER BY

They do different jobs. The window ORDER BY decides ranks. The query ORDER BY decides what the user sees. If you omit the outer ORDER BY, Db2 may return ranked rows in any sequence. Always ORDER BY the partition key, then the rank, then a unique tie-breaker for reports.

NULLS FIRST and NULLS LAST on the window sort control whether unknown salaries rank as best or worst. Make that choice explicit in HR queries.

Explain It Like I'm Five

Imagine a sports day. PARTITION BY is “line up by class.” ORDER BY is “tallest first.” RANK gives out place ribbons but if two kids are the same height they share first place and nobody gets second (the next kid is third). DENSE_RANK still shares first, but the next kid is second — no skipped ribbon. ROW_NUMBER hands out numbered stickers 1, 2, 3 even if two kids are the same height — someone just stands a little to the left. You cannot ask “only show ribbon 1” while kids are still lining up (WHERE); you take the photo of everyone with ribbons, then keep the pictures you want.

Exercises

1. Rank employees in DSN8C10.EMP by SALARY descending within WORKDEPT using RANK.
2. Using the four-salary table above, write the DENSE_RANK values from memory, then check.
3. Write ROW_NUMBER to number employees alphabetically by LASTNAME, EMPNO with a stable order.
4. Build a nested query that returns the top two salaries per department.
5. Explain why RANK() OVER (ORDER BY SALARY DESC) in the WHERE clause is illegal and how to filter ranks correctly.