SQLServer中Partition By及row_number 函数使用详解
partition by关键字是分析性函数的一部分,它和聚合函数不同的地方在于它能返回一个分组中的多条记录,而聚合函数一般只有一条反映统计值的记录,partition by用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组。
今天群里看到一个问题,在这里概述下:查询出不同分类下的最新记录。一看这不是很简单的么,要分类那就用Group By;要最新记录就用Order By呗。然后在自己的表中试着做出来:
首先呢我把表中的数据按照提交时间倒序出来:
“corp_name”就是分类的GUID(请原谅我命名的随意性)。 OK, 这里按照最开始的想法加上Group By来看一下显示效果:
呃,嗯。这尼玛和想象中的结果不一样啊,看来写代码还是要理性分析问题,意念是无法控制结果滴!
既然要求是不同分类的数据,除了使用Group By之外,还有别的函数能用吗?度娘了一下结果还真有,over(partition by )函数,那么它和平时用的Group By有什么区别呢? Group By除了对结果进行单纯的分组之外呢,一般都和聚合函数一起使用,Partition By也具有分组功能,属于Oracle的分析函数,在这里就不详细的不啦不啦不啦了。
看代码:
over(partition by corp_name order by submit_time desc ) as t 。就是按照corp_name分类并按时间倒序出来, t 这里一列呢就是不同corp_name类出现的次数,需求是只查询出不同分类的最新提交数据,那么我们只需要针对 t 再进行一次筛选即可:
好啦,结果已经出来,不求各位看官喜欢,但求看在我头像中的胸器望点个赞, 好人一生平安哦!!!
ps:SQL Server数据库partition by 与ROW_NUMBER()函数使用详解
关于SQL的partition by 字段的一些用法心得
先看例子:
if object_id( TESTDB ) is not null drop table TESTDB
create table TESTDB(A varchar(8), B varchar(8))
insert into TESTDB
select A1 , B1 union all
select A1 , B2 union all
select A1 , B3 union all
select A2 , B4 union all
select A2 , B5 union all
select A2 , B6 union all
select A3 , B7 union all
select A3 , B3 union all
select A3 , B4
所有的信息
SELECT * FROM TESTDB
A B
-
A1 B1
A1 B2
A1 B3
A2 B4
A2 B5
A2 B6
A3 B7
A3 B3
A3 B4
使用PARTITION BY 函数后
SELECT *,ROW_NUMBER() OVER(PARTITION BY A ORDER BY A DESC) NUM FROM TESTDB
A B NUM
-
A1 B1 1
A1 B2 2
A1 B3 3
A2 B4 1
A2 B5 2
A2 B6 3
A3 B7 1
A3 B3 2
A3 B4 3
可以看到结果中多出一列NUM 这个NUM就是说明了相同行的个数,比如A1有3个,他就给每个A1标上是第几个。
仅仅使用ROW_NUMBER() OVER的结果
SELECT *,ROW_NUMBER() OVER(ORDER BY A DESC)NUM FROM TESTDB
A B NUM
A3 B7 1
A3 B3 2
A3 B4 3
A2 B4 4
A2 B5 5
A2 B6 6
A1 B1 7
A1 B2 8
A1 B3 9
可以看到它只是单纯标出了行号。
深入一点应用
SELECT A = CASE WHEN NUM = 1 THEN A ELSE END,B
FROM (SELECT A,NUM = ROW_NUMBER() OVER(PARTITION BY A ORDER BY A DESC) FROM TESTDB) T
A B
A1 B1
B2
B3
A2 B4
B5
B6
A3 B7
B3
B4
接下来我们就通过几个实例来一一介绍ROW_NUMBER()函数的使用。
实例如下:
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.统计出每一个各户的所有订单并按每一个客户下的订单的金额 升序排序,同时给每一个客户的订单进行编号。这样就知道每个客户下几单了。
如图:
代码如下:
select ROW_NUMBER() over(partition by customerID order by totalPrice) as rows,customerID,totalPrice, DID from OP_Order
4.统计每一个客户最近下的订单是第几次下的订单。
代码如下:
with tabs as
(
select ROW_NUMBER() over(partition by customerID order by totalPrice) as rows,customerID,totalPrice, DID from OP_Order
)
select MAX(rows) as 下单次数 ,customerID from tabs group by customerID
5.统计每一个客户所有的订单中购买的金额最小,而且并统计改订单中,客户是第几次购买的。
如图:
上图:rows表示客户是第几次购买。
思路:利用临时表来执行这一操作。
1.先按客户进行分组,然后按客户的下单的时间进行排序,并进行编号。
2.然后利用子查询查找出每一个客户购买时的最小价格。
3.根据查找出每一个客户的最小价格来查找相应的记录。
代码如下:
with tabs as
(
select ROW_NUMBER() over(partition by customerID order by insDT) as rows,customerID,totalPrice, DID from OP_Order
)
select * from tabs
where totalPrice in
(
select MIN(totalPrice)from tabs group by customerID
)
6.筛选出客户第一次下的订单。
思路。利用rows=1来查询客户第一次下的订单记录。
代码如下:
with tabs as
(
select ROW_NUMBER() over(partition by customerID order by insDT) as rows,* from OP_Order
)
select * from tabs where rows = 1
select * from OP_Order
7.rows_number()可用于分页
思路:先把所有的产品筛选出来,然后对这些产品进行编号。然后在where子句中进行过滤。
8.注意:在使用over等开窗函数时,over里头的分组及排序的执行晚于“where,group by,order by”的执行。
如下代码:
select
ROW_NUMBER() over(partition by customerID order by insDT) as rows,
customerID,totalPrice, DID
from OP_Order where insDT 2011-07-22
以上代码是先执行where子句,执行完后,再给每一条记录进行编号。
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 SQLServer中Partition By及row_number 函数使用详解
相关文章
- VB语言使用ADO连接、操作SQLServer数据库教程
- SQLServer 错误 2518 对象 ID O_ID (对象”O_NAME”):由于禁用了公共语言运行时(CLR),无法检查此对象的计算列和用户定义类型。 故障 处理 修复 支持远程
- SQLServer 错误 26014 无法加载用户指定的证书 [Cert Hash(sha1) “%hs”]。 服务器将不接受连接。 您应该验证是否正确安装了证书。 请参阅联机丛书中的“配置证书以供 SSL 使用”。 故障 处理 修复 支持远程
- 使用SqlServer构建音乐库(sqlserver音乐库)
- 外如何正确使用SQLServer除级联之外的其他操作(sqlserver除级联)
- SQLServer遭受被锁定困扰(sqlserver 被锁)
- 使用SQLServer组合让想象变为现实(sqlserver组合框)
- 使用SQL Server提升算法性能的方法(sqlserver算法)
- 使用SQLServer记录第一次经历(sqlserver第一条)
- 使用SQLServer的架构构建你的梦想应用(sqlserver的架包)
- 清华大学使用SQLServer解决数据库问题(sqlserver清华)
- 使用SQLServer快速操作表(sqlserver操作表)
- SQLServer数据库扩展功能探索(sqlserver扩展库)
- 字符SQLServer实现字符串截取的技巧(sqlserver 截取)
- 存储过程如何使用SQLServer编写专业的存储过程(sqlserver怎么写)
- 使用SQLServer解决堵塞问题(sqlserver 堵塞)
- 版使用SQLServer单机版的技术优势(sqlserver单机)
- 十年:SQLServer:三十载昔日,技术演进可歌可泣(sqlserver 前三)
- 在SQLServer中优雅使用列变量(sqlserver列变量)
- 使用SQLserver实现高效字词搜索(sqlserver 分词)
- 精准管理:使用SQLServer分区优化数据库存储(sqlserver分区间)
- 都能做SqlServer:你梦想的助力(sqlserver 什么)
- 写使用 SQL Server 助力数据库管理(sqlserver 书)
- 使用SQLServer实现数据库分页查询(sqlserver.分页)
- sqlserver局部变量的使用
- SQLServer误区30日谈第13天在SQLServer2000兼容模式下不能使用DMV
- SQLServer高级内容之子查询和表链接概述及使用
- SqlServer使用cursor处理重复数据过程详解