天道酬勤,学无止境

MySQL实现排名/分组排名/合计(8.0窗口函数rank/dense_rank、5.6/5.7自行模拟)

读完本文,您将掌握:

  • MySQL查询时直接显示排名/分组排名/分组合计的方法,支持5.6/5.7/8.0版本
  • MySQL 8.0中rank()dense_rank()的用法与区别
  • MySQL 8.0的window窗口函数用法
  • MySQL 变量的一些用法

更多MySQL函数介绍,可查看《MySQL函数和运算符》

目录

  • 目标效果
  • 实现方案
    • MySQL 8.0
      • 全局排名
      • 分组排名
      • 合计
      • 小结
    • MySQL 5.6/5.7
      • 全局排名
      • 分组排名
      • 合计
  • 总结

目标效果

模拟数据如下:

DROP TABLE IF EXISTS `test_sum`;
CREATE TABLE `test_sum` (
  `year` SMALLINT NOT NULL,
  `province` VARCHAR(32) NOT NULL,
  `num` INT UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `test_sum` (`year`,`province`,`num`) VALUES
(2018,'北京',1),
(2018,'上海',3),
(2018,'浙江',7),
(2019,'北京',5),
(2019,'上海',5),
(2019,'浙江',11);

目标实现三个场景:

  • 结果集会显示全局排名
    在这里插入图片描述

    排名分有间隔和无间隔两种情况:主要区别为出现并列排名后,下一个排名是按行号,还是在上一个排名后+1

  • 结果集分几个组后独立排名
    在这里插入图片描述
  • 结果集分几个组后显示每行数据、和合计值
    在这里插入图片描述

实现方案

因为不同MySQL版本,实现方案有所不同,下面分开介绍方案。

小提示:判断MySQL函数在哪个版本支持,可以在MySQL官方的《函数和运算符参考文档》中查找、对比不同版本。

MySQL 8.0

MySQL 8.0提供了窗口函数1。其实现的核心,通过window获取结果集、在window中可以指定排序和分组,然后主查询通过over连接window结果集,进行相应运算。窗口函数包括排名等函数。

全局排名

只要使用内置的rank()dense_rank()函数2即可。

SELECT *,
	ROW_NUMBER() OVER w AS '行号',
	RANK()       OVER w AS '排名-有间隔',
	DENSE_RANK() OVER w AS '排名-无间隔'
FROM `test_sum` 
WINDOW w AS (ORDER BY `num`);

WINDOWOVER是窗口函数的主要特征。

分组排名

分组排名的实现,与上面全局排名的区别,仅仅是WINDOW中增加了PARTITION

SELECT *,
	ROW_NUMBER() OVER w AS '行号',
	RANK()       OVER w AS '排名-有间隔',
	DENSE_RANK() OVER w AS '排名-无间隔'
FROM `test_sum`
WINDOW w AS (partition by `year` ORDER BY `num`)

合计

SELECT *, SUM(`num`) over w AS '分组合计'
FROM `test_sum` 
WINDOW w AS (partition by `year`)
ORDER BY `year`, `province`

窗口函数下的分组合计,和之前理解的group分组有所不同,区别在于合计函数并不会导致分组的行合并成一行。

小结

思考窗口函数的实现思路,看起来像是window查询出后,按照排序、分组在内存中组织临时表(有序、指定分区),然后按分区开始运算主查询函数,运算后的结果按分区键关联到临时表中,然后整合数据输出。

window中指定排序、主查询不指定排序,最终的结果是按window的排序来输出,而不是主查询默认的顺序;而多个window都指定了排序时,会使用最后一个的排序(可以尝试同个列用升序和降序),因而有此猜测。
后面会考虑追踪sql执行过程、验证猜想,有结果再补充更新本文。

MySQL item_rank源代码文档:https://dev.mysql.com/doc/dev/mysql-server/latest/classItem__rank.html

MySQL 5.6/5.7

上面说的三个效果,MySQL 8.0以下都需要自行模拟。

全局排名

全局排名实现思路:

  • 查询结果集,指定排序
  • 声明变量,结果集每行都通过变量计算、排名需否增加

最终SQL如下:

select a.*,
  @rowNo := @rowNo + 1 as '行号',
  if(`num` = @preFactor, @curRank, @curRank := @rowNo) as '排名-有间隔',
  if(`num` = @preFactor, @curDenseRank, @curDenseRank := @curDenseRank + 1) as '排名-无间隔',
  @preFactor := `num` as "临时列"
from `test_sum` a , 
  (select @rowNo :=0, @curDenseRank := 0, @curRank := 0, @preFactor := NULL) q 
order by `num`;

要点说明:

  • (select @rowNo :=0, @curDenseRank := 0, @curRank := 0, @preFactor := NULL) q这个子查询,只是快速声明变量的一个方式,等价于在select前执行set @rowNo =0, @curDenseRank = 0, @curRank = 0, @preFactor = NULL;
  • 每获取一行数据,select后的表达式都会计算一次,这就像在对所有数据进行循环遍历时计算一样。

分组排名

思路:

  • 按分组排序,确定分组开始位置的行;
  • 该分组所有行,在其全局排名上,减去改组开始位置行的全局排名、然后+1即可
select a.*,
  @rowNo := @rowNo + 1 as '行号',
  if( @prePartition = `year`, @rankOffset := @rankOffset, @rankOffset := @rowNo - 1) as 'offset_rank',
  if( @prePartition = `year`, @denseRankOffset := @denseRankOffset, @denseRankOffset := @curDenseRank) as 'offset_dense',
  if(`num` = @preFactor, @curRank, @curRank := @rowNo) as '排名-有间隔',
  @curRank - @rankOffset as '分组排名-有间隔',
  if(`num` = @preFactor, @curDenseRank, @curDenseRank := @curDenseRank + 1) as '排名-无间隔',
  @curDenseRank - @denseRankOffset as '分组排名-无间隔',
  @preFactor := `num` as temp, @prePartition := `year`
from `test_sum` a ,
  (select @rowNo :=0, @curDenseRank := 0, @curRank := 0, @preFactor := NULL,
     @prePartition := NULL, @rankOffset := 0, @denseRankOffset := 0) q
order by `year`, `num`;

效果如下:
在这里插入图片描述

合计

先看SQL,思路稍微有点绕,结合下面的截图来讲:

SQl如下:

select `year`, `province`, `num`, if(@year = `y1`, @num := @num, @num:=`sum`) AS '分组合计', @year:=`y1`
from
  (
       select *,
           if(@year = `year` , @num:=@num + `num` , @num:=`num`) `sum`,
           @year:=`year` 'y1'
       from `test_sum` a, (select @year:=NULL, @num:=0) temp
       order by `year`, `province` desc
   ) temp
order by 1, 2

执行效果截图:

  1. 子查询:指定分组+任意列,但需确保组合列是唯一的,排序与实际查询需要的顺序相反(倒序)
    • 结果集中用变量进行合计,并作为临时列
    • 合计时发现是新分组,合计时从0开始合计
    • 每一组的最后一行,实际上会得到本组的合计值
      在这里插入图片描述
  2. 主查询:处理子查询的结果,按实际需要排序(正序)
    • 子查询时每组最后一行,在主查询中变成每组第一行
    • 使用变量缓存第一行的合计值;
      • 发现是新分组时,更新变量为当前行的合计值
      • 不是第一行,使用缓存的合计值;
        在这里插入图片描述

方案不足:用子查询排序后再分组,但实际上都在内存中,消耗太大;即使加limit限制,对大数据量时不可靠

总结

虽然8.0支持了内置函数,但5.7的解决方案也提供了更多MySQL开发的可能。
MySQL8.0新增加的窗口函数,还有其他一些具体函数也非常有用,可以自行去进一步了解2


以上。感谢您的阅读。


  1. MySQL官方文档 - 窗口函数语法:https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html ↩︎

  2. MySQL官方文档 - 窗口函数概述: https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html ↩︎ ↩︎

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐
  • SQL窗口函数教程及练习笔记(sqlite)
    文章目录 教程数据说明sqlite 代码rank函数对比三种专业分组函数:rank, dense_rank, row_number聚合函数作为窗口函数 使用场景排名问题TopN问题 教程 通俗易懂的学会:SQL窗口函数 by 猴子 数据说明 数据为《sql面试50题》中的【成绩表】score,创建和插入数据可以在连接中找到。 sqlite 代码 我本地使用的是sqlite数据库。 mysql从8.0开始支持,leetcode上面的mysql版本比较老,因此没有在leetcode上面找题型做练习。 rank函数 SELECT *, rank() over (partition by c_id order by score DESC) as ranking FROM score; 结果 s_id c_id score ranking ---------- ---------- ---------- ---------- 01 01 80 1 03 01 80 1 05 01 76 3 02 01 70 4 04 01 50 5 06 01 31 6 01 02 90 1 07 02 89 2 05 02 87 3 03 02 80 4 02 02 60 5 04 02 30 6 01 03 99 1 07 03 98 2 02 03 80 3 03 03 80 3 06 03 34 5
  • 数据库刷题笔记1
    目录 简化等值查询mysql中limit和offset的用法LIMIT 分页查询In 与 exists 的区别IN 语句:只执行一次EXISTS语句:执行student.length次区别及应用场景not in 和not exists 日期函数格式转换开窗函数函数简介语法简介 聚合函数排名函数 简化等值查询 多表连接查询,且去掉重复列,使用 USING() select * from school left join course USING (courseid) #相当于 select * from school left join course on school.courseid=course.courseid; mysql中limit和offset的用法 mysql里分页一般用limit来实现,例如: 1、select* from user limit 3; 表示直接取前三条数据 2、select * from user limit 1,3; 表示取2,3,4三条条数据 3、select * from user limit 3 offset 1; 表示取2,3,4三条条数据 解释: 1、当 limit后面跟一个参数的时候,该参数表示要取的数据的数量 例如 select* from user limit 3; 表示直接取前三条数据 2、当limit后面跟两个参数的时候
  • 解决数据排名问题——mysql中的窗口函数
    对于SQL语句的使用,我们知道怎样使用它的增删改查,但是如果遇到这样的查询需求时,怎样得到各部门工资排名前N名员工列表,环比如何计算,查找各部门每人工资占部门工资的百分比。对于这写类型的查询需求,传统的查询语句解决起来非常的繁琐,但要解决此类问题,最方面的就是使用窗口函数。 窗口函数的定义 什么是窗口函数,窗口的概念十分重要,可以理解为记录集合,窗口函数也就是满足某种条件的记录集合上执行的特殊函数,对于每条记录都要在此窗口内执行函数,有的函数,随着记录不同,窗口大小都是固定的,这种属于静态窗口,有的函数则相反,不同的记录对应着不同的函数。窗口函数也叫OLAP函数,可以对数据库数据进行实时分析处理 窗口函数和聚合函数有所不同,聚合函数时将多个记录聚合在一起,而窗口函数时每条记录都会执行,有几条记录执行完还是几条 按照功能划分,可以将窗口函数分为下面5种类型 序号函数:row_number()/rank()/dense_rank() 分布函数:percent_rank()/cume_dist() 前后函数:lag()/lead() 头尾函数:first_val()/last_val() 其他函数:nth_value()/nfile() 1、窗口函数的使用 # 窗口函数语法<窗口函数> over (partition by <要分组的列名> order By <要排序的列名>) 1
  • mysql8.0新特性 之 窗口函数 使用示例及详解
    什么是窗口函数 MySQL从8.0开始支持窗口函数,这个功能在大多数据库中早已支持,有的也叫分析函数。 窗口的概念非常重要,它可以理解为记录集合,窗口函数也就是在满足某种条件的记录集合上执行的特殊函数对于每条记录都要在此窗口内执行函数,有的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口;有的函数则相反,不同的记录对应着不同的窗口,这种动态变化的窗口叫滑动窗口。简单的说窗口函数就是对于查询的每一行,都使用与该行相关的行进行计算。 窗口函数和普通聚合函数很容易混淆,二者区别如下: - 聚合函数是将多条记录聚合为一条;而窗口函数是每条记录都会执行,有几条记录执行完还是几条。 - 聚合函数也可以用于窗口函数中。 窗口函数功能 名称描述ROW_NUMBER()为分区中的每一行分配一个顺序整数【没有重复值的排序(记录相等也是不重复的),可以进行分页使用】RANK()与DENSE_RANK()函数相似,不同之处在于当两行或更多行具有相同的等级时,等级值序列中存在间隙【跳跃排序】DENSE_RANK()根据该ORDER BY子句为分区中的每一行分配一个等级。它将相同的等级分配给具有相等值的行。如果两行或更多行具有相同的排名,则排名值序列中将没有间隙【连续排序】PERCENT_RANK()计算分区或结果集中行的百分数等级CUME_DIST()计算一组值中一个值的累积分布LAG(
  • MySQL8.0新特性学习笔记(二):窗口函数
    目录 一,简介 二,窗口函数的两种写法 三,窗口表达式语法 1,PARTITION BY子句 2,ORDER BY子句 3,frame子句 四,窗口函数介绍 1,序号函数:row_number(),rank(),dense_rank() 2,分布函数:percent_rank() / cume_dist() 3,前后函数:lead() / lag() 4,头尾函数:first_val() / last_val() 5,其他函数:nth_value() / nfile() 6,原生聚合函数 MySQL8.0引入的窗口函数,可以比较方便的实现一些分析和统计功能,这些功能不用窗口函数也能实现,不过实现的sql可能会比较复杂。 一,简介 什么是窗口函数 窗口函数引入的其实不只是几个函数,而是一套完整的语法,窗口函数是此语法中的一部分。 语法: 窗口函数 over 窗口表达式。 over是窗口函数语法的关键字。 从语法上来看,窗口函数的使用实际上分为两部分:窗口函数和窗口。 窗口函数在sql中往往使用在查询结果中,返回一列值,就像查询一个字段一样。 窗口 从数据结构上来看,要使用窗口函数,首先我们要有一个窗口,所谓窗口,个人理解是一个数据集,数据集的内容可以类比group by分组之后的一组数据,只不过在使用了窗口函数的sql中,每一行记录都有一个自己的窗口,每行记录所对应的窗口
  • 【读书笔记】mysql 窗口函数总结
    什么是窗口函数? 窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。 窗口函数有什么用? 实际工作中,我们会常常遇到group by 后根据聚合起来的每组进行排名,求topN,累加等等问题 基本语法 <窗口函数>over (partition by <需要在哪类组别内比较的列名>order by <用于排序的列名> 详解 1、专用窗口函数 rank、dense_rank、row_number等等。 2、聚合函数,sum、avg、count、max、min等等。 举个例子 select *, rank() over (partition by 姓名 order by 充值金额 desc) as ranking from 订单表 表示的是按每个人的充值金额进行倒序排序,充值金额最大的那一笔排名第一。 partition by 指的是按姓名分组 order by 指的是按金额进行排序,可加asc、desc 为什么要有窗口函数呢? 虽然我们常常通过聚合能够将源数据汇总成少量的几行,一行只表示一个类别,只能跨类别比较,每个类别里面的数据都被忽略了。但有了窗口函数,我们可以不改变原表行数,进行组内比较。 常用的函数区别 ##专用函数 1、rank()over(partition by 班级 order by
  • SQL窗口函数练习题(排名问题、topN问题)
    ** 笔记内容包括SQL窗口函数的介绍、练习题的解答全流程。 窗口函数语法 排名问题:每个部门按业绩来排名 topN问题:找出每个部门排名前N的员工进行奖励 窗口函数语法: <窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>) <窗口函数>可以放以下两种函数: 1) 专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。 2) 聚合函数,如sum. avg, count, max, min等 专用窗口函数 rank, dense_rank, row_number这三个函数的区别如下: 对表分组 Group by VS partition by用来对表分组 练习题:双十一分析 创建一个库 :create database Exercise;查看库是否建立成功:show databases;使用这个练习库: use Exercise;查看库里的表格:show tables;创建表格 create table 双十一登陆表( id int, 姓名 varchar(100), 邮箱地址 varchar(100), 最后登陆时间 date ); 插入信息数据: insert into 双十一登陆表(id,姓名,邮箱地址,最后登陆时间) values(100, 'test4', 'test
  • SQL Sever中的窗口函数row_number()rank()dense_rank()
    三个排序函数row_number()rank()dense_rank() 对于row_number()函数,他可以为表排序并返回一个序号 create table score (学号 nvarchar(10) not null, 课程 nvarchar(10) not null, 成绩 nvarchar(10) not null) insert into score values('01','语文',90), ('01','数学',67), ('02','语文',82), ('02','数学',78), ('03','语文',65), ('03','数学',98), ('04','语文',82), ('04','数学',98) 比如最简单的给这个表排序,比如根据学号倒序 select *,row_number()over(order by 学号 desc) 序号 from score 如图,row_number()over(order by 字段),则会根据你需要的字段进行排序,并给每一行一个编号,但是这些编号是连续的,比如学号为4号的有两行,但是row_number()函数并没有给他们相同的序号也就是排名,也就是说利用这个函数排序不存在相同名次的情况。如果数据的值相同,则先出现的数据排名更靠前。 row_number()函数也能分组进行排序,比如我想知道每门课程的排名情况。
  • mysql开窗函数排序_SQL Server进阶(八)查询——开窗函数、四大排名函数、透视数据、逆透视数据...
    概述 ROW_NUMBER() OVER(PARTITION BY CustId ORDER BY ID DESC) https://www.jb51.net/article/75533.htm 开窗函数 SELECTempid, ordermonth, val,SUM(val) OVER(PARTITION BYempidORDER BYordermonth ROWSBETWEENUNBOUNDED PRECEDINGAND CURRENT ROW) ASrunvalFROM Sales.EmpOrders; 四大排名函数(ROW_NUMBER、RANK、DENSE_RANK、NTILE) ROW_NUMBER()的用途的非常广泛,排序最好用他,一般可以用来实现web程序的分页,他会为查询出来的每一行记录生成一个序号,依次排序且不会重复,注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。 rownum列就是row_number函数生成的序号列,其基本原理是先使用over子句中的排序语句对记录进行排序,然后按照这个顺序生成序号。over子句中的order by子句与SQL语句中的order by子句没有任何关系,这两处的order by 可以完全不同。 dense_rank函数的功能与rank函数类似,dense
  • MySQL数据库知识要点归纳和总结
    数据库知识要点归纳和总结 各知识点总结1.mysql中的`distinct`的用法2.`group by` 与聚合函数3.`limit`的用法4.`left join`的用法和理解5.`窗口函数`的应用6.排序的自连接方法7.`EXCEPT、UNION`和`INTERSECT`的用法8.DATE日期函数 阅读之前看这里👉:博主是正在学习数据分析的一员,博客记录的是在学习过程中一些总结,也希望和大家一起进步,在记录之时,未免存在很多疏漏和不全,如有问题,还请私聊博主指正。 博客地址:天阑之蓝的博客,学习过程中不免有困难和迷茫,希望大家都能在这学习的过程中肯定自己,超越自己,最终创造自己。 各知识点总结 1.mysql中的distinct的用法 distinct的作用 在mysql中,distinct关键字的主要作用就是对数据库表中一个或者多个字段重复的数据进行过滤,只返回其中的一条数据给用户,distinct只可以在select中使用。 distinct的使用语法是这样的: select distinct expression[,expression...] from tables [where conditions]; 2.group by 与聚合函数 group by做为分组来使用,后面为条件,可以有多个条件,条件相同的为一组,配合聚合函数进行相关统计。
  • MySQL中的排名函数(Rank function in MySQL)
    问题 我需要找出客户的等级。 在这里,我为我的需求添加了相应的ANSI标准SQL查询。 请帮助我将其转换为MySQL。 SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender], FirstName, Age, Gender FROM Person 有什么功能可以找出MySQL的排名吗? 回答1 一种选择是使用排名变量,例如: SELECT first_name, age, gender, @curRank := @curRank + 1 AS rank FROM person p, (SELECT @curRank := 0) r ORDER BY age; (SELECT @curRank := 0)部分允许变量初始化,而无需单独的SET命令。 测试用例: CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1)); INSERT INTO person VALUES (1, 'Bob', 25, 'M'); INSERT INTO person VALUES (2, 'Jane', 20, 'F'); INSERT INTO person VALUES (3, 'Jack', 30
  • MYSQL常见面试题之——窗口函数
    阅读之前看这里👉:博主是正在学习数据分析的一员,博客记录的是在学习过程中一些总结,也希望和大家一起进步,在记录之时,未免存在很多疏漏和不全,如有问题,还请私聊博主指正。 博客地址:天阑之蓝的博客,学习过程中不免有困难和迷茫,希望大家都能在这学习的过程中肯定自己,超越自己,最终创造自己。 MYSQL常见面试题之——窗口函数知识总结 一、窗口函数:1.窗口函数的基本用法:2.窗口函数与普通聚合函数的区别:计算移动平均 3.(面试考点)序号函数:row_number(),rank(),dense_rank()的区别4.分布函数:percent_rank(),cume_dist()5.前后函数:lag(expr,n),lead(expr,n) 二、面试题1.用户行为分析2.学生成绩分析 复习知识点:窗口函数 一、窗口函数: 窗口函数也称为OLAP函数,OLAP 是OnLine Analytical Processing 的简称,意思是对数据库数据 进行实时分析处理。例如,市场分析、创建财务报表、创建计划等日常性商务工作。窗口函数就是为了实现OLAP 而添加的标准SQL 功能。 1.窗口函数的基本用法: <窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>) over关键字用来指定函数执行的窗口范围,若后面括号中什么都不写
  • Oracle分析函数
    分析函数概述 和聚合函数相似,但是对于每一组记录,无论多少行,聚合函数只返回一行值,而分析函数对其中每一行记录都返回值这一组记录,称为分析函数的一个窗口(WINDOW)由窗口决定了要处理数据的范围,该范围在物理上可以由指定的行数来确定,或者在逻辑上由相对偏移量来确定分析函数总是在除了ORDER BY之外的其他子句运算后才执行的,所以它不能出现在 where、group by等子句中,只能出现在select列表和order by子句中 分析函数功能 排名 相邻 统计 分析函数功能--排名 几种不同的排名需求   – 排名无并列,且每个排名与紧接着的下一个排名都是连续的   – 排名有并列,且并列的排名与紧接着的下一个排名不连续   – 排名有并列,且并列的排名与紧接着的下一个排名连续   – 如下例,对CNT列排序的结果,从左到右分别符合上述三种需求,结果即为:     – 对cnt列的不同排名,sql如何写?with t as (select rownum*10 cnt from dual connect by rownum<5 union all select rownum*4010 from dual connect by rownum<3) select cnt, row_number()over(order by cnt) rn, rank()over(order by
  • Hive窗口函数Over partition by
    - 目录 1、聚合函数+over2、partition by子句3、order by子句4、★window子句(里面包含) - PRECEDING:往前 - FOLLOWING:往后 - CURRENT ROW:当前行 - UNBOUNDED:起点5、★窗口函数中的序列函数:包含NTILE(n),row_number、rank、dense_rank 简介 本文主要介绍hive中的窗口函数。hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析 注意:OVER():指定分析函数工作的数据窗口大小,决定了聚合函数的范围,这个数据窗口大小可能会随着行的变而变化,同时可以使用以下进行限定范围。 概念 我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的。 但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数。 在深入研究Over字句之前,一定要注意:在SQL处理中,窗口函数都是最后一步执行,而且仅位于Order by字句之前. 数据准备 我们准备一张order表,字段分别为name、orderdate、cost 数据内容如下: jack,2015-01-01,10 tony,2015-01-02
  • sql Server:按总分排名,按排名排序(sql Server: Rank by sum of points and order by ranking)
    问题 我有一个包含这些字段的游戏桌: ID Name Email Points ---------------------------------- 1 John john@aaa.com 120 2 Test bob@aaa.com 100 3 John john@bbb.com 80 4 Bob bob@aaa.com 50 5 John john@aaa.com 80 我想通过电子邮件对它们进行分组(电子邮件识别出两个玩家都是相同的,无论第 2 行和第 4 行的名称不同),并且在结果中还有点数和最后输入的名称,并以最高点数对它们进行排名到最低 我想从示例表中得到的结果是: Ranking Name Points Games_Played Average_Points ------------------------------------------------------------------------------------------ 1 John 200 2 100 2 Bob 150 2 75 3 John 80 1 80 我可以实现获得排名,总分和平均分,但获得最后输入的名称我认为需要再次加入同一张表,这似乎有点错误。 任何想法如何做到这一点? 回答1 显示名称和分组为​​电子邮件将导致使用例如 MIN(Name) 并导致重复名称。 Select Rank()
  • oracle中的RANK()和DENSE_RANK()函数有什么区别?(What's the difference between RANK() and DENSE_RANK() functions in oracle?)
    问题 RANK()和DENSE_RANK()函数之间有什么区别? 如何在下面的emptbl表中找出第n个薪水? DEPTNO EMPNAME SAL ------------------------------ 10 rrr 10000.00 11 nnn 20000.00 11 mmm 5000.00 12 kkk 30000.00 10 fff 40000.00 10 ddd 40000.00 10 bbb 50000.00 10 ccc 50000.00 如果表中的数据为nulls ,如果我想找出nth薪水会怎样? 回答1 RANK为您提供有序分区内的排名。 领带被分配相同的等级,而下一个等级被跳过。 因此,如果您在第2级有3个项目,则列出的下一个级别将是第5级。 DENSE_RANK再次为您提供了有序分区中的排名,但是这些排名是连续的。 如果存在包含多个项目的等级,则不会跳过任何等级。 至于空值,则取决于ORDER BY子句。 这是一个简单的测试脚本,您可以使用它来查看会发生什么: with q as ( select 10 deptno, 'rrr' empname, 10000.00 sal from dual union all select 11, 'nnn', 20000.00 from dual union all select 11, 'mmm', 5000
  • SQL窗口函数不懂?五大应用场景让你一步到位
    公众号: 数据小斑马,关注即可获得价值1000元的数据分析学习资料 SQL系列目录(文末有大礼赠送): SQL技巧初级系列①—建表/更新表/删除表 SQL技巧初级系列②—聚合和排序(group by,having,order by) SQL技巧初级系列③——数据拼接(集合运算union和列连接join) SQL技巧中级系列①——字符串函数的使用 SQL技巧中级系列②——日期函数的使用 SQL技巧高级系列②——聚合函数和CASE WHEN的使用 在窗口分析函数出现之前,存在很多 SQL 难以解决的问题,很多都要通过复杂的相关子查询完成,或到Excel中用函数处理 2003年ISO SQL标准加入了窗口分析函数,使得这些难题轻松被攻克,大大提升了效率,减少了Excel卡死导致前功尽弃的情况 本文从聚合/排序/极值/移动/切片五大场景来讲解窗口分析函数 窗口分析函数,可以指定数据窗口进行统计分析,它和普通聚合函数的区别: ① 窗口函数对每个组返回多行,而聚合函数对每个组只返回一行 ② 窗口函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化,而聚合函数是针对所有数据进行统计 窗口函数写法: 函数( ) over (partition by 列名2 order by 列名3 rows between n/unbounded preceding and m
  • SQL刷题记录
    nowcoder SQL刷题记录: https://www.nowcoder.com/ta/sql 复习题目:12, 18, 21, 44, 57, 88, 60, 61, 91, 88 60虽然做出来了,但是对over中的partition by 和order by 还是不够理解,可以尝试不用window function可以做出来吗 61虽然做出来了,但是学习子查询欠缺 91虽然做出来了,但是with语法并不会 88有大佬使用了一个NB的解法,这个题和76题并不同,76题是单个条目取中位数的数据,但是88是很多条目累计的和,如果使用76的方法肯定是行不通的 这个题要么取巧,要么还是好好复习 如果over中使用了order by 则之后的clause不用再使用order by语句,order by在window中使用会导致所有的行都有效 【Mysql】查找字符串'10,A,B' 中逗号','出现的次数cnt。 题目描述:查找字符串'10,A,B' 中逗号','出现的次数cnt。。知识点总结: 统计字符串长度:char_length('string')/char_length(column_name) 1、返回值为字符串string或者对应字段长度,长度的单位为字符,一个多字节字符(例如,汉字)算作一个单字符; 2、不管汉字还是数字或者是字母都算是一个字符; 3、任何编码下
  • MySQL8.0开窗函数
    作为一名数据分析师,日常工作中经常会遇到类似这样的需求: 怎么样得到各部门工资排名前N名员工列表?查找各部门每人工资占部门总工资的百分比?累计求和如何计算? 对于这样的需求,使用传统的SQL实现起来比较困难。这类需求都有一个共同的特点,需要在单表中满足某些条件的结果集内部做一些函数操作,不是简单的表连接,也不是简单的聚合可以实现的,通常费了大半天时间写出来一堆长长的晦涩难懂的SQL,且性能低下,难以维护。要解决此类问题,最方便的就是使用开窗函数。 开窗函数简介 MySQL从8.0开始支持开窗函数,这个功能在大多商业数据库如Oracle和PostgreSQL中早已支持,也叫分析函数。 什么是开窗 它可以理解为记录集合,开窗函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数,有的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口;有的函数则相反,不同的记录对应着不同的窗口,这种动态变化的窗口叫滑动窗口。开窗函数的本质还是聚合运算,只不过它更具灵活性,它对数据的每一行,都使用与该行相关的行进行计算并返回计算结果。 开窗函数和普通聚合函数的区别 聚合函数是将多条记录聚合为一条;而开窗函数是每条记录都会执行,有几条记录执行完还是几条。聚合函数也可以用于开窗函数中。 create table order_tab( order_id int, user
  • MySQL学习笔记六:表的运算和联结、窗口函数与grouping
    表的运算 新建立的product2表格如下: 对记录取并集,在两个select语句之间用union连接,如果想包含重复行就用union all连接。注意连接的两个表要列数相同、类型一致,且order by只能在末尾用一次。取交集在mysql中可以使用关联子查询: -- 取并集union(行方向) select product_id,product_name from product union select product_id,product_name from product2; -- 输出10个不同的商品记录 select product_id,product_name from product union all select product_id,product_name from product2 order by product_id; -- 取交集intersect、减法except在mysql中不可用,可按照关联子查询方式取交集: select product_id,product_name from product as p1 where product_id= (select product_id from product2 as p2 where p1.product_id=p2.product_id); 以列为单位的表联结 内联结:inner join