网站基本模板,厦门网站建设系统,搭建一个企业网站,湛江市出行防疫政策一、窗口函数的知识点
1.1 窗户函数的定义 窗口函数可以拆分为【窗口函数】。窗口函数官网指路#xff1a;
LanguageManual WindowingAndAnalytics - Apache Hive - Apache Software Foundationhttps://cwiki.apache.org/confluence/display/Hive/LanguageManual%20Windowin…一、窗口函数的知识点
1.1 窗户函数的定义 窗口函数可以拆分为【窗口函数】。窗口函数官网指路
LanguageManual WindowingAndAnalytics - Apache Hive - Apache Software Foundationhttps://cwiki.apache.org/confluence/display/Hive/LanguageManual%20WindowingAndAnalytics
窗口限定函数的计算范围窗口函数针对分组后的数据从逻辑角度指定计算的范围并没有从物理上真正的切分只有group by 是物理分组真正意义上的分组函数计算逻辑 窗口函数的位置跟sql里面聚合函数的位置一样from - join - on - where - group by-select 后面的普通字段窗口函数 - having - order by - lmit 。 窗口函数不能跟聚合函数同时出现。聚合函数包括count、sum、 min、max、avg。sql 执行顺序from - join - on - where - group by-select 后面的普通字段聚合函数- having - order by - limit
1.2 窗户函数的语法 窗口函数window_name over ( [partition by 字段...] [order by 字段...] [窗口子句] )
window_name给窗口指定一个别名。over用来指定函数执行的窗口范围如果后面括号中什么都不写即over() 意味着窗口包含满足where 条件的所有行窗口函数基于所有行进行计算。符号[] 代表可选项 | 代表二选一 partition by 子句 窗口按照哪些字段进行分组窗口函数在不同的分组上分别执行。分组间互相独立。order by 子句每个partition内部按照哪些字段进行排序如果没有partition 那就直接按照最大的窗口排序且默认是按照升序asc排列。窗口子句显示声明范围不写窗口子句的话会有默认值。常用的窗口子句如下 rows between unbounded preceding and unbounded following; -- 上无边界到下无边界一般用于求 总和rows between unbounded preceding and current row; --上无边界到当前记录(累计值)rows between 1 preceding and current row; --从上一行到当前行rows between 1 preceding and 1 following; --从上一行到下一行rows between current row and 1 following; --从当前行到下一行 ps: over()里面有order by子句但没有窗口子句时 即 窗口函数 over ( partition by 字段... order by 字段... )此时窗口子句是有默认值的---- rows between unbounded preceding and current row (上无边界到当前行)。 此时窗口函数语法窗口函数 over ( partition by 字段... order by 字段... ) 等价于 窗口函数 over ( partition by 字段... order by 字段... rows between unbounded preceding and current row) 需要注意有个特殊情况当order by 后面跟的某个字段是有重复行的时候 窗口函数 over ( partition by 字段... order by 字段... ) 不写窗口子句的情况下窗口子句的默认值是range between unbounded preceding and current row(上无边界到当前相同行的最后一行)。 因此遇到order by 后面跟的某个字段出现重复行且需要计算【上无边界到当前行】那就需要手动指定窗口子句 rows between unbounded preceding and current row 偷懒省略窗口子句会出问题~ ps: 窗口函数的执行顺序是在where之后所以如果where子句需要用窗口函数作为条件需要多一层查询在子查询外面进行。 【例如】求出登录记录出现间断的用户Id
selectid
from (selectid,login_date,lead(login_date, 1, 9999-12-31)over (partition by id order by login_date) next_login_date--窗口函数 lead(向后取n行)--lead(column1,n,default)over(partition by column2 order by column3) 查询当前行的后边第n行数据如果没有就为nullfrom (--用户在同一天可能登录多次需要去重selectid,date_format(date, yyyy-MM-dd) as login_datefrom user_loggroup by id, date_format(date, yyyy-MM-dd)) tmp1) tmp2
where datediff(next_login_date, login_date) 2
group by id;
1.3 窗口函数分类 哪些函数可以是窗口函数呢(放在over关键字前面的) 聚合函数
sum(column) over (partition by .. order by .. 窗口子句);
count(column) over (partition by .. order by .. 窗口子句);
max(column) over (partition by .. order by .. 窗口子句);
min(column) over (partition by .. order by .. 窗口子句);
avg(column) over (partition by .. order by .. 窗口子句); ps : 高级聚合函数 collect_list 收集并形成list集合结果不去重; collect_set 收集并形成set集合结果去重; 举例
--每个月的入职人数以及姓名select
month(replace(hiredate,/,-)),count(*) as cnt,collect_list(name) as name_list
from employee
group by month(replace(hiredate,/,-));/*
输出结果
month cn name_list
4 2 [宋青书,周芷若]
6 1 [黄蓉]
7 1 [郭靖]
8 2 [张无忌,杨过]
9 2 [赵敏,小龙女]
*/ 排序函数 row_number() 、rank()、dense_rank() 函数不支持自定义窗口子句。
-- 顺序排序——1、2、3
row_number() over(partition by .. order by .. )-- 并列排序跳过重复序号——1、1、3横向加
rank() over(partition by .. order by .. )-- 并列排序不跳过重复序号——1、1、2纵向加
dense_rank() over(partition by .. order by .. ) 前后函数 lag和lead函数不支持自定义窗口子句。
-- 取得column列的前n行如果存在则返回如果不存在返回默认值default
lag(column,n,default) over(partition by.. order by...) as lag_test
-- 取得column列的后n行如果存在则返回如果不存在返回默认值default
lead(column,n,default) over(partition by.. order by...) as lead_test 头尾函数
first_value(column,true) ---当前窗口column列的第一个数值如果有null值则跳过
first_value(column,false) ---当前窗口column列的第一个数值如果有null值不跳过
last_value(column,true) --- 当前窗口column列的最后一个数值如果有null值则跳过
last_value(column,false) --- 当前窗口column列的最后一个数值如果有null值,不跳过
1.4 排序函数 rank/dense_rank/row_number 函数一般用于求分组topN。
-- 顺序排序——1、2、3
row_number() over(partition by .. order by .. )-- 并列排序跳过重复序号——1、1、3横向加
rank() over(partition by .. order by .. )-- 并列排序不跳过重复序号——1、1、2纵向加
dense_rank() over(partition by .. order by .. )
二、实际案例
2.1 每个学生成绩第二高的科目
0 问题描述 根据学生成绩表求出每个学生成绩第二高的科目。
1 数据准备
create table if not exists table5
(class string comment 学科,student string comment 学生姓名,score int comment 成绩
)comment 学生成绩表;insert overwrite table table5 values
(a,吱吱1,100),
(a,吱吱2,60),
(b,吱吱1,80),
(b,吱吱2,70),
(c,吱吱2,50),
(c,吱吱3,90);
2 数据分析
3种排序函数的区别row_number 行号-- 1 2 3 ;rank 重复跳过--1 1 3;dense_rank 重复不跳过 --1 1 2
selectclass,student
from (selectclass,student,score,dense_rank() over (partition by student order by score desc) rnfrom table5) tmp1
where rn 2;
3 小结 排序函数在分组tpoN场景应用十分广泛需要注意的是在sql语句中窗口函数的执行顺序是在where过滤条件之后所以如果where子句需要用窗口函数作为条件需要多一层查询在子查询外面进行。
