Sql Server 开窗函数Over()的使用实例详解

利用over()，将统计信息计算出来，然后直接筛选结果集

declare @t table(
ProductID int,
ProductName varchar(20),
ProductType varchar(20),
Price int)
insert @t
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

查询要求：查出每类产品中价格最高的信息

--做法一：找到每个组里,价格最大的值；然后再找出每个组里价格等于这个值的
--缺点:要进行一次join

select t1.* from @t t1
 join (select ProductType, max(Price) Price from @t group by ProductType) t2
 on t1.ProductType = t2.ProductType
 where t1.Price = t2.Price
 order by ProductType

--做法二：利用over()，将统计信息计算出来，然后直接筛选结果集。
--over() 可以让函数(包括聚合函数)与行一起输出。

 ;with cte as(select *, max(Price) over(partition by (ProductType)) MaxPrice from @t)
select ProductID,ProductName,ProductType,Price from cte where Price = MaxPrice
 order by ProductType

-over() 的语法为：over([patition by ] <order by >)。需要注意的是，over() 前面是一个函数，如果是聚合函数，那么order by 不能一起使用。

--over() 的另一常用情景是与 row_number() 一起用于分页。

现在来介绍一下开窗函数。

窗口函数OVER（）指定一组行，开窗函数计算从窗口函数输出的结果集中各行的值。

开窗函数不需要使用GROUP BY就可以对数据进行分组，还可以同时返回基础行的列和聚合列。

1．排名开窗函数

ROW_NUMBER、DENSE_RANK、RANK、NTILE属于排名函数。

排名开窗函数可以单独使用ORDER BY 语句，也可以和PARTITION BY同时使用。

PARTITION BY用于将结果集进行分组，开窗函数应用于每一组。

ODER BY 指定排名开窗函数的顺序。在排名开窗函数中必须使用ORDER BY语句。

例如查询每个雇员的定单，并按时间排序

;WITH OrderInfo AS
(
 SELECT ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY OrderDate) AS Number,
 OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK)
)
SELECT Number,OrderID,CustomerID, EmployeeID ,OrderDate
From OrderInfo WHERE Number BETWEEN 0 AND 10

窗口函数根据PARTITION BY语句按雇员ID对数据行分组，然后按照ORDER BY 语句排序，排名函数ROW_NUMBER()为每一组的数据分从1开始生成一个序号。

ROW_NUMBER（）为每一组的行按顺序生成一个唯一的序号

RANK（）也为每一组的行生成一个序号，与ROW_NUMBER()不同的是如果按照ORDER BY的排序，如果有相同的值会生成相同的序号，并且接下来的序号是不连序的。例如两个相同的行生成序号3，那么接下来会生成序号5。

DENSE_RANK（）和RANK（）类似，不同的是如果有相同的序号，那么接下来的序号不会间断。也就是说如果两个相同的行生成序号3，那么接下来生成的序号还是4。

NTILE (integer_expression) 按照指定的数目将数据进行分组，并为每一组生成一个序号。

2．聚合开窗函数

很多聚合函数都可以用作窗口函数的运算，如SUM,AVG,MAX,MIN。

聚合开窗函数只能使用PARTITION BY子句或都不带任何语句，ORDER BY不能与聚合开窗函数一同使用。

例如，查询雇员的定单总数及定单信息

 WITH OrderInfo AS
(
SELECT COUNT(OrderID) OVER(PARTITION BY EmployeeID) AS TotalCount,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK)
)
SELECT OrderID,CustomerID, EmployeeID ,OrderDate,TotalCount From OrderInfo ORDER BY EmployeeID

如果窗口函数不使用PARTITION BY 语句的话，那么就是不对数据进行分组，聚合函数计算所有的行的值

 WITH OrderInfo AS
 (
 SELECT COUNT(OrderID) OVER() AS Count,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK)
 )

总结

以上所述是小编给大家介绍的Sql Server 开窗函数Over()的使用实例详解，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对我们网站的支持！

赞 (0)

