时间戳

首页 > 解决方案 > 按另一个表上的 DATETIME 范围分组

sql-server - 按另一个表上的 DATETIME 范围分组

问题描述

我有一个有 2 列 startdatetime 和 stopdatetime 的表。

STARTDATETIME             |         STOPDATETIME          |      ID
2019-05-05T05:00:00Z              2019-05-05T06:00:00Z            1
2019-05-05T07:00:00Z              2019-05-05T08:00:00Z            1
2019-05-05T05:00:00Z              2019-05-05T06:00:00Z            2
2019-05-05T07:00:00Z              2019-05-05T08:00:00Z            2
2019-05-05T08:00:00Z              2019-05-05T10:00:00Z            2

第二个表有一个包含 eventdatetime 的列。

EVENTDATETIME           |        ID         |       Event
 2019-05-05T05:30:00Z             1                   1
 2019-05-05T05:45:00Z             1                   1
 2019-05-05T07:30:00Z             1                   1
 2019-05-05T07:30:00Z             2                   1
 2019-05-05T07:40:00Z             2                   1
 2019-05-05T07:50:00Z             2                   1

我正在尝试在第一个表中进行选择并加入第二个表，其中 eventdatetime 位于行之间并获取事件总和。我不知道如何完成的是通过考虑其他参数的位置来创建一个动态组。

预期输出：

 STARTDATETIME             |         STOPDATETIME          |      ID    | SUM(EVENT)
2019-05-05T05:00:00Z              2019-05-05T06:00:00Z            1        2
2019-05-05T07:00:00Z              2019-05-05T08:00:00Z            1        1
2019-05-05T05:00:00Z              2019-05-05T06:00:00Z            2        0
2019-05-05T07:00:00Z              2019-05-05T08:00:00Z            2        3
2019-05-05T08:00:00Z              2019-05-05T10:00:00Z            2        0

标签： sql-servergroup-by

解决方案

SELECT 
      r.ID
      ,r.STARTDATETIME
      ,r.STOPDATETIME
      ,Sum(e.EVENT) as [SUM(Event)]
  FROM dbo.GroupDatetimeRanges as r
  Left Join dbo.GroupDatetimeEvents as e
  on r.ID = e.ID and e.EVENTDATETIME between r.STARTDATETIME and r.STOPDATETIME
  Group By 
        r.ID
      ,r.STARTDATETIME
      ,r.STOPDATETIME

结果

ID  STARTDATETIME           STOPDATETIME           SUM(Event)
1   2019-05-05T05:00:00Z    2019-05-05T06:00:00Z    2
1   2019-05-05T07:00:00Z    2019-05-05T08:00:00Z    1
2   2019-05-05T05:00:00Z    2019-05-05T06:00:00Z    NULL
2   2019-05-05T07:00:00Z    2019-05-05T08:00:00Z    3
2   2019-05-05T08:00:00Z    2019-05-05T10:00:00Z    NULL

推荐阅读