Common Table Expressions and Window Functions simple, maintainable, fast queries
summary
MariaDB Server 10.2 adds Common Table Expressions and window functions and shows concrete performance gains: rewriting running-account sums with PARTITION/ORDER/ROWS replaces per-row subqueries, top-N-per-group using RANK plus a CTE scans 20M rows in one pass instead of exploding joins, and using lag/lead computes service-interval deltas on 100M rows in minutes (~5 min) rather than not finishing. The implementation supports merging and condition pushdown but not yet CTE reuse, and GROUP_CONCAT and certain range-frame features remain unimplemented.
AI-written summary · about
chapters (8)
- 0:00Introduction
- 4:20Optimizations
- 8:27Database vendors
- 12:45Window functions
- 18:22Window function example
- 22:51Practical examples
- 25:58Top in query
- 31:23Summary
related talks
44mBeyond WHERE and GROUP BY
50mCommon table expressions (CTE) and window functions in MySQL 8.0
32mWindowing Functions for Database Analytics
19mSet operations UNION, INTERSECT, and EXCEPT in MariaDB
01mWindowing Functions for Database Analytics
49mModern T SQL for Better Performance
27mAnalytical Queries in MySQL
01h 00mComplex Queries with Modern SQL