存储架构

MySQL 8's Windowing Function Part 1

微信扫一扫,分享到朋友圈

MySQL 8's Windowing Function Part 1
0 0

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.

PHPWithMySQL
感谢您的支持!

    Obama personally warned Zuckberberg over fake news, report claims

    上一篇

    【BOSS说】合星财富辛晓冬:中国中产家庭的资产配置呈两极分化

    下一篇

    您也可能喜欢

    评论已经被关闭。

    插入图片