DB2 running totals and window patterns

Window functions keep the detail rows

A conventional GROUP BY changes the grain of a result. If twenty transactions belong to one account, grouping by account normally turns those twenty rows into one row. A window aggregate does not collapse them. Db2 evaluates the function over a related set of rows and places the answer beside each current detail row.

1
2
3
4
5
6
7
8
SELECT ACCOUNT_ID,
       TXN_TS,
       TXN_ID,
       AMOUNT,
       SUM(AMOUNT) OVER (
         PARTITION BY ACCOUNT_ID
       ) AS ACCOUNT_TOTAL
  FROM ACCOUNT_TXN;

Every transaction remains visible, but each carries its account total. With no window ORDER BY, the aggregate sees the whole partition. That is not yet a running total: every row in one account receives the same answer. Adding window ORDER BY introduces a meaningful current position, and adding a frame states how far backward or forward the aggregate can see.

The three decisions inside OVER

1. PARTITION BY: where should the calculation reset?

PARTITION BY divides the rows that survive FROM, JOIN, and WHERE into independent partitions. A running account balance normally partitions by ACCOUNT_ID. A departmental payroll total partitions by WORKDEPT. If you omit PARTITION BY, the entire qualifying result is one partition, so the calculation never resets.

Partitioning is logical; it does not require the table itself to use table-controlled partitioning or a partitioned table space. The expressions in the OLAP clause simply define groups for this calculation. A composite business key can require several expressions, such as PARTITION BY COMPANY_ID, ACCOUNT_ID.

2. ORDER BY: what does before and after mean?

ORDER BY inside OVER defines calculation order. For transactions, a timestamp alone may not be unique, so use a stable tie-breaker such as TXN_ID:

1
ORDER BY TXN_TS, TXN_ID

This window ORDER BY does not promise final result order. Db2 is still free to return rows in any sequence unless the full SELECT has an outer ORDER BY. Production reports commonly repeat the same keys at query level.

3. The frame: which ordered rows participate?

The frame moves as Db2 evaluates each current row. UNBOUNDED PRECEDING means the first row of this partition. CURRENT ROW is the current physical row for ROWS, but for RANGE it represents the current ORDER BY value and its peers. A numeric bound such as 2 PRECEDING means two rows under ROWS; under RANGE it means a value offset and has stricter ORDER BY type and key-count rules.

A reliable running total with SUM

