ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > MS SQL 2012窗口函数介绍

MS SQL 2012窗口函数介绍

原创 Linux操作系统 作者：hegenhua 时间：2012-11-09 10:57:21 0 删除 编辑
      MS SQL SERVER 2012引入了不少令人振奋的新功能，其中就包括新增加的窗口函数以及对以往版本中已有的窗口函数功能增强。
     新功能允许我们在OVER()的 order by代码块中使用ROWS/RANGE 进一步限制记录数。
     ROWS/RANGE 子句
     ROWS 子句通过指定当前行之前或之后的固定数目的行，限制分区中的行数。以下为q其几种使用方式：
  
  • <无符号整数> PRECEDING -当前行前面的固定行数
  • CURRENT ROW - 当前行
  • UNBOUNDED PRECEDING - 当前行之前的所有记录
  • <无符号整数> FOLLOWING - 当前行之后的固定行数
  • UNBOUNDED FOLLOWING - 当前行之后的所有记录
        
所以我们可以如下这样使用：
    ROWS BETWEEN 3 PRECEEDING AND 1 FOLLOWING
    ROWS BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW
    ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING  
   RANGE BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW
   RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
   RENGE CURRENT ROW
 
 
RANGE子句只可以和UNBOUNDED 以及CURRENT ROW一起使用，不能指定固定数值的行数
 
RANGE子句与ROWS子句的还有一个显著差别，这个差别就是对CURRENT ROW的定义。
对于ROWS子句，CURRENT ROW确实是只代表当前行，而对于RANGE子句，CURRENT ROW代表所有那些行--即与当前正在处理的行所在分区的order by
子句后面跟的字段的值相同的所有行。
For RANGE the CURRENT ROW represents all the rows with the same value in the fields specified in the ORDER BY clause within
current partition as the current row being processed.
 
  当没有指定ROWS/RANGE子句，SQL SERVER将会使用RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW做为default值
 
 
使用窗口函数的samples:
   准备测试用的数据-- Create test database
