Windowing Functions for Database Analytics

on YouTube ↗32m

" summary

Introduction to SQL window functions with concrete MySQL 8.0 examples showing how to get running totals, moving averages, quartiles and percent ranks from table data. Demonstrates actual queries: MySQL 8.0 syntax, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frames, and LEAD/LAG for offsets; contrasts mistaken aggregate SUM() use vs partitioned window SUM(), shows partitioning by column, named windows, row_number/rank/dense_rank outputs, and handling of NULL and rollup so queries produce the per-row analytics expected.

AI-written summary · about

" chapters (12)

1. 0:00Introduction to talk
2. 1:08What are windowing functions
3. 3:39Aggregate vs window functions
4. 8:58Key windowing function syntax
5. 12:47Window frames and partitions
6. 16:43Running totals by department
7. 21:58Ranking and percentiles
8. 24:09Calculating percentages
9. 24:54Quartiles and averages
10. 26:17Lead and lag functions
11. 28:44Syntax and window definition
12. 29:05Resources and closing

" related talks

• 50mCommon table expressions (CTE) and window functions in MySQL 8.0Dmitriy Lenev · Backend Conf 2018
• 01h 06mPostgres Window MagicBruce Momjian · PostgresOpen Silicon Valley 2017
• 34mCommon Table Expressions and Window Functions simple, maintainable, fast queriesVicentiu-Marian Ciorbaru · Percona Live Open Source Database Conference 2017
• 51mUnderstanding window functions in DAXMarco Russo · SQLBits 2024
• 46mT-SQL Window Function PerformanceKathi Kellenberger · Women Data Summit 2021
• 44mBeyond WHERE and GROUP BYSergey Golubchik · HighLoad++ 2017
• 49mModern T SQL for Better PerformanceKathi Kellenberger · SQL Server Virtual Conference 2021
• 01h 00mComplex Queries with Modern SQLHolger Jakobs · FrOSCon 2016