快捷搜索:  汽车  科技

大数据分析mysql数据(数据分析系列1732)

大数据分析mysql数据(数据分析系列1732)窗口的概念非常重要,它可以理解为记录集合,窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每 条记录都要在此窗口内执行函数,有的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口;有的函数则相反,不同的记录对应着不同的窗口,这种动态变化的窗口叫滑动窗口。 窗口函数和普通聚合函数也很容易混淆,二者区别如下:什么叫窗口? 01 MySQL窗口函数MySQL从8.0开始支持窗口函数,这个功能在大多商业数据库和部分开源数据库中早已支持,有的也叫分析函数。

大数据分析mysql数据(数据分析系列1732)(1)

在日常的数据分析工作中,我们可能会经常遇到类似这样的需求:环比如何计算?怎么样得到各部门工资排名前N名员工列表?查找各部门每 人工资占部门总工资的百分比?

对于这样的需求,使用传统的SQL实现起来比较困难。这类需求都有一个共同的特点,需要在单表中满足某些条件的记 录集内部做一些函数操作,不是简单的表连接,也不是简单的聚合可以实现的,通常会让写SQL的同学焦头烂额、绞尽 脑汁,费了大半天时间写出来一堆长长的晦涩难懂的自连接SQL,且性能低下,难以维护。

要解决此类问题,最方便的就是使用窗口函数。

大数据分析mysql数据(数据分析系列1732)(2)

PART

01

MySQL窗口函数

MySQL从8.0开始支持窗口函数,这个功能在大多商业数据库和部分开源数据库中早已支持,有的也叫分析函数。

什么叫窗口?

窗口的概念非常重要,它可以理解为记录集合,窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每 条记录都要在此窗口内执行函数,有的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口;有的函数则相反,不同的记录对应着不同的窗口,这种动态变化的窗口叫滑动窗口。
窗口函数和普通聚合函数也很容易混淆,二者区别如下:

  • 聚合函数是将多条记录聚合为一条;而窗口函数是每条记录都会执行,有几条记录执行完还是几条。
  • 聚合函数也可以用于窗口函数中,这个后面会举例说明。

    下面是一个窗口函数的简单例子:

--按用户id分组,消费金额进行降序排select * FROM ( select id order_money member_id create_time row_number() over(partition by member_id ORDER BY order_money desc) as row_num from dm_order_2018) t;

上面例子中,row_number()over(partition by member_id order by order_money desc)这部分都属于窗口函数,它的功能是显示 每个用户按照订单金额从大到小排序的序号。

大数据分析mysql数据(数据分析系列1732)(3)

PART

02

窗口函数分类及语法

按照功能划分,可以把MySQL支持的窗口函数分为如下几类:

  • 序号函数:row_number() / rank() / dense_rank()
  • 分布函数:percent_rank() / cume_dist()
  • 前后函数:lag() / lead()
  • 头尾函数:first_val() / last_val()
  • 其他函数:nth_value() / nfile()
    窗口函数语法:

window_function_name(expression) OVER ( [partition_defintion] [order_definition] [frame_definition] )PARTITION BY <expression>[{ <expression>...}] 以xx字段作为维度;ORDER BY <expression> [ASC|DESC] [{ <expression>...}] 以xx字段排序,默认升序;[frame_definition] frame_unit {<frame_start>|<frame_between>} 可以认为是更细分的累计。

先指定作为窗口函数的函数名,然后是OVER(…),就算OVER里面没有内容,括号也需要保留。窗口函数的一个概念是当前行,当前行属于某个窗口,窗口由“[partition_defintion]”,“[order_definition]”,“[frame_definition]“确定。
partition_defintion
翻译过来应该是分区,语法是"PARTITION BY < expression>[{ < expression>…}]",它会根据单个或者多个表达式的计算结果来分区(列名也是一种表达式,它的结果就是列名本身)。
在前面的例子中,结果中的每一行都有自己的分区。
frame_definition
这里先讲frame_definition,可能应该是叫帧吧。它的作用是在分区里面再进一步细分窗口。语法是"frame_unit {< frame_start>|< frame_between>}",
frame_unit有两种,分别是ROWS和RANGE,由ROWS定义的frame是由开始和结束位置的行确定的,由RANGE定义的frame由在某个值区间的行确定。
如果只指定了frame的开始位置,那么结束位置就默认为当前行。frame_start有三种:
UNBOUNDED PRECEDING: 区间的第一行
N PRECEDING: 当前行之前的N行,N可以是数字,也可以是一个能计算出数字的表达式 CURRENT ROW: 当前行
frame_between的可以取的值如下:
frame_start:如前面所列
UNBOUNDED FOLLOWING:区间的最后一行
N FOLLOWING:当前行之后的N行,N可以是数字,也可以是一个能计算出数字的表达式
如果没显式指定frame的话,MySQL会认为frame是“ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING”

今天分享下MySQL中的窗口函数,这对分析查询很有帮助,在工作中,可能会遇到各种百分比、排序、累积求和等需求需要进行窗口查询。明天举个具体案例来逐个讲解数据分析函数。

欢迎关注微信公众号,访问更多精彩:数据之魅

如需转载,请联系授权,谢谢合作。

大数据分析mysql数据(数据分析系列1732)(4)

猜您喜欢: