This presentation will highlight the new support for SQL window functions in MySQL 8.0: showing how they can be used to simplify and speed up analytical queries. The talk will first give a gentle introduction to basic concepts like partitions, frames and peers, explaining the differences between physical and logical frames. Then we move on to the two kinds of window functions and show their usage: SQL aggregates like COUNT and SUM used as window functions, as well as the dedicated window functions like ROW_NUMBER, NTILE, LEAD and more.
We will also cover some implementation aspects, particularly as they pertain to performance.
MySQL offers all the standard non-aggregate window functions, as well as most of the existing MySQL aggregate functions used as window functions. Window functions have the potential to greatly speed up many kinds of queries, and should be in the repertoire of all SQL developers.
Dag is a M.Sc. from NTNU, Trondheim, Norway in Computer Science, 1980. After 20 yeas an a compiler writer, he has been doing relational databases since 2000, at Sun and now Oracle, first on Clustra (a DB similar to MySQL Cluster), on Derby a.k.a Java DB, and currently on MySQL. Dag's last work was co-authoring a JSON datatype and functions for MySQL.