SQL - Calculate sum of depth_progress for previous 24 hrs for every record?

user_17412833 0
Viewed 43

SQL - Calculate sum of md_delta for previous 24 hrs for every record.

Here is an example of what I am shooting for

enter image description here

Basically, I am trying to create a query that returns the base table unaltered, such that for each row in left table it returns the sum of MD_DELTA over the previous 24 hrs for each unique ID.

I'm thinking I would likely need to use a correlated subquery but its does not seem to be working correctly.

Here is an code example:

SELECT
 PG."INFO_ID" AS ID
,PG."RANK"
,PG."TIMEPOINT" 
,PG."MD_DELTA"
,G."TOTAL_24HR_DELTA"
FROM CTE_PGROUPS AS PG
    LEFT JOIN( 
    /*
       CORRELATED SUB-QUERY TO GET THE SUM OF THE DEPTH_PROGRESS FROM THEPREVIOUS 
       24-HOUR TIME PERIOD FOR EACH
    */
    SELECT 
      G."TIMEPOINT"
      ,SUM(G."MD_DELTA") AS TOTAL_24HR_DELTA
    FROM CTE_PGROUPS AS G
    WHERE G."TIMEPOINT" >= DATEADD('DAY',-1, G."TIMEPOINT")
    GROUP BY G."TIMEPOINT"
   ) AS G ON PG."TIMEPOINT" = G."TIMEPOINT"
correlated-subquery snowflake-cloud-data-platform sql
1 Answers

Using this CTE for data:

WITH data AS (
    SELECT 
    *
    FROM VALUES
        (306, '2022-03-21 01:00:00'::timestamp, 0.5),  
        (306, '2022-03-21 08:00:00'::timestamp, 0.5),  
        (306, '2022-03-21 16:00:00'::timestamp, 0.5),  
        (306, '2022-03-21 21:00:00'::timestamp, 0.5),  
        (306, '2022-03-22 02:00:00'::timestamp, 0.5),  
        (306, '2022-03-22 06:00:00'::timestamp, 0.5),  
        (306, '2022-03-22 12:00:00'::timestamp, 0.5),  
        (306, '2022-03-22 18:00:00'::timestamp, 0.5),  
        (306, '2022-03-22 22:00:00'::timestamp, 0.5)
        v(id, timepoint, depth_progress)
)

this SQL gives:

SELECT d.id, 
    d.timepoint, 
    sum(d2.depth_progress)
FROM data AS d
JOIN data AS d2 
    ON d.id = d2.id 
        AND d2.timepoint between dateadd(day,-1,d.timepoint) and d.timepoint
GROUP BY 1,2
ORDER BY 1,2;

gives:

ID TIMEPOINT SUM(D2.DEPTH_PROGRESS)
306 2022-03-21 01:00:00.000 0.5
306 2022-03-21 08:00:00.000 1
306 2022-03-21 16:00:00.000 1.5
306 2022-03-21 21:00:00.000 2
306 2022-03-22 02:00:00.000 2
306 2022-03-22 06:00:00.000 2.5
306 2022-03-22 12:00:00.000 2.5
306 2022-03-22 18:00:00.000 2.5
306 2022-03-22 22:00:00.000 2.5

and if your table as "missive" I would pre-condition that data like:

WITH massive_pre_condition as (
    select *,
        dateadd(day,-1,timepoint) as minus_one_day,
        timepoint::date as d1,
        minus_one_day::date as d2
    FROM data
)
SELECT d.id, 
    d.timepoint, 
    sum(d2.depth_progress)
FROM massive_pre_condition AS d
JOIN massive_pre_condition AS d2 
    ON d.id = d2.id 
        AND d2.d1 IN (d.d1, d.d2)
        AND d2.timepoint between d.minus_one_day and d.timepoint
GROUP BY 1,2
ORDER BY 1,2;
user_439920
Related