sql server 学习笔记 ( row_number, rank, dense_rank over partition by order by )

更新: 2021-09-15

customer 1-n orders 想获取最新的 orders 可以使用 cross/outer apply 

之前使用 with cte row number 来实现, 其实还有一个方式是用 cross apply

cross apply 类似 inner join, outer 类似 left join 

区别是它可以实现比较复杂的 join 方式

参考这里: 

https://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join

里面很多好的例子. 

 

 

 

更新: 2019-09-21

 

refer : 

https://stevestedman.com/2013/04/rows-and-range-preceding-and-following/

https://sqlwithmanoj.com/tag/unbounded-preceding/

http://www.kodyaz.com/t-sql/sum-top-n-rows-with-sql-server-sum-aggregation-function-with-over-clause-rows-clause.aspx

https://www.red-gate.com/simple-talk/sql/learn-sql-server/window-functions-in-sql-server-part-2-the-frame/

 

之前说过 row_number() + over 的概念

其实不只是 row_number() 

sum, avg, first_value 这些也都是搭配 over 来用的

sum, avg 通常是搭配 group by 用的, 但是 group by 往往只能有一次

你 group 了 a column 就无法 group b 了

但是如果是用 sum + over 就不同了

因为它是在 select 的地方使用所以可以多种变化

比如 

select sum(qty) over (partition by columnA), sum(qty) over (partition by columnB)

紧跟着还有 rows 的用法

sum(qty) over (partition by columnA rows between current row and 4 following | preceding )

就是说只拿当前的 row 和 之前 | 之后 4 条记入来 sum 

玩法大概就是拿当前这一条和上下几条, 如果是想拿完而不是固定几条

就写 unbounded following | preceding

除了 rows 还有一个 range 

区别是在 partition by 出现 multiple rows 的时候，range 会一起算, rows 会一个一个算. 

更 rank vs row_number 有点像

 

虽然这些看上去很灵活，但是我发现如果要想做一个简单的 sum top 3 是非常幸苦的.

最终还是要用 row_number 来实现... 

 

 

 

refer :

https://blog.csdn.net/winer2008/article/details/4283539

https://www.cnblogs.com/linJie1930906722/p/6036053.html

https://www.c-sharpcorner.com/blogs/rownumber-function-with-partition-by-clause-in-sql-server1

https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group/12190849

 

要想 group by get first row, 网上给出的方案多半是长这样的. 

with cte as
(
   select *, row_number() over (partition by DocumentID order by DateCreated desc) as rn
   from hydro.dbo.CustomerItemPrices
)
select *
from cte
where rn = 1

 

with xx as 类似 temp table 这里不多讲. 

row_number 就是做出 1,2,3 这种号码. 

而 over (partition by column order by column) 则是分组和排序. 

也就是说这个 row_number 并不是基于 当下的表,  而是基于分组排序后的表.

所以后来 where rn = 1 就能把第一条 row 取出来了. 

除了常用的 row_number

还有 rank 和 dense_rank

它们有一点区别而且. 

row_number 是 1,2,3,4 即使值相同依然会给一个新号码

rank 则是 1,2,2,4 如果值相同会共用号码, 然后会跳号, 3 就没了. 

dense_rank 则是 1,2,2,3 值相同共用号码，但是它不会跳，所以 3 依然会有.

 

 

 

 

 

 

;WITH cte AS(SELECT*,
         ROW_NUMBER()OVER(PARTITIONBY DocumentID ORDERBY DateCreated DESC)AS rn
   FROM DocumentStatusLogs
)SELECT*FROM cte
WHERE rn =1