Postgres Window Magic
summary
A practical walkthrough of Postgres window functions that pins down actual behavior: defaults are range vs rows (default = range), order by changes what “current row” means, and frame clauses determine visible rows; examples use generate_series(1,10), duplicates, and a temp employees table to show sums, cumulative totals, partition by behavior, and functions like lag/lead, row_number, first_value/last_value, rank/dense_rank. Slides and runnable SQL (Postgres 10) demonstrate each case and recommended explicit framing.
AI-written summary · about
chapters (25)
- 0:00Intro
- 2:46Window Functions
- 11:45Window Function Syntax
- 15:52Generic Aggregates
- 20:11One Following
- 21:24Order By
- 24:29Empty Window
- 31:00Partition By
- 34:52Layering
- 35:40Questions
- 39:59RowNumber
- 41:59Lag
- 43:25First Last Value
- 45:16Third Last Value
- 47:26Rank and Dense Rank
- 48:13Rank
- 48:26Rank Example
- 52:56Create a temp table
- 53:17Create a generic area
- 53:32Group Buy
- 53:48Rollup
- 54:04Salaries
- 54:16Window Function
- 55:20Window Average
- 58:24Ranks
related talks
32mWindowing Functions for Database Analytics
01mWindowing Functions for Database Analytics
33mMaking Data Dance
46mT-SQL Window Function Performance
21mA Hands-on Experience with Complex SQL
45mSELECT 'amazing_features' FROM "postgresql"
43mImplementing Row Pattern Recognition
34mSimple and Awesome Database Tricks