MySQL 8’s Windowing Function Part 1

存储架构 2017-09-25 阅读原文

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

责编内容by:PHPWithMySQL阅读原文】。感谢您的支持!

您可能感兴趣的

MYSQL_HISTFILE and .mysql_history The MySQL manual says : "mysql Logging On Unix, the mysql client logs statements executed interactivel...
PHP Script Successfully Creates Tables in MySQL bu... I wrote a php loop to parse JSON data and create and input it into MySQL tables. The problem is that the tables are crea...
DBLE ——基于 MySQL的高可扩分布式中间件... dble是上海爱可生信息技术股份有限公司基于mysql的高可扩展性的分布式中间件,存在以下几个优势特性: 数据水平拆分 随着业务的发展,您可以使用dble来替换原始的单个MySQL实例。 兼容Mysql 与MySQL...
MySQL 5.7.18的安装与主从复制 CentOS6.7安装mysql5.7.18 1、 解压到/usr/local目录 # tar -zxvf mysql-5.7.18-linux-glibc2.5-i686.tar.gz -C /usr/loca...
Mysql Drop Table as PreparedStatement does not wor... This prepared statement seems like valid SQL to me. PreparedStatement dropTable = cnx.prepareStatement( "DROP TABL...