DEMA
Updated: 31 Oct 2012
Use DEMA to calculate the daily exponential weighted moving average of column values in an ordered resultant table, without the need for a self-join. DEMA allows you to define the size of a window calculates the DEMA for each row in the window. If the column values are presented to the functions out of order, an error message will be generated.
Syntax
SELECT [Example].[wct].[DEMA](
<@Val, float,>
,<@Days, int,>
,<@Lag, int,>
,<@FirstValue, nvarchar(4000),>
,<@RowNum, int,>
,<@Id, tinyint,>)
GO
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.
@Days
the size of the window, including the current row. @Days is the number or rows, including the current row, in the window. @Days is an expression of type int or of a type that can be implicitly converted to int.
@Lag
permits the exponentially weighted moving average to by returned with the current row (@Lag = 0) or the subsequent row (@Lag = 1). @Lag is an expression of type int or of a type that can be implicitly converted to int.
@FirstValue
Identifies whether the first value returned for the window is calculated using the exponential-weighted moving average from the beginning of the set ('E'), the last value immediately prior to the current row ('L') or the simple average ('S').
@RowNum
the number of the row within the group for which the sum 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 DEMA calculation. @Id allows you to specify multiple DEMA calculations within a resultant table. @Id is an expression of type tinyint or of a type that can be implicitly converted to tinyint.
Remarks
· If @Id is NULL then @Id = 0.
· To calculate daily weighted moving averages, use the DWMA function.
· To calculate a moving exponentially weighted moving average, use the MovingEWMA function,
· Alpha is automatically calculated as 2 / (@Days + 1).
· @Lag must be 0 or 1.
· @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 will calculate the exponentially-weighted moving average for the closing price of MSFT. We will create a temporary table, #p, populate it with some data and then run the SELECT.
--Create the temporary table
CREATE TABLE #p(
ticker varchar(4),
date_trade datetime,
price_adj_close money,
PRIMARY KEY (ticker, date_trade)
)
--Put data into the table
INSERT INTO #p VALUES ('MSFT','2012-10-31',28.54)
INSERT INTO #p VALUES ('MSFT','2012-10-26',28.21)
INSERT INTO #p VALUES ('MSFT','2012-10-25',27.88)
INSERT INTO #p VALUES ('MSFT','2012-10-24',27.9)
INSERT INTO #p VALUES ('MSFT','2012-10-23',28.05)
INSERT INTO #p VALUES ('MSFT','2012-10-22',28)
INSERT INTO #p VALUES ('MSFT','2012-10-19',28.64)
INSERT INTO #p VALUES ('MSFT','2012-10-18',29.5)
INSERT INTO #p VALUES ('MSFT','2012-10-17',29.59)
INSERT INTO #p VALUES ('MSFT','2012-10-16',29.49)
INSERT INTO #p VALUES ('MSFT','2012-10-15',29.51)
INSERT INTO #p VALUES ('MSFT','2012-10-12',29.2)
INSERT INTO #p VALUES ('MSFT','2012-10-11',28.95)
INSERT INTO #p VALUES ('MSFT','2012-10-10',28.98)
INSERT INTO #p VALUES ('MSFT','2012-10-09',29.28)
INSERT INTO #p VALUES ('MSFT','2012-10-08',29.78)
INSERT INTO #p VALUES ('MSFT','2012-10-05',29.85)
INSERT INTO #p VALUES ('MSFT','2012-10-04',30.03)
INSERT INTO #p VALUES ('MSFT','2012-10-03',29.86)
INSERT INTO #p VALUES ('MSFT','2012-10-02',29.66)
INSERT INTO #p VALUES ('MSFT','2012-10-01',29.49)
INSERT INTO #p VALUES ('MSFT','2012-09-28',29.76)
INSERT INTO #p VALUES ('MSFT','2012-09-27',30.16)
INSERT INTO #p VALUES ('MSFT','2012-09-26',30.17)
INSERT INTO #p VALUES ('MSFT','2012-09-25',30.39)
INSERT INTO #p VALUES ('MSFT','2012-09-24',30.78)
INSERT INTO #p VALUES ('MSFT','2012-09-21',31.19)
INSERT INTO #p VALUES ('MSFT','2012-09-20',31.45)
INSERT INTO #p VALUES ('MSFT','2012-09-19',31.05)
INSERT INTO #p VALUES ('MSFT','2012-09-18',31.18)
INSERT INTO #p VALUES ('MSFT','2012-09-17',31.21)
INSERT INTO #p VALUES ('MSFT','2012-09-14',31.21)
INSERT INTO #p VALUES ('MSFT','2012-09-13',30.94)
INSERT INTO #p VALUES ('MSFT','2012-09-12',30.78)
INSERT INTO #p VALUES ('MSFT','2012-09-11',30.79)
INSERT INTO #p VALUES ('MSFT','2012-09-10',30.72)
INSERT INTO #p VALUES ('MSFT','2012-09-07',30.95)
INSERT INTO #p VALUES ('MSFT','2012-09-06',31.35)
INSERT INTO #p VALUES ('MSFT','2012-09-05',30.39)
INSERT INTO #p VALUES ('MSFT','2012-09-04',30.39)
INSERT INTO #p VALUES ('MSFT','2012-08-31',30.82)
INSERT INTO #p VALUES ('MSFT','2012-08-30',30.32)
INSERT INTO #p VALUES ('MSFT','2012-08-29',30.65)
INSERT INTO #p VALUES ('MSFT','2012-08-28',30.63)
INSERT INTO #p VALUES ('MSFT','2012-08-27',30.69)
INSERT INTO #p VALUES ('MSFT','2012-08-24',30.56)
INSERT INTO #p VALUES ('MSFT','2012-08-23',30.26)
INSERT INTO #p VALUES ('MSFT','2012-08-22',30.54)
INSERT INTO #p VALUES ('MSFT','2012-08-21',30.8)
INSERT INTO #p VALUES ('MSFT','2012-08-20',30.74)
INSERT INTO #p VALUES ('MSFT','2012-08-17',30.9)
INSERT INTO #p VALUES ('MSFT','2012-08-16',30.78)
INSERT INTO #p VALUES ('MSFT','2012-08-15',30.2)
INSERT INTO #p VALUES ('MSFT','2012-08-14',30.13)
INSERT INTO #p VALUES ('MSFT','2012-08-13',30.19)
INSERT INTO #p VALUES ('MSFT','2012-08-10',30.22)
INSERT INTO #p VALUES ('MSFT','2012-08-09',30.3)
INSERT INTO #p VALUES ('MSFT','2012-08-08',30.13)
INSERT INTO #p VALUES ('MSFT','2012-08-07',30.06)
INSERT INTO #p VALUES ('MSFT','2012-08-06',29.75)
INSERT INTO #p VALUES ('MSFT','2012-08-03',29.55)
INSERT INTO #p VALUES ('MSFT','2012-08-02',29)
INSERT INTO #p VALUES ('MSFT','2012-08-01',29.22)
INSERT INTO #p VALUES ('MSFT','2012-07-31',29.28)
--Calculate the 5-day exponentially weighted moving average
SELECT cast(date_trade as date) as date_trade
,price_adj_close
,wct.DEMA(price_adj_close,5,0,NULL, ROW_NUMBER() OVER (ORDER BY date_trade),NULL) as [5-day]
FROM #p
ORDER BY 1
--Clean up
DROP TABLE #p
This produces the following result.
date_trade price_adj_close 5-day
---------- --------------------- ----------------------
2012-07-31 29.28 NULL
2012-08-01 29.22 NULL
2012-08-02 29.00 NULL
2012-08-03 29.55 NULL
2012-08-06 29.75 29.4492592592593
2012-08-07 30.06 29.36
2012-08-08 30.13 29.6166666666667
2012-08-09 30.30 29.8444444444444
2012-08-10 30.22 29.9696296296296
2012-08-13 30.19 30.0430864197531
2012-08-14 30.13 30.0720576131687
2012-08-15 30.20 30.1147050754458
2012-08-16 30.78 30.3364700502972
2012-08-17 30.90 30.5243133668648
2012-08-20 30.74 30.5962089112432
2012-08-21 30.80 30.6641392741621
2012-08-22 30.54 30.6227595161081
2012-08-23 30.26 30.5018396774054
2012-08-24 30.56 30.5212264516036
2012-08-27 30.69 30.5774843010691
2012-08-28 30.63 30.5949895340461
2012-08-29 30.65 30.6133263560307
2012-08-30 30.32 30.5155509040205
2012-08-31 30.82 30.6170339360136
2012-09-04 30.39 30.5413559573424
2012-09-05 30.39 30.4909039715616
2012-09-06 31.35 30.7772693143744
2012-09-07 30.95 30.8348462095829
2012-09-10 30.72 30.796564139722
2012-09-11 30.79 30.794376093148
2012-09-12 30.78 30.7895840620987
2012-09-13 30.94 30.8397227080658
2012-09-14 31.21 30.9631484720438
2012-09-17 31.21 31.0454323146959
2012-09-18 31.18 31.0902882097973
2012-09-19 31.05 31.0768588065315
2012-09-20 31.45 31.2012392043543
2012-09-21 31.19 31.1974928029029
2012-09-24 30.78 31.0583285352686
2012-09-25 30.39 30.8355523568457
2012-09-26 30.17 30.6137015712305
2012-09-27 30.16 30.4624677141537
2012-09-28 29.76 30.2283118094358
2012-10-01 29.49 29.9822078729572
2012-10-02 29.66 29.8748052486381
2012-10-03 29.86 29.8698701657588
2012-10-04 30.03 29.9232467771725
2012-10-05 29.85 29.8988311847817
2012-10-08 29.78 29.8592207898544
2012-10-09 29.28 29.6661471932363
2012-10-10 28.98 29.4374314621575
2012-10-11 28.95 29.274954308105
2012-10-12 29.20 29.2499695387367
2012-10-15 29.51 29.3366463591578
2012-10-16 29.49 29.3877642394385
2012-10-17 29.59 29.4551761596257
2012-10-18 29.50 29.4701174397505
2012-10-19 28.64 29.1934116265003
2012-10-22 28.00 28.7956077510002
2012-10-23 28.05 28.5470718340001
2012-10-24 27.90 28.3313812226668
2012-10-25 27.88 28.1809208151112
2012-10-26 28.21 28.1906138767408
2012-10-31 28.54 28.3070759178272