Snowflake allows us to calculate the rolling average based on the current value and two preceding values. What about there is a gap in the time series data?
e.g. in the following example, I want to calculate three days moving average. For 7/30, the following query would use 7/25 data when computing the 3-day moving average for 7/30. Is there a way I can avoid this?
CREATE OR REPLACE TABLE STOCK_PRICE(TRADE_DATE DATE, SYMBOL STRING, CLOSE_PRICE float); INSERT INTO STOCK_PRICE VALUES ('2020-07-25', 'AAPL', '800.0'), ('2020-07-25', 'AXP', '90.0'), ('2020-07-30', 'AAPL', '1010.0'), ('2020-07-30', 'AXP', '112.0'), ('2020-07-31', 'AAPL', '1025.0'), ('2020-07-31', 'AXP', '105.0'), ('2020-08-03', 'AAPL', '978.0'), ('2020-08-03', 'AXP', '110.0'), ('2020-08-04', 'AAPL', '970.0'), ('2020-08-04', 'AXP', '115.0'), ('2020-08-05', 'AAPL', '990.0'), ('2020-08-05', 'AXP', '120.0'), ('2020-08-06', 'AAPL', '995.0'), ('2020-08-06', 'AXP', '125.0'), ('2020-08-07', 'AAPL', '990.0'), ('2020-08-07', 'AXP', '122.0'), ('2020-08-10', 'AAPL', '998.0'), ('2020-08-10', 'AXP', '124.0') SELECT TRADE_DATE, SYMBOL, CLOSE_PRICE, AVG(CLOSE_PRICE) OVER (PARTITION BY SYMBOL ORDER BY TRADE_DATE ROWS between 2 PRECEDING AND CURRENT ROW) AS MV_AVG_5DAY FROM STOCK_PRICE 2 Answers
The following seems to work, using your small dataset. It's based on 2 ideas:
- Filling in the "missing" date/symbol records in your data
- The AVG function ignores records with NULL values
The basic approach is as follows:
Create a dataset of all the dates between the min and max TRADE_DATE values in your table
Create a dataset of the unique symbol values in your dataset
Join these 2 datasets together to get all the date/symbol combinations
Join this to your table to get a dataset with no date/symbol gaps
Run a variant of your query against this new dataset
-- Set MIN/MAX dates set min_date = (select min(trade_date) from stock_price); set max_date = (select max(trade_date) from stock_price); -- set parameter to be used as generator "constant" including the start day set num_days = (Select datediff(day, $min_date,$max_date+1)); -- Create a list of all dates between the min/max dates in the original table with date_list as ( select dateadd(day,'-' || row_number() over (order by null),dateadd(day, '+1', $max_date)) as date from table (generator(rowcount => ($num_days))) ), -- Get a unique list of symbols symbol_list as ( select distinct symbol from stock_price ), -- Create a data set containing every combination of date/symbol all_dates_symbols as ( select date, symbol from date_list, symbol_list -- Cartesian product ), -- Get the stock price for all dates/symbols. This will be null for any combinations not in the original table stock_price_all_dates as ( select t1.date "TRADE_DATE", t1.symbol "SYMBOL", t2.close_price "CLOSE_PRICE" from all_dates_symbols t1 left outer join STOCK_PRICE t2 on t1.date = t2.trade_date and t1.symbol = t2.symbol ), -- Calculate the average over the preceding x days. Nulls for any date/symbol should not be included MV_5DAY AS ( SELECT T1.TRADE_DATE, T1.SYMBOL, T1.CLOSE_PRICE, AVG(T1.CLOSE_PRICE) OVER (PARTITION BY T1.SYMBOL ORDER BY T1.TRADE_DATE ROWS between 2 PRECEDING AND CURRENT ROW) AS MV_AVG_5DAY FROM stock_price_all_dates T1 ) -- Join back to original table to exclude all records that don't exist in that table SELECT T1.TRADE_DATE, T1.SYMBOL, T1.CLOSE_PRICE, T1.MV_AVG_5DAY FROM MV_5DAY T1 INNER JOIN STOCK_PRICE T2 ON T1.TRADE_DATE = T2.TRADE_DATE AND T1.SYMBOL = T2.SYMBOL;-- clean up previously set parameter variable unset num_days; unset min_date; unset max_date;
What is the expected output for days that have less than 3 days of windowed data? For example, should the first row calculate an average of 800 (based on just that row)? If so, then something like this might work (although it could become unwieldy if you ever want the window to be far greater than just 3 days):
WITH CTE_STOCK_PRICE AS ( SELECT TRADE_DATE ,SYMBOL ,CLOSE_PRICE ,CLOSE_PRICE AS CLOSE_PRICE_FOR_AVG FROM STOCK_PRICE UNION ALL SELECT TRADE_DATE ,SYMBOL ,CLOSE_PRICE ,LAG(CLOSE_PRICE,1) OVER (PARTITION BY SYMBOL ORDER BY TRADE_DATE) AS CLOSE_PRICE_FOR_AVG FROM STOCK_PRICE QUALIFY LAG(TRADE_DATE,1) OVER (PARTITION BY SYMBOL ORDER BY TRADE_DATE) = DATEADD(DAY, -1, TRADE_DATE) UNION ALL SELECT TRADE_DATE ,SYMBOL ,CLOSE_PRICE ,LAG(CLOSE_PRICE,2) OVER (PARTITION BY SYMBOL ORDER BY TRADE_DATE) AS CLOSE_PRICE_FOR_AVG FROM STOCK_PRICE QUALIFY LAG(TRADE_DATE,2) OVER (PARTITION BY SYMBOL ORDER BY TRADE_DATE) = DATEADD(DAY, -2, TRADE_DATE) ) SELECT TRADE_DATE ,SYMBOL ,ANY_VALUE(CLOSE_PRICE) AS CLOSE_PRICE ,AVG(CLOSE_PRICE_FOR_AVG) AS MV_AVG_3DAY FROM CTE_STOCK_PRICE GROUP BY TRADE_DATE ,SYMBOL ORDER BY TRADE_DATE ,SYMBOL ; Also note that you specified that you want a 3 day window, but your computed column was named MV_AVG_5DAY; I adjusted my column name to match a 3 day spec.
