RunningPRODUCT
Updated: 20 Dec 2013
Use RunningPRODUCT to calculate the product of column values in an ordered resultant table, without the need for a self-join. The product is calculated for each value from the first value to the last value in the ordered group or partition. If the column values are presented to the functions out of order, an error message will be generated.
Syntax
SELECT [wctWindowing].[wct].[RunningPRODUCT](
<@Val, float,>
,<@RowNum, int,>
,<@Id, tinyint,>)
Arguments
@Val
the value passed into the function. @Val is an expression of type float or of a type that can be implicitly converted to float.
@RowNum
the number of the row within the group for which the product is being calculated. If @RowNum for the current row in a set is less than or equal to the previous @RowNum and @RowNum is not equal to 1, an error message will be generated. @RowNum is an expression of type int or of a type that can be implicitly converted to int.
@Id
a unique identifier for the RunningPRODUCT calculation. @Id allows you to specify multiple moving products within a resultant table. @Id is an expression of type tinyint or of a type that can be implicitly converted to tinyint.
Return Type
float
Remarks
· If @Id is NULL then @Id = 0.
· If @RowNum is equal to 1, RunningPRODUCT is equal to @Val
· @RowNum must be in ascending order.
· There may be cases where the order in which the data are returned to the function and the order in which the results are returned are different, generally due to parallelism. You can use OPTION(MAXDOP 1) or OPTION(MAXDOP 1,FORCE ORDER) to help eliminate this problem.
Example
In this example we simply calculate the product of x after each row
SELECT *
,wct.RunningProduct(
x, --@Val
rn, --@RowNum
NULL --@Id
) as PRODUCT
FROM (VALUES
(1,1)
,(2,0.5)
,(3,0.333333333333333)
,(4,0.25)
,(5,0.2)
,(6,0.166666666666667)
,(7,0.142857142857143)
,(8,0.125)
,(9,0.111111111111111)
,(10,0.1)
)n(rn,x)
This produces the following result.
rn x PRODUCT
----------- --------------------------------------- ----------------------
1 1.000000000000000 1
2 0.500000000000000 0.5
3 0.333333333333333 0.166666666666666
4 0.250000000000000 0.0416666666666666
5 0.200000000000000 0.00833333333333332
6 0.166666666666667 0.00138888888888889
7 0.142857142857143 0.000198412698412699
8 0.125000000000000 2.48015873015873E-05
9 0.111111111111111 2.75573192239859E-06
10 0.100000000000000 2.75573192239859E-07
You can combine the RunningPRODUCT function with other arithmetic operations. In this example, we calculate the geometric return for a portfolio by adding 1 to the monthly return figures and then subtracting 1 from the result.
SELECT *
,wct.RunningPRODUCT(1+r,ROW_NUMBER() OVER (ORDER BY dt), NULL) - 1 as [Geometric Return]
FROM (VALUES
('2013-01-31',0.099)
,('2013-02-28',0.006)
,('2013-03-31',0.086)
,('2013-04-30',0.064)
,('2013-05-31',0.003)
,('2013-06-30',-0.011)
,('2013-07-31',0.046)
,('2013-08-31',-0.012)
,('2013-09-30',0.069)
,('2013-10-31',0.094)
,('2013-11-30',0.073)
,('2013-12-31',-0.021)
)n(dt,r)
This produces the following result.
dt r Geometric Return
---------- --------------------------------------- ----------------------
2013-01-31 0.099 0.099
2013-02-28 0.006 0.105594
2013-03-31 0.086 0.200675084
2013-04-30 0.064 0.277518289376
2013-05-31 0.003 0.281350844244128
2013-06-30 -0.011 0.267255984957443
2013-07-31 0.046 0.325549760265485
2013-08-31 -0.012 0.309643163142299
2013-09-30 0.069 0.400008541399118
2013-10-31 0.094 0.531609344290635
2013-11-30 0.073 0.643416826423851
2013-12-31 -0.021 0.60890507306895
In this example, we combine the XLeratorDB LAG function with the RunningPRODUCT function to calculate the geometric returns using the portfolio values.
SELECT *
,wct.RunningProduct(val/wct.LAG(val,1,NULL,ROW_NUMBER() OVER (ORDER BY dt),NULL),ROW_NUMBER() OVER (ORDER BY dt),NULL) - 1 as [Geometric Return]
FROM (VALUES
('2012-12-31',100000)
,('2013-01-31',106200)
,('2013-02-28',110448)
,('2013-03-31',114865.92)
,('2013-04-30',119115.96)
,('2013-05-31',120188)
,('2013-06-30',127399.28)
,('2013-07-31',127781.48)
,('2013-08-31',131231.58)
,('2013-09-30',136743.31)
,('2013-10-31',148639.98)
,('2013-11-30',146559.02)
,('2013-12-31',154619.77)
)n(dt,val)
This produces the following result.
dt val Geometric Return
---------- --------------------------------------- ----------------------
2012-12-31 100000.00 NULL
2013-01-31 106200.00 0.0620000000000001
2013-02-28 110448.00 0.10448
2013-03-31 114865.92 0.1486592
2013-04-30 119115.96 0.1911596
2013-05-31 120188.00 0.20188
2013-06-30 127399.28 0.2739928
2013-07-31 127781.48 0.2778148
2013-08-31 131231.58 0.3123158
2013-09-30 136743.31 0.3674331
2013-10-31 148639.98 0.4863998
2013-11-30 146559.02 0.4655902
2013-12-31 154619.77 0.5461977
In this example we use the LAG and the RunningPRODUCT functions to calculate portfolio returns and compare them to the returns on a benchmark. Note that the @Id parameter must be unique for each invocation of the LAG function and for each invocation of the RunningPRODUCT function but that the same @Id parameter can be used in each function once.
SELECT dt
,port/wct.LAG(
port, --@val
1, --@Offset
NULL, --@DefaultValue
ROW_NUMBER() OVER (ORDER BY dt), --@RowNum
0 --@id
) - 1 as [Monthly Portfolio]
,wct.RunningProduct(
port/wct.LAG(
port, --@val
1, --@offset
NULL, --@DefaultValues
ROW_NUMBER() OVER (ORDER BY dt), --@RowNum
1 --@id
), --@val
ROW_NUMBER() OVER (ORDER BY dt), --@RowNum
0 --@id
) - 1 as [Y-T-D Portfolio]
,bmk/wct.LAG(
bmk, --@val
1, --@offset
NULL, --@DefaultValue
ROW_NUMBER() OVER (ORDER BY dt), --@RowNum
2 --@Id
) - 1 as [Monthly Benchmark]
,wct.RunningProduct(
bmk/wct.LAG(
bmk, --@Val
1, --@Offset
NULL, --@DefaultValue
ROW_NUMBER() OVER (ORDER BY dt), --@RowNum
3 --@Id
), --@Val
ROW_NUMBER() OVER (ORDER BY dt), --@RowNum
1
) - 1 as [Y-T-D Benchmark]
FROM (VALUES
('2012-12-31',100000,1426.19)
,('2013-01-31',106200,1498.11)
,('2013-02-28',110448,1514.68)
,('2013-03-31',114865.92,1569.19)
,('2013-04-30',119115.96,1597.57)
,('2013-05-31',120188,1630.74)
,('2013-06-30',127399.28,1606.28)
,('2013-07-31',127781.48,1685.73)
,('2013-08-31',131231.58,1632.97)
,('2013-09-30',136743.31,1681.55)
,('2013-10-31',148639.98,1756.54)
,('2013-11-30',146559.02,1805.81)
,('2013-12-31',154619.77,1810.65)
)n(dt,port,bmk)
This produces the following results, which has been reformatted for ease of viewing.
|
dt
|
Monthly Portfolio
|
Y-T-D Portfolio
|
Monthly Benchmark
|
Y-T-D Benchmark
|
|
12/31/2012
|
NULL
|
NULL
|
NULL
|
NULL
|
|
1/31/2013
|
0.062
|
0.062
|
0.050428064
|
0.050428064
|
|
2/28/2013
|
0.04
|
0.10448
|
0.011060603
|
0.062046431
|
|
3/31/2013
|
0.04
|
0.1486592
|
0.035987799
|
0.100267145
|
|
4/30/2013
|
0.037000008
|
0.1911596
|
0.018085764
|
0.120166317
|
|
5/31/2013
|
0.008999969
|
0.20188
|
0.020762783
|
0.143424088
|
|
6/30/2013
|
0.06
|
0.2739928
|
-0.014999325
|
0.126273498
|
|
7/31/2013
|
0.003000017
|
0.2778148
|
0.049462111
|
0.181981363
|
|
8/31/2013
|
0.027
|
0.3123158
|
-0.031298013
|
0.144987694
|
|
9/30/2013
|
0.042000028
|
0.3674331
|
0.029749475
|
0.179050477
|
|
10/31/2013
|
0.087000015
|
0.4863998
|
0.04459576
|
0.231631129
|
|
11/30/2013
|
-0.014000002
|
0.4655902
|
0.028049461
|
0.266177718
|
|
12/31/2013
|
0.055000027
|
0.5461977
|
0.002680238
|
0.269571375
|