SQL Server 分组后取Top N-阿里云开发者社区

开发者社区> maojunxu> 正文

SQL Server 分组后取Top N

简介: 近日,工作中突遇一需求:将一数据表分组,而后取出每组内按一定规则排列的前N条数据。乍想来,这本是寻常查询,无甚难处。可提笔写来,终究是困住了笔者好一会儿。冥思苦想,遍查网络,不曾想这竟然是SQL界的一个经典话题。
+关注继续查看

近日,工作中突遇一需求:将一数据表分组,而后取出每组内按一定规则排列的前N条数据。乍想来,这本是寻常查询,无甚难处。可提笔写来,终究是困住了笔者好一会儿。冥思苦想,遍查网络,不曾想这竟然是SQL界的一个经典话题。今日将我得来的若干方法列出,抛砖引玉,以期与众位探讨。

  正文之前,对示例表结构加以说明。

                    表SectionTransactionLog,用来记录各部门各项活动的日志表
                     SectionId,部门Id
                     SectionTransactionType,活动类型
                     TotalTransactionValue,活动花费
                     TransactionDate,活动时间

  我们设定的场景为:选出每部门(SectionId)最近两次举行的活动。

  笔者用来测试的SectionTransactionLog表中数据超3,000,000。

一、 嵌套子查询方式

1

1 SELECT * FROM SectionTransactionLog mLog
2 where 
3     (select COUNT(*) from SectionTransactionLog subLog
4     where subLog.SectionId = mLog.SectionId and subLog.TransactionDate >= mLog.TransactionDate)<=2
5 order by SectionId, TransactionDate desc

  运行时间:34秒

  该方式原理较简单,只是在子查询中确定该条记录是否是其Section中新近发生的2条之一。

2

复制代码
1 SELECT * FROM SectionTransactionLog mLog
2 where mLog.Id in
3     (select top 2 Id 
4     from SectionTransactionLog subLog
5     where subLog.SectionId = mLog.SectionId
6     order by TransactionDate desc)
7 order by SectionId, TransactionDate desc
复制代码

  运行时间:1分25秒

  在子查询中使用TransactionDate排序,取top 2。并应用in关键字确定记录是否符合该子查询。

二、 自联接方式

复制代码
1 select mLog.* from SectionTransactionLog mLog
2 inner join
3 (SELECT rankLeft.Id, COUNT(*) as rankNum FROM SectionTransactionLog rankLeft
4 inner join SectionTransactionLog rankRight 
5 on rankLeft.SectionId = rankRight.SectionId and rankLeft.TransactionDate <= rankRight.TransactionDate
6 group by rankLeft.Id
7 having COUNT(*) <= 2) subLog on mLog.Id = subLog.Id
8 order by mLog.SectionId, mLog.TransactionDate desc
复制代码

  运行时间:56秒

  该实现方式较为巧妙,但较之之前方法也稍显复杂。其中,以SectionTransactionLog表自联接为基础而构造出的subLog部分为每一活动(以Id标识)计算出其在Section内部的排序rankNum(按时间TransactionDate)。

  在自联接条件rankLeft.SectionId = rankRight.SectionId and rankLeft.TransactionDate <= rankRight.TransactionDate的筛选下,查询结果中对于某一活动(以Id标识)而言,与其联接的只有同其在一Section并晚于或与其同时发生活动(当然包括其自身)。下图为Id=1的活动自联接示意:

  从上图中一目了然可以看出,基于此结果的count计算,便为Id=1活动在Section 9022中的排次rankNum。

  而后having COUNT(*) <= 2选出排次在2以内的,再做一次联接select出所需信息。

三、 应用ROW_NUMBER()(SQL SERVER 2005及之后)

复制代码
1 select * from
2 (
3 select *, ROW_NUMBER() over(partition by SectionId order by TransactionDate desc) as rowNum
4 from SectionTransactionLog
5 ) ranked
6 where ranked.rowNum <= 2
7 order by ranked.SectionId, ranked.TransactionDate desc
复制代码

  运行时间:20秒

  这是截至目前效率最高的实现方式。ROW_NUMBER() over(partition by SectionId order by TransactionDate desc)完成了分组、排序、取行号的整个过程。

 

效率思考

  下面我们对上述的4种方法做一个效率上的统计。

方法 耗时(秒) 排名
应用ROW_NUMBER() 20 1
嵌套子查询方式1  34 2
自联接方式 56 3
嵌套子查询方式2 85 4

  4种方法中,嵌套子查询2所用时最长,其效率损耗在什么地方了呢?难道果真是使用了in关键字的缘故?下图为其执行计划(execute plan):

 

  从图中,我们可以看出优化器将in解析为了Left Semi Join, 其损耗极低。而该查询绝大部分性能消耗在子查询的order by处(Top N Sort)。果然,若删掉子查询中的order by TransactionDate desc子句(当然结果不正确),其耗时仅为8秒。

  添加有效索引可提高该查询方法的性能。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
7376 0
01.SQLServer性能优化之----强大的文件组----分盘存储
汇总篇:http://www.cnblogs.com/dunitian/p/4822808.html#tsql 文章内容皆自己的理解,如有不足之处欢迎指正~谢谢 前天有学弟问逆天:“逆天,有没有一种方式可以让我一个表存到两个数据库文件中,或者说怎么把一个表的数据平摊到其他数据库文件中?” (⊙o⊙)…,逆天数据库优化不是很强悍,不过类似的情景倒是见过,可以给你一个思路。
813 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
9090 0
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 7 章 查询_7.4. 组合查询
7.4. 组合查询 两个查询的结果可以用集合操作并、交、差进行组合。语法是 query1 UNION [ALL] query2 query1 INTERSECT [ALL] query2 query1 EXCEPT [ALL] query2 query1和query2都是可以使用以上所有特性的查询。
896 0
SQL 单表多条记录分组查询分页代码
SQL表结构如下图: SQL 分页语句: SELECT TOP 5 *,(select count(*) inum from (select distinct AreaID...
575 0
Storm Topology及分组原理
Storm的通信机制,需要满足如下一些条件以满足Storm的语义。 1、建立数据传输的缓冲区。在通信连接没有建立之前把发送的数据缓存起来。数据发送方可以在连接建立之前发送消息,而不需要等连接建立起来,可是的接收方是独立运行的。
644 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
5966 0
+关注
702
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《Nacos架构&原理》
立即下载
《看见新力量:二》电子书
立即下载
云上自动化运维(CloudOps)白皮书
立即下载