SQL - 计算每条记录过去 24 小时的深度进度总和？

Question

SQL - 计算每条记录过去 24 小时的 md_delta 总和。

这是我拍摄的示例

基本上，我试图创建一个返回未更改的基表的查询，这样对于左表中的每一行，它返回 MD_DELTA 的总和以前的每个唯一 ID 24 小时。

我想我可能需要使用相关子查询，但它似乎无法正常工作。

这是一个代码示例：

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"

Answer 1

使用此 CTE 获取数据：

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)
)

此 SQL 给出：

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;

给出：

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

如果您的表为“邮件”，我会预先调整该数据喜欢：

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;