SQL助你面大厂(窗口函数)

这篇具有很好参考价值的文章主要介绍了SQL助你面大厂(窗口函数)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

       在面试过程中窗口函数的应用可谓是数不胜数,前提你要知道什么是窗口函数,最常用的窗口函数有哪些?语法是什么?分别用的场景是什么?今天会以这三个问题开始我们今天的学习

什么是窗口函数?

       所谓的窗口函数就是动态处理数据的一类函数,也叫做“OLAP”函数,可以对数据库数据进行实时分析处理,“窗口函数”由两部分组成:分析函数+窗口函数

--模板
函数名(字段名) over(partition by xxx,yyy order by zzz)

--详细介绍
分析函数 over (partition by "要分组的列")
              order by "要排序的列"
              rows between "开始的行" and "结束的行")

       over()部分才是“窗口函数”,只不过因为它的特点大家都这样叫,其作用就是划定窗口,分析函数的作用是在窗口内对记录进行统计

over函数中有3个参数

  • partition by

       其表示的是分区或者分组,可以理解成为group by,但是低位有所不同,如果partition by 和group by 后的字段相同,partition by 就会失效,因为group by先分组(划分窗口),如果字段相同则partition by 就没有可分的了

如果partition函数想要做到像group by一样的效果,必须要和distinct关键字进行去重

  • order by

       表示排序,排序、累加都是有排序的操作在里面,根据设么条件排名、根据什么条件进行累加,order by后面可以跟一个字段也可以跟多个字段,位置优先,越在前面排序的优先级越高,默认“asc(升序)” 小-大,desc(降序)大-小

  • rows between "开始的行" and "结束的行"

这部分是控制窗口大小的地方,一般默认就行,也不会进行书写,但是也可以自定义窗口大小:

proceding:往前
following:往后
unbounded:无穷尽的
current row:当前行
unbounded proceding:往前所有行
unbounded following:往后所有行

 举几个例子方便理解:

rows between 2 preceding and current row         --取当前行和前2行,这个可用于求移动平均 
rows between current row and 2 following         --取当前行和后2行,这个可用于求移动平均
rows between unbounded preceding and current row --取当前行和之前所有行,这个可用于求累加;如果没有写rows between参数,则默认的窗口就是这个范围
rows between current row and unbounded following --取当前行和之后所有行
rows between 2 preceding and 2 following         --取当前行的前面2行和下面2行,总共5行,这个可用于求移动平均

动态窗口的动态演示图:

SQL助你面大厂(窗口函数),SQL突击,sql,数据库

 接下来我们来讲一般常用的三种窗口函数:

我们现在有一个班级的成绩数据,要求按规定进行查询:

CREATE TABLE class(
cid VARCHAR(10),
NAME VARCHAR(20),
grades INT
)
DROP TABLE class

INSERT INTO class (cid,NAME,grades) VALUES('1','路飞',60),('1','鸣人',70),('1','佐助',75),('1','萧炎',80),('1','唐三',90),('1','奕星',91),('2','霸天虎',50),('2','玉环',60),('2','索隆',66),('2','墨子',85),('2','铁甲小宝',89)

SQL助你面大厂(窗口函数),SQL突击,sql,数据库

       假如动漫学院期末成绩出来了,老师让你帮根据一定的规则去查询成绩,你可以帮忙完成这个工作么?

  • 聚合类的窗口函数 sum() over()(举例)       count\avg\max\min

情境一:现在要求让每位同学后面都新增一列各自班级的总分

SELECT*,
  SUM(grades) OVER (PARTITION BY cid) AS "班级总分"
FROM class

SQL助你面大厂(窗口函数),SQL突击,sql,数据库

 情境二:计算同一个班级内,每个同学和比他分数低的总分是多少?

SELECT*,
  SUM(grades) OVER (PARTITION BY cid ORDER BY grades) AS "累加分数"
FROM class

