计算给定日期时间+1小时的值

Question

我有这样的数据集：

Date - Time Dimension1  Dimension2  Count
10-1-2020 00:18:15  A   B   2
10-1-2020 01:18:00  A   B   3
10-1-2020 01:45:00  A   B   2
10-1-2020 01:48:00  A   B   1
10-1-2020 03:02:00  B   A   5

我们在Dataset中看到的DateTime列有2维，最后显示的是相对于日期时间+1小时的值的计数。

Record 1 : Given Date : 01/10/2020 12:18:15 AM  Date + 1Hour : 01/10/2020 01:18:15 AM 

因此，在这两个日期之间，我需要找出匹配记录的数量。对于这个日期范围，有2个记录在1小时内，所以计数是2。

代码：

SELECT
D5,
CAL_DATE,
Transaction_DATETIME,
D1,
D2,
D3,
D4,
COUNT(D4) AS CNT
FROM TAB1
WHERE 
timestamp(Transaction_DATETIME, 'MM-D-YYYY HH:mm:ss TT') >= timestamp(Transaction_DATETIME, 'MM-D-YYYY HH:mm:ss TT')
AND timestamp(Transaction_DATETIME, 'MM-D-YYYY HH:mm:ss TT') <= Timestamp(Transaction_DATETIME + Time#('01:00:00','HH:mm:ss'),'MM-D-YYYY HH:mm:ss TT')
GROUP By
D5,
DATE,
Transaction_DATETIME,
D1,
D2,
D3,
D4;

在这个问题上请求你的帮助。

谢谢。

Answer 1

SQL支持用于此目的的窗口函数。在许多数据库中，语法如下所示：

select t.*,
       count(*) over (partition by dim1, dim2
                      order by datetime
                      range between current row and interval '1 hour' following
                     ) as cnt
from t;

并非所有数据库都支持间隔范围为范围的窗口功能。在这些查询中，需要一个子查询：

select t.*,
       (select count(*)
        from t t2
        where t2.dim1 = t.dim1 and t2.dim2 = t.dim2 and
              t2.datetime >= t.datetime and
              t2.datetime < t.datetime + interval '1 hour'
       ) as cnt
from t;

请再次注意，日期/时间函数因数据库而异而臭名昭著。以上是标准SQL，但可能需要对数据库进行调整。