SQL query with loop to collect 14 day averages-CodePudding

I am a python user, new to SQL. I am trying to make a new table that is the average of the "NumberValue" column between two dates from "RecordDate" column, and slide that date range so that the new table has columns "average" that is the average of "NumberValue" over the date range, "start_date" that is the beginning of the range, and "end_date" that is the end of the range. I can do this for a single date range at a time with something like:

SELECT AVG(NumberValue) AS average, DATEADD(DAY, -14, GETDATE()) AS start_date, DATEADD(DAY, 0, GETDATE()) AS end_date
FROM ResultsData
WHERE RecordDate BETWEEN DATEADD(DAY, -14, GETDATE()) AND DATEADD(DAY, 0, GETDATE())

but would like to slide the -14 and 0 with some sort of loop. Something like looping over the expression below with i and j changing by -14 each iteration and appending the new row each iteration:

SELECT AVG(NumberValue) AS average, DATEADD(DAY, i, GETDATE()) AS start_date, DATEADD(DAY, j, GETDATE()) AS end_date
FROM ResultsData
WHERE RecordDate BETWEEN DATEADD(DAY, i, GETDATE()) AND DATEADD(DAY, j, GETDATE())

I'd also like to make sure the date ranges are within what is available in the table, I'm assuming I can do this with some sort of a WHILE RecordDate > MIN(RecordDate) inside the loop?

Can anyone suggest the best way to do this?

CodePudding user response：

you can use the over window to create a look back. The over uses the current row then averages 14 preceding rows of data for price. you can use matplotlib.pyplot to see the moving average against the actual data.

the range window will average by date accumulatively. The moving average does not change until the date changes.

select *,
  IsNull(avg(Price) OVER(ORDER BY Date
     ROWS BETWEEN 14 PRECEDING AND CURRENT ROW ),0)
     as moving_average
from stock_price;


try range



SELECT 
      cast([date] as date) as date
      ,[price]
      ,IsNull(avg([price]) over (order by cast([date]  as date)
      RANGE BETWEEN
          unbounded preceding and current row
      ),0) moving_average
  FROM stock_price

CodePudding user response：

I think you want something more like this. If you aggregate all the daily averages you'll get down to one row per date. From that point it's easy to use the rolling window. Note that it does assume there will be at least one row to represent every date.

with params as (
    select cast(<start> as date) as startDt, cast(<end> as date) as endDt
), d as (
    select
        cast("date" as date) as dt,
        case when cast("date" as date) >= startDt then 1 end as keep,
        avg(NumberValue) as average,
        count(*) as weight 
    from T cross apply (select * from params) p
    where cast("date" as date) between dateadd(day, -13, startDt) and endDt
    group by cast("date" as date)
)
select dt,
    avg(average * weight)
        over (order by dt rows between 13 preceding and current row)
from d
where keep = 1;