Window Functions and Running Calculations
A window function calculates for each row using rows from its partition, without collapsing those rows. Partitioning and ordering support ranks, running totals and previous-row comparisons. Aggregate frames define which rows contribute to each sum.
What a learner can do afterwards
- Compute a repeatable running total per customer using a stated order and an explicit row frame
- Rank rows within a partition and handle ties deliberately
- Explain how a window keeps individual orders while grouping only by customer produces customer summaries
1 · Read
You have used queries to choose which rows appear. A window function adds a calculation to each row that reaches the window stage; it does not collapse those rows. GROUP BY customer instead produces one summary row per customer. OVER defines the window. PARTITION BY c separates customers. SUM(a) OVER(PARTITION BY c), without window ordering, gives each customer's full total beside each of their rows.
Sales(c,id,a) stores customer, unique non-null order id and amount. Here id follows order time. SELECT c,id,a,SUM(a) OVER(PARTITION BY c ORDER BY id ROWS UNBOUNDED PRECEDING) FROM Sales adds a running total. A's rows (1,4) and (2,7) get totals 4 and 11. B's row (3,6) gets 6. Each customer has a separate sum.
These examples use PostgreSQL. ROWS UNBOUNDED PRECEDING means a frame from the partition's first row through the current row, in window order. With ORDER BY but no frame, the default aggregate frame also includes all rows tied with the current row. A unique window ordering key makes row-by-row totals repeatable. ORDER BY outside OVER sorts the output; it does not set the window order.
RANK() OVER(PARTITION BY c ORDER BY score DESC) ranks each customer's scores, highest first. Scores 90,90,75 get 1,1,3: ties share rank and leave gaps. DENSE_RANK() gives 1,1,2. ROW_NUMBER() gives distinct numbers 1,2,3. Add a unique id to its ORDER BY for repeatable numbering within tied scores. Do not add id to RANK or DENSE_RANK when equal scores must share a rank.
LAG(a) OVER(PARTITION BY c ORDER BY id) returns the previous row's amount within each customer partition. For A's amounts 4 then 7, the previous amounts are NULL then 4. B's only amount, 6, has NULL. With no default supplied, LAG returns NULL when there is no previous row. It looks one row back in window order, not for an id exactly one less.
Windows keep detail rows. Partitions separate calculations, row frames define running sums, and ranking ties need a stated rule.
The lesson, questions and flashcards were written for this dataset. The SQL examples use PostgreSQL conventions.
2 · Watch
به انگلیسیTake it off screen
Where it sits
Optional connections
This opens up
8 questions wait behind this lesson, each with its answer explained. Every answer feeds the sky: stars light as they are learned, and dim when it is time to come back.