SQL助你面大厂(窗口函数),SQL突击,sql,数据库

SELECT*,
  SUM(grades) OVER (PARTITION BY cid ORDER BY grades ROWS BETWEEN unbounded preceding AND current ROW ) AS "累加分数"
FROM class

 这条SQL语句的效果和上面那条SQL语句效果一致,不写的话默认就是这个范围

SQL助你面大厂(窗口函数),SQL突击,sql,数据库

  • 排序类的窗口函数

    SQL助你面大厂(窗口函数),SQL突击,sql,数据库

  1. row_number(顺序,相同的不做特别处理)

    假如现在让你将1、2班的成绩排个名,从低到高,你有没有什么好的办法?|
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY cid ORDER BY grades DESC) AS "成绩排名"
    FROM class;

    SQL助你面大厂(窗口函数),SQL突击,sql,数据库

  2. rank(相同的做特别处理,会进行占位)

           在拍完名之后,发现相同成绩的同学居然排名不一样,这又让老师犯难了,怎么样才能使得相同成绩的同学拥有相同的排名
    SELECT *,
      RANK() OVER (PARTITION BY cid ORDER BY grades DESC) AS "成绩排名"
    FROM class;

    SQL助你面大厂(窗口函数),SQL突击,sql,数据库

  3. dense_rank(相同的做特殊处理,不占位)

           但是又新出现了一个问题,相同的成绩的排名确实排名也相同了,但是后面的那个同学居然是从3开始的,自动的将2跳过了,这也是我们不希望看到的
     
    SELECT *,
      DENSE_RANK() OVER (PARTITION BY cid ORDER BY grades DESC) AS "成绩排名"
    FROM class;

    SQL助你面大厂(窗口函数),SQL突击,sql,数据库

  • 偏移类的、跨行的窗口函数
  1. lag(后面)

    如果现在又有一个任务,让每位同学知道比自己低1(N)名的同学的成绩
     
    SELECT*,
    LAG(grades,1) OVER (PARTITION BY cid ORDER BY grades) AS "低一名的成绩"
    FROM class

    SQL助你面大厂(窗口函数),SQL突击,sql,数据库

    但是最后的结果中却出现了null值,我们想如果查不到数据结果置为0,该怎么去实现呢?
    LAG(字段,差值,默认值)
     
    SELECT*,
    LAG(grades,1,0) OVER (PARTITION BY cid ORDER BY grades) AS "低一名的成绩"
    FROM class

    SQL助你面大厂(窗口函数),SQL突击,sql,数据库

  2. 2.lead(前面)

 如果现在又有一个任务,让每位同学知道比自己高1(N)名的同学的成绩


SELECT*,
LEAD(grades,1,0) OVER (PARTITION BY cid ORDER BY grades) AS "高一名的成绩"
FROM class

SQL助你面大厂(窗口函数),SQL突击,sql,数据库

 最常用的窗口函数已经介绍完了,学习SQL的路上一路有我!!!文章来源地址https://www.toymoban.com/news/detail-662367.html

