ROW_NUMBER() OVER()函数用法详解 （分组排序 例子多）

最新推荐文章于 2022-12-01 16:24:14 发布
最新推荐文章于 2022-12-01 16:24:14 发布
阅读量1.3k 收藏
点赞数
分类专栏： 大数据 数据分析 文章标签： hive 大数据 row_nuber()over()

转载自：https://blog.csdn.net/qq_25221835/article/details/82762416，感谢作者的分享

原

ROW_NUMBER() OVER()函数用法详解 （分组排序 例子多）

2018年09月18日 19:11:38 阅读数：6915 
																														</div>
			<div class="operating">
								</div>
		</div>
	</div>
</div>
<article class="baidu_pl">
	<div id="article_content" class="article_content clearfix csdn-tracking-statistics" data-pid="blog" data-mod="popu_307" data-dsm="post">
							<div class="article-copyright">
              					<svg class="icon" title="CSDN认证原创" aria-hidden="true" style="width:53px; height: 18px; vertical-align: -4px;">
						<use xlink:href="#CSDN_Cert"></use>
				</svg>
              					
				版权声明：我最大的幸福，是你的博客与我有关					https://blog.csdn.net/qq_25221835/article/details/82762416				</div>
							            <link rel="stylesheet" href="https://csdnimg.cn/release/phoenix/template/css/ck_htmledit_views-f57960eb32.css">
					<div class="htmledit_views" id="content_views">
            <p><span style="color:#7c79e5;">语法格式：row_number() over(partition by 分组列 order by&nbsp;排序列 desc)</span></p>

row_number() over()分组排序功能：

在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、  order by 的执行。

例一：

表数据：


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     create 
     
     table TEST_ROW_NUMBER_OVER(
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
           
     
     id 
     
     varchar(
     
     10) 
     
     not 
     
     null,
    
    
    
   
   
  3. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
           
     
     name 
     
     varchar(
     
     10) 
     
     null,
    
    
    
   
   
  4. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
            age 
     
     varchar(
     
     10) 
     
     null,
    
    
    
   
   
  5. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
            salary 
     
     int 
     
     null
    
    
    
   
   
  6. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     );
    
    
    
   
   
  7. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     select * 
     
     from TEST_ROW_NUMBER_OVER t;
    
    
    
   
   
  8. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
     
    
    
    
   
   
  9. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     insert 
     
     into TEST_ROW_NUMBER_OVER(
     
     id,
     
     name,age,salary) 
     
     values(
     
     1,
     
     'a',
     
     10,
     
     8000);
    
    
    
   
   
  10. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     insert 
     
     into TEST_ROW_NUMBER_OVER(
     
     id,
     
     name,age,salary) 
     
     values(
     
     1,
     
     'a2',
     
     11,
     
     6500);
    
    
    
   
   
  11. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     insert 
     
     into TEST_ROW_NUMBER_OVER(
     
     id,
     
     name,age,salary) 
     
     values(
     
     2,
     
     'b',
     
     12,
     
     13000);
    
    
    
   
   
  12. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     insert 
     
     into TEST_ROW_NUMBER_OVER(
     
     id,
     
     name,age,salary) 
     
     values(
     
     2,
     
     'b2',
     
     13,
     
     4500);
    
    
    
   
   
  13. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     insert 
     
     into TEST_ROW_NUMBER_OVER(
     
     id,
     
     name,age,salary) 
     
     values(
     
     3,
     
     'c',
     
     14,
     
     3000);
    
    
    
   
   
  14. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     insert 
     
     into TEST_ROW_NUMBER_OVER(
     
     id,
     
     name,age,salary) 
     
     values(
     
     3,
     
     'c2',
     
     15,
     
     20000);
    
    
    
   
   
  15. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     insert 
     
     into TEST_ROW_NUMBER_OVER(
     
     id,
     
     name,age,salary) 
     
     values(
     
     4,
     
     'd',
     
     16,
     
     30000);
    
    
    
   
   
  16. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     insert 
     
     into TEST_ROW_NUMBER_OVER(
     
     id,
     
     name,age,salary) 
     
     values(
     
     5,
     
     'd2',
     
     17,
     
     1800);

一次排序：对查询结果进行排序（无分组）


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     select 
     
     id,
     
     name,age,salary,row_number()
     
     over(
     
     order 
     
     by salary 
     
     desc) rn
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     from TEST_ROW_NUMBER_OVER t

结果：

