SQL - Calculate sum of md_delta for previous 24 hrs for every record.
Here is an example of what I am shooting for
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"
CodePudding user response:
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;