Median of last 90 days over time series

Marcos Rapoport 1 Reputation point
2022-01-25T21:00:14.267+00:00

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?

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,591 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,784 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Viorel 111.5K Reputation points
    2022-01-25T22:06:38.147+00:00

    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
    

  2. Isabellaz-1451 3,611 Reputation points
    2022-01-26T06:21:29.943+00:00

    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.

    0 comments No comments

  3. LiHong-MSFT 10,046 Reputation points
    2022-01-26T07:28:55.353+00:00

    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.

    0 comments No comments