Median of last 90 days over time series

Question

Hi people, i'm using SQL Server 2019 and i've tried for the last month and couldn't get a good answer. I have a table with two columns: date and price, which have a price for each working day for the last 20 years, for about 200 stocks, aproximately 250 * 200 * 20 = 1000000 rows. I just want to transform this two columns table into a new three columns table with a third column standing for the median of the price of the last 90 days. I dont care about the first <90 days of median of the stocks.

I made this query

select * from (
select Date, Product_Id, Price
from Data as d) a
cross apply (
select top 1 PERCENTILE_CONT(0.5) within group (order by Price) over () as PriceMedian
from (
select Price
from Data as d
where d.Product_Id = a.Product_Id
and d.ValDate <= a.ValDate
order by ValDate desc
offset 0 rows fetch next 90 rows only) q) q

It does solves the problem but performs poorly. For the indexes I just made a primary key with Date and Product_id columns which generated the clustered unique index but thats all.
I can't even run the query completely, only select just some few days.

Can anyone help me?

Answer 1

Maybe something like this:

select ValDate, Product_Id,
    lag(Price, 45) over (partition by Product_Id order by ValDate) as PriceMedian
from Data

-- or:

select ValDate, Product_Id,
    (lag(Price, 44) over (partition by Product_Id order by ValDate) +
      lag(Price, 45) over (partition by Product_Id order by ValDate)) / 2 as PriceMedian
from Data

Answer 2

Hi @Marcos Rapoport

Maybe you can try this：

  select  * from Data outtable  
 cross apply( select DISTINCT Product_Id, PERCENTILE_CONT(0.5) within group (order by price) over () as PriceMedian from Data intable where outtable.Product_Id = intable.Product_Id and intable.Date<= outtable.Date and DATEDIFF(DAY,intable.Date,outtable.Date)<90 ) a  

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi,@Marcos Rapoport
Please check this:

;WITH CTE AS  
(  
 SELECT date, price FROM Data   
 ORDER BY date DESC  
 OFFSET 0 rows FETCH NEXT 90 rows only   
)  
SELECT * FROM Data  CROSS APPLY   
(SELECT (  
         (SELECT MAX(price) FROM (SELECT TOP(50) PERCENT price   
		                          FROM CTE   
			                      ORDER BY price)AS M1)    
        +  
		 (SELECT MIN(price) FROM (SELECT TOP(50) PERCENT price   
		                          FROM CTE   
			                      ORDER BY price DESC)AS M2)  
	   )/2 AS PriceMedian )A  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.