进一步排序：根据id分组排序


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     select 
     
     id,
     
     name,age,salary,row_number()
     
     over(
     
     partition 
     
     by 
     
     id 
     
     order 
     
     by salary 
     
     desc) 
     
     rank
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     from TEST_ROW_NUMBER_OVER t

结果：

 再一次排序：找出每一组中序号为一的数据


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     select * 
     
     from(
     
     select 
     
     id,
     
     name,age,salary,row_number()
     
     over(
     
     partition 
     
     by 
     
     id 
     
     order 
     
     by salary 
     
     desc) 
     
     rank
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     from TEST_ROW_NUMBER_OVER t)
    
    
    
   
   
  3. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     where 
     
     rank <
     
     2

结果：

排序找出年龄在13岁到16岁数据，按salary排序


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     select 
     
     id,
     
     name,age,salary,row_number()
     
     over(
     
     order 
     
     by salary 
     
     desc)  
     
     rank
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     from TEST_ROW_NUMBER_OVER t 
     
     where age 
     
     between 
     
     '13' 
     
     and 
     
     '16'

结果：结果中 rank 的序号，其实就表明了 over(order by salary desc) 是在where age between and 后执行的

例二：

1.使用row_number（）函数进行编号，如

select email,customerID, ROW_NUMBER() over(order by psd) as rows from QT_Customer

原理：先按psd进行排序，排序完后，给每条数据进行编号。

2.在订单中按价格的升序进行排序，并给每条记录进行排序代码如下：

select DID,customerID,totalPrice,ROW_NUMBER() over(order by totalPrice) as rows from OP_Order

3.统计出每一个各户的所有订单并按每一个客户下的订单的金额 升序排序，同时给每一个客户的订单进行编号。这样就知道每个客户下几单了：


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     select ROW_NUMBER() 
     
     over(
     
     partition 
     
     by customerID  
     
     order 
     
     by totalPrice)
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
     
     
     as 
     
     rows,customerID,totalPrice, DID 
     
     from OP_Order

4.统计每一个客户最近下的订单是第几次下的订单：


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     with tabs as  
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     (  
    
    
    
   
   
  3. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     select ROW_NUMBER() 
     
     over(
     
     partition 
     
     by customerID  
     
     order 
     
     by totalPrice)
    
    
    
   
   
  4. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
     
     
     as 
     
     rows,customerID,totalPrice, DID 
     
     from OP_Order  
    
    
    
   
   
  5. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
      )  
    
    
    
   
   
  6. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     select 
     
     MAX(
     
     rows) 
     
     as 
     
     '下单次数',customerID 
     
     from tabs 
    
    
    
   
   
  7. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     group 
     
     by customerID

5.统计每一个客户所有的订单中购买的金额最小，而且并统计改订单中，客户是第几次购买的：

思路：利用临时表来执行这一操作。

1.先按客户进行分组，然后按客户的下单的时间进行排序，并进行编号。

2.然后利用子查询查找出每一个客户购买时的最小价格。

3.根据查找出每一个客户的最小价格来查找相应的记录。


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         with tabs as  
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
          (  
    
    
    
   
   
  3. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
        
     
     select ROW_NUMBER() 
     
     over(
     
     partition 
     
     by customerID  
     
     order 
     
     by insDT) 
    
    
    
   
   
  4. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     as 
     
     rows,customerID,totalPrice, DID 
     
     from OP_Order  
    
    
    
   
   
  5. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         )  
    
    
    
   
   
  6. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
         
     
     select * 
     
     from tabs  
    
    
    
   
   
  7. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
        
     
     where totalPrice 
     
     in   
    
    
    
   
   
  8. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         (  
    
    
    
   
   
  9. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
        
     
     select 
     
     MIN(totalPrice)
     
     from tabs 
     
     group 
     
     by customerID  
    
    
    
   
   
  10. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
          )

6.筛选出客户第一次下的订单。

思路。利用rows=1来查询客户第一次下的订单记录。


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         with tabs as  
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         (  
    
    
    
   
   
  3. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
        
     
     select ROW_NUMBER() 
     
     over(
     
     partition 
     
     by customerID  
     
     order 
     
     by insDT) 
     
     as 
     
     rows,* 
     
     from OP_Order  
    
    
    
   
   
  4. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         )  
    
    
    
   
   
  5. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
        
     
     select * 
     
     from tabs 
     
     where 
     
     rows = 
     
     1 
    
    
    
   
   
  6. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
        
     
     select * 
     
     from OP_Order

