Windowing Functions are new to MySQL with Version 8. I have been very lucky in the past few days to find two excellent resources that I want to share with you. Both are worth the time and effort to study.
At the Southern California Linux Expo last week, Bruce Momjian of EnterpriseDB and PostgreSQL fame gave an amazing tutorial on Common Table Expressions and Windowing Functions (slides for both at https://momjian.us/main/presentations/sql.html). Bruce is an amazing presenter and I highly recommend going to his sessions at conferences. So, what can a MySQL-er learn from a Postrgrestian?
Plenty.
In this case the two databases are using SQL and the features that turn that language from descriptive to imperative. But showing how these features is very hard. Bruce said it took him six years to finish the Windowing Function presentation. And the results are impressive.
You will have to make some changes to the presented code as MySQL does not have sequences -- create a table named x, with a single int column named x, and fill it with the numbers one through 10 for the first windowing function examples. The big item to study is the various partitioning terminology which can get confusing but is spelled out explicitly in this tutorial.
The next tutorial is from Alex Yeskov and can be found at https://blog.statsbot.co/sql-window-functions-tutorial-b5075b87d129 and is a great addendum to the other tutorial. He stresses learning by doing and do he does!!
His examples include calculating revenue growth, running totals, dealing with duplicated data, finding the top N rows, and examining repeat customer purchases.
Doing these two tutorials will not turn you into a windowing function expert. But you will have a better understanding of how they work and that will make you a better SQL programmer