相关推荐

  • sql server如何利用开窗函数over()进行分组统计

    这是一道常见的面试题,在实际项目中经常会用到. 需求:求出以产品类别为分组,各个分组里价格最高的产品信息. 实现过程如下: declare @t table( ProductID int, ProductName varchar(20), ProductType varchar(20), Price int) --测试数据 insert @t select 1,'name1','P1',3 union all select 2,'name2','P1',5 union all select 3,

  • SQL Server 2012 开窗函数

    废话不多说了,直接给大家贴代码了,具体代码如下所示: -- 开窗函数:在结果集的基础上进一步处理(聚合操作) -- Over函数,添加一个字段显示最大年龄 SELECT * , MAX(StuAge) OVER ( ) MaxStuAge FROM dbo.Student; -- Over函数,添加一个字段显示总人数 SELECT * , COUNT(StuID) OVER ( ) StuCount FROM dbo.Student; -- Partition By 分组统计数量 -- 根据性别

  • Sql Server 开窗函数Over()的使用实例详解

    利用over(),将统计信息计算出来,然后直接筛选结果集 declare @t table( ProductID int, ProductName varchar(20), ProductType varchar(20), Price int) insert @t select 1,'name1','P1',3 union all select 2,'name2','P1',5 union all select 3,'name3','P2',4 union all select 4,'name4

  • SQL Server 开窗函数 Over()代替游标的使用详解

    前言: 今天在优化工作中遇到的sql慢的问题,发现以前用了挺多游标来处理数据,这样就导致在数据量多的情况下,需要一行一行去遍历从而计算需要的数据,这样处理的结果就是数据慢,容易卡死. 语法介绍: 1.与Row_Number() 函数结合使用,对结果进行排序,这个是我们使用的非常多的 2.与聚合函数结合使用,利用over子句的分组和排序,对需要的数据进行操作 例如:SUM() Over() 累加值.AVG() Over() 平均数 MAX() Over() 最大值.MIN() Over() 最小值

  • SQL Server中identity(自增)的用法详解

    一.identity的基本用法 1.含义 identity表示该字段的值会自动更新,不需要我们维护,通常情况下我们不可以直接给identity修饰的字符赋值,否则编译时会报错 2.语法 列名 数据类型 约束 identity(m,n) m表示的是初始值,n表示的是每次自动增加的值 如果m和n的值都没有指定,默认为(1,1) 要么同时指定m和n的值,要么m和n都不指定,不能只写其中一个值,不然会出错 3.实例演示 不指定m和n的值 create table student1 ( sid int p

  • JavaScript学习小结之被嫌弃的eval函数和with语句实例详解

    前面的话 eval和with经常被嫌弃,好像它们的存在就是错误.在CSS中,表格被嫌弃,在网页中只是用表格来展示数据,而不是做布局,都可能被斥为不规范,矫枉过正.那关于eval和with到底是什么情况呢?本文将详细介绍eval()函数和with语句 eval 定义 eval()是一个全局函数,javascript通过eval()来解释运行由javascript源代码组成的字符串 var result = eval('3+2'); console.log(result,typeof result)

  • python open函数中newline参数实例详解

    python open函数中newline参数实例详解

    目录 问题的由来 具体实例 总结 问题的由来 我在读pythoncsv模块文档 看到了这样一句话 如果 csvfile 是文件对象,则打开它时应使用 newline=‘’.其备注:如果没有指定 newline=‘’,则嵌入引号中的换行符将无法正确解析,并且在写入时,使用 \r\n 换行的平台会有多余的 \r 写入.由于 csv 模块会执行自己的(通用)换行符处理,因此指定 newline=‘’ 应该总是安全的. 我就在思考open函数中的newline参数的作用,因为自己之前在使用open函数时

  • awk正则表达式和内置函数的使用方法实例详解

    awk正则表达式及内置函数实例详解: 1.模糊匹配: 复制代码 代码如下: awk '{if($3~/97/) print $0}' data.f:如果第三项中含有"97"则打印该行 awk '{if($4!~/ufcx/) print $0}' data.f:如果第三项中不含ufcx有则打印 2.精确匹配: 复制代码 代码如下: awk '{if($5==66) print $0}' data.f:如果第五项是66则打印 awk '{if($5!=66)print $0}' data

  • SQL Server中row_number分页查询的用法详解

    ROW_NUMBER()函数将针对SELECT语句返回的每一行,从1开始编号,赋予其连续的编号.在查询时应用了一个排序标准后,只有通过编号才能够保证其顺序是一致的,当使用ROW_NUMBER函数时,也需要专门一列用于预先排序以便于进行编号. ROW_NUMBER() 说明:返回结果集分区内行的序列号,每个分区的第一行从1开始. 语法:ROW_NUMBER () OVER ([ <partition_by_clause> ] <order_by_clause>) . 备注:ORDER

  • Sql server中内部函数fn_PhysLocFormatter存在解析错误详解

    前言 有网友指出,SQL Server 2012中fn_PhysLocFormatter内部函数在解析数据行记录位置时存在错误,见:http://www.itpub.net/thread-1751655-1-1.html,实际测试后发现,一是2008R2中同样存在问题,二是不仅页号解析存在问题,槽号解析也存在同样问题. 下面先查看表NT_SiteInfo的数据行记录位置. select SiteID,%%physloc%%,sys.fn_PhysLocFormatter(%%physloc%%)

  • 开窗函数有浅入深详解（一）

    开窗函数有浅入深详解（一）

    在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题,很多都要通过复杂的相关子查询或者存储过程来完成.为了解决这些问题,在2003年ISO  SQL标准加入了开窗函数,开窗函数的使用使得这些经典的难题可以被轻松的解决. 目前在 MSSQLServer.Oracle.DB2 等主流数据库中都提供了对开窗函数的支持,不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持. 为了更加清楚地理解,我们来建表并进行相关的查询(截图为MSSQLServer中的结果) MYSQL,MSSQLServe

  • SQL server分页的四种方法思路详解(最全面教程)

    目录 方法一:三重循环 思路 方法二:利用max(主键) 方法三:利用row_number关键字 第四种方法:offset /fetch next(2012版本及以上才有) 总结   这篇博客讲的是SQL server的分页方法,用的SQL server 2012版本.下面都用pageIndex表示页数,pageSize表示一页包含的记录.并且下面涉及到具体例子的,设定查询第2页,每页含10条记录.   首先说一下SQL server的分页与MySQL的分页的不同,mysql的分页直接是用lim

随机推荐