DWMA
Updated 2024-03-14 14:59:13.047000
Syntax
SELECT [westclintech].[wct].[DWMA](
<@Val, float,>
,<@Days, int,>
,<@Lag, int,>
,<@RowNum, int,>
,<@Id, tinyint,>)
Description
Use the scalar function DWMA function to calculate a daily weighted moving average across multiple rows within a resultant table, without the need for a self-join. DWMA allows you to define the size of a window and calculates the DWMA for each row in the window. The following equation describes the DWMA calculation:
\operatorname{DWMA}=\ \frac{nx_0+\left(n-1\right)x_{-1}+\left(n-2\right)x_{-2}.\ .\ .{+\ x}_{-n+1}}{\frac{n\left(n+1\right)}{2}}
Where:
{
"columns": [
{
"field": "column 1",
"maxWidth": 200
},
{
"field": "column 2",
"maxWidth": 500
}
],
"rows": [
{
"column 1": "n",
"column 2": "is the window size"
},
{
"column 1": "x₀",
"column 2": "is the most current row in the window"
} ]
}
If the column values are presented to the functions out of order, an error message will be generated.
Arguments
@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.
@Id
a unique identifier for the DWMA calculation. @Id allows you to specify multiple DWMA calculations within a resultant table. @Id is an expression of type tinyint or of a type that can be implicitly converted to tinyint.
@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 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.
@Lag
Indicates that the daily weighted moving average is to be displayed in 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.
Return Type
Remarks
If @Id is NULL then @Id = 0.
@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.
Examples
In this example we will calculate the 5-day 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 weighted moving average
SELECT cast(date_trade as date) as date_trade,
price_adj_close,
wct.DWMA(price_adj_close, 5, 0, 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.4446666666667
2012-08-07 30.06 29.678
2012-08-08 30.13 29.8826666666667
2012-08-09 30.30 30.0833333333333
2012-08-10 30.22 30.1706666666667
2012-08-13 30.19 30.2033333333333
2012-08-14 30.13 30.1866666666667
2012-08-15 30.20 30.1886666666667
2012-08-16 30.78 30.3793333333333
2012-08-17 30.90 30.578
2012-08-20 30.74 30.678
2012-08-21 30.80 30.7613333333333
2012-08-22 30.54 30.7133333333333
2012-08-23 30.26 30.5493333333333
2012-08-24 30.56 30.52
2012-08-27 30.69 30.5566666666667
2012-08-28 30.63 30.5766666666667
2012-08-29 30.65 30.6146666666667
2012-08-30 30.32 30.5353333333333
2012-08-31 30.82 30.6186666666667
2012-09-04 30.39 30.5413333333333
2012-09-05 30.39 30.484
2012-09-06 31.35 30.7626666666667
2012-09-07 30.95 30.8613333333333
2012-09-10 30.72 30.8413333333333
2012-09-11 30.79 30.8513333333333
2012-09-12 30.78 30.8313333333333
2012-09-13 30.94 30.8386666666667
2012-09-14 31.21 30.9633333333333
2012-09-17 31.21 31.0706666666667
2012-09-18 31.18 31.1353333333333
2012-09-19 31.05 31.1306666666667
2012-09-20 31.45 31.2413333333333
2012-09-21 31.19 31.2313333333333
2012-09-24 30.78 31.086
2012-09-25 30.39 30.8393333333333
2012-09-26 30.17 30.572
2012-09-27 30.16 30.36
2012-09-28 29.76 30.1006666666667
2012-10-01 29.49 29.8466666666667
2012-10-02 29.66 29.7353333333333
2012-10-03 29.86 29.7393333333333
2012-10-04 30.03 29.8206666666667
2012-10-05 29.85 29.8506666666667
2012-10-08 29.78 29.8513333333333
2012-10-09 29.28 29.666
2012-10-10 28.98 29.406
2012-10-11 28.95 29.1946666666667
2012-10-12 29.20 29.1386666666667
2012-10-15 29.51 29.2293333333333
2012-10-16 29.49 29.3313333333333
2012-10-17 29.59 29.4526666666667
2012-10-18 29.50 29.5033333333333
2012-10-19 28.64 29.2306666666667
2012-10-22 28.00 28.782
2012-10-23 28.05 28.4506666666667
2012-10-24 27.90 28.1653333333333
2012-10-25 27.88 27.986
2012-10-26 28.21 28.0246666666666
2012-10-31 28.54 28.202
In this example, we use the LAG function to shoe how the DWMA calculation works.
SELECT cast(date_trade as date) as date_trade,
price_adj_close,
(wct.LAG(price_adj_close * 1, 5, NULL, ROW_NUMBER() OVER (ORDER BY date_trade)
, 5)
+ wct.LAG(price_adj_close * 2, 4, NULL, ROW_NUMBER() OVER (ORDER BY
date_trade), 4)
+ wct.LAG(price_adj_close * 3, 3, NULL, ROW_NUMBER() OVER (ORDER BY
date_trade), 3)
+ wct.LAG(price_adj_close * 4, 2, NULL, ROW_NUMBER() OVER (ORDER BY
date_trade), 2)
+ wct.LAG(price_adj_close * 5, 1, NULL, ROW_NUMBER() OVER (ORDER BY
date_trade), 1)
) / 15 as [LAG 5-day ],
wct.DWMA(price_adj_close, 5, 1, ROW_NUMBER() OVER (ORDER BY date_trade),
NULL) as [DWMA 5-day]
FROM #p;
This produces the following result.
{"columns":[{"field":"date_trade","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"price_adj_close","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"LAG 5-day"},{"field":"DWMA 5-day"}],"rows":[{"date_trade":"2012-07-31","price_adj_close":"29.28","LAG 5-day":"NULL","DWMA 5-day":"NULL"},{"date_trade":"2012-08-01","price_adj_close":"29.22","LAG 5-day":"NULL","DWMA 5-day":"NULL"},{"date_trade":"2012-08-02","price_adj_close":"29.00","LAG 5-day":"NULL","DWMA 5-day":"NULL"},{"date_trade":"2012-08-03","price_adj_close":"29.55","LAG 5-day":"NULL","DWMA 5-day":"NULL"},{"date_trade":"2012-08-06","price_adj_close":"29.75","LAG 5-day":"NULL","DWMA 5-day":"NULL"},{"date_trade":"2012-08-07","price_adj_close":"30.06","LAG 5-day":"29.4446666666667","DWMA 5-day":"29.4446666666667"},{"date_trade":"2012-08-08","price_adj_close":"30.13","LAG 5-day":"29.678","DWMA 5-day":"29.678"},{"date_trade":"2012-08-09","price_adj_close":"30.30","LAG 5-day":"29.8826666666667","DWMA 5-day":"29.8826666666667"},{"date_trade":"2012-08-10","price_adj_close":"30.22","LAG 5-day":"30.0833333333333","DWMA 5-day":"30.0833333333333"},{"date_trade":"2012-08-13","price_adj_close":"30.19","LAG 5-day":"30.1706666666667","DWMA 5-day":"30.1706666666667"},{"date_trade":"2012-08-14","price_adj_close":"30.13","LAG 5-day":"30.2033333333333","DWMA 5-day":"30.2033333333333"},{"date_trade":"2012-08-15","price_adj_close":"30.20","LAG 5-day":"30.1866666666667","DWMA 5-day":"30.1866666666667"},{"date_trade":"2012-08-16","price_adj_close":"30.78","LAG 5-day":"30.1886666666667","DWMA 5-day":"30.1886666666667"},{"date_trade":"2012-08-17","price_adj_close":"30.90","LAG 5-day":"30.3793333333333","DWMA 5-day":"30.3793333333333"},{"date_trade":"2012-08-20","price_adj_close":"30.74","LAG 5-day":"30.578","DWMA 5-day":"30.578"},{"date_trade":"2012-08-21","price_adj_close":"30.80","LAG 5-day":"30.678","DWMA 5-day":"30.678"},{"date_trade":"2012-08-22","price_adj_close":"30.54","LAG 5-day":"30.7613333333333","DWMA 5-day":"30.7613333333333"},{"date_trade":"2012-08-23","price_adj_close":"30.26","LAG 5-day":"30.7133333333333","DWMA 5-day":"30.7133333333333"},{"date_trade":"2012-08-24","price_adj_close":"30.56","LAG 5-day":"30.5493333333333","DWMA 5-day":"30.5493333333333"},{"date_trade":"2012-08-27","price_adj_close":"30.69","LAG 5-day":"30.52","DWMA 5-day":"30.52"},{"date_trade":"2012-08-28","price_adj_close":"30.63","LAG 5-day":"30.5566666666667","DWMA 5-day":"30.5566666666667"},{"date_trade":"2012-08-29","price_adj_close":"30.65","LAG 5-day":"30.5766666666667","DWMA 5-day":"30.5766666666667"},{"date_trade":"2012-08-30","price_adj_close":"30.32","LAG 5-day":"30.6146666666667","DWMA 5-day":"30.6146666666667"},{"date_trade":"2012-08-31","price_adj_close":"30.82","LAG 5-day":"30.5353333333333","DWMA 5-day":"30.5353333333333"},{"date_trade":"2012-09-04","price_adj_close":"30.39","LAG 5-day":"30.6186666666667","DWMA 5-day":"30.6186666666667"},{"date_trade":"2012-09-05","price_adj_close":"30.39","LAG 5-day":"30.5413333333333","DWMA 5-day":"30.5413333333333"},{"date_trade":"2012-09-06","price_adj_close":"31.35","LAG 5-day":"30.484","DWMA 5-day":"30.484"},{"date_trade":"2012-09-07","price_adj_close":"30.95","LAG 5-day":"30.7626666666667","DWMA 5-day":"30.7626666666667"},{"date_trade":"2012-09-10","price_adj_close":"30.72","LAG 5-day":"30.8613333333333","DWMA 5-day":"30.8613333333333"},{"date_trade":"2012-09-11","price_adj_close":"30.79","LAG 5-day":"30.8413333333333","DWMA 5-day":"30.8413333333333"},{"date_trade":"2012-09-12","price_adj_close":"30.78","LAG 5-day":"30.8513333333333","DWMA 5-day":"30.8513333333333"},{"date_trade":"2012-09-13","price_adj_close":"30.94","LAG 5-day":"30.8313333333333","DWMA 5-day":"30.8313333333333"},{"date_trade":"2012-09-14","price_adj_close":"31.21","LAG 5-day":"30.8386666666667","DWMA 5-day":"30.8386666666667"},{"date_trade":"2012-09-17","price_adj_close":"31.21","LAG 5-day":"30.9633333333333","DWMA 5-day":"30.9633333333333"},{"date_trade":"2012-09-18","price_adj_close":"31.18","LAG 5-day":"31.0706666666667","DWMA 5-day":"31.0706666666667"},{"date_trade":"2012-09-19","price_adj_close":"31.05","LAG 5-day":"31.1353333333333","DWMA 5-day":"31.1353333333333"},{"date_trade":"2012-09-20","price_adj_close":"31.45","LAG 5-day":"31.1306666666667","DWMA 5-day":"31.1306666666667"},{"date_trade":"2012-09-21","price_adj_close":"31.19","LAG 5-day":"31.2413333333333","DWMA 5-day":"31.2413333333333"},{"date_trade":"2012-09-24","price_adj_close":"30.78","LAG 5-day":"31.2313333333333","DWMA 5-day":"31.2313333333333"},{"date_trade":"2012-09-25","price_adj_close":"30.39","LAG 5-day":"31.086","DWMA 5-day":"31.086"},{"date_trade":"2012-09-26","price_adj_close":"30.17","LAG 5-day":"30.8393333333333","DWMA 5-day":"30.8393333333333"},{"date_trade":"2012-09-27","price_adj_close":"30.16","LAG 5-day":"30.572","DWMA 5-day":"30.572"},{"date_trade":"2012-09-28","price_adj_close":"29.76","LAG 5-day":"30.36","DWMA 5-day":"30.36"},{"date_trade":"2012-10-01","price_adj_close":"29.49","LAG 5-day":"30.1006666666667","DWMA 5-day":"30.1006666666667"},{"date_trade":"2012-10-02","price_adj_close":"29.66","LAG 5-day":"29.8466666666667","DWMA 5-day":"29.8466666666667"},{"date_trade":"2012-10-03","price_adj_close":"29.86","LAG 5-day":"29.7353333333333","DWMA 5-day":"29.7353333333333"},{"date_trade":"2012-10-04","price_adj_close":"30.03","LAG 5-day":"29.7393333333333","DWMA 5-day":"29.7393333333333"},{"date_trade":"2012-10-05","price_adj_close":"29.85","LAG 5-day":"29.8206666666667","DWMA 5-day":"29.8206666666667"},{"date_trade":"2012-10-08","price_adj_close":"29.78","LAG 5-day":"29.8506666666667","DWMA 5-day":"29.8506666666667"},{"date_trade":"2012-10-09","price_adj_close":"29.28","LAG 5-day":"29.8513333333333","DWMA 5-day":"29.8513333333333"},{"date_trade":"2012-10-10","price_adj_close":"28.98","LAG 5-day":"29.666","DWMA 5-day":"29.666"},{"date_trade":"2012-10-11","price_adj_close":"28.95","LAG 5-day":"29.406","DWMA 5-day":"29.406"},{"date_trade":"2012-10-12","price_adj_close":"29.20","LAG 5-day":"29.1946666666667","DWMA 5-day":"29.1946666666667"},{"date_trade":"2012-10-15","price_adj_close":"29.51","LAG 5-day":"29.1386666666667","DWMA 5-day":"29.1386666666667"},{"date_trade":"2012-10-16","price_adj_close":"29.49","LAG 5-day":"29.2293333333333","DWMA 5-day":"29.2293333333333"},{"date_trade":"2012-10-17","price_adj_close":"29.59","LAG 5-day":"29.3313333333333","DWMA 5-day":"29.3313333333333"},{"date_trade":"2012-10-18","price_adj_close":"29.50","LAG 5-day":"29.4526666666667","DWMA 5-day":"29.4526666666667"},{"date_trade":"2012-10-19","price_adj_close":"28.64","LAG 5-day":"29.5033333333333","DWMA 5-day":"29.5033333333333"},{"date_trade":"2012-10-22","price_adj_close":"28.00","LAG 5-day":"29.2306666666667","DWMA 5-day":"29.2306666666667"},{"date_trade":"2012-10-23","price_adj_close":"28.05","LAG 5-day":"28.782","DWMA 5-day":"28.782"},{"date_trade":"2012-10-24","price_adj_close":"27.90","LAG 5-day":"28.4506666666667","DWMA 5-day":"28.4506666666667"},{"date_trade":"2012-10-25","price_adj_close":"27.88","LAG 5-day":"28.1653333333333","DWMA 5-day":"28.1653333333333"},{"date_trade":"2012-10-26","price_adj_close":"28.21","LAG 5-day":"27.986","DWMA 5-day":"27.986"},{"date_trade":"2012-10-31","price_adj_close":"28.54","LAG 5-day":"28.0246666666667","DWMA 5-day":"28.0246666666666"}]}