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.
