Hi
I am working on a rolling average calculation that is not working quite right. Here is the code:
AVG(T1.PRICE) OVER (PARTITION BY T1.UNIT ORDER BY T1.DATE ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
This will calculate a rolling average using the last 20 rows including the current row. This works great if there are actually 20 rows of data. The problem is if there are less than 20 rows. For example, if there are 10 records available. In this case, the above code will calculate average for 10 records. This is incorrect behaviour. If the logic is asking for 20 records then there needs to be at least 20 records available. If there are less than 20 available then output NULL. How can I modify my above code so if there are less than 20 records available then output NULL otherwise output the average? 20 is just an example. It can be any integer value.
The underlying data is financial data so there will be only data available on business days so there will be gaps in the dates. This is important to know because if I wanted, say last 5 days of data, then I would need count 5 records not subtracting 5 from today's date to get the last 5 days. 5 is an example. It can be any integer value.
Thank you
December 28, 2024 at 9:36 pm
Where's your sample data? A calendar table determines the workdays? This is asking a bit. Using AVG OVER can only accept literal numbers in the RANGE PRECEDING and afaik it cannot be parameterized in any way. So presumably you're ok with adjusting the query itself to alter the range, yes?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
SELECT T1.UNIT,
T1.DATE,
CASE WHEN COUNT(T1.PRICE) OVER (PARTITION BY T1.UNIT ORDER BY T1.DATE ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) = 20
THEN AVG(T1.PRICE) OVER (PARTITION BY T1.UNIT ORDER BY T1.DATE ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
ELSE NULL
END AS ROLLING_AVG
FROM YourTable T1
;
December 28, 2024 at 11:00 pm
SELECT T1.UNIT,
T1.DATE,
CASE WHEN COUNT(T1.PRICE) OVER (PARTITION BY T1.UNIT ORDER BY T1.DATE ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) = 20
THEN AVG(T1.PRICE) OVER (PARTITION BY T1.UNIT ORDER BY T1.DATE ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
ELSE NULL
END AS ROLLING_AVG
FROM YourTable T1
;
Thank you so much. it works!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply