SQL | 窗口函数 | 名次

最新推荐文章于 2023-08-31 17:48:09 发布
最新推荐文章于 2023-08-31 17:48:09 发布
阅读量872 收藏 2
点赞数 1
分类专栏： SQL 文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_46023346/article/details/116459204
版权
一、编写sql语句实现每班前三名，分数一样并列，同时求出前三名按名次排序的分差

1、创建表

CREATE TABLE IF NOT EXISTS stu(
Stu_no int,
class int,
score int
);
alter table stu change column Stu_no stu_no int;

2、插入数据

INSERT INTO stu VALUES(1,1901,90);
INSERT INTO stu VALUES(2,1901,90);
INSERT INTO stu VALUES(3,1901,83);
INSERT INTO stu VALUES(4,1901,60);
INSERT INTO stu VALUES(5,1902,66);
INSERT INTO stu VALUES(6,1902,23);
INSERT INTO stu VALUES(7,1902,99);
INSERT INTO stu VALUES(8,1902,67);
INSERT INTO stu VALUES(9,1902,87);

3、逻辑SQL
（1）添加一列排序，分数一样的并列，因为用DENSE_RANK() OVER()

mysql> SELECT stu_no,class,score,DENSE_RANK() OVER(PARTITION BY class ORDER BY score DESC) as drk
    ->  FROM stu;
+--------+-------+-------+-----+
| stu_no | class | score | drk |
+--------+-------+-------+-----+
|      1 |  1901 |    90 |   1 |
|      2 |  1901 |    90 |   1 |
|      3 |  1901 |    83 |   2 |
|      4 |  1901 |    60 |   3 |
|      7 |  1902 |    99 |   1 |
|      9 |  1902 |    87 |   2 |
|      8 |  1902 |    67 |   3 |
|      5 |  1902 |    66 |   4 |
|      6 |  1902 |    23 |   5 |
+--------+-------+-------+-----+
9 rows in set (0.01 sec)

(2)使用LEAD() OVER()函数，按照班级分组名次排序，求出该名次的前一名分数。
使用IFNULL(expr1,expr2)函数（功能类似于Oracle的NVL()，如果不为NULL返回expr1，如果为NULL返回expr2)，将空值转换为其他值
添加WHERE条件删选出前3名

mysql> SELECT stu_no,class,score,drk,score-IFNULL(LAG(score) OVER(PARTITION BY class ORDER BY drk),0) AS dev
    -> FROM (SELECT stu_no,class,score,DENSE_RANK() OVER(PARTITION BY class ORDER BY score DESC) as drk
    ->       FROM stu) t1
    -> WHERE drk<4;
+--------+-------+-------+-----+------+
| stu_no | class | score | drk | dev  |
+--------+-------+-------+-----+------+
|      1 |  1901 |    90 |   1 |   90 |
|      2 |  1901 |    90 |   1 |    0 |
|      3 |  1901 |    83 |   2 |   -7 |
|      4 |  1901 |    60 |   3 |  -23 |
|      7 |  1902 |    99 |   1 |   99 |
|      9 |  1902 |    87 |   2 |  -12 |
|      8 |  1902 |    67 |   3 |  -20 |
+--------+-------+-------+-----+------+
7 rows in set (0.00 sec)
二、某APP每天访问数据存放在表access_log里面
日期字段用户类型用户账号用户访问时间
dsuser_typeuser_idlog_time

1、每天整体的访问UV、PV
UV为访问人数，去UV时需按照用户去重
PV为访问次数（同一用户访问10次，PV加10）
如果ds为年月日时分秒的显示方式

SELECT DATE(ds),COUNT(DISTINCT userid) AS UV,COUNT(1) AS PV
FROM access_log
GROUP BY DATE(ds);

2、每天每个类型的访问UV、PV

SELECT DATE(ds),user_type,COUNT(DISTINCT userid) AS UV,COUNT(1) AS PV
FROM access_log
GROUP BY DATE(ds),user_type;

3、每天每个类型中最早访问时间和最晚访问时间
使用FRIST() OVER() \ LAST() OVER()窗口函数

SELECT DATE(ds),user_type
	   FIRST(log_time) OVER(PARTITION BY ds,user_type ORDER BY log_time) AS '最早访问时间'，
	   LAST(log_time) OVER(PARTITION BY ds,user_type ORDER BY log_time) AS '最晚访问时间'
