Moving avg using OVER RANGE BETWEEN days-CodePudding

I want to find the average of item price bought within the last 365 days. Items are not guaranteed to be bought every day, so I can't fix the number of rows to look back at. So I am trying to use RANGE instead of ROWS, specifying that I look back 365 days from current row's date.

Sample data:

Group by Store and Item
I want to find the avg of prices bought within the last 12 months

Store	Item	Date bought	Price	Avg price across last 365 days
Store 1	Item 1	1/2/2022	1.00	1.00
Store 1	Item 1	6/1/2022	1.75	1.375
Store 1	Item 1	11/2/2022	2.10	1.617
Store 1	Item 1	1/5/2023	3.00	2.283
Store 2	Item 1	3/2/2022	1.55	1.55
Store 2	Item 1	5/5/2022	2.80	2.175

I have tried:

SELECT  
    store, item, date, price,
    SUM(price) OVER (PARTITION BY store, item
                     ORDER BY date ASC
                     RANGE BETWEEN 365 DAY PRECEDING AND CURRENT ROW) AS avg_price
FROM table

Error I get is:

Msg 102, Level 15, State 1, Line 102
Incorrect syntax near 'DAY'

I have tried these variations to address the error but can't get past it:

RANGE BETWEEN '365' DAY PRECEDING AND CURRENT ROW
RANGE BETWEEN INTERVAL 365 DAY PRECEDING AND CURRENT ROW
RANGE BETWEEN 365 PRECEDING AND CURRENT ROW

#3 produces the error

Msg 4194, Level 16, State 1, Line 98
RANGE is only supported with UNBOUNDED and CURRENT ROW window frame delimiters.

Is this a syntax error? I am using Microsoft SQL Server Management Studio.

CodePudding user response：

A good old self-join should work (I converted your dates into ISO format):

with cte as (
    select *
    
    from (
        VALUES  (N'Store 1', N'Item 1', N'2022-01-02', 1.00, 1.00)
        ,   (N'Store 1', N'Item 1', N'2022-06-01', 1.75, 1.375)
        ,   (N'Store 1', N'Item 1', N'2022-11-01', 2.10, 1.617)
        ,   (N'Store 1', N'Item 1', N'2023-01-05', 3.00, 2.283)
        ,   (N'Store 2', N'Item 1', N'2022-03-02', 1.55, 1.55)
        ,   (N'Store 2', N'Item 1', N'2022-05-05', 2.80, 2.175)
    ) t (Store,Item,[Date bought],Price,[Avg price across last 365 days])
    )
select  AVG(c2.price), c.Store, c.Item, c.[Date bought]
from CTE c
LEFT JOIN CTE c2
    On  c2.Store = c.Store
    AND c2.Item = c.Item
    AND c2.[Date bought]  between DATEADD(YEAR, -1,CAST(c.[Date bought] AS DATETIME)) AND c.[Date bought]
GROUP BY c.Store, c.Item, c.[Date bought]

CodePudding user response：

SELECT  
    store, 
    item, 
    date, 
    price,
    AVG(price) AS avg_price
FROM table
WHERE
    date > (select dateadd(year, -1, getdate()));
GROUP BY
    store,
    item,
    date,
    price

the WHERE query will curate your data to all the input in the last year and sql already come with an average query called AVG remove the group by if you don't want all of your data to be in groups