The safest beginner pattern states all three decisions and makes ordering unique:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT ACCOUNT_ID,
       TXN_TS,
       TXN_ID,
       AMOUNT,
       SUM(AMOUNT) OVER (
         PARTITION BY ACCOUNT_ID
         ORDER BY TXN_TS, TXN_ID
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS RUNNING_AMOUNT
  FROM ACCOUNT_TXN
 WHERE TXN_STATUS = 'POSTED'
 ORDER BY ACCOUNT_ID, TXN_TS, TXN_ID;

WHERE is applied before the window calculation, so only posted transactions contribute. When ACCOUNT_ID changes, the total restarts. Within one account, the timestamp and transaction id determine exactly which row enters next. The explicit ROWS frame avoids accidentally grouping timestamp peers.

SUM ignores null expression values. If a null AMOUNT should mean zero, write SUM(COALESCE(AMOUNT, 0)) rather than assuming null is stored as zero. Also consider the result data type. Very large integer or decimal accumulations can overflow the result type; cast to a suitable DECIMAL before SUM when the business maximum demands it.

SUM, AVG, and COUNT windows

SUM: cumulative and conditional amounts

CASE inside SUM creates a conditional running total. This is useful when one ordered stream contains credits and debits:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT ACCOUNT_ID,
       TXN_TS,
       TXN_ID,
       TXN_TYPE,
       AMOUNT,
       SUM(CASE
             WHEN TXN_TYPE = 'C' THEN AMOUNT
             ELSE DECIMAL(0, 15, 2)
           END) OVER (
             PARTITION BY ACCOUNT_ID
             ORDER BY TXN_TS, TXN_ID
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) AS RUNNING_CREDITS,
       SUM(CASE
             WHEN TXN_TYPE = 'D' THEN AMOUNT
             ELSE DECIMAL(0, 15, 2)
           END) OVER (
             PARTITION BY ACCOUNT_ID
             ORDER BY TXN_TS, TXN_ID
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) AS RUNNING_DEBITS
  FROM ACCOUNT_TXN;

Use ELSE zero when every row should contribute a numeric value. Without ELSE, CASE returns null for unmatched rows; SUM ignores those nulls, which can be acceptable but can produce null before the first matching event. Matching decimal types also avoids unintended type promotion.

AVG: trailing and centered moving averages

A three-row trailing average uses the current row and at most two previous rows. At the beginning of a partition the frame is smaller; Db2 does not invent missing rows.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT SENSOR_ID,
       SAMPLE_TS,
       READING,
       AVG(READING) OVER (
         PARTITION BY SENSOR_ID
         ORDER BY SAMPLE_TS
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS AVG_LAST_3,
       COUNT(READING) OVER (
         PARTITION BY SENSOR_ID
         ORDER BY SAMPLE_TS
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS VALUES_IN_AVG
  FROM SENSOR_SAMPLE
 ORDER BY SENSOR_ID, SAMPLE_TS;

COUNT(READING) reveals how many non-null readings AVG actually used. COUNT(*) would count rows even when READING is null. If each sensor can produce multiple rows at the same SAMPLE_TS, add a sample sequence to ORDER BY. For a centered average, use ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING, but remember that this includes a future row from the reporting sequence.

A row-based “last three” is not the same as “last three days.” ROWS counts observations. A RANGE offset is value-based, but offset RANGE frames have restrictions, including a single suitable sort-key expression. For calendar reporting, many teams first build one row per day in a common table expression, then apply a ROWS frame to that regular series.

COUNT: sequence counts and completeness checks

COUNT(*) with a cumulative frame gives the row position within a partition. It differs from ROW_NUMBER mainly because COUNT is an aggregate and accepts a frame, while ROW_NUMBER is a numbering specification. COUNT(expression) counts only non-null expression values.

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT ORDER_ID,
       EVENT_TS,
       EVENT_ID,
       STATUS,
       COUNT(*) OVER (
         PARTITION BY ORDER_ID
         ORDER BY EVENT_TS, EVENT_ID
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS EVENT_NUMBER,
       COUNT(COMPLETED_TS) OVER (
         PARTITION BY ORDER_ID
       ) AS COMPLETION_EVENTS
  FROM ORDER_EVENT;

EVENT_NUMBER progresses one row at a time. COMPLETION_EVENTS has no window ORDER BY, so it repeats the total count of non-null completion timestamps for the whole order partition.

ROWS versus RANGE: ties change the answer

Suppose two transactions have the same POSTING_DATE. With ORDER BY POSTING_DATE and a RANGE cumulative frame, both are peers. The running amount shown for each includes both transactions. The result advances by date groups, not individual rows.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT POSTING_DATE,
       TXN_ID,
       AMOUNT,
       SUM(AMOUNT) OVER (
         ORDER BY POSTING_DATE
         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS TOTAL_BY_DATE_GROUP,
       SUM(AMOUNT) OVER (
         ORDER BY POSTING_DATE, TXN_ID
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS TOTAL_BY_TRANSACTION
  FROM ACCOUNT_TXN
 WHERE ACCOUNT_ID = 1001
 ORDER BY POSTING_DATE, TXN_ID;

Neither result is universally correct. RANGE expresses a business rule like “balance through this posting date,” where transactions on the same date belong together. ROWS expresses “balance after this transaction,” which requires a deterministic transaction sequence.

For aggregate windows that specify ORDER BY but omit an explicit frame, Db2 uses the cumulative peer-aware behavior equivalent to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This default is concise, but explicit frames are easier to review and less likely to surprise maintainers.

LAG and LEAD: compare neighbouring rows

LAG returns an expression from an earlier row in the window sequence. LEAD returns one from a later row. The default offset is one. They stay within the current partition, so the first LAG and final LEAD normally return null. An optional default can replace that out-of-partition null.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT ACCOUNT_ID,
       TXN_TS,
       TXN_ID,
       AMOUNT,
       LAG(AMOUNT) OVER (
         PARTITION BY ACCOUNT_ID
         ORDER BY TXN_TS, TXN_ID
       ) AS PREVIOUS_AMOUNT,
       LEAD(AMOUNT) OVER (
         PARTITION BY ACCOUNT_ID
         ORDER BY TXN_TS, TXN_ID
       ) AS NEXT_AMOUNT
  FROM ACCOUNT_TXN
 ORDER BY ACCOUNT_ID, TXN_TS, TXN_ID;

This replaces a common self-join whose only purpose is to locate the previous or next row. It does not calculate a difference automatically. Put the window result in a nested table expression or common table expression, then calculate and filter outside when that is clearer:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH BALANCE_HISTORY AS (
  SELECT ACCOUNT_ID,
         AS_OF_DATE,
         BALANCE,
         LAG(BALANCE, 1, DECIMAL(0, 15, 2)) OVER (
           PARTITION BY ACCOUNT_ID
           ORDER BY AS_OF_DATE
         ) AS PRIOR_BALANCE
    FROM DAILY_BALANCE
)
SELECT ACCOUNT_ID,
       AS_OF_DATE,
       BALANCE,
       PRIOR_BALANCE,
       BALANCE - PRIOR_BALANCE AS DAILY_CHANGE
  FROM BALANCE_HISTORY
 WHERE BALANCE <> PRIOR_BALANCE
 ORDER BY ACCOUNT_ID, AS_OF_DATE;

The third LAG argument is used only when the requested preceding row is outside the partition; it does not replace a null BALANCE stored in an existing preceding row. Current Db2 for z/OS levels also support null-treatment options for LAG and LEAD, but package APPLCOMPAT and subsystem function level govern newer syntax. Verify deployment compatibility before using optional features beyond the basic form.

Ordering, ties, and repeatable answers

• Choose ORDER BY keys that match the business sequence, not merely columns that happen to be indexed.
• Add a unique tie-breaker for ROWS calculations when each row needs a repeatable intermediate value.
• Use RANGE deliberately when equal ORDER BY values should share one cumulative answer.
• Specify NULLS FIRST or NULLS LAST when nullable ordering keys are valid and their position matters.
• Repeat the ordering at query level when consumers require rows in that sequence.

A tie-breaker can change meaning. ORDER BY POSTING_DATE, TXN_ID no longer has the same peer groups as ORDER BY POSTING_DATE alone. That is desirable for ROWS-based transaction sequencing, but not if the business rule says every transaction on one date is simultaneous. Decide the rule before adjusting SQL to make output look tidy.

Filtering window results

Window values are produced after the input rows have been filtered. You cannot normally refer to a newly assigned window alias in the WHERE clause of that same query block. Compute the window in an inner query or CTE, then filter in an outer SELECT.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WITH RUNNING AS (
  SELECT ACCOUNT_ID,
         TXN_TS,
         TXN_ID,
         AMOUNT,
         SUM(AMOUNT) OVER (
           PARTITION BY ACCOUNT_ID
           ORDER BY TXN_TS, TXN_ID
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS RUNNING_AMOUNT
    FROM ACCOUNT_TXN
   WHERE TXN_STATUS = 'POSTED'
)
SELECT ACCOUNT_ID, TXN_TS, TXN_ID, AMOUNT, RUNNING_AMOUNT
  FROM RUNNING
 WHERE RUNNING_AMOUNT > 100000
 ORDER BY ACCOUNT_ID, TXN_TS, TXN_ID;

Moving TXN_STATUS to the outer query would change the calculation because rejected rows would first participate in SUM and only then disappear. Predicate placement is part of the business definition, not just a tuning detail.

Performance caveats on Db2 for z/OS

Windows often require Db2 to organize rows by partition and order keys. A large unfiltered partition can need a substantial sort and work-file space. Several window expressions can share processing when their PARTITION BY, ORDER BY, and framing needs are compatible; unrelated orderings can require additional work.

• Apply selective predicates before the window when that matches the required answer. Fewer input rows usually mean less sorting and fewer pages in work files.
• Return only required columns. Wide rows increase data movement and intermediate storage.
• Consider indexes whose leading columns support selective predicates and then the partition and order keys. An index is not a guarantee that every sort disappears; joins, direction, expressions, and optimizer choices still matter.
• Avoid wrapping order keys in unnecessary functions. ORDER BY DATE(TXN_TS), for example, can prevent direct use of ordering already available on TXN_TS and also creates larger peer groups.
• Use EXPLAIN and the appropriate PLAN_TABLE information to inspect sort and access-path decisions. Test with realistic partition sizes and tie distributions.
• Watch numeric result types and overflow. A fast query that fails near month end is still incorrectly designed.

Do not replace a clear window expression with correlated subqueries merely to avoid the word “sort.” A correlated running-total subquery can repeatedly scan earlier rows and scale poorly. Compare real access paths and elapsed and CPU measurements instead of assuming one syntax is always faster.

Related aggregation patterns

Conditional SUM and COUNT combine naturally with windows. String aggregation is a different pattern: on Db2 for z/OS, use LISTAGG with WITHIN GROUP to build an ordered string for a group rather than assuming that every aggregate accepts the same OVER syntax. GROUP BY, LISTAGG ordering, and window ordering solve related but distinct problems.

You can also place a grouped result in a CTE and then apply a window to those grouped rows. For example, first produce one row per month with SUM(SALES_AMOUNT), then calculate a running year-to-date total across the monthly rows. This two-stage design makes the grain explicit and can dramatically reduce the number of rows entering the window.

Explain It Like I'm Five

Imagine each account has its own line of numbered cups. PARTITION BY puts cups for different accounts into separate lines. ORDER BY tells you which cup is first, second, and third. A running SUM pours all the water from the first cup through the cup you are holding into a measuring jug. ROWS says “count actual cups.” RANGE says “all cups with the same number sticker belong together.” AVG tells you the average water in the nearby cups, COUNT tells you how many cups were used, LAG peeks at the cup just behind you, and LEAD peeks at the cup just ahead. Every cup stays in the line; the calculation only adds a useful note beside it.

Exercises

• Write a running SUM of SALARY for DSN8C10.EMP, partitioned by WORKDEPT and ordered by HIREDATE plus EMPNO. Explain why EMPNO is included.
• Compare an omitted frame, explicit RANGE UNBOUNDED PRECEDING, and explicit ROWS UNBOUNDED PRECEDING on data containing duplicate dates.
• Calculate a five-row trailing AVG with ROWS BETWEEN 4 PRECEDING AND CURRENT ROW. Add COUNT(column) to show how many non-null values participate.
• Use LAG to find the change from the previous daily balance. Decide whether the first row should show null, zero, or the full opening balance, and encode that rule.
• Build cumulative credit and debit totals with CASE expressions inside two SUM windows.
• Use a CTE to return only rows where a running total first exceeds a threshold. Explain why the window alias is filtered in the outer query.

Frequently asked questions