FROM access_log;

4、每天每个类型中访问次数最高的10个用户
（1)每天每个类型中 各用户的访问次数

SELECT DATE(ds),user_type,user_id,COUNT(1) AS co
FROM access_log
GROUP BY DATE(ds),user_type,user_id;

(2)取出每天每个类型中访问次数为前10的用户
使用ROW_NUMBER() OVER()
使用WHERE条件筛选出排序<=10的

SELECT user_id,rn
FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY DATE(ds),user_type ORDER BY co) AS rn
	  FROM (SELECT DATE(ds),user_type,user_id,COUNT(1) AS co
	 		FROM access_log
	  		GROUP BY DATE(ds),user_type,user_id) t1 ) t2
WHERE rn<=10;
三、部门前3高的工资
四、男女各自第一名

（0:男，1:女）
1、创建表

CREATE TABLE IF NOT EXISTS score(
id INT,
sex INT,
chinese_s INT,
math_s INT
);

2、插入数据

INSERT INTO score VALUES(0,0,70,50);
INSERT INTO score VALUES(1,0,90,70);
INSERT INTO score VALUES(2,1,80,90);

(3)逻辑SQL
（a）男女各自语文第一名
INNER JOIN 使用

mysql> SELECT s1.id,s1.sex,s1.chinese_s,s1.math_s
    -> FROM score s1
    -> INNER JOIN (SELECT sex,MAX(chinese_s) AS m_chinese_s
    -> 			   FROM score
    -> 			   GROUP BY sex) s2
    -> ON s1.sex=s2.sex
    -> AND s1.chinese_s=s2.m_chinese_s;
+------+------+-----------+--------+
| id   | sex  | chinese_s | math_s |
+------+------+-----------+--------+
|    1 |    0 |        90 |     70 |
|    2 |    1 |        80 |     90 |
+------+------+-----------+--------+
2 rows in set (0.01 sec)

（b）男生成绩语文大于80，女生数学成绩大于70
UNION使用

mysql> SELECT * FROM score WHERE sex=0 AND chinese_s>80
    -> UNION
    -> SELECT * FROM score WHERE sex=1 AND math_s>70;
+------+------+-----------+--------+
| id   | sex  | chinese_s | math_s |
+------+------+-----------+--------+
|    1 |    0 |        90 |     70 |
|    2 |    1 |        80 |     90 |
+------+------+-----------+--------+
2 rows in set (0.00 sec)
##或者
mysql> SELECT * FROM score WHERE (sex=0 AND chinese_s>80) OR (sex=1 AND math_s>70);
+------+------+-----------+--------+
| id   | sex  | chinese_s | math_s |
+------+------+-----------+--------+
|    1 |    0 |        90 |     70 |
|    2 |    1 |        80 |     90 |
+------+------+-----------+--------+
2 rows in set (0.00 sec)
五、时间/日期排序

用户登录时间表login2，

  • id
  • 姓名name
  • 邮箱地址mail
  • 最后登录时间logintime

生成一张临时表（表名：用户登录表），表中呈现四列数据分别为：姓名，最后登录时间，登录时间排名，登录天数排名

要求：

  1. “登录时间排名”这一列：按时间给出每个人的登录次数，登录时间最早的为1，之后的分别是2，3，4等。
  2. “登录天数排名”这一列：按天给出每个人的登录次数，同一天多次登录认为是同一次，最早标记为1，之后以此类推。

1、创建表

CREATE TABLE IF NOT EXISTS login2(
id INT,
name VARCHAR(10),
mail VARCHAR(50),
logindate TIMESTAMP
);

2、插入数据

INSERT INTO login2 VALUES(99,'name2','mail2@126.com','2020-10-20 16:00'),
						 (13,'name1','mail1@126.com','2020-03-20 15:00'), 
						 (20,'name1','mail1@126.com','2020-10-20 14:13'),
						 (43,'name1','mail1@126.com','2020-10-20 14:20'),
						 (45,'name3','mail3@126.com','2020-04-20 14:17'),
						 (49,'name3','mail3@126.com','2020-05-20 14:22');

3、逻辑SQL
要求分析：

  1. “登录时间排名”这一列：按时间给出每个人的登录次数，登录时间最早的为1，之后的分别是2，3，4等。

    连续排序，无并列名次，ROW_NUMBER() OVER()