到了这里,关于SQL助你面大厂(窗口函数)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 【SQL】MySQL中的窗口函数(开窗函数)

    窗口函数是 MYSQL8.0 新增的 聚合函数: 多行变一行,常见的sum,count,max,min 窗口函数: 行数不变,常见的row_number,rank 语法格式: 窗口函数(表达式) over (partition by … order by … frame_clause) partition by是分区,类似于group by,如去掉相当于对所有数据进行计算 order by排序 frame_c

    2024年02月07日
    浏览(47)
  • SQL窗口分析函数使用详解系列三之偏移量类窗口函数

    本文以HiveSQL语法进行代码演示。 对于其他数据库来说同样也适用,比如SparkSQL,FlinkSQL以及Mysql8,Oracle,SqlServer等传统的关系型数据库。 已更新第一类聚合函数类,点击这里阅读 ①SQL窗口函数系列一之聚合函数类 ②SQL窗口函数系列二之分组排序窗口函数 本节介绍Hive窗口分

    2024年04月26日
    浏览(40)
  • 【sql高级】postgresql之窗口函数用法

    窗口函数在在SQL中是非常有用的工具,特别是在需要对查询结果进行分析、排名、聚合或者对结果进行一些特定的计算时。以下是一些常见的场景: 排名和分组:窗口函数可以轻松地对结果集进行排名、分组和分区。例如,你可以使用 ROW_NUMBER()、RANK()、DENSE_RANK() 等函数来为

    2024年03月23日
    浏览(44)
  • 【SQL篇】窗口函数和公共表达式

    思路 先给每个数进行排名 用这些数减去自己的排名,如果减了之后的结果是一样的,说明这几个数是连续的 用logid减去排名得出来的数进行group by,也就是把连续的数全都放在一个一个小组里面,求出每个小组的最大值和最小值就可以了 1596题 总结 这里有个坑,count并不会根

    2024年02月02日
    浏览(39)
  • 选读SQL经典实例笔记15_窗口函数

    2.2.1.1. 分组不为空 2.2.1.1.1. 一个分组至少要拥有一个成员(行 2.2.1.1.2. 无法从一个空表中生成任何分组 2.2.1.2. 分组具有唯一性 2.2.1.2.1. 如果查询语句使用了GROUP BY子句,那么通常而言SELECT列表里就不再需要使用DISTINCT了 2.2.2.1. COUNT永远大于0 2.2.2.1.1. 无法从一个空表

    2024年02月14日
    浏览(38)
  • [SQL挖掘机] - 窗口函数 - 计算移动平均

    在窗口函数使用时,计算的是累积到当前行的所有的数据的相关操作。 实际上,还可以指定更加详细的汇总范围。该汇总范围称为 框架 (frame)。 其实这里也可以理解成一个 窗口 , 这个 窗口 是我们可以进行设置的. 之前我们介绍的窗口函数是根据 partition by 进行分组, 然后根

    2024年02月14日
    浏览(36)
  • Spark_SQL函数定义(定义UDF函数、使用窗口函数)

                        一、UDF函数定义         (1)函数定义         (2)Spark支持定义函数         (3)定义UDF函数                 (4)定义返回Array类型的UDF         (5)定义返回字典类型的UDF 二、窗口函数         (1)开窗函数简述         (2)

    2024年02月03日
    浏览(47)
  • mysql 8.0 窗口函数 之 序号函数 与 sql server 序号函数 一样

    sql server 序号函数 序号函数 ROW_NUMBER() 顺序排序 RANK() 并列排序,会跳过重复的序号,比如序号为1,1,3 DENSE_RANK() 并列排序,不会跳过重复的序号,比如 序号为 1,1,2 语法结构 窗口函数的优点 使用窗口函数,只用了一步就完成了查询,而且,由于没有用到临时表,执行的

    2024年02月11日
    浏览(39)
  • mysql 8.0 窗口函数 之 分布函数 与 sql server (2017以后支持) 分布函数 一样

    mysql 分布函数 percent_rank() :等级值 百分比 cume_dist() :累积分布值 percent_rank() 计算方式 (rank-1)/(rows-1), 其中 rank 的值为使用RANK()函数产生的序号,rows 的值为当前窗口的总记录数。

    2024年02月11日
    浏览(42)
  • SQL删除重复的记录(只保留一条)-窗口函数row_number()

    关于删除mysql表中重复数据问题,本文中给到两种办法:聚合函数、窗口函数row_number()的方法。 (注意:MySQL从8.0开始支持窗口函数) 测试数据准备:首先创建一个测试表test,插入一些测试数据,模拟一些重复数据(最终目标:删除重复数据,但不处理null行) 先查询下重复数

    2024年02月05日
    浏览(46)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包