成都建网站公司,市场推广方案模板,东莞响应式网站价格,广州有哪些科技公司在以前的MySQL版本中是没有窗口函数的#xff0c;直到MySQL8.0才引入了窗口函数。窗口函数是对查询中的每一条记录执行一个计算#xff0c;并且这个计算结果是用与该条记录相关的多条记录得到的。1.窗口函数与聚合函数窗口函数与聚合函数很像#xff0c;他们都是在一组记录而…在以前的MySQL版本中是没有窗口函数的直到MySQL8.0才引入了窗口函数。窗口函数是对查询中的每一条记录执行一个计算并且这个计算结果是用与该条记录相关的多条记录得到的。1.窗口函数与聚合函数窗口函数与聚合函数很像他们都是在一组记录而不是整张表上执行的。但是一个聚合函数在一组记录执行后只返回一条结果而窗口函却会对改分组内的每行记录都返回一个结果。2.常见的窗口函数MySQL8.0中定义的窗口函数主要有以下几种函数名参数描述cume_dist()否累计分布值。即分组值小于等于当前值的行数与分组总行数的比值。取值范围为(0,1]。dense_rank()否不间断的组内排序。使用这个函数时可以出现1,1,2,2这种形式的分组。first_value()是first_value(expr)返回分组内截止当前行的第一个值。lag()是;lag(expr,[N,[default]])从当前行开始往前取第N行如果N缺失默认为1。若没有没有则默认返回default。default默认值为NULLlast_value()是;last_value(expr)返回分组内截止当前行的最后一个值。lead()是;lead(expr,[N,[default]])从当前行开始往后取第N行。函数功能与lag()相反其余与lag()相同。nth_value()是;nth_value(expr,N)返回分组内截止当前行的第N行。first_value\last_value\nth_value函数功能相似只是返回分组内截止当前行的不同行号的数据。ntile()是;ntile(N)返回当前行在分组内的分桶号。在计算时要先将改分组内的所有数据划分成N个桶之后返回每个记录所在的分桶号。返回范围从1到Npercent_rank()否累计百分比。该函数的计算结果为小于该条记录值的所有记录的行数/该分组的总行数-1. 所以改记录的返回值为[0,1]rank()否间断的组内排序。其排序结果可能出现如下结果1,1,3,4,4,6row_number()否当前行在其分组内的序号。不管其排序结果中是否出现重复值其排序结果都为1,2,3,4,5注‘参数’列说明该函数是否可以加参数。“否”说明该函数的括号内不可以加参数。expr即可以代表字段也可以代表在字段上的计算比如sum(col)等。以下相同。3. over子句over子句可以指定如何将记录划分分区以供窗口函数处理。如果over()为空则是将整个查询记录作为一个分组。如果over子句不为空则其可以指定查询记录划分分组的方式以及记录在分组内部的排序方式。除此之外over子句也可以和聚合函数一起用。如果聚合函数后出现over子句那么这些聚合函数也就变成了窗口函数。如果没有over子句则他们仍然是聚合函数。可以使用over子句的聚合函数主要有以下几种avg()、bit_and()、bit_or()、bit_xor()、count()、max()、min()、stddev_pop()、stddev()、std()、stddev_samp()、sum()、var_pop()、variance()、var_samp()而对于前一部分中介绍的窗口函数来说over()子句是强制必须要有的。over子句中常见的语法形式为over_clause:{OVER (window_spec) | OVER window_name}其中window_spec:[window_name] [partition_clause] [order_clause] [frame_clause]window_name: 是指在查询语句定义的window子句。如果遇到group by、having子句order by子句那么window子句要放到having子句和order by子句中间。其语法如下WINDOW window_name AS (window_spec)[, window_name AS (window_spec)] ...而window_spec:[window_name] [partition_clause] [order_clause] [frame_clause]从语法结构可以看出来window子句其实只是把放在over()括号中的内容单独抽出来。partition_clause:即parittion by expr子句。用来指定记录分组方式。语法中的expr不仅可以是字段本身也可以是计算表达式。比如记录中有个timestramp类型的字段 ts在MySQL中partition by ts 和partition by hour(ts)都是有效的。order_clause: 即 order by expr desc|asc,expr desc|asc。 用来指定分组内的排序方式。frame_clause: 用来指定当前分组中的子集划分方式。frame可以在依据当前行的位置在每个分组内移动。使用frame来计算流水流水总和(从分区开始到当前行)及滚动平均(rolling averages)。其语法结构如下frame_clause:frame_units frame_extentframe_units:{ROWS | RANGE}frame_extent:{frame_start | frame_between}frame_between:BETWEEN frame_start AND frame_endframe_start, frame_end:{ CURRENT ROW| UNBOUNDED PRECEDING| UNBOUNDED FOLLOWING|expr PRECEDING| expr FOLLOWING}其中frame_units用来指示当前行和frame的关系ROWS: 用来定义frame的开始行和结束行(偏移量依据的是位置)RANGE: 定义frame的区间。(偏移量的基准为当前行的值)frame_entent用来指示frame的开始行和结束行。一种是通过指定start和end(frame_start,frame_end。frame_end可以不指定没有明确给出的话当前行默认为结束行)另一种使用between(frame_between)。frame_between的语法很简单。下面来看frame_start和frame_end。current row:和rows一起用时边界就是当前行。和range一起用时边界是当前行的对等点(个人理解这里所说的对等点应为与当前行的值相等的所有记录)。unbounded precceding:使用它时每个分区的第一行即为边界。unbounded following:使用它时每个分区的第一行即为边界。expr preceding\expr following: 可以由expr个性化的设置向上(preceding)向下(following)的偏移量。4.代码示例表结构如下4.1 row_number\dense_rank\ rankselect order_date,sum(quantity) as quantity,rank()over(ORDER BY sum(quantity) desc) as rank_result,dense_rank()over(ORDER BY sum(quantity) desc) as dense_result,row_number()over(ORDER BY sum(quantity) desc) as row_resultfrom spm_ordergroup by order_date-- 限定一部分数据没有实际意义能展示出这三个函数的区别就可以了having quantity98order by quantity desc运行结果如下从上面结果看出rank()函数一旦遇到重复值序号会断。比如2个7之后下个出现的序号是9。dense_rank()函数中即使有重复值但是序号是连续的。2个7之后下个出现的序号是8。row_number()不会出现相同的序号。4.2 cume_dist\percent_rankselect order_date,num,cume_dist()over(order by num asc) as cume_result,percent_rank()over(order by num asc) as percent_resultfrom (select order_date,count(1) as numfrom spm_ordergroup by order_datehaving num27)aorder by num asc代码运行结果如下分析如下cume_dist()首先总的记录有10条。当num27时num小于等于27的值共有5个所以其cume_dist()值为0.5当num28时小于等于28的值共有7个所以cume_dist()值为0.7 以此类推。percent_rank().当num27时num小于27的记录数为0所以percent_rank()为0当num28时num28的记录数共有5个所以percent_rank()的值为5/9 而当num29时其cume_dist()7/9以此类推直到最大值36对应的值为1.这两个函数的作用有点像计算中位数。4.3 first_value\last_value\nth_valueselect sales_name,year_date,num,first_value(num)over(PARTITION by sales_name order by year_date asc) as first_result,last_value(num)over(PARTITION by sales_name order by year_date asc) as last_result,nth_value(num,2)over(PARTITION by sales_name order by year_date asc) as nth_resultfrom (select sales_name,year(order_date)as year_date,count(1) as numfrom spm_orderwhere sales_name in (杨健,楚杰,洪光)group by year(order_date),sales_nameorder by sales_name asc,year_date asc)a代码运行结果如下(要注意这三个函数计算结果都是截止当前行)4.4 ntile()select sales_name,year_date,num,ntile(8)over(order by num asc) as n_binfrom (select sales_name,year(order_date)as year_date,count(1) as numfrom spm_orderwhere sales_name in (杨健,楚杰,洪光)group by year(order_date),sales_nameorder by sales_name asc,year_date asc)a代码运行结果如下从结果上进行分析首先分桶号从1到N都会出现其次关于每个桶应该有多少条记录。可以假设有N个桶m个球(球数为总记录数)标号从1到N依次往1号桶到N号桶里投球每次只投1个球。循环往复直到m个球全都投入到N个桶中。最后每个桶里有多少球现在每个桶里就有多少条记录。4.5 lag\leadselect sales_name,year_date,num,lag(num,2)over(PARTITION by sales_name order by year_date asc) as lag_result,lead(num,2)over(PARTITION BY sales_name order by year_date asc) as lead_resultfrom (select sales_name,year(order_date)as year_date,count(1) as numfrom spm_orderwhere sales_name in (杨健,楚杰,洪光)group by year(order_date),sales_nameorder by sales_name asc,year_date asc)a代码运行结果如下注意lag()和lead()函数中出现的字段可以与over()子句中order by中出现的字段不一致。在代码lag(num,2)中2代表的想要取数的那一行相比当前行的偏移量(lead中也类似)。4.6 聚合函数select sales_name,year_date,num,sum(num)over(PARTITION by sales_name) as sum_order,avg(num)over(PARTITION by sales_name) as mean_orderfrom (select sales_name,year(order_date)as year_date,count(1) as numfrom spm_orderwhere sales_name in (杨健,楚杰,洪光)group by year(order_date),sales_nameorder by sales_name asc,year_date asc)a代码运行结果如下4.7 order by子句select sales_name,year_date,num,sum(num)over(partition by sales_name) as count_1,count(num)over(partition by sales_name order by num) as count_2from (select sales_name,year(order_date)as year_date,count(1) as numfrom spm_orderwhere sales_name in (杨健,楚杰,洪光)group by year(order_date),sales_nameorder by sales_name asc,year_date asc)a代码运行结果如下当frame_clause不存在的时候默认的frame与order by子句是否存在有关如果有order by子句则默认的frame是从当前分区第一行到当前行。即在此种情况下默认的frame为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW如果没有order by子句则默认的frame是指该分区。如果此时也没有partition by子句则相当于全部数据。4.8 window子句select sales_name,year(order_date) as year_1,count(1) as num,sum(count(1)) over w as sales_order,sum(count(1)) over (w_1) as year_order,rank()over(w order by count(1) desc) as rank_order-- 三种写法都是符合语法规范的from spm_orderwhere sales_name in (杨健,楚杰,洪光)group by sales_name,year(order_date)window w as (PARTITION by sales_name),w_1 as (PARTITION by year(order_date))order by sales_order代码运行结果如下4.9 rows和rangerows和range是不能单独使用的但是因为实在不理解这两个用法上的区别所以就进行了单独的验证。select sales_name,month_1,rn_1,num,sum(num)over(order by month_1 rows between 2 preceding and 1 preceding) as month_row,sum(num)over(order by month_1 range between 2 preceding and 1 preceding) as month_range,sum(num)over(order by rn_1 range between 2 preceding and 1 preceding) as rn_rangefrom (SELECT sales_name,month(order_date) as month_1,count(1) as num,-- 由于rank()over()返回的是unsigned,当相减结果为负时(between子句会用到减法)会报错所以这里转成signed类型cast(rank()over(order by month(order_date)) as signed) as rn_1from spm_orderwhere sales_name in (洪光,范彩)group by sales_name,month(order_date))aorder by month_1 asc代码运行结果如下对以上代码分析首先在这里我新建了一个rn_1列。rn_1列和month_1的区别在于month_1的数据是连续的而rn_1列是有中断的(两个1之后出现的是3我是故意要创建一个中断的序列来分析一下range的作用范围)先来看month_row的区别month_row列的计算结果为当前行在分区中按month_1升序排序之后排在其前面的两行(between and限定的)的sum求和值。所以rows后面的between and限定的偏移量是基于他们在分区中的排列位置的。再来看month_range通过分析其实验结果可以发现month_range列的计算为分区内month_1当前行-1和month_1当前行-2(-1-2是由between an子句决定的。preceding代表负following代表正)所有列的sum求和值。再来看rn_range, rn_range列的计算结果为分区内month_1当前行-2的所有里列的sum求和值。所以rang后面的between and限定的偏移量依据的是当前行的数值。
