做网站需要多少空间,wordpress有广告插件,删除wordpress主题,手游折扣平台app哪个好文章目录 学前复习开窗函数窗口函数及应用排名函数错行函数取值函数分箱函数聚合函数 总结 学前复习
在学习开窗函数前可以先复习下之前的内容#xff1a;Mysql 常用函数和基础查询#xff0c;还有遗漏的一些函数如下#xff1a;
转换函数#xff1a;CAST(expression AS … 文章目录 学前复习开窗函数窗口函数及应用排名函数错行函数取值函数分箱函数聚合函数 总结 学前复习
在学习开窗函数前可以先复习下之前的内容Mysql 常用函数和基础查询还有遗漏的一些函数如下
转换函数CAST(expression AS data_type)
用于将某种数据类型的表达式显式转换为另一种数据类型CAST()函数的参数是一个表达式它包括用 AS关键字分隔的源值和目标数据类型
expression:任何有效的表达式。AS:用于分隔两个参数在AS之前的是要处理的数据在AS之后是要转换的数据类型。data_type:目标系统所提供的数据类型包括bigint和sql_variant不能使用用户定义的数据类 型。
可以转换的类型:
二进制同带binary前缀的效果 : BINARY字符型可带参数 : CHAR()日期 : DATE时间: TIME日期时间型 : DATETIME浮点数 : DECIMAL整数 : SIGNED无符号整数 : UNSIGNED
示例
select cast(9.0 AS decimal);
------------------------
| cast(9.0 AS decimal) |
------------------------
| 9 |
------------------------逻辑函数
IFNULL(expression, alt_value)
判断第一个表达式是否为 NULL如果为 NULL 则返回第二个参数的值如果不为 NULL 则返回第一个 参数的值。
IF(expr1,expr2,expr3)
如果expr1的值为true则返回expr2的值如果expr1的值为false则返回expr3的值。
CASE WHEN expr1 THEN expr2 [WHEN expr3 THEN expr4…ELSE expr] END
如果expr1的值为true则返回expr2的值如果expr3的值为false则返回expr4的值…
开窗函数
开窗函数是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数有的函数随着记录不同窗口大小都是固定的这种属于静态窗口;有的函数则相反不同的记录对应着不同的窗口这种动态变化的窗口叫滑动窗口。开窗函数的本质还是聚合运算只不过它更具灵活性它对数据的每一行都使用与该行相关的行进行计算并返回计算结果。
语法
开窗函数名([字段名]) over([partition by 分组字段] [order by 排序字段 [desc]] [ 窗口分区])开窗函数的一个概念是当前行当前行属于某个窗口窗口由over关键字用来指定函数执行的窗口范围如果后面括号中什么都不写则意味着窗口包含满足where条件的所有行开窗函数基于所有行进 行计算;如果不为空则有三个参数来设置窗口:
partition by 分组字段窗口按照哪些字段进行分组开窗函数在不同的分组上分别执行。order by 排序字段按照哪些字段进行排序开窗函数将按照排序后的记录顺序进行编号。可以和 partition by 分组字段配合使用也可以单独使用。 窗口分区它是排序之后的功能扩展标识在排序之后的一个范围
rows|range between start_expr and end_expr其中rows和range为二选其一
rows是物理范围即根据order by子句排序后取的前N行及后N行的数据计算与当前行的值无关只与排序后的行号相关range是逻辑范围根据order by子句排序后指定当前行对应值的范围取值行数不固定只要行值在范围内对应行都包含在内。between start_expr and end_expr来指定范围的起始点和终结点start_expr为起始点end_expr为终结点。
start_expr为起始点end_expr为终结点有下面几种选项
current row以当前行为起点。unbounded preceding指明窗口开始于分组的第一行以排序之后的第一行为起点。unbounded following以排序之后的最后一行为终点。n preceding以当前行的前面第n行为起点。n following以当前行的后面第n行为起点。
比如如下几个示例
rows between 1 preceding and 1 following 窗口范围是当前行、前一行、后一行一共三行记录。
rows unbounded preceding 窗口范围是分区中的第一行到当前行。
rows between unbounded preceding and unbounded following 窗口范围是当前分区中所有行 等同于不写。开窗函数和普通聚合函数的区别:
聚合函数是将多条记录聚合为一条;而开窗函数是每条记录都会执行有几条记录执行完还是几 条。聚合函数也可以用于开窗函数中
窗口函数及应用
不是所有的函数都支持开窗函数目前支持的窗口函数有
排名函数row_number()、rank()、dense_rank()
聚合函数sum()、avg()、count()、max()、min()
错行函数lead()、lag()
取值函数first_value()、last_value
分箱函数ntile()
数据准备1
create table test(id int,name varchar(10),sale int);
insert into test values(1,aaa,100);
insert into test values(1,bbb,200);
insert into test values(1,ccc,200);
insert into test values(1,ddd,300);
insert into test values(2,eee,400);
insert into test values(2,fff,200);数据准备2
create table test2( id int,val int);
insert into test2
values (1, 1),(1, 2),(1, 3),(1, 4),(1, 5),(2, 6),(2, 7),(2, 8),(2, 9),(1, 3),(1, 5);排名函数
row_number()未指定分组partition by子句则全部数据视为一个分组进行排序在分组内部进行排序组内的排序是连续且唯一的。rank()未指定分组partition by子句则全部数据视为一个分组进行排序在分组内部进行跳跃排序有相同的排名时有相同序号排序序号不连续。dense_rank()未指定分组partition by子句则全部数据视为一个分组进行排序在分组内部进行连续排序有相同排名时有相同序号但是排序序号连续。
应用示例
select t.id, t.name, t.sale, row_number() over (order by sale) as row_number1 -- 只有排序没有分组则全部数据视为一个分组进行排序, row_number() over (partition by id order by sale) as row_number2 -- 指定分组排序, rank() over (order by sale) as rank1, rank() over (partition by id order by sale) as rank2, dense_rank() over (order by sale) as dense_rank1, dense_rank() over (partition by id order by sale) as dense_rank2
from test as t;
------------------------------------------------------------------------------------
| id | name | sale | row_number1 | row_number2 | rank1 | rank2 | dense_rank1 | dense_rank2 |
------------------------------------------------------------------------------------
| 1 | aaa | 100 | 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | bbb | 200 | 2 | 2 | 2 | 2 | 2 | 2 |
| 1 | ccc | 200 | 3 | 3 | 2 | 2 | 2 | 2 |
| 1 | ddd | 300 | 5 | 4 | 5 | 4 | 3 | 3 |
| 2 | fff | 200 | 4 | 1 | 2 | 1 | 2 | 1 |
| 2 | eee | 400 | 6 | 2 | 6 | 2 | 4 | 2 |
------------------------------------------------------------------------------------rank1列中 sale200 有三个值分组排序后都一样但后面的排序序号不连续直接跳到了5rank2同理理解。
dense_rank1列中 sale200 有三个值分组排序后都一样但后面的排序序号是连续的dense_rank2同理理解。
想要深度了解排名可以参考Mysql 常见排名实现。
错行函数
lead()向下取值如果向下取值没有数据的时候显示为NULL。 lag()向上取值如果向上取值没有数据的时候显示为NULL。
lead(expr,offset,default) over(partition by col1 order by col2)
lag(expr,offset,default) over(partition by col1 order by col2)其中
expr通常是直接是列名也可以是从其他行返回的表达式offset是默认为1表示在当前分区内基于当前行的偏移行数default是在offset指定的偏移行数超出了分组的范围时(因为默认会返回null)可以通过设置这个字段来返回一个默认值来替代null。
应用示例
select t.id, t.name, t.sale, lead(sale) over (order by sale) as lead1 -- 只有排序没有分组则全部数据视为一个分组进行排序, lead(sale) over (partition by id order by sale) as lead2 -- 指定分组排序, lead(sale, 2, empty) over (order by sale) as lead3 -- 分组排序向下偏移2没有数据显示 empty, lag(sale) over (order by sale) as lag1, lag(sale) over (partition by id order by sale) as lag2, lag(sale, 2, empty) over (order by sale) as lag3
from test as t;
----------------------------------------------------------
| id | name | sale | lead1 | lead2 | lead3 | lag1 | lag2 | lag3 |
----------------------------------------------------------
| 1 | aaa | 100 | 200 | 200 | 200 | NULL | NULL | empty |
| 1 | bbb | 200 | 200 | 200 | 200 | 100 | 100 | empty |
| 1 | ccc | 200 | 200 | 300 | 300 | 200 | 200 | 100 |
| 1 | ddd | 300 | 400 | NULL | empty | 200 | 200 | 200 |
| 2 | fff | 200 | 300 | 400 | 400 | 200 | NULL | 200 |
| 2 | eee | 400 | NULL | NULL | empty | 300 | 200 | 200 |
----------------------------------------------------------lead函数与lag函数是两个偏移量函数主要用于查找当前行字段的上一个值或者下一个值可以设定偏移量和没有数据返回的默认值。
取值函数
first_value(expr) over(partition by col1 order by col2)
last_value(expr) over(partition by col1 order by col2)其中expr通常是直接是列名也可以是从其他行返回的表达式根据字段col1进行分组在分组内部根据字段col2进行排序first_value函数返回一组排序值后的第一个值last_value返回一组排序值后的最后一个值。
应用示例
select t.id, t.name, t.sale, first_value(sale) over () as first_value1 -- 没有分组和排序, first_value(sale) over (order by sale) as first_value2 -- 没有分组只有排序, first_value(sale) over (partition by id order by sale) as first_value3 -- 分组排序第一个值, last_value(sale) over () as last_value1, last_value(sale) over (order by sale) as last_value2, last_value(sale) over (partition by id order by sale) as last_value3
from test as t;
---------------------------------------------------------------------------------------------------
| id | name | sale | first_value1 | first_value2 | first_value3 | last_value1 | last_value2 | last_value3 |
---------------------------------------------------------------------------------------------------
| 1 | aaa | 100 | 100 | 100 | 100 | 400 | 100 | 100 |
| 1 | bbb | 200 | 100 | 100 | 100 | 400 | 200 | 200 |
| 1 | ccc | 200 | 100 | 100 | 100 | 400 | 200 | 200 |
| 1 | ddd | 300 | 100 | 100 | 100 | 400 | 300 | 300 |
| 2 | fff | 200 | 100 | 100 | 200 | 400 | 200 | 200 |
| 2 | eee | 400 | 100 | 100 | 200 | 400 | 400 | 400 |
---------------------------------------------------------------------------------------------------分箱函数
ntile(ntile_num) over(partition by col1 order by col2)ntile_num是一个整数用于创建“桶”的数量即分组的数量不能小于等于0。其次需要注意的是在over函数内尽量要有排序order by 子句。
应用示例
select t.id, t.name, t.sale, ntile(4) over (order by sale) as ntile1, ntile(4) over (partition by id order by sale) as ntile2
from test as t;
----------------------------------
| id | name | sale | ntile1 | ntile2 |
----------------------------------
| 1 | aaa | 100 | 1 | 1 |
| 1 | bbb | 200 | 1 | 2 |
| 1 | ccc | 200 | 2 | 3 |
| 1 | ddd | 300 | 3 | 4 |
| 2 | fff | 200 | 2 | 1 |
| 2 | eee | 400 | 4 | 2 |
----------------------------------聚合函数
sum(expr)只有分组没有排序显示分组的汇总值如果既有分组也有排序那么排序之后的开窗函数是默认排序之后第一行数据到当前行逻辑层面的汇总值。avg(expr)只有分组没有排序显示分组的平均值如果既有分组也有排序那么排序之后的开窗函数是默认排序之后第一行数据到当前行逻辑层面的平均值。count(expr)只有分组没有排序显示分组的计数如果既有分组也有排序那么排序之后的开窗函数是默认排序之后第一行数据到当前行逻辑层面的计数。max(expr)只有分组没有排序显示分组的最大值如果既有分组也有排序那么排序之后的开窗函数是默认排序之后第一行数据到当前行逻辑层面的最大值。min()只有分组没有排序显示分组的最小值如果既有分组也有排序那么排序之后的开窗函数是默认排序之后第一行数据到当前行逻辑层面的最小值。
应用示例
不使用 窗口分区下的应用
select id, val, max(val) over () as max1 -- 没有分组和排序, max(val) over (partition by id) as max2 -- 有分组没有排序, max(val) over (partition by id order by val) as max3 -- 既有分组也有排序, min(val) over () as min1, min(val) over (partition by id) as min2, min(val) over (partition by id order by val) as min3, sum(val) over () as sum1, sum(val) over (partition by id) as sum2, sum(val) over (partition by id order by val) as sum3, avg(val) over () as avg1, avg(val) over (partition by id) as avg2, avg(val) over (partition by id order by val) as avg3, count(val) over () as count1, count(val) over (partition by id) as count2, count(val) over (partition by id order by val) as count3
from test2;
------------------------------------------------------------------------------------------------------------------
| id | val | max1 | max2 | max3 | min1 | min2 | min3 | sum1 | sum2 | sum3 | avg1 | avg2 | avg3 | count1 | count2 | count3 |
------------------------------------------------------------------------------------------------------------------
| 1 | 1 | 9 | 5 | 1 | 1 | 1 | 1 | 53 | 23 | 1 | 4.8182 | 3.2857 | 1.0000 | 11 | 7 | 1 |
| 1 | 2 | 9 | 5 | 2 | 1 | 1 | 1 | 53 | 23 | 3 | 4.8182 | 3.2857 | 1.5000 | 11 | 7 | 2 |
| 1 | 3 | 9 | 5 | 3 | 1 | 1 | 1 | 53 | 23 | 9 | 4.8182 | 3.2857 | 2.2500 | 11 | 7 | 4 |
| 1 | 3 | 9 | 5 | 3 | 1 | 1 | 1 | 53 | 23 | 9 | 4.8182 | 3.2857 | 2.2500 | 11 | 7 | 4 |
| 1 | 4 | 9 | 5 | 4 | 1 | 1 | 1 | 53 | 23 | 13 | 4.8182 | 3.2857 | 2.6000 | 11 | 7 | 5 |
| 1 | 5 | 9 | 5 | 5 | 1 | 1 | 1 | 53 | 23 | 23 | 4.8182 | 3.2857 | 3.2857 | 11 | 7 | 7 |
| 1 | 5 | 9 | 5 | 5 | 1 | 1 | 1 | 53 | 23 | 23 | 4.8182 | 3.2857 | 3.2857 | 11 | 7 | 7 |
| 2 | 6 | 9 | 9 | 6 | 1 | 6 | 6 | 53 | 30 | 6 | 4.8182 | 7.5000 | 6.0000 | 11 | 4 | 1 |
| 2 | 7 | 9 | 9 | 7 | 1 | 6 | 6 | 53 | 30 | 13 | 4.8182 | 7.5000 | 6.5000 | 11 | 4 | 2 |
| 2 | 8 | 9 | 9 | 8 | 1 | 6 | 6 | 53 | 30 | 21 | 4.8182 | 7.5000 | 7.0000 | 11 | 4 | 3 |
| 2 | 9 | 9 | 9 | 9 | 1 | 6 | 6 | 53 | 30 | 30 | 4.8182 | 7.5000 | 7.5000 | 11 | 4 | 4 |
------------------------------------------------------------------------------------------------------------------max1 在没有分组和排序情况下取全部数据的最大值
max2 在有分组没有排序情况下取分组区间内数据的最大值
max3 在既有分组也有排序则去分组排序过程中的最大值即分组排序当前行与首行内数据的最大值
剩下的开窗聚合结果与max逻辑类似。
使用 窗口分区的应用
rows|range between start_expr and end_expr通过以下示例进一步理解
select id, val, sum(val) over (partition by id order by val) as sum1-- 不指定窗口分区相当于默认的规则即range between unbounded preceding and current row, sum(val) over (partition by id order by val rows between unbounded preceding and current row) as sum2 -- 指定窗口分区为rows物理行范围的第一行到当前行, sum(val) over (partition by id order by val range between unbounded preceding and current row) as sum3 -- 指定窗口分区为range范围的第一个数值到当前行对应的数值, sum(val) over (partition by id order by val rows between 1 preceding and 1 following) as sum4 -- 指定窗口分区为rows物理行的前一行到当前行的后一行, sum(val) over (partition by id order by val range between 1 preceding and 1 following) as sum5 -- 指定窗口分区为range范围的上一个数值到当前行对应的数值的下一个数值
from test2;Windows可以被定义和命名也可以写成以下方式使其整体看起来更加简洁。
select id, val, sum(val) over w1 as sum1-- 不指定窗口分区相当于默认的规则即range between unbounded preceding and current row, sum(val) over w2 as sum2 -- 指定窗口分区为rows物理行范围的第一行到当前行, sum(val) over w3 as sum3 -- 指定窗口分区为range范围的第一个数值到当前行对应的数值, sum(val) over w4 as sum4 -- 指定窗口分区为rows物理行的前一行到当前行的后一行, sum(val) over w5 as sum5 -- 指定窗口分区为range范围的上一个数值到当前行对应的数值的下一个数值
from test2window w1 as (partition by id order by val),w2 as (partition by id order by val rows between unbounded preceding and current row),w3 as (partition by id order by val range between unbounded preceding and current row),w4 as (partition by id order by val rows between 1 preceding and 1 following),w5 as (partition by id order by val range between 1 preceding and 1 following);
------------------------------------------
| id | val | sum1 | sum2 | sum3 | sum4 | sum5 |
------------------------------------------
| 1 | 1 | 1 | 1 | 1 | 3 | 3 |
| 1 | 2 | 3 | 3 | 3 | 6 | 9 |
| 1 | 3 | 9 | 6 | 9 | 8 | 12 |
| 1 | 3 | 9 | 9 | 9 | 10 | 12 |
| 1 | 4 | 13 | 13 | 13 | 12 | 20 |
| 1 | 5 | 23 | 18 | 23 | 14 | 14 |
| 1 | 5 | 23 | 23 | 23 | 10 | 14 |
| 2 | 6 | 6 | 6 | 6 | 13 | 13 |
| 2 | 7 | 13 | 13 | 13 | 21 | 21 |
| 2 | 8 | 21 | 21 | 21 | 24 | 24 |
| 2 | 9 | 30 | 30 | 30 | 17 | 17 |
------------------------------------------rows是物理范围只和排序之后的行号有关和当前行的数值无关。range是逻辑层面的范围逻辑范围意思是排序之后把具有相同的值看成同一行。
弄懂以上两点并且结合示例得到结果分析你就能清楚明白了。
总结
开窗函数是MySQL中强大的查询工具能对数据进行更灵活的处理和分析其中排名函数使用应用广泛面试中也常常考察建议大家多去自己实际操作一遍并且结合数据查询结果了解开窗函数的使用我相信你一定会豁然开朗希望本文能给你带来帮助喜欢的话就一键三连哦
