LAG function + AVG combined
-
Hello everyone,
I have a situation where I need to create something most likely like LAG - AVG functions together.
I need to create a column called 'ROLL1'
-- ROLL1 should be average of ONQTYCOGS for each next week adding up to it. For example, for the week '20210109' it should be just as it is '45,992,580.37'. For the next week (20210116) it should be average of two weeks (20210109) and (20210116). It should move like that up to 52 next cells (weeks). When it hits 52 weeks, it has to start omitting the 1st cell, and start doing average of 2 - 53, after that 3 - 54.
-- It should not look at DC, just pure calculation of ONQTYCOGS on rolling 1 to 52 weeks basis.
That is the sample of output I am trying to achieve.
I thought it has smth to do with LAG function.
Does someone have any thoughts?
-
If you are hoping for a coded solution, please provide DDL and INSERTs with sample data, and desired results based on the sample data.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
As per Phil needs data. Hmm... either the rows would need to be expanded or dynamic sql? In order to use ROWS BETWEEN N PRECEDING we're stuck with N being
<unsigned value specification> ::=
{ <unsigned integer literal> }Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Expanding on Phil's good point, we will need your help in order to be able to help you, so please help us!
😎
It would be greatly appreciated if you could provide the DDL (create table) script, sample data as an insert statement, the desired output from the sample data, and what you have tried so far.
-
/* drop table DC_ANALYSIS;
create table DC_ANALYSIS (
DC int,
WED int,
ONQTYCOGS decimal(12,2),
OBCOGS decimal(12,2))
*/
The explanation what I am trying to achieve is above. ROLL1 should be average of 1 week for the first row, for the second row average of two weeks, for the 3rd row average of the first 3 rows and so on. It should go up to 52 weeks. On week 53 it needs to start being average of 2 - 53 weeks, on week 54 average of 3 - 54 weeks.
-
Quick advice, don't post data as an Excel spreadsheet unless the question is not a database question but a question about Excel!
😎
I'll show you how to post sample data, unfortunately, since you didn't do that, I only have time for that demonstration, no time left for answering the question.
USE TEEST;
GO
SET NOCOUNT ON;
GO
-- SAMPLE DATA
DECLARE @DC_ANALYSIS TABLE
(
DC INT,
WED INT,
ONQTYCOGS DECIMAL(12,2),
OBCOGS DECIMAL(12,2)
);
INSERT INTO @DC_ANALYSIS(DC,WED,ONQTYCOGS,OBCOGS)
VALUES
(1,20210109,46006690.08,4418684.037)
,(1,20210116,46850620.15,4600730.795)
,(1,20210123,44024873.22,4945695.255)
,(1,20210130,41890335.09,5099716.106)
,(1,20210206,42779078.76,5261295.795)
,(1,20210213,42981144.91,5948491.009)
,(1,20210220,40253618.61,5270004.655)
,(1,20210227,40081124.44, 4808358.71)
,(1,20210306,41839026.44, 4045523.57)
,(1,20210313,44106466.19,4134441.098)
,(1,20210320, 43467977.9,5162273.281)
,(1,20210327,42064240.71,6620009.677)
,(1,20210403,41762181.63,5357849.181)
,(1,20210410,44058284.45,4143532.612)
,(1,20210417,47404094.49,5809324.163)
,(1,20210424,45972185.94,5171018.652)
,(1,20210501,44036490.23,7402723.215)
,(1,20210508,45586986.52,4061109.503)
,(1,20210515,45856752.66,5537647.346)
,(1,20210522,45059547.06,5599946.096)
,(1,20210529,45068938.29,5563866.229)
,(1,20210605,45645400.35,3711782.447)
,(1,20210612,46170147.85,4097535.318)
,(1,20210619,42815884.02,6405624.495)
,(1,20210626,41378848.72,4227208.357)
,(1,20210703,41217299.48,5270425.849)
,(1,20210710,42843858.84,3197818.704)
,(1,20210717,42131174.54,4407717.534)
,(1,20210724,43938004.04,5108379.476)
,(1,20210731,43326104.83,5105471.482)
,(1,20210807,43666749.17,4610093.756)
,(1,20210814,46187581.65,4952768.015)
,(1,20210821, 45837567,5472258.072)
,(1,20210828,46725879.11,5433125.834)
,(1,20210904,49262332.02, 5342681.81)
,(1,20210911,51681950.06,4430270.265)
,(1,20210918, 55591083.1,6782395.253)
,(1,20210925,65113008.42,5663694.583)
,(1,20211002,63231337.55,8060232.788)
,(1,20211009,65854526.05,7317676.297)
,(1,20211016,65250065.22,10172733.14)
,(1,20211023, 67308147.7, 8567027.18)
,(1,20211030,65976157.48,8379320.922)
,(1,20211106,64461910.21,8345083.484)
,(1,20211113,61572911.58,7825205.827)
,(1,20211120,60603980.63,7844334.699)
,(1,20211127,59269147.12,6631390.212)
,(1,20211204,58461805.87,6667207.764)
,(1,20211211,56442154.92,6360453.332)
,(1,20211218,54461393.65, 6408360.29)
,(1,20211225,52197525.66,5560020.649)
,(1,20220101,52226709.51,3766830.704)
,(1,20220108,51256136.33,5470130.996)
,(1,20220115,50942014.42,5107289.539)
,(1,20220122,49661795.38,5233847.714)
,(1,20220129,50939692.92,4683925.842)
,(1,20220205,51842675.25,6315180.064)
,(1,20220212,52416091.25,5929761.057)
,(1,20220219,52113561.16,5886823.042)
,(1,20220226,53260743.58,5829605.979)
,(1,20220305,54539004.27,5357195.922)
,(1,20220312, 55015012.4,4528263.465)
,(1,20220319,56071575.94,4032710.182)
,(1,20220326,55028008.15,6751057.514)
,(1,20220402,55405347.08,5763818.488)
,(1,20220409,55155780.68,6982275.463)
,(1,20220416,53824327.58,6191991.545)
,(1,20220423,52926230.86,6525188.153)
,(1,20220430,52894642.02,6431423.763)
,(1,20220507,53010438.65,4994582.338)
,(1,20220514,52465937.79,5809314.961)
,(1,20220521,51780323.55,5369958.252)
,(1,20220528,49253492.25,6189012.969)
,(1,20220604,47888588.65,4611582.829)
,(1,20220611,49668100.03,5215077.276)
,(1,20220618,47928291.35,6058771.394)
,(1,20220625,48034040.02,5184427.017)
,(1,20220702,47506443.86,6736250.874)
,(1,20220709,49127379.91,3141593.356)
,(1,20220716,49265664.71,6283595.281)
,(1,20220723,51680061.53,5011031.338)
,(1,20220730,53758643.86,5167640.135)
,(1,20220806,59193598.16,5894838.776)
,(1,20220813, 60948960.6,8084639.927)
,(1,20220820,67145956.66,7887696.237)
,(1,20220827,65192361.59, 5778539.09)
,(1,20220903, 69958813.1,6434818.991)
,(1,20220910,72128928.01,6665959.857)
,(1,20220917,74452265.94,8707111.927)
,(1,20220924,76908252.85,7866325.378)
,(1,20221001,80363890.78,8789075.648)
,(1,20221008,84041538.24,8739580.656)
,(1,20221015,85426695.52,9247728.417)
,(1,20221022,83562126.05,10967278.43)
,(1,20221029,81138730.93,11822986.68)
,(1,20221105,79732490.19,9080443.799)
,(1,20221112,77560949.75,8632897.217)
,(1,20221119,75148086.02,9168638.466)
,(1,20221126,72452319.36,8270097.263)
,(1,20221203,70856978.21,7351314.694)
;
SELECT
DA.DC
,DA.WED
,DA.ONQTYCOGS
,DA.OBCOGS
FROM @DC_ANALYSIS DA -
I thought that I need to do just simple moving average
AVG([ONQTYCOGS]) OVER ( ORDER BY [WED]
ROWS BETWEEN 52 PRECEDING AND CURRENT ROW
) ROLL1
but it doesn't return what I need (the output should be as in Excel)
- JeremyU wrote:
I thought that I need to do just simple moving average
AVG([ONQTYCOGS]) OVER ( ORDER BY [WED] ROWS BETWEEN 52 PRECEDING AND CURRENT ROW ) ROLL1
but it doesn't return what I need (the output should be as in Excel)
It should be ROWS BETWEEN 51 PRECEDING, not 52 PRECEDING. You want a total of 52 rows (51 preceding plus the current) not 53 rows (52 preceding plus the current).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
@drew.allen it is still wrong though.
The first row should return 46006690.094600 and it returns smth unexpected 36980572.510250
-
You should ORDER BY the entire query in the same order as the ORDER BY in the OVER clause.
SELECT DA.DC,
DA.WEEK,
DA.ONQTYCOGS,
DA.OBCOGS,
AVG(DA.ONQTYCOGS) OVER (ORDER BY DA.DC, DA.WEEK ROWS BETWEEN 51 PRECEDING AND CURRENT ROW) ROLL1
FROM @WEEKLYFINALDC DA
ORDER BY DA.DC, DA.WEEK -
@jonathan-2 AC Roberts it works. Thank you!!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply