SQL Server 分組後取Top N

　　近日，工作中突遇一需求：將一資料表分組，而後取出每組內按一定規則排列的前N條資料。乍想來，這本是尋常查詢，無甚難處。可提筆寫來，終究是困住了筆者好一會兒。冥思苦想，遍查網路，不曾想這竟然是SQL界的一個經典話題。今日將我得來的若干方法列出，拋磚引玉，以期與眾位探討。

　　本文之前，對樣本表結構加以說明。

　　　　　　　　　　　　　　　　　　　 表SectionTransactionLog，用來記錄各部門各項活動的日誌表
　　　　　　　　　　　　　　　　　　　　　SectionId，部門Id
　　　　　　　　　　　　　　　　　　　　　SectionTransactionType，活動類型
　　　　　　　　　　　　　　　　　　　　　TotalTransactionValue，活動花費
　　　　　　　　　　　　　　　　　　　　　TransactionDate，啟用時間

　　我們設定的情境為：選出每部門（SectionId）最近兩次舉行的活動。

　　筆者用來測試的SectionTransactionLog表中資料超3,000,000。

一、 嵌套子查詢方式

1

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

　　已耗用時間：34秒

　　該方式原理較簡單，只是在子查詢中確定該條記錄是否是其Section中新近發生的2條之一。

2

1 SELECT * FROM SectionTransactionLog mLog2 where mLog.Id in3     (select top 2 Id 4     from SectionTransactionLog subLog5     where subLog.SectionId = mLog.SectionId6     order by TransactionDate desc)7 order by SectionId, TransactionDate desc

　　已耗用時間：1分25秒

　　在子查詢中使用TransactionDate排序，取top 2。並應用in關鍵字確定記錄是否符合該子查詢。

二、 自我聯結方式

1 select mLog.* from SectionTransactionLog mLog2 inner join3 (SELECT rankLeft.Id, COUNT(*) as rankNum FROM SectionTransactionLog rankLeft4 inner join SectionTransactionLog rankRight 5 on rankLeft.SectionId = rankRight.SectionId and rankLeft.TransactionDate <= rankRight.TransactionDate6 group by rankLeft.Id7 having COUNT(*) <= 2) subLog on mLog.Id = subLog.Id8 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 * from2 (3 select *, ROW_NUMBER() over(partition by SectionId order by TransactionDate desc) as rowNum4 from SectionTransactionLog5 ) ranked6 where ranked.rowNum <= 27 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秒。

　　添加有效索引可提高該查詢方法的效能。