先来个图大家乐呵乐呵 窗口函数OVER()指定一组行,开窗函数计算从窗口函数输出的结果集中各行的值。 在SQL SERVER 2005/2008支持两种排名开窗函数和聚集开窗函数 先来跟大家说一下over() 的语法为: function(column)over([patition by(column) ] <order by (column) asc >)。需要注意的是,over() 前面是一个函数。 很多聚合函数都可以用作窗口函数的运算,如SUM,AVG,MAX,MIN。 聚合开窗函数只能使用PARTITION BY子句或都不带任何语句,ORDER BY不能与聚合开窗函数一同使用。 举例说明: 例子①:求出以Splx(/*商品类型第一个字母。方便理解哈*/)为分组,各个分组里 Lshj(/*零售价第一个字母*/)最高的spID(/*商品ID的第一个字母*/)、splx、lshj、spName。 实现过程如下: --测试数据 declare @demo table( spID int, spName varchar(20), Splx varchar(20), Lshj int) insert @demo select 1,'name1','P1',3 union all select 2,'name2','P1',5 union all select 3,'name3','P2',4 union all select 4,'name4','P2',4 --方法一:找到每个组里,lshj最大的值;然后再找出每个组里lshj等于这个值的 select t1.* from @demo t1 join (select Splx, max(Lshj) Lshj from @demo group by Splx) t2 on t1.Splx = t2.Splx where t1.Lshj = t2.Lshj order by t1.Splx --方法二:利用over(),将统计信息计算出来,然后直接筛选结果集。 --over() 可以让函数(包括聚合函数)与行一起输出 ,且开窗函数不需要使用GROUP BY就可以对数据进行分组。 with cte as( select *, max(Lshj) over(partition by (Splx)) MaxLshj from @demo ) select * from cte where Lshj = MaxLshj order by Splx ROW_NUMBER、DENSE_RANK、RANK、NTILE属于排名函数。 排名开窗函数可以单独使用ORDER BY 语句,也可以和PARTITION BY同时使用。 PARTITION BY用于将结果集进行分组,开窗函数应用于每一组。 ODER BY 指定排名开窗函数的顺序。在排名开窗函数中必须使用ORDER BY语句。 例子②:求出以splx为分组,以lshj从高到低的排序。 select *, row_number() over(partition by (Splx) order by lshj desc) MaxLshj from @demo --over() 的另一常用情景是与 排名函数(ROW_NUMBER、DENSE_RANK、RANK、NTILE) 一起用于分页。 下面来详细介绍下排名开窗函数: ROW_NUMBER()为每一组的行按顺序生成一个唯一的序号 RANK()也为每一组的行生成一个序号,与ROW_NUMBER()不同的是如果按照ORDER BY的排序,如果有相同的值会生成相同的序号,并且接下来的序号是不连序的。例如两个相同的行生成序号3,那么接下来会生成序号5。 DENSE_RANK()和RANK()类似,不同的是如果有相同的序号,那么接下来的序号不会间断。也就是说如果两个相同的行生成序号3,那么接下来生成的序号还是4。 NTILE (integer_expression) 按照指定的数目将数据进行分组,并为每一组生成一个序号。 /*温馨提示:大家好好看哦。因为很多公司入职面试的时候经常提及哈。 在此感谢:战略掌柜,提出的这个开窗函数让大家分享哈。 如果大家还有啥好的提议或者不明白的地方就在下面提哈。楼主尽量满足大家的建议。*/1.聚合开窗函数
2.排名开窗函数