--======================
CREATE DATABASE WindowFunctionsTest
GO
USE WindowFunctionsTest
GO
--Create Testing Tables
CREATE TABLE [dbo].[Accounts](
 [TransactionID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [TransactionDate] [datetime] NULL,
 [Balance] [float] NULL
)
GO
CREATE TABLE [dbo].[MultiAccounts](
 [TransactionID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [AccountID] [int] NOT NULL,
 [TransactionDate] [datetime] NULL,
 [Balance] [float] NULL
)
GO
 
--Fill test tables with data
INSERT INTO [dbo].[Accounts](
 [TransactionDate],
 [Balance]
)
SELECT '2000-1-1', 100 UNION ALL
SELECT '2000-1-1', -50 UNION ALL
SELECT '2000-1-2', 200 UNION ALL
SELECT '2000-1-3', 500 UNION ALL
SELECT '2000-1-4', -200 UNION ALL
SELECT '2000-1-5', 1000 UNION ALL
SELECT '2000-1-5', -300 UNION ALL
SELECT '2000-1-6', -300 UNION ALL
SELECT '2000-1-7', -200 UNION ALL
SELECT '2000-1-8', 2000 UNION ALL
SELECT '2000-1-9', 100 UNION ALL
SELECT '2000-1-10', -50 UNION ALL
SELECT '2000-1-10', 500 UNION ALL
SELECT '2000-1-11', 200 UNION ALL
SELECT '2000-1-12', 200 UNION ALL
SELECT '2000-1-13', 1000 UNION ALL
SELECT '2000-1-14', 1000 UNION ALL
SELECT '2000-1-15', -500 UNION ALL
SELECT '2000-1-15', -300 UNION ALL
SELECT '2000-1-16', 1000 UNION ALL
SELECT '2000-1-17', 1000 UNION ALL
SELECT '2000-1-18', -800 UNION ALL
SELECT '2000-1-19', 2000 UNION ALL
SELECT '2000-1-20', -1000
GO
 
INSERT [dbo].[MultiAccounts] (
 [AccountID],
 [TransactionDate],
 [Balance]
)
SELECT 1, '2000-1-1', 100 UNION ALL
SELECT 1, '2000-1-1', -50 UNION ALL
SELECT 1, '2000-1-2', 200 UNION ALL
SELECT 1, '2000-1-3', 500 UNION ALL
SELECT 1, '2000-1-4', -200 UNION ALL
SELECT 1, '2000-1-5', 1000 UNION ALL
SELECT 1, '2000-1-5', -300 UNION ALL
SELECT 1, '2000-1-6', -300 UNION ALL
SELECT 1, '2000-1-7', -200 UNION ALL
SELECT 2, '2000-1-1', 2000 UNION ALL
SELECT 2, '2000-1-2', 100 UNION ALL
SELECT 2, '2000-1-3', -50 UNION ALL
SELECT 2, '2000-1-4', 500 UNION ALL
SELECT 2, '2000-1-5', 200 UNION ALL
SELECT 2, '2000-1-6', 200 UNION ALL
SELECT 2, '2000-1-7', 1000 UNION ALL
SELECT 2, '2000-1-7', 1000 UNION ALL
SELECT 3, '2000-1-1', 800 UNION ALL
SELECT 3, '2000-1-2', -300 UNION ALL
SELECT 3, '2000-1-3', 1000 UNION ALL
SELECT 3, '2000-1-4', 1000 UNION ALL
SELECT 3, '2000-1-5', -800 UNION ALL
SELECT 3, '2000-1-6', 2000 UNION ALL
SELECT 3, '2000-1-7', -1000
GO
 
----------------------------------------------
Samples for ROWS:
 
SELECT
    [TransactionID]
    ,[TransactionDate]
    ,[Balance]
    ,SUM(Balance) OVER (ORDER BY TransactionDate, TransactionID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CummulativeBalance
FROM [dbo].[Accounts]
ORDER BY TransactionDate, TransactionID
GO
 
Result:
TransactionID TransactionDate         Balance                CummulativeBalance
------------- ----------------------- ---------------------- ----------------------
1             2000-01-01 00:00:00.000 100                    100
2             2000-01-01 00:00:00.000 -50                    50
3             2000-01-02 00:00:00.000 200                    250
4             2000-01-03 00:00:00.000 500                    750
5             2000-01-04 00:00:00.000 -200                   550
6             2000-01-05 00:00:00.000 1000                   1550
7             2000-01-05 00:00:00.000 -300                   1250
8             2000-01-06 00:00:00.000 -300                   950
9             2000-01-07 00:00:00.000 -200                   750
10            2000-01-08 00:00:00.000 2000                   2750
11            2000-01-09 00:00:00.000 100                    2850
12            2000-01-10 00:00:00.000 -50                    2800
13            2000-01-10 00:00:00.000 500                    3300
14            2000-01-11 00:00:00.000 200                    3500
15            2000-01-12 00:00:00.000 200                    3700
16            2000-01-13 00:00:00.000 1000                   4700
17            2000-01-14 00:00:00.000 1000                   5700
18            2000-01-15 00:00:00.000 -500                   5200
19            2000-01-15 00:00:00.000 -300                   4900
20            2000-01-16 00:00:00.000 1000                   5900
21            2000-01-17 00:00:00.000 1000                   6900
22            2000-01-18 00:00:00.000 -800                   6100
23            2000-01-19 00:00:00.000 2000                   8100
24            2000-01-20 00:00:00.000 -1000                  7100
(24 row(s) affected)

SELECT
    [TransactionID]
    ,[TransactionDate]
    ,[Balance]
    ,SUM(Balance) OVER (ORDER BY TransactionDate, TransactionID ROWS  UNBOUNDED PRECEDING ) AS CummulativeBalance
FROM [dbo].[Accounts]
ORDER BY TransactionDate, TransactionID
GO
 
Result: 结果与上一句带有AND CURRENT ROW相同
TransactionID TransactionDate         Balance                CummulativeBalance
------------- ----------------------- ---------------------- ----------------------
1             2000-01-01 00:00:00.000 100                    100
2             2000-01-01 00:00:00.000 -50                    50
3             2000-01-02 00:00:00.000 200                    250
4             2000-01-03 00:00:00.000 500                    750
5             2000-01-04 00:00:00.000 -200                   550
6             2000-01-05 00:00:00.000 1000                   1550
7             2000-01-05 00:00:00.000 -300                   1250
8             2000-01-06 00:00:00.000 -300                   950
9             2000-01-07 00:00:00.000 -200                   750
10            2000-01-08 00:00:00.000 2000                   2750
11            2000-01-09 00:00:00.000 100                    2850
12            2000-01-10 00:00:00.000 -50                    2800
13            2000-01-10 00:00:00.000 500                    3300
14            2000-01-11 00:00:00.000 200                    3500
15            2000-01-12 00:00:00.000 200                    3700
16            2000-01-13 00:00:00.000 1000                   4700
17            2000-01-14 00:00:00.000 1000                   5700
18            2000-01-15 00:00:00.000 -500                   5200
19            2000-01-15 00:00:00.000 -300                   4900
20            2000-01-16 00:00:00.000 1000                   5900
21            2000-01-17 00:00:00.000 1000                   6900
22            2000-01-18 00:00:00.000 -800                   6100
23            2000-01-19 00:00:00.000 2000                   8100
24            2000-01-20 00:00:00.000 -1000                  7100
(24 row(s) affected)
Samples for RANGE：
--请注意，当ORDER BY 后面所跟字段的值相同时，这里是TransactionDate，CummulativeBalance也相同
--比如，第12和13两条记录的TransactionDate都为‘2000-01-10 00:00:00.000 ’，尽管第12条记录的Balance=-50，第13条记录的Balance=500，但是二者的
--CummulativeBalance都是3300，这就是RANGE与ROWS的重要区别之一
SELECT
    [TransactionID]
    ,[TransactionDate]
    ,[Balance]
    ,SUM(Balance) OVER (ORDER BY TransactionDate RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CummulativeBalance
FROM [dbo].[Accounts]
ORDER BY TransactionDate, TransactionID
GO
 
Result：
TransactionID TransactionDate         Balance                CummulativeBalance
------------- ----------------------- ---------------------- ----------------------
1             2000-01-01 00:00:00.000 100                    50
2             2000-01-01 00:00:00.000 -50                    50
3             2000-01-02 00:00:00.000 200                    250
4             2000-01-03 00:00:00.000 500                    750
5             2000-01-04 00:00:00.000 -200                   550
6             2000-01-05 00:00:00.000 1000                   1250
7             2000-01-05 00:00:00.000 -300                   1250
8             2000-01-06 00:00:00.000 -300                   950
9             2000-01-07 00:00:00.000 -200                   750
10            2000-01-08 00:00:00.000 2000                   2750
11            2000-01-09 00:00:00.000 100                    2850
12            2000-01-10 00:00:00.000 -50                    3300
13            2000-01-10 00:00:00.000 500                    3300
14            2000-01-11 00:00:00.000 200                    3500
15            2000-01-12 00:00:00.000 200                    3700
16            2000-01-13 00:00:00.000 1000                   4700
17            2000-01-14 00:00:00.000 1000                   5700
18            2000-01-15 00:00:00.000 -500                   4900
19            2000-01-15 00:00:00.000 -300                   4900
20            2000-01-16 00:00:00.000 1000                   5900
21            2000-01-17 00:00:00.000 1000                   6900
22            2000-01-18 00:00:00.000 -800                   6100
23            2000-01-19 00:00:00.000 2000                   8100
24            2000-01-20 00:00:00.000 -1000                  7100
(24 row(s) affected)
 
 

samples for FOLLOWING：

SELECT
    [TransactionID]
 ,[AccountID]
    ,[TransactionDate]
    ,[Balance]
    ,SUM(Balance) OVER (PARTITION BY [AccountID] ORDER BY TransactionDate, TransactionID ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS CummulativeBalance
FROM [dbo].[MultiAccounts]
ORDER BY AccountID, TransactionDate, TransactionID

Result：

TransactionID AccountID   TransactionDate         Balance                CummulativeBalance
------------- ----------- ----------------------- ---------------------- ----------------------
1             1           2000-01-01 00:00:00.000 100                    750
2             1           2000-01-01 00:00:00.000 -50                    650
3             1           2000-01-02 00:00:00.000 200                    700
4             1           2000-01-03 00:00:00.000 500                    500
5             1           2000-01-04 00:00:00.000 -200                   0
6             1           2000-01-05 00:00:00.000 1000                   200
7             1           2000-01-05 00:00:00.000 -300                   -800
8             1           2000-01-06 00:00:00.000 -300                   -500
9             1           2000-01-07 00:00:00.000 -200                   -200
10            2           2000-01-01 00:00:00.000 2000                   4950
11            2           2000-01-02 00:00:00.000 100                    2950
12            2           2000-01-03 00:00:00.000 -50                    2850
13            2           2000-01-04 00:00:00.000 500                    2900
14            2           2000-01-05 00:00:00.000 200                    2400
15            2           2000-01-06 00:00:00.000 200                    2200
16            2           2000-01-07 00:00:00.000 1000                   2000
17            2           2000-01-07 00:00:00.000 1000                   1000
18            3           2000-01-01 00:00:00.000 800                    2700
19            3           2000-01-02 00:00:00.000 -300                   1900
20            3           2000-01-03 00:00:00.000 1000                   2200
21            3           2000-01-04 00:00:00.000 1000                   1200
22            3           2000-01-05 00:00:00.000 -800                   200
23            3           2000-01-06 00:00:00.000 2000                   1000
24            3           2000-01-07 00:00:00.000 -1000                  -1000

(24 row(s) affected)

由于在此使用AccountID做了partition by ,所以相对于TransactionID=1的第一行记录，会把accountid=1的所有记录的Balance sum到一块，得到750，因为使用了 CURRENT ROW AND UNBOUNDED FOLLOWING，意思是将从当前行以后的所有记录的balance全部sum到一块。

而对于AccountID=2，TransactionID=10的记录，这是另外一组数据，所以这条记录的CummulativeBalance是将位于TransactionID=10之后的（包括TransactionID=10的这条记录）所有

AccountID=2的记录的Balance全部SUM在一块，因此是4950。

 

--another sample：

--下面的这个case是把当前行，当前行的前一行和后一行的Balance sum到一块（基于以AccountID分组，TransactionDate, TransactionID 排序）

SELECT
    [TransactionID]
 ,[AccountID]
    ,[TransactionDate]
    ,[Balance]
    ,SUM(Balance) OVER (PARTITION BY [AccountID] ORDER BY TransactionDate, TransactionID
                  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS CummulativeBalance
FROM [dbo].[MultiAccounts]
ORDER BY AccountID, TransactionDate, TransactionID

 

Result:

  TransactionID AccountID   TransactionDate         Balance                CummulativeBalance
------------- ----------- ----------------------- ---------------------- ----------------------
1             1           2000-01-01 00:00:00.000 100                    50
2             1           2000-01-01 00:00:00.000 -50                    250
3             1           2000-01-02 00:00:00.000 200                    650
4             1           2000-01-03 00:00:00.000 500                    500
5             1           2000-01-04 00:00:00.000 -200                   1300
6             1           2000-01-05 00:00:00.000 1000                   500
7             1           2000-01-05 00:00:00.000 -300                   400
8             1           2000-01-06 00:00:00.000 -300                   -800
9             1           2000-01-07 00:00:00.000 -200                   -500
10            2           2000-01-01 00:00:00.000 2000                   2100
11            2           2000-01-02 00:00:00.000 100                    2050
12            2           2000-01-03 00:00:00.000 -50                    550
13            2           2000-01-04 00:00:00.000 500                    650
14            2           2000-01-05 00:00:00.000 200                    900
15            2           2000-01-06 00:00:00.000 200                    1400
16            2           2000-01-07 00:00:00.000 1000                   2200
17            2           2000-01-07 00:00:00.000 1000                   2000
18            3           2000-01-01 00:00:00.000 800                    500
19            3           2000-01-02 00:00:00.000 -300                   1500
20            3           2000-01-03 00:00:00.000 1000                   1700
21            3           2000-01-04 00:00:00.000 1000                   1200
22            3           2000-01-05 00:00:00.000 -800                   2200
23            3           2000-01-06 00:00:00.000 2000                   200
24            3           2000-01-07 00:00:00.000 -1000                  1000

(24 row(s) affected)

CummulativeBalance字段值得来的解释说明：

举例如下，

TransactionID=2的记录，CummulativeBalance=前一条记录的Balance+当前记录的Balance+后一条记录的Balance=100+（-50）+200=250

TransactionID=1的记录，CummulativeBalance=前一条记录的Balance（这里没有）+当前记录的Balance+后一条记录的Balance=100+（-50）=50

TransactionID=9的记录，CummulativeBalance=前一条记录的Balance+当前记录的Balance+后一条记录的Balance（这里没有）=-300+（-200）=-500

 

 

    

 

 
 
 
 

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/101629/viewspace-748786/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 介绍SQl server 2012的新分析函数--Offset Functions
下一篇： 使用窗口函数解决实际问题---虚拟数字辅助表
请登录后发表评论 登录
全部评论
hegenhua

注册时间：2011-02-17

  • 博文量
    24
  • 访问量
    63100

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593