精华内容
下载资源
问答
  • SQL 窗口函数

    2021-02-03 10:01:17
    文章目录SQL 窗口函数 SQL 窗口函数

    背景

    翻完《SQL必知必会》这本书之后, 去刷了一下SQL相关的题目. 发现其中一类题需要涉及到分组之后组内排序(如选出每组某个字段排序后前10的条目)这样的操作, 于是去查阅了相关的资料, 发现基本上使用窗口函数解决此类问题, 于是乎在此处总结一下窗口函数的用法.

    窗口函数基本语法

    <窗口函数> over ([partition by <用于分组的列名>]
                    order by <用于排序的列名>)
    

    窗口函数部分可以是专业的窗口函数rank() dense_rank() row_number() 等, 还可以是常见的聚合函数 avg sum count max min 等等操作. 此处并不是局限于专业窗口函数.

    parition by: 指定分组列名, 此处如果不指定, 就是作用在全数据集上面, 而不是指定分组上面

    order by: 指定排序的字段.

    案例

    创建一个成绩表, 里面含有学生id,班级id, 语文数学以及外语相关的分数.

    CREATE TABLE IF NOT EXISTS `achievement` (
    	`学号` char(5) NOT NULL,
        `班级` char(5) NOT NULL,
        `数学` int(3) NOT NULL,
        `语文` int(3) NOT NULL,
        `英语` int(3) NOT NULL,
        PRIMARY KEY (`学号`)
    )ENGINE=InnoDB CHARSET=utf8mb4;
    

    批量插入数据

    INSERT INTO achievement VALUES('10001', '1',98, 96, 99);
    INSERT INTO achievement VALUES('10002', '1',97, 96, 95);
    INSERT INTO achievement VALUES('10003', '1',92, 95, 96);
    INSERT INTO achievement VALUES('20001', '2',97, 96, 90);
    INSERT INTO achievement VALUES('20002', '2',90, 96, 92);
    INSERT INTO achievement VALUES('20003', '2',95, 96, 97);
    INSERT INTO achievement VALUES('30001', '3',99, 96, 99);
    INSERT INTO achievement VALUES('30002', '3',97, 99, 100);
    
    SELECT * FROM achievement;
    

    结果:
    成绩

    试验1: 查找出每一个班中数学成绩排名第一的学生的成绩信息.
    首先是对班级进行分区 partition by 班级 然后以数学成绩进行降序排列 order by 数学 desc 构建一个含有每一个班级中数学排名的新字段排名

    SELECT 学号, 班级, 数学, 语文, 英语, rank() over (partition by 班级 order by 数学 desc) AS 排名 FROM achievement 
    

    排名

    在此基础上选择出 排名=1的行即可

    SELECT 学号, 班级, 数学, 语文, 英语 FROM (SELECT 学号, 班级, 数学, 语文, 英语, rank() over (partition by 班级 order by 数学 desc) AS 排名 FROM achievement) As tmp WHERE 排名 = 1;
    

    最终排名结果

    试验2: 选择全年级数学成绩排名前三的学生信息
    可能有人直接就 ORDER BY 数学 DESC LIMIT 3 但是忽略了成绩相同的情况.

    SELECT 学号, 班级, 数学, 语文, 英语, dense_rank() over (order by 数学 desc) as 排名 FROM achievement;
    

    在这里插入图片描述

    这里有两个点注意, 第一没有了partiton by 则是在全部数据集合上排序;第二此处窗口函数使用的是 dense_rank 上一个案例是 rank 看两者区别

    SELECT 学号, 班级, 数学, 语文, 英语, rank() over (order by 数学 desc) as 排名 FROM achievement;
    

    在这里插入图片描述

    两者区别在于相同排名之后, dense_rank 会从下一个紧挨着的排名开始, 而 rank处理是相同的排名的行会”占用位置“, 下一个排名计数会不一样.

    row_number只是为每一行标个序号

    SELECT 学号, 班级, 数学, 语文, 英语, row_number() over (order by 数学 desc) as 行号 FROM achievement;
    

    在这里插入图片描述

    完成这个题完全的sql语句如下

    SELECT 学号, 班级, 数学, 语文, 英语 FROM (SELECT 学号, 班级, 数学, 语文, 英语, dense_rank() over (order by 数学 desc) as 排名 FROM achievement) AS tmp WHERE 排名 < 4;
    

    结果

    试验3: 窗口函数用聚合函数替代

    SELECT *, sum(数学) over (partition by 班级 order by 数学) as 数学分数 FROM achievement;
    

    结果

    可以看到最后一列 结果很不一样 结果是按班级进行分组 然后组内按照数学分数升序排列累加计算数学分数之和. 这种操作会让人联想到group by 操作, 但是group by 操作是粗粒度的组内聚合计算, 最终结果是每组一行而不能像上面那样不改变行数, 而且group by 也没有组内排序功能.

    SELECT 班级, SUM(数学) AS 数学分数 FROM achievement GROUP BY 班级;
    

    在这里插入图片描述

    相比之下窗口函数有更灵活的分区(partition by) 以及 组内排序功能(order by)

    展开全文
  • SQL窗口函数

    2020-12-24 20:02:50
    SQL窗口函数 一、窗口函数应用场景:分组处理数据 二、窗口函数基本语法 <窗口函数> over (partition by <用于分组的字段名> order by <用于排序的字段名>) 上面代码含义为: 首先将数据表...

    一、窗口函数应用场景:分组处理数据

    二、窗口函数基本语法

    <窗口函数> over (partition by <用于分组的字段名> 
    				order by <用于排序的字段名>)
    

    上面代码含义为:
    首先将数据表根据<用于分组的字段名>分段,并且根据<用于排序的字段名>排序,而后对经过这两步处理的数据表执行<窗口函数>
    注:partition bygroup by的区别:group by改变了表的行数,经过分组后每一组的数据成为一行,而partition by不改变表的行数。

    三、常用窗口函数及使用方法

    窗口函数可以分为两类:
    1.专用窗口函数,如rank, dense_rank, row_number等
    2.聚合函数,如max, min, sum等

    1.三种排序函数:rank()、dense_rank()、row_number()
    rank() over (partition by <分区字段> order by <排序字段>)
    dense_rank() over(partition by <分区字段> order by <排序字段>)
    row_number() over(partition by <分区字段> order by <排序字段>)
    
    三种函数效果区分:

    rank()、dense_rank()、row_number()函数都会对各分区内的数据依据排序字段进行排序,row_number()会赋予排序后的数据不同的行号,但rank()与dense_rank()会给相同排名的记录相同的序号,dense_rank()赋予使下一个排名的记录顺延的序号,而rank()则会跳过这几个序号。
    举例如下:如果按照成绩排序后数据为下表:

    id score
    1 99
    2 99
    3 99
    4 98

    rank()、dense_rank()、row_number()处理的结果分别如下:

    • rank()
    id score rk
    1 99 1
    2 99 1
    3 99 1
    4 98 4
    • dense_rank()
    id score rk
    1 99 1
    2 99 1
    3 99 1
    4 98 2
    • row_number
    id score rk
    1 99 1
    2 99 2
    3 99 3
    4 98 4
    展开全文
  • sql窗口函数

    2020-04-21 17:32:49
    sql窗口函数 该文章属于转载 https://blog.csdn.net/qq_41805514/article/details/81772182

    sql窗口函数
    该文章属于转载
    https://blog.csdn.net/qq_41805514/article/details/81772182

    展开全文

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 3,425
精华内容 1,370
关键字:

sql窗口函数