开窗函数 count_MySQL 开窗函数

开窗函数 count_MySQL 开窗函数Hive SQL 大厂必考常用窗口函数及相关面试题全网最全大数据面试提升手册!目录一、窗口函数概述:1.窗口函数的分类2.窗口函数与普通聚合函数的区别:二、窗口函数的基本用法1.基本语法2.设置窗口的方法1)

Hive SQL 大厂必考常用窗口函数及相关面试题   
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数全网最全大数据面试提升手册!目录   一、窗口函数概述:1.窗口函数的分类2.窗口函数与普通聚合函数的区别:   二、窗口函数的基本用法1.基本语法2.设置窗口的方法1)window_name2)partition by 子句3) order by子句4)rows 指定窗口大小3.开窗函数中加order by 和 不加 order by的区别   三、窗口函数用法举例1.序号函数:row_number() / rank() / dense_rank()(面试重点)2.分布函数:percent_rank() / cume_dist()3.前后函数 lag(expr,n,defval)、lead(expr,n,defval)(面试重点)4.头尾函数:FIRST_VALUE(expr),LAST_VALUE(expr)5.聚合函数+窗口函数联合使用   四、面试题1.用户行为分析2.学生成绩分析   一、窗口函数概述:   窗口函数也称为OLAP函数,OLAP 是OnLine Analytical Processing 的简称,意思是对数据库数据进行实时分析处理。例如,市场分析、创建财务报表、创建计划等日常性商务工作。窗口函数就是为了实现OLAP 而添加的标准SQL 功能。1. 窗口函数的分类   按照功能划分:   序号函数:row_number() / rank() / dense_rank()   分布函数:percent_rank() / cume_dist()   前后函数:lag() / lead()   头尾函数:first_val() / last_val()   聚合函数+窗口函数联合:   求和 sum() over()   求最大/小 max()/min() over()   求平均 avg() over()   其他函数:nth_value() / nfile()   如上,窗口函数的用法多种多样,不仅有专门的的窗口函数,还可以与聚合函数配合使用。2. 窗口函数与普通聚合函数的区别:   聚合函数是将多条记录聚合为一条;窗口函数是每条记录都会执行,有几条记录执行完还是几条。   窗口函数兼具GROUP BY 子句的分组功能以及ORDER BY 子句的排序功能。但是,PARTITION BY 子句并不具备 GROUP BY 子句的汇总功能。   举例:若原表中有id一样的10行数据,使用GROUP BY,返回的结果是将多条记录聚合成一条;而使用 rank() 等窗口函数并不会减少原表中 记录的行数,结果中仍然包含 10 行数据。   窗口函数兼具分组和排序两种功能。   二、窗口函数的基本用法   如有基础数据:
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数1. 基本语法   其中:   <窗口函数>:指需要使用的分析函数,如row_number()、sum()等。   over() : 用来指定函数执行的窗口范围,这个数据窗口大小可能会随着行的变化而变化;   如果括号中什么都不写,则意味着窗口包含满足WHERE条件的所有行,窗口函数基于所有行进行计算。如:   结果:
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   sum(score) over() as sum_score 会聚合所有的数据,将结果接到每一行的后面(窗口函数不会改变结果原表行数)。2. 设置窗口的方法   如果不为空,则支持以下4中语法来设置窗口。   1)window_name   给窗口指定一个别名。如果SQL中涉及的窗口较多,采用别名可以看起来更清晰易读,如:   2)partition by 子句   窗口按照哪些字段进行分组,窗口函数在不同的分组上分别执行,如:   实例1:   结果:
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   sum(score) OVER(PARTITION BY uid) AS sum_score 会按照 uid 分组,分别求和,展示在每个分组的末尾。   如果我想看某个uid有多少行记录,并标明序号该如何实现?使用序号函数row_number()请看:
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   可以看到,row_number()按照uid分组并从上到下按照顺序标号。我们看到1004中的score是无序的,如果想按照score降序排名应该怎么做呢?(实际场景:成绩排名)   可以结合 order by 子句实现   3)order by子句   按照哪些字段进行排序,窗口函数将按照排序后的记录顺序进行编号,如:
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   这样就实现了每个uid内的分数降序排名,order by 后面可以跟多个列名,大家可以试一试。   当order by 与聚合类函数连用时,特别需要注意理解,如下面几个例子:   先看前面的例子,单独使用 partition by uid   结果:
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   单独使用order by uid   结果:
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   注意观察uid 从1004->1005的变化,两条SQL的结果有什么不同:   partition by 按照uid分组,分别对score求和,”接到每一行的末尾“   分组内求和,分组间相互独立。   order by 按照uid排序,对”序号“相同的素进行求和,不同”序号“的数累加求和   如果将”序号“认为是分组的话,个人理解这是一个分组求和并累加的过程   即分组内求和,分组间累加。   再看,order by score 的例子   结果:
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   总结一下:   如果使⽤环境为hive,over()开窗函数前分排序函数和聚合函数两种。   当为排序函数,如row_number(),rank()等时,over中的order by只起到窗⼝内排序作⽤。   当为聚合函数,如max,min,count等时,over中的order by不仅起到窗⼝内排序,还起到窗⼝内从当前⾏到之前所有⾏的聚合(多了⼀个范围)。   4)rows 指定窗口大小   a.先看个例子,按照顺序,求score的平均值:   注意score相同的部分:
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   如果想要sql先按照score降序排列,每一行计算从第一行到当前行的score平均值,该怎么计算呢?——想办法做一个不重复的key   实现:
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   现在改下需求,希望求”我与前两名的平均值”应该怎么实现呢?   分析一下,”我与前两名”指的是当前行以及前两行总共三行数据求平均,也就是说,我们需要限定窗口的范围或者窗口大小。   b.引入窗口框架   指定窗口大小,又称为窗口框架。框架是对窗口进行进一步分区,框架有两种范围限定方式:   一种是使用 ROWS 子句,通过指定当前行之前或之后的固定数目的行来限制分区中的行数。   另一种是使用 RANGE 子句,按照排列序列的当前值,根据相同值来确定分区中的行数。   语法:   RANGE | ROWS的区别是什么?   RANGE表示按照值的范围进行范围的定义,而ROWS表示按照行的范围进行范围的定义;边界规则的可取值见下表:
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   需要注意:   当使用框架时,必须要有 order by 子句,如果仅指定了order by 子句而未指定框架,那么默认框架将采用 RANGE UNBOUNDED PRECEDING AND CURRENT ROW(表示当前行以及一直到第一行的数据)。   如果窗口函数没有指定 order by 子句,也就不存在 ROWS/RANGE 窗口的计算。   PS: RANGE 只支持使用 UNBOUNDED 和 CURRENT ROW 窗口框架分隔符。   OK,回到前面的需求:求”我与前两名的平均值”。因为要”我与前两名”,所以我们会用到规则 2 PRECEDING
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   如果要求当前行及前后一行呢?   结果略~   其他组合举例:3. 开窗函数中加order by 和 不加 order by的区别   如果使⽤环境为hive,over()开窗函数前分排序函数和聚合函数两种。   当为排序函数,如row_number(),rank()等时,over中的order by只起到窗⼝内排序作⽤。   当为聚合函数,如max,min,count等时,over中的order by不仅起到窗⼝内排序,还起到窗⼝内从当前⾏到之前所有⾏的聚合(多了⼀个范围)。   如:   上⾯①②中的min_salary字段的值会不⼀样,原因是②中使⽤order by后,等同于 min(salary) over(partition by dept order by userid range between unbounded preceding and current row ),当然可以在order by后使⽤框架⼦句,即rows,range等,如果没有写框架⼦句,就默认在窗⼝范围中当前⾏到之前所有⾏的数据进⾏统计。   再看个例子:   得到结果:
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   需要注意表中标注的部分   三、窗口函数用法举例   1. 序号函数:row_number() / rank() / dense_rank()(面试重点)   三者区别:   RANK(): 并列排序,跳过重复序号——1、1、3   ROW_NUMBER(): 顺序排序——1、2、3   DENSE_RANK(): 并列排序,不跳过重复序号——1、1、2
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   我们来探索一下,如果不使用窗口函数,如何实现分数排序呢?(使用自连接的方法)
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   这里..,如果想要7改为5呢,不跳过位次。相当于DENSE_RANK 函数。   只需要改 COUNT(P2.score) 为 COUNT(distinct P2.score) 即可。2. 分布函数:percent_rank() / cume_dist()   1)percent_rank():   percent_rank()函数将某个数值在数据集中的排位作为数据集的百分比值返回,此处的百分比值的范围为 0 到 1。此函数可用于计算值在数据集内的相对位置。如班级成绩为例,返回的百分数30%表示某个分数排在班级总分排名的前30%。   每行按照公式(rank-1) / (rows-1)进行计算。其中,rank为RANK()函数产生的序号,rows为当前窗口的记录总行数。
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   2)cume_dist():   如果按升序排列,则统计:小于等于当前值的行数/总行数。   如果是降序排列,则统计:大于等于当前值的行数/总行数。   如:查询小于等于当前成绩(score)的比例。
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数3. 前后函数 lag(expr,n,defval)、lead(expr,n,defval)(面试重点)   Lag()和Lead()分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。   在实际应用当中,若要用到取今天和昨天的某字段差值时,Lag()和Lead()函数的应用就显得尤为重要。当然,这种操作可以用表的自连接实现,但是LAG()和LEAD()与left join、rightjoin等自连接相比,效率更高,SQL更简洁。下面我就对这两个函数做一个简单的介绍。   函数语法如下:   其中   exp_str 是字段名   Offset 是偏移量,即是上1个或上N个的值,假设当前行在表中排在第5行,offset 为3,则表示我们所要找的数据行就是表中的第2行(即5-3=2)。   Defval 默认值,当两个函数取 上N 或者 下N 个值,当在表中从当前行位置向前数N行已经超出了表的范围时,lag() 函数将defval这个参数值作为函数的返回值,若没有指定默认值,则返回NULL,那么在数学运算中,总要给一个默认值才不会出错。   用途:   返回位于当前行的前n行的expr的值:LAG(expr,n)   返回位于当前行的后n行的expr的值:LEAD(expr,n)   举例:查询前1名同学及后一名同学的成绩和当前同学成绩的差值(只排分数,不按uid分组)   先将前一名和后一名的分数与当前行的分数放在一起:
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   再做diff:
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数4. 头尾函数:FIRST_VALUE(expr),LAST_VALUE(expr)   用途:   返回第一个expr的值:FIRST_VALUE(expr)   返回最后一个expr的值:LAST_VALUE(expr)   应用场景:截止到当前成绩,按照分数排序查询第1个和最后1个的分数
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数5. 聚合函数+窗口函数联合使用   聚合函数也可以用于窗口函数。   原因就在于窗口函数的执行顺序(逻辑上的)是在FROM,JOIN,WHERE,GROUP BY,HAVING之后,在ORDER BY,LIMIT,SELECT DISTINCT之前。它执行时GROUP BY的聚合过程已经完成了,所以不会再产生数据聚合。   注:窗口函数是在where之后执行的,所以如果where子句需要用窗口函数作为条件,需要多一层查询,在子查询外面进行   前面基本用法中已经有部分举例,如:
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   四、面试题   1. 用户行为分析   表1:用户行为表tracking_log,大概字段有(user_id‘用户编号’,opr_id‘操作编号’,log_time‘操作时间’)如下所示:
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   问题:   1)统计每天符合以下条件的用户数:A操作之后是B操作,AB操作必须相邻   分析:(1)统计每天,所以需要按天分组统计求和(2)A操作之后是B,且AB操作必须相邻,那就涉及一个前后问题,所以想到用窗口函数中的lag()或lead()。   2)统计用户行为序列为A-B-D的用户数,其中:A-B之间可以有任何其他浏览记录(如C,E等),B-D之间除了C记录可以有任何其他浏览记录(如A,E等)2. 学生成绩分析   表:Enrollments (student_id, course_id) 是该表的主键。
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   1)查询每位学生获得的最高成绩和它所对应的科目,若科目成绩并列,取 course_id 最小的一门。查询结果需按 student_id 增序进行排序。   分析:因为需要最高成绩和所对应的科目,所以可采用窗口函数排序分组取第一个   解法2:IN 解法   取成绩在最大成绩之中的学生的最小课程号的课程   2)查询每一科目成绩最高和最低分数的学生,输出course_id,student_id,score   我们可以按科目查找成绩最高的同学和最低分的同学,然后利用union连接起来   解法2:case-when   如果这个文章对你有帮助,不要忘记「在看」「点赞」「收藏」三连啊喂!   
开窗函数 count_MySQL 开窗函数
开窗函数 count_MySQL 开窗函数   2022年全网首发|大数据专家级技能模型与学习指南(胜天半子篇)   互联网最坏的时代可能真的来了   我在B站读大学,大数据专业   我们在学习Flink的时候,到底在学习什么?   193篇文章暴揍Flink,这个合集你需要一下   Flink生产环境TOP难题与优化,阿里巴巴藏经阁YYDS   Flink CDC我吃定了耶稣也留不住他!| Flink CDC线上问题小盘点   我们在学习Spark的时候,到底在学习什么?   在所有Spark模块中,我愿称SparkSQL为最强!   硬刚Hive | 4万字基础调优面试小总结   数据治理方法论和实践小百科全书   标签体系下的用户画像建设小指南   4万字长文 | ClickHouse基础&实践&调优全视角解析   【面试&个人成长】2021年过半,社招和校招的经验之谈   大数据方向另一个十年开启 |《硬刚系列》第一版完结   我写过的关于成长/面试/职场进阶的文章   当我们在学习Hive的时候在学习什么?「硬刚Hive续集」

2024最新激活全家桶教程,稳定运行到2099年,请移步至置顶文章:https://sigusoft.com/99576.html

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请联系我们举报,一经查实,本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处:https://sigusoft.com/67258.html

(0)
上一篇 2024年 8月 8日
下一篇 2024年 8月 8日

相关推荐

关注微信