SQL2005 学习笔记 窗口函数(OVER)

1.简介：
SQL Server 2005中的窗口函数帮助你迅速查看不同级别的聚合，通过它可以非常方便地累计总数、移动平均值、以及执行其它计算。
窗口函数功能非常强大，使用起来也十分容易。可以使用这个技巧立即得到大量统计值。
窗口是用户指定的一组行。 开窗函数计算从窗口派生的结果集中各行的值。
2.适用范围：
排名开窗函数和聚合开窗函数.
也就是说窗口函数是结合排名开窗函数或者聚合开窗函数一起使用
OVER子句前面必须是排名函数或者是聚合函数

3.例题：


代码如下:

--建立订单表
create table SalesOrder(
OrderID int, --订单id
OrderQty decimal(18,2) --数量
)
go
--插入数据
insert into SalesOrder
select 1,2.0
union all
select 1,1.0
union all
select 1,3.0
union all
select 2,6.0
union all
select 2,1.1
union all
select 3,8.0
union all
select 3,1.1
union all
select 3,7.0
go
--查询得如下结果
select * from SalesOrder
go
OrderID OrderQty
----------- ------------
1 2.00
1 1.00
1 3.00
2 6.00
2 1.10
3 8.00
3 1.10
3 7.00

现要求显示汇总总数，每当所占比例，分组汇总数，每单在各组所占比例，要求格式如下：
OrderID OrderQty 汇总 每单比例 分组汇总 每单在各组比例
1 2.00 29.20 0.0685 6.00 0.3333
1 1.00 29.20 0.0342 6.00 0.1667
1 3.00 29.20 0.1027 6.00 0.5000
2 6.00 29.20 0.2055 7.10 0.8451
2 1.10 29.20 0.0377 7.10 0.1549
3 8.00 29.20 0.2740 16.10 0.4969
3 1.10 29.20 0.0377 16.10 0.0683
3 7.00 29.20 0.2397 16.10 0.4348


代码如下:

--利用窗口函数和聚合开窗函数,可以很快实现上述要求
select OrderID,OrderQty,
sum(OrderQty) over() as [汇总],
convert(decimal(18,4), OrderQty/sum(OrderQty) over() ) as [每单所占比例],
sum(OrderQty) over(PARTITION BY OrderID) as [分组汇总],
convert(decimal(18,4),OrderQty/sum(OrderQty) over(PARTITION BY OrderID)) as [每单在各组所占比例]
from SalesOrder
order by OrderID

窗口函数是sql2005新增加的,下面我们看看在sql2000里面怎么实现上述的结果：
sql2000的实现步骤较麻烦,先计算出总数，再分组计算汇总，最后连接得到结果


代码如下:

--sql2000
declare @sum decimal(18,2)
select @sum=sum(OrderQty)
from SalesOrder
--按OrderID，计算每组的总计，然后插入临时表
select OrderID,sum(OrderQty) as su
into #t
from SalesOrder
group by OrderID
--连接临时表，得到结果
select s.OrderID,s.OrderQty,
@sum as [汇总],
convert(decimal(18,4),s.OrderQty/@sum) as [每单所占比例],
t.su as [分组汇总],
convert(decimal(18,4),s.OrderQty/t.su) as [每单在各组所占比例]
from SalesOrder s join #t t
on t.OrderID=s.OrderID
order by s.OrderID
drop table #t
go

上面演示的都是窗口函数与聚合开窗函数的使用，它与排名开窗函数请看下面例题:


代码如下:

--与排名开窗函数使用
select OrderID,OrderQty,
rank() over(PARTITION BY orderid order by OrderQty ) as [分组排名],
rank() over(order by OrderQty ) as [排名]
from SalesOrder
order by orderid asc
--查询得如下结果
OrderID OrderQty 分组排名 排名
1 2.00 2 4
1 3.00 3 5
1 1.00 1 1
2 1.10 1 2
2 6.00 2 6
3 7.00 2 7
3 8.00 3 8
3 1.10 1 2

赞 (0)

