国外网站模版,百度seo权重,网址查询地址查询,天津建设网站培训最近在搞一些面试和课程答辩的时候#xff0c;问什么是窗口函数#xff0c;知道哪些窗口函数?最多的答案就是row_number、rank、dense_rank#xff0c;在问一下还有其他的吗#xff1f;这时同学就蒙了,还有其他的窗口函数#xff1f;其实上面的回答也只是专用窗口函数问什么是窗口函数知道哪些窗口函数?最多的答案就是row_number、rank、dense_rank在问一下还有其他的吗这时同学就蒙了,还有其他的窗口函数其实上面的回答也只是专用窗口函数并不是窗口函数的整体定义那今天我们就来好好聊聊窗口函数。
1.窗口函数概念
我们首先来谈谈什么是窗口函数窗口函数是指在指定的数据滑动窗口中实现各种统计分析的操作。窗口函数是与分析函数一起使用或按照专用窗口函数使用组成比如窗口聚合函数、窗口排序函数等实用函数。
常用的分析函数sum()、max()、min()、avg()、count()、......
专用窗口函数row_number()、rank()、dense_rank()......
具体语法
这个很重要只要满足这个语法的都算窗口函数具体使用语法如下
分析函数/专用窗口函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)
窗口函数的3个组成部分可以单独使用也可以混合使用也可以全都不用下面是三部分的详细解释。
1.partition by 字段
是对指定的字段进行分组后续都会以组为单位把每个分组单独作为一个窗口进行统计分析操作。划分的范围被称为窗口这也是窗口函数的由来。
# 案例 01对窗口中的数据求和并把求和结果分别分发到对应窗口的每一条数据中with temp as(
select A as col1,1 as col2
union all
select A as col1,1 as col2
union all
select B as col1,1 as col2
)select
col1
,sum(col2) over(partition by col1) as 对窗口中的数据求和
from temp
输出结果
col 对窗口中的数据求和
A 2
A 2
B 1
案例 02对整体数据求和并把求和结果分发到每一条数据中with temp as(
select A as col1,1 as col2
union all
select A as col1,1 as col2
union all
select B as col1,1 as col2
)select
col1
,sum(col2) over() as 对整体数据求和
from temp
输出结果
col 对整体数据求和
A 3
A 3
B 3
注意聚合函数是将多条记录聚合为一条窗口函数是每条记录都会执行有几条记录执行完还是几条。窗口函数兼具GROUP BY 子句的分组功能以及ORDER BY 子句的排序功能。但是PARTITION BY 子句并不具备 GROUP BY 子句的汇总功能。
2.order by 字段
大家都知道order by 是排序字段(这里多说一句四个不要的区别理解了吗)它用在窗口函数里会有不一样的效果。
情景一order by 与 partition by 连用的时候可以对各个分组内的数据按照指定的字段进行排序。如果没有 partition by 指定分组字段那么会对全局的数据进行排序。
with temp as(
select A as col1,1 as col2
union all
select C as col1,1 as col2
union all
select B as col1,1 as col2
)select col1,row_number() over(order by col1 desc) as 排序 from temp
输出结果
col1 排序
C 1
C 2
B 3
A 4 情景二当为聚合函数如maxmincount等时over中的order by不仅起到窗⼝内排序还起到窗⼝内从当前⾏到之前所有⾏的聚合多了⼀个范围。
案例 01对数据进行全局排序with temp_01 as(
select A as user_id,1 as cnt
union all
select D as user_id,2 as cntunion all
select D as user_id,3 as cnt
union all
select B as user_id,4 as cnt
union all
select B as user_id,5 as cnt
)
select user_id,sum(cnt) over(partition by user_id) as sum_all from temp_01
select user_id,sum(cnt) over(partition by user_id order by cnt) as sum_all from temp_01 情景三当排序的维度不存在重复的情况下即 order by 指定的字段使用 order by 分析函数 sum()可以产生求整体累计数的效果。但是当 order by 指定的字段组合数据存在重复的时候会在不重复的数据中产生累计效果而重复的数据中也是会把整体的累计结果分发到每条重复的数据中。
with temp_01 as(
select A as user_id,1 as cnt
union all
select D as user_id,2 as cntunion all
select D as user_id,3 as cnt
union all
select B as user_id,4 as cnt
union all
select B as user_id,4 as cnt
)
select user_id,sum(cnt) over(partition by user_id order by cnt) as sum_all from temp_01 3.rows between 开始位置 and 结束位置
rows between 是指划分窗口中函数具体的作用数据范围。rows between 常用的参数如下
n preceding往前
n following往后
current row当前行
unbounded起点一般结合precedingfollowing使用###########
unbounded preceding表示该窗口最前面的行起点
unbounded following表示该窗口最后面的行终点
这些参数需要好好记忆使用例子如下
1.rows between unbounded preceding and current row表示从起点到当前行的数据进行
2.rows between current row and unbounded following表示当前行到终点的数据进行
3.rows between unbounded preceding and unbounded following 表示起点到终点的数据
rows between unbounded preceding and current row与 partition by 、order by 连用可以产生对窗口中的数据求累计数的效果。
with temp_01 as(
select D as user_id,2 as cntunion all
select D as user_id,2 as cnt
union all
select B as user_id,4 as cnt
union all
select B as user_id,5 as cntunion allselect A as user_id,1 as cnt
)
select user_id,cnt,sum(cnt) over(partition by user_id order by cnt rows between unbounded preceding and current row) as sum_all from temp_01 2.窗口函数分类
说过了什么是窗口函数明白什么是窗口函数所以以后面试过程中问到什么是窗口函数不要在简单的说排序啦接下来我们在谈谈具体有哪些函数。
2.1 排序窗口函数
这个就是大家最熟悉或者也只能回答出来的函数了
排序并产生自增编号自增编号不重复且连续我们可以使用函数row_number() 、over()。
排序并产生自增编号自增编号会重复且不连续我们可以使用函数rank() 、over()。
排序并产生自增编号自增编号会重复且连续我们可以使用函数dense_rank() 、over()。 2.2 聚合窗口函数
聚合函数配置over形成的窗口函数可以在是我们实际工作中用到累计窗口中平均值、窗口中最大值最小值等的场景。
求窗口中的累计值
我们可以使用sum() over() 求窗口中 3 天的平均价格
我们可以使用 avg() over() 输出结果 求分组中的最大值/最小值
max() over() as 窗口中的最大值
min() over() as 窗口中的最小值
求分组中的总记录数
我们可以使用 count() over()
举了两个简单的例子可以参考例子更容易让大家理解。
with temp_01 as(
select A as col1,10 as col2
union all
select C as col1,10 as col2
union all
select C as col1,20 as col2
union all
select A as col1,20 as col2
union all
select A as col1,20 as col2
)
select
col1
,col2
,max(col2) over(partition by col1) as 窗口中的最大值
,min(col2) over(partition by col1) as 窗口中的最小值
from temp_01
结果
输出结果
col1 col2 窗口中的最大值 窗口中的最小值
A 10 20 10
A 20 20 10
A 20 20 10
C 10 20 10
C 20 20 10
2.3 位移窗口函数
获取分组中往前 n 行的值
基础语法lead(field,n,default_value) over()
获取分组中往后 n 行的值
基础语法lag(field,n, default_value) over() 2.4 极值窗口函数
获取分组内第一行的值
我们可以使用first_value(col,true/false) over()作用是取分组内排序后截止到当前行第一个值。
注意
1.当第二个参数为 true 的时候会跳过空值
2.当 over() 中不指定排序的时候会默认使用表中数据的原排序。
获取分组内最后一行的值
我们可以使用last_value(col,true/false) over()作用是取分组内排序后截止到当前行最后一个值。所以如果使用 order by 排序的时候想要取最后一个值需要与 rows between unbounded preceding and unbounded following 连用。
注意
1.当第二个参数为 true 的时候会跳过空值
2.当 over() 中不指定排序的时候会默认使用表中数据的原排序。
3.当 over() 中指定排序的时候要与 rows between unbounded preceding and unbounded following 连用。 2.5 分箱窗口函数
ntile() over() 分箱窗口函数多用于统计百分比用于将分组数据按照顺序切分成 n 片返回当前切片值如果切片不均匀默认增加到第一个切片中。
案例查询考试成绩前 20% 的人。 输出结果 相信介绍到这里我们对于什么是窗口函数有哪些窗口函数都有了一个全面的认识了面试中我们就按照这样的分类一一介绍来打动我们的面试官。
含泪整理的超全窗口函数数据开发必备