7.注意：在使用over等开窗函数时，over里头的分组及排序的执行晚于“where，group by，order by”的执行。


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
        
     
     select   
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         ROW_NUMBER() 
     
     over(
     
     partition 
     
     by customerID  
     
     order 
     
     by insDT) 
     
     as 
     
     rows,  
    
    
    
   
   
  3. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         customerID,totalPrice, DID  
    
    
    
   
   
  4. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
        
     
     from OP_Order 
     
     where insDT>
     
     '2011-07-22'

 

奔跑的乌班
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL Server中row_number分页查询的用法详解
01-19
在查询时应用了一个排序标准后，只有通过编号才能够保证其顺序是一致的，当使用ROW_NUMBER函数时，也需要专门一列用于预先排序以便于进行编号。 ROW_NUMBER() 说明：返回结果集分区内行的序列号，每个分区的第一行从...
Sql Server 开窗函数Over()的使用实例详解
09-09
Sql Server的开窗函数Over()是数据库查询中的一个重要工具，它允许我们在不使用Group By的情况下对数据进行分组处理，并可以结合聚合函数进行复杂的计算。本文将深入探讨Over()函数的使用实例及其在排名和聚合方面的...
SQL--解释：ROW_NUMBER()OVER(PARTITION BY 字段名 ORDER BY字段名 DESC )
Mimo_YY的博客
05-07 791
ROW_NUMBER() OVER (ORDER BY name DESC) 注：是先把name列降序，再为降序以后的每条name记录返回一个序号。 引用：博客园：分组函数 partition by 的详解，与order by 区别 partition by关键字是分析性函数的一部分，它和聚合函数（如group by）不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录， partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组。 partit
ROW_NUMBER() OVER(partition by 分组列 order by 排序列)
woshimyc的专栏
04-26 1129
多用于对数据的快速排序和分组，也可用于分页，简单的逻辑判断。 先创建一个测试用表和数据。 -- Create table create table Test_Orders ( id NUMBER(19) not null, User_Id VARCHAR2(50 CHAR), Total_Price F...
分区函数Partition By的与row_number()的用法以及与排序rank()的用法详解(获取分组(分区)中前几条记录)（转）...
weixin_33704591的博客
06-29 398
转载地址：http://www.cnblogs.com/linJie1930906722/p/6036053.html partition by关键字是分析性函数的一部分，它和聚合函数不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录，partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组，分区函数一般与排名函数一起使用。 准备测试...
Oracle的ROW_NUMBER() OVER()函数
ConstXiong
03-20 1万+
ROW_NUMBER() OVER() 函数的作用：分组排序 原理：row_number() over() 函数，over() 里的分组以及排序的执行晚于 where、group by、order by 的执行。 语法：row_number() over( partition by 分组列 order by 排序列 desc ) 实践： 建立学生数据表 create tab...
SQL Server中row_number函数的常见用法示例详解
12-14
一.SQL Server Row_number函数简介 ROW_NUMBER()是一个Window函数，它为结果集的分区中的每一行分配一个连续的整数。 行号以每个分区中第一行的行号开头。 以下是ROW_NUMBER()函数的语法实例： select *，row_...
SQL Server 开窗函数 Over()代替游标的使用详解
12-14
1、与Row_Number() 函数结合使用，对结果进行排序，这个是我们使用的非常多的 　 2、与聚合函数结合使用，利用over子句的分组和排序，对需要的数据进行操作 例如：SUM() Over() 累加值、AVG() Over() 平均数 MAX() ...
Mysql实现Oracle中row_number ( ) over ( partition BY ORDER BY DESC )
qq_33883124的博客
05-13 1080
Mysql实现Oracle中row_number ( ) over ( partition BY 字段1 ORDER BY 字段2 DESC ) 例表： 案例1，row_number ( ) over (ORDER BY 字段2 DESC ) Oracle Mysql SELECT b.*, row_number ( ) over ( ORDER BY PRICE DESC ) AS rk ...
row_number() over(partition by order by)
tanghuan0827的博客
12-01 684
row_number() over(partition by 列名1 order by 列名2 desc)的使用 微末6662018-06-04 21:52:1833746收藏17 分类专栏：oracle 表示根据列名1分组，然后在分组内部根据列名2排序，而此函数计算的值就表示每组内部排序后的顺序编号,可以用于去重复值 与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码． ...
sql函数--04---ROW_NUMBER() OVER()函数用法详解
时光里的博客
10-26 1万+
ROW_NUMBER() OVER()函数 使用范围: 这个函数可以Oracle中使用 MySQL用不了 语法格式： row_number() over(partition by 分组列 order by 排序列 desc) 注意: 在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。 案例 1 表数据： create table TEST_ROW_NUMBER_OVER(
row_number() OVER(PARTITION BY)
热门推荐
あずにゃん梓喵的博客
03-12 12万+
row_number()排序函数 统计每个部门薪资最高的员工信息（同一个部门的员工按照薪资进行降序排序） 第一种写法：row_number() over(partition by 一个或多个分组列 order by 一个或多个排序列 asc/desc) as 别名 //如果不写asc/desc的话，默认为asc 第二种写法：row_number() over...
Oracle中ROW_NUMBER() OVER()函数用法
tnb_ml的博客
09-26 2万+
row_number() over() 函数，over() 里的分组以及排序的执行晚于 where、group by、order by 的执行。row_number() over( partition by 分组列 order by 排序列 desc )3. 获取到每个班级分数排名第一的学生。根据工资排序并添加序号。
row_number(),over()函数组合使用排序取最新一条
枯萎花一朵
12-01 959
partition by 用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组，它和聚合函数不同的地方在于它能够返回一个分组中的多条记录，而聚合函数一般只有一个反映统计值的记录。
java代码实现ROW_NUMBER ( ) over ( PARTITION BY）语句
qq_37084960的博客
08-26 1135
对List根据某两个字段进行分组排序，排序字段·的类型为Date，即java代码实现ROW_NUMBER ( ) over ( PARTITION BY）语句 public <T> List<T> groupByOrder(List<T> originalList, String groupName, String orderName) throws ...
Oracle使用 row_number() 出现ORA-00923:未找到要求的from 字段
05-26 1164
原语句 select *,row_number() over( order by 'updtime' desc) clm1 from table_name 修改后的语句 select a.*,row_number() over( order by 'updtime' desc) clm1 from table_name a
Oracle之ROW_NUMBER() OVER() 分组排序函数
张大安的博客
02-26 2108
文章目录ROW_NUMBER() OVER() 分组排序 ROW_NUMBER() OVER() 分组排序 语法： row_number() over(partition by 分组列 order by 排序列 desc) rn 注意：在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。 常见需求：根据某个字段分组取出最新的一条数据。 原数据： SELECT rir.id, rir.IND
ROW_NUMBER() OVER()函数详解
最新发布
09-14
ROW_NUMBER() OVER()函数是一种用于在查询结果中为每一行分配一个唯一的数字序号的窗口函数。它可以在ORDER BY子句中定义排序规则，并且可以根据指定的PARTITION BY子句对结果进行分组。 该函数的语法是： ROW_NUMBER() OVER ([PARTITION BY expression1 [, expression2, ...]] ORDER BY expression [ASC | DESC] [, expression [ASC | DESC]] ...) 其中，PARTITION BY子句定义了分组的依据，可以按照一个或多个表达式进行分组。ORDER BY子句定义了排序规则，可以按照一个或多个表达式进行排序。 在使用ROW_NUMBER() OVER()函数的时候，其中的分组以及排序的执行是在WHERE、GROUP BY和ORDER BY之后进行的。这意味着，先会根据WHERE子句进行过滤，然后根据GROUP BY子句进行分组，最后根据ORDER BY子句进行排序。然后，ROW_NUMBER()函数会按照指定的排序规则对每一行进行编号，生成唯一的数字序号。 举个例子，假设有一个名为TEST_ROW_NUMBER_OVER的表，包含id、name、age和salary字段。我们可以使用以下查询语句来使用ROW_NUMBER() OVER()函数为每一行分配一个唯一的序号： SELECT id, name, age, salary, ROW_NUMBER() OVER(ORDER BY salary DESC) AS rn FROM TEST_ROW_NUMBER_OVER 在这个例子中，我们对salary字段进行降序排序，并使用ROW_NUMBER()函数为每一行分配一个唯一的序号。最后的结果集中包含id、name、age、salary和rn字段，其中rn字段存储了每一行的序号。 除了上述的基本用法外，ROW_NUMBER() OVER()函数还可以与其他窗口函数一起使用，例如SUM()、AVG()等，以对结果集进行更复杂的计算和分析。 总结起来，ROW_NUMBER() OVER()函数是一种用于为查询结果中的每一行分配唯一序号的窗口函数，可以根据指定的排序规则对结果进行排序，并且可以根据指定的分组规则对结果进行分组。它的执行顺序是在WHERE、GROUP BY和ORDER BY之后。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值