每个人：按照用户名name分组   
登录时间越早排序越靠前：登录时间升序ASC，默认即升序

  2. “登录天数排名”这一列：按天给出每个人的登录次数，同一天多次登录认为是同一次，最早标记为1，之后以此类推。

    按天排序：DATE_FORMAT()对logindate类型进行转换 ‘%Y%m%d’ 
连续排序，有并列名次，DENSE_RANK()  OVER() 
每个人：按照用户名name分组 
登录时间越早排序越靠前：登录时间升序ASC，默认即升序

mysql> SELECT name AS '姓名',
    ->    logindate AS '最后登录时间',
    ->    ROW_NUMBER() OVER(PARTITION BY name ORDER BY logindate) AS '登录时间排名',
    ->    DENSE_RANK() OVER(PARTITION BY name ORDER BY DATE_FORMAT(logindate,'%Y%m%d')) AS '登录天数排名'
    -> FROM login2;
+--------+---------------------+--------------------+--------------------+
| 姓名   | 最后登录时间        | 登录时间排名       | 登录天数排名       |
+--------+---------------------+--------------------+--------------------+
| name1  | 2020-03-20 15:00:00 |                  1 |                  1 |
| name1  | 2020-10-20 14:13:00 |                  2 |                  2 |
| name1  | 2020-10-20 14:20:00 |                  3 |                  2 |
| name2  | 2020-10-20 16:00:00 |                  1 |                  1 |
| name3  | 2020-04-20 14:17:00 |                  1 |                  1 |
| name3  | 2020-05-20 14:22:00 |                  2 |                  2 |
+--------+---------------------+--------------------+--------------------+
6 rows in set (0.00 sec)
惜木兮
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL-根据时间筛选前十条数据
qq_42005256的博客
09-20 1184
SQL-根据时间筛选前十条数据。
hive top10 sql简化实现方法
tanhaidi的博客
07-15 2157
最近接手一个项目，好多报表都要去top10展示，在写sql的时候发现需要一层套一层，一层套一层，至少需要套三层以上；然后就在想，这个sql能不能精简一下？看着更加清晰一些呢？ 我们来看一个简单的例子，表数据如下： 需求是看城市用户数的top10 按正常的思路，是先按城市分组统计人数，然后用开窗函数做排名，再然后取排名前10。 通过这个思路写出的sql SELECT cityname ,cnt FROM ( SELECT cityname ,cnt ,row_n
SQL分组后取topN
qq_56870570的博客
08-31 404
求每门课程成绩前两人。
查找排名（窗口函数）
云海的博客
05-06 519
一、问题： 有一个薪水表salaries简况如下: CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`)); 现要求对所有员工的薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序...
Mysql获取每组前N条记录（开窗函数）
smile_life_的博客
09-18 3050
由于项目需求，我需要把查询出来的数据进行分组，并且只需要每组前三条记录，后来了解到MySQL实现开窗函数的方法，用了之后发现是可以，但今天发现这个方法在Navicat Premium软件中运行第一次所查询出来的数据不符合，运行第二次才是符合要求，具体原因有空再了解。后来发现以下方法可以实现我的需求，万分感谢，很6 转：https://blog.csdn.net/wzy_1988/article/d...
开窗函数总结
m0_54423474的博客
06-30 7760
4.2.1，表的数据 4.2.3，开窗函数查询 1，结果如下： 2，结果如下，可以参照这个结果进行理解rows和range的区别 3，结果如下，可以用于获取当前数据行的 上次登录时间 的需求 4，结果如下，结合lead()函数 可以获取用户 上次登录时间与下次登录时间的 需求 5，结果如下，可以用于指定时间内最新或最旧数据的需求。 6，结果如下，可用于求比例的需求 7，结果如下： 7，结果如下： 8，结果如下 9，结果如下： ,10，结果
SQL窗口函数
viccwt的博客
03-16 111
SQL窗口函数 一、窗口函数应用场景：分组处理数据 二、窗口函数基本语法 <窗口函数> over (partition by <用于分组的字段名> order by <用于排序的字段名>) 上面代码含义为： 首先将数据表根据<用于分组的字段名>分段，并且根据<用于排序的字段名>排序，而后对经过这两步处理的数据表执行<窗口函数> 注：partition by与group by的区别：group by改变了表的行数，经过分组后每
SQLServer中的窗口函数
03-02
SQLServer2012之后对窗口函数进行了极大的加强，但对于很多开发人员来说，对窗口函数却不甚了解，导致了这样强大的功能被浪费，因此本篇文章主要谈一谈SQLServer中窗口函数的概念。窗口函数，也可以被称为OLAP函数或...
SQL窗口函数速查表.pdf
10-30
SQL窗口函数是数据库查询中的一个强大工具，它们允许在单个查询中对数据集进行复杂的分析，而无需多次聚合或子查询。窗口函数的核心概念在于“窗口”——它定义了一个行集，在这个集上执行计算，而不是在整个结果集...
SQL2005 学习笔记 窗口函数(OVER)
09-11
【SQL2005窗口函数】是SQL Server 2005引入的一种强大的数据分析工具，它允许用户在结果集的“窗口”上进行计算，而不仅仅是单行或整个结果集。这种函数使得处理复杂的统计计算变得更为简便，比如累计求和、移动平均...
SQL中窗口函数的使用
07-31
窗口函数是基于一个滑动窗口，也就是与当前行相关的一组数据行计算出一个结果。 本文章通过图文形式介绍了常用的取值窗口函数和排名窗口函数。 取值窗口函数：lag和lead函数不支持动态的窗口大小，而是以整个分区...
Hive 窗口函数大全
suly_liping的博客
01-03 2970
hive的窗口函数使用集合和使用示例
SQL明细数据排名,取前TOP10
黄可乐的博客
03-16 3527
根据指定维度构造排名,取出该维度前10
SQL开窗函数之percent_rank、first_value、nth的用法
weixin_46599926的博客
12-11 3563
本文主要讲解了SQL开窗函数中percent_rank(), cume_dist(), first_value(), last_value(), nth_value(), ntile() 一共六个函数的用法
SQL成绩统计分析—排名问题
热门推荐
NoBug Blog
07-18 2万+
ROW_NUMBER()，RANK()，DENSE_RANK()，NTILE()最近写了近半个月的成绩统计服务 ， 被SQL折磨半死，下面介绍下在SQL成绩统计分析中遇到的一些小问题，首先介绍下成绩分析中经常遇到的问题 就是排名问题，这里就不得不提到SQL中的ROW_NUMBER()，RANK()，DENSE_RANK()，NTILE()这四个好兄弟了，
MYSQL取表中分数排名前10%的学生信息
Derick的博客
06-16 5234
MYSQL取表中分数排名前10%的学生信息 SELECT `name` FROM( select table.`name`, @row_num:=@row_num+1 as row_num from table, (SELECT @row_num:=1) as b ORDER BY score DESC ) new_table where new_table.row_num<=@row_num*0.1; 使用变量@row_num，先以score排序，然后添加排名列（@row_num:=@row_
133.Spark大型电商项目-用户活跃度分析模块-统计指定时间内访问次数最多的10个用户
someby的博客
08-05 1338
本篇文章记录统计指定时间内访问次数最多的10个用户，使用SparkSession来分析数据。 代码 UserActiveDegreeAnalyze.scala package graduation.scala.spark import org.apache.spark.SparkConf import org.apache.spark.sql.SparkSession /** ...
SQL | UPDATE | 将所有获取奖金的员工当前的薪水增加10%
weixin_46023346的博客
09-08 3712
update在需要多表连接时的使用
sql server 窗口函数
最新发布
05-10
SQL Server 窗口函数是一种特殊的函数，它可以用来在一个查询的结果集中执行聚合计算、排序、排名等操作。与普通的聚合函数不同的是，窗口函数可以对每个查询结果行单独进行计算，而不是对整个结果集进行计算。 SQL Server 支持多种窗口函数，包括 ROW_NUMBER、RANK、DENSE_RANK、NTILE、LAG、LEAD、FIRST_VALUE 和 LAST_VALUE 等。这些窗口函数可以通过 OVER 子句进行调用，并且可以根据需要指定分区（PARTITION BY）和排序（ORDER BY）规则。 举个例子，假设我们有一个订单表 orders，其中包含订单编号、客户名称和订单金额等信息。我们可以使用窗口函数来计算每个客户的订单金额排名： ``` SELECT order_id, customer_name, order_amount, RANK() OVER (PARTITION BY customer_name ORDER BY order_amount DESC) AS rank FROM orders; ``` 这条 SQL 语句会返回一个结果集，其中包含每个订单的编号、客户名称、订单金额和该客户的订单金额排名。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值