相关推荐

  • SQL进行排序、分组、统计的10个新技巧分享

    1.使用排序使数据有序通常,你的所有数据真正需要的仅仅是按某种顺序排列.SQL的ORDER BY语句可以以字母或数字顺序组织数据.因此,相似的值按组排序在一起.然而,这个分组时排序的结果,并不是真的分组.ORDER BY显示每条记录而分组可能代表很多记录.2.进行分组除去重复值 排序和分组之间的最大区别是:排序的数据显示所有记录(在限定标准范围之内),而分组数据不是显示所有记录.GROUP BY语句对于同样的值只显示一条记录.例如,下面的语句中的GROUP BY语句对数据源中重复出现的数据只返回

  • SQLserver 实现分组统计查询（按月、小时分组）

    SQLserver 实现分组统计查询（按月、小时分组）

    设置AccessCount字段可以根据需求在特定的时间范围内如果是相同IP访问就在AccessCount上累加. 复制代码 代码如下: Create table Counter ( CounterID int identity(1,1) not null, IP varchar(20), AccessDateTime datetime, AccessCount int ) 该表在这儿只是演示使用,所以只提供了最基本的字段 现在往表中插入几条记录 insert into Counter selec

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

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

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

  • 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,

  • sqlserver2005使用row_number() over分页的实现方法

    语法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN) <BR> 例子: 复制代码 代码如下: select * from ( select *, ROW_NUMBER() OVER(Order by a.CreateTime DESC ) AS RowNumber from table_name as a ) as b where RowNumber BETWEEN 1 and 5 将会返回table表 其中有一列名字为 RowNu

  • Sql Server 分组统计并合计总数及WITH ROLLUP应用

    WITH ROLLUP 在生成包含小计和合计的报表时,ROLLUP 运算符很有用.ROLLUP 运算符生成的结果集类似于 CUBE 运算符所生成的结果集. 复制代码 代码如下: SELECT [Source], COUNT(*) AS OrderTotal FROM [ExternalOrder] Where OrderStatus=1 AND (CheckPayment=1 ) and TicketDate >= '2012-11-1' AND TicketDate < '2012-12-1

  • SQL2005利用ROW_NUMBER() OVER实现分页功能

    SQL2005利用ROW_NUMBER() OVER实现分页功能

    1.首先介绍ROW_NUMBER() OVER的基本用法 2.看一下实例数据 初始化数据 create table employee (empid int ,deptid int ,salary decimal(10,2)) insert into employee values(1,10,5500.00) insert into employee values(2,10,4500.00) insert into employee values(3,20,1900.00) insert into

  • Sql Server：多行合并成一行，并做分组统计的两个方法

    复制代码 代码如下: --创建 test 表 ,插入数据 CREATE TABLE test(code varchar(50), [values] varchar(10),[count] int)INSERT test SELECT '001', 'aa',1UNION ALL SELECT '001', 'bb',2UNION ALL SELECT '002', 'aaa',4UNION ALL SELECT '002', 'bbb',5UNION ALL SELECT '002', 'ccc

  • SQL2005 学习笔记 窗口函数(OVER)

    1.简介: SQL Server 2005中的窗口函数帮助你迅速查看不同级别的聚合,通过它可以非常方便地累计总数.移动平均值.以及执行其它计算. 窗口函数功能非常强大,使用起来也十分容易.可以使用这个技巧立即得到大量统计值. 窗口是用户指定的一组行. 开窗函数计算从窗口派生的结果集中各行的值. 2.适用范围: 排名开窗函数和聚合开窗函数. 也就是说窗口函数是结合排名开窗函数或者聚合开窗函数一起使用 OVER子句前面必须是排名函数或者是聚合函数 3.例题: 复制代码 代码如下: --建立订单表 c

  • SQL2005学习笔记 APPLY 运算符

    使用APPLY运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数. 表值函数作为右输入,外部表表达式作为左输入. 通过对右输入求值来获得左输入每一行的计算结果,生成的行被组合起来作为最终输出. APPLY运算符生成的列的列表是左输入中的列集,后跟右输入返回的列的列表. APPLY 运算符的左操作数和右操作数都是表表达式. 这些操作数之间的主要区别是:右操作数可以使用表值函数,从左操作数获取一个列作为函数的参数之一.左操作数可以包括表值函数,但不能以来自右操作数的列作为参数. 演示一下

  • SQL2005 学习笔记 公用表表达式(CTE)

    SQL2005 学习笔记 公用表表达式(CTE)

    公用表表达式 (CTE) 可以认为是在单个 SELECT.INSERT.UPDATE.DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集. CTE 与派生表类似,具体表现在不存储为对象,并且只在查询期间有效. 与派生表的不同之处在于,CTE 可自引用,还可在同一查询中引用多次. CTE可用于: 1.创建递归查询(我个人认为CTE最好用的地方) 2.在同一语句中多次引用生成的表 CTE优点: 使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点. 查询可以分为单独块.简

  • SQL2005学习笔记 EXCEPT和INTERSECT运算符

    1.简介 EXCEPT和INTERSECT运算符使您可以比较两个或多个SELECT语句的结果并返回非重复值. 2.区别 EXCEPT运算符返回由EXCEPT运算符左侧的查询返回.而又不包含在右侧查询所返回的值中的所有非重复值. INTERSECT返回由INTERSECT运算符左侧和右侧的查询都返回的所有非重复值. 3.注意事项 (1).使用EXCEPT或INTERSECT比较的结果集必须具有相同的结构.它们的列数必须相同,并且相应的结果集列的数据类型必须兼容 (2).INTERSECT运算符优先

  • angularjs学习笔记之简单介绍

    一.angularjs简介 AngularJS 是一个为动态WEB应用设计的结构框架.它能让你使用HTML作为模板语言,通过扩展HTML的语法,让你能更清楚.简洁地构建你的应用组件.它的创新点在于,利用 数据绑定 和 依赖注入,它使你不用再写大量的代码了.这些全都是通过浏览器端的Javascript实现,这也使得它能够完美地和任何服务器端技术结合. 说了这么多,估计你啥都没有理解...对吗?别着急,我来说说他的几个特点吧:模块化,数据双向绑定,依赖注入,指令.下面我们就跟着这几个特点进行学习.

  • AngularJS学习笔记之表单验证功能实例详解

    本文实例讲述了AngularJS学习笔记之表单验证功能.分享给大家供大家参考,具体如下: 一.执行基本的表单验证 <!DOCTYPE html> <html ng-app='exampleApp'> <head> <meta charset="UTF-8"> <title>表单</title> <script src="../../js/angular.min.js" type="

  • 正则表达式学习笔记

    正则表达式学习笔记 正则表达式(regular expression)描述了一种字符串匹配的模式,可以用来检查一个串是否含  有某种子串.将匹配的子串做替换或者从某个串中取出符合某个条件的子串等. 列目录时, dir *.txt或ls *.txt中的*.txt就不是一个正则表达式,因为这里*与正则式的*  的含义是不同的. 为便于理解和记忆,先从一些概念入手,所有特殊字符或字符组合有一个总表在后面,最后一  些例子供理解相应的概念. 正则表达式 是由普通字符(例如字符 a 到 z)以及特殊字符(

  • vue mint-ui学习笔记之picker的使用

    vue mint-ui学习笔记之picker的使用

    本文介绍了vue mint-ui picker的使用,分享给大家,也给自己留个学习笔记 Picker的使用 import { Picker } from 'mint-ui'; Vue.component(Picker.name, Picker); API 示例一:picker的简单使用 xxx.vue: <template> <div id="app"> <mt-picker :slots="slots" ></mt-pic

  • Go语言学习笔记之反射用法详解

    本文实例讲述了Go学习笔记之反射用法.分享给大家供大家参考,具体如下: 一.类型(Type) 反射(reflect)让我们能在运行期探知对象的类型信息和内存结构,这从一定程度上弥(mi)补了静态语言在动态行为上的不足.同时,反射还是实现元编程的重要手段. 和 C 数据结构一样,Go 对象头部并没有类型指针,通过其自身是无法在运行期获知任何类型相关信息的.反射操作所需要的全部信息都源自接口变量.接口变量除存储自身类型外,还会保存实际对象的类型数据. func TypeOf(i interface{

  • linux Shell学习笔记第一天

    linux Shell学习笔记第一天

    以下是Shell学习1-2天学习笔记 ---------我是分隔符--------- 硬件去执行, 内核与硬件之间进行操作. 命令解析器. shell脚本的组成元素 系统命令 文本处理工具(sort.grep.sed.awk-) 变量 条件判断 环循结构 函数 Shell Scripts Center(SSC) ---------------------------- 非负Grep / awk 几天. 统计Wc Sort 排序 sort|head / sed字段处理 awk 数据区域判断 awk

随机推荐