Sunday, September 24, 2017

MySQL 8's Windowing Function Part 1

MySQL will have Windowing functions and CTEs which will mean it will be easier to do data analysis with MySQL. You can now make calculations on data from each row in a query plus rows related to that row. Windows will make it easier to group items when GROUP BY does not meet needs. This is a great breakthrough but the new documentation has a steep learning curve if you are starting from zero. Hopefully this and following blogs will make it easier to get started with Windowing Functions.

OVER & WINDOW as a keywords

Let's start with the world_x sample database. The sample below orders the city table by the CountryCode but notice the window w as (order by CountryCode) phrase. This sets up a group for analysis, or a window on the data. For this example we will get the row number, rank, and dense rank of the data in that group. So for CountryCode of ABW we get a row number of 1, rank of 1, and dense rank of 1. The dense rank, the last column, increases by one as the CountryCode increases. The rank column increases also but keeps the same number as the rest of the CountryCode group. A GROUP BY would collapse all this information to a single line for each CountryCode which is not as interesting.
mysql> select ID, Name, CountryCode, row_number() over w as 'row#', 
rank() over w as 'rank', dense_rank() over w as 'dr' from city 
window w as (order by CountryCode) limit 10;
+-----+----------------+-------------+------+------+----+
| ID  | Name           | CountryCode | row# | rank | dr |
+-----+----------------+-------------+------+------+----+
| 129 | Oranjestad     | ABW         |    1 |    1 |  1 |
|   1 | Kabul          | AFG         |    2 |    2 |  2 |
|   2 | Qandahar       | AFG         |    3 |    2 |  2 |
|   3 | Herat          | AFG         |    4 |    2 |  2 |
|   4 | Mazar-e-Sharif | AFG         |    5 |    2 |  2 |
|  56 | Luanda         | AGO         |    6 |    6 |  3 |
|  57 | Huambo         | AGO         |    7 |    6 |  3 |
|  58 | Lobito         | AGO         |    8 |    6 |  3 |
|  59 | Benguela       | AGO         |    9 |    6 |  3 |
|  60 | Namibe         | AGO         |   10 |    6 |  3 |
+-----+----------------+-------------+------+------+----+
10 rows in set (0.01 sec)


Good Material

Do you know where good material on SQL Windowing functions lurk? Please send it on to me as I am having difficulty finding good novice to intermediate level training materials.