- PRECEDING -当前行前面的固定行数
- CURRENT ROW - 当前行
- UNBOUNDED PRECEDING - 当前行之前的所有记录
- FOLLOWING - 当前行之后的固定行数
- UNBOUNDED FOLLOWING - 当前行之后的所有记录
--======================
CREATE DATABASE WindowFunctionsTest
GO
USE WindowFunctionsTest
GO
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
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
[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
[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
------------- ----------------------- ---------------------- ----------------------
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
[TransactionID]
,[TransactionDate]
,[Balance]
,SUM(Balance) OVER (ORDER BY TransactionDate, TransactionID ROWS UNBOUNDED PRECEDING ) AS CummulativeBalance
FROM [dbo].[Accounts]
ORDER BY TransactionDate, TransactionID
GO
------------- ----------------------- ---------------------- ----------------------
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
[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
------------- ----------------------- ---------------------- ----------------------
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
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博客 ” ,链接:http://blog.itpub.net/101629/viewspace-748786/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/101629/viewspace-748786/