sql / intermediate
Snippet
Computing Cumulative Totals with Window Framing
Window functions compute aggregates over defined subsets without collapsing dataset rows. Using explicit frame boundaries such as UNBOUNDED PRECEDING AND CURRENT ROW ensures predictable running totals according to ANSI SQL standards.
snippet.sql
sql
1
2
3
4
5
6
7
8
9
10
11
SELECTemployee_id,department_id,hire_date,salary,SUM(salary) OVER (PARTITION BY department_idORDER BY hire_dateROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_dept_totalFROM employees;
Breakdown
1
SUM(salary) OVER (
Initiates a window function to compute an aggregate without collapsing rows.
2
PARTITION BY department_id
Resets the cumulative calculation for each distinct department.
3
ORDER BY hire_date
Establishes the chronological sequence for accumulating salary amounts.
4
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Explicitly defines the frame to include all prior rows up to the current row.