SQL如何按时间段分组

Question

我正在尝试按时间段对数据进行分组。每个时段为 5 分钟，我想查看从 08:00 到 18:00 每 5 分钟发生了什么。

我创建了一个表，其中包含该时间范围内的所有时间段。例如：

StartTime           EndTime             IsBusinessHours
08:40:00.0000000    08:45:00.0000000    1
08:45:00.0000000    08:50:00.0000000    1
08:50:00.0000000    08:55:00.0000000    1
08:55:00.0000000    09:00:00.0000000    1

等等。

Select 
    TimeDimension.[StartTime],
    TimeDimension.[EndTime],
    activity.[Description],
    activity.[StartTime]
From
    TimeDimension 
    Full Outer Join Activity 
       on (
              Convert(varchar,activity.StartTime,108) >= Convert(varchar,TimeDimension.starttime, 108) 
          And Convert(varchar,activity.StartTime,108) <= Convert(varchar,TimeDimension.endtime, 108)
       )
Where               
    activity.Date = @DateParam 
And TimeDimension.isbusinesshours = 1

我希望数据按 5 分钟时间段分组，但我得到的是：

08:20:00.0000000    08:25:00.0000000 Some activity
08:30:00.0000000    08:35:00.0000000 Some activity
08:45:00.0000000    08:50:00.0000000 Three activities in this time period. First
08:45:00.0000000    08:50:00.0000000 Three activities in this time period. Second 
08:45:00.0000000    08:50:00.0000000 Three activities in this time period. Third

当我想看到的是：

08:20:00.0000000    08:25:00.0000000 Some activity
08:25:00.0000000    08:30:00.0000000 NULL
08:30:00.0000000    08:35:00.0000000 Some activity
08:35:00.0000000    08:40:00.0000000 NULL
08:45:00.0000000    08:50:00.0000000 Three activities in this time period. First
08:45:00.0000000    08:50:00.0000000 Three activities in this time period. Second 
08:45:00.0000000    08:50:00.0000000 Three activities in this time period. Third

这意味着我正在显示发生某些活动的时间段，而不是所有活动该范围内的时间段。我已将表称为 TimeDimension - 但我不确定这是否正确。直觉告诉我，这与分析服务有关。

谢谢

Answer 1

注 1：使用 VARCHAR 执行 DATETIME 算术会产生较差的性能。

注 2：您有一个 OUTER JOIN，但有一个不考虑 NULL 的 WHERE 子句。

这就是我要使用的...

WITH
  FilteredActivity AS
(
  SELECT
    Description,
    DATEADD(DAY, -DATEDIFF(DAY, 0, StartTime), StartTime) AS StartTime
  FROM
    Activity
  WHERE
    Date = @DateParam
)

SELECT
  TimeDimension.[StartTime],
  TimeDimension.[EndTime],
  activity.[Description],
  activity.[StartTime]
FROM
  TimeDimension
LEFT JOIN
  FilteredActivity AS [Activity]
    ON  Activity.StartTime >= TimeDimension.StartTime
    AND Activity.StartTime <  TimeDimension.EndTime
WHERE
  TimeDimension.isbusinesshours = 1

CTE 过滤

• 开始时的 CTE 将活动过滤为仅一个日期
• 这避免了 WHERE 子句中与 OUTER JOIN 配合不佳的条件

CTE 格式

• CTE 还将 StartTime 精简为 TimePart 仅
• 当 StartTime 也包含 DATE 时才需要 DATEADD/DATEDIFF 业务（
• 如果它已经是 DATE）时间，只需使用 StartTime

独占与包含 EndTime

• 我有 < EndTime 而不是 <= EndTime
• 这假定时间间隔的形式为 08:00 到 08:05 和 08:05 到 08:10 等
• 将 EndTime 设置为“您不想包含的第一次”可以使事情变得
• 更容易 不再将 Activity.StartTime 向下舍入到最近的分钟，例如
• 并且没有 08 的奇怪间隔： 00 至08:04 等

使用 Exclusive EndTime 值的替代方法是将 Activity.StartTime 值四舍五入到最接近的分钟。下面不使用字符串，而是使用 DateTime 函数...
- DATEADD(分钟, DATEDIFF(分钟, 0, Activity.StartTime), 0)

Answer 2

您说您想要对结果进行分组，但您没有将 GROUP 应用于查询。

但是，如果您聚合结果，您将丢失您想要的不同信息（Description、StartDate），除非它们与组中的其他记录匹配。

正如 Scorpi0 评论的那样，您想要的输出示例将会很有用。

Answer 3

您在活动表上有一个过滤器：activity.Date = @DateParam。

它阻止获取 TimeDimension 表的每一行。将过滤器放在连接子句中，您将看到所有数据。

Select 
    TimeDimension.[StartTime],
    TimeDimension.[EndTime],
    activity.[Description],
    activity.[StartTime]
From
    TimeDimension 
    Full Outer Join Activity 
       on (
              Convert(varchar,activity.StartTime,108) >= Convert(varchar,TimeDimension.starttime, 108) 
          And Convert(varchar,activity.StartTime,108) <= Convert(varchar,TimeDimension.endtime, 108)
          And activity.Date = @DateParam 
       )
Where TimeDimension.isbusinesshours = 1

或者你也可以这样做：

Select 
    TimeDimension.[StartTime],
    TimeDimension.[EndTime],
    activity.[Description],
    activity.[StartTime]
From
    TimeDimension 
    Full Outer Join Activity 
       on (
              Convert(varchar,activity.StartTime,108) >= Convert(varchar,TimeDimension.starttime, 108) 
          And Convert(varchar,activity.StartTime,108) <= Convert(varchar,TimeDimension.endtime, 108)
       )
Where TimeDimension.isbusinesshours = 1
And (activity.Date Is Null Or activity.Date = @DateParam)

Answer 4

您需要将活动时间条件从常见的Where子句移至Join条件，如下所示：

Select 
    TimeDimension.[StartTime],
    TimeDimension.[EndTime],
    activity.[Description],
    activity.[StartTime]
From
    TimeDimension 
    Full Outer Join Activity 
       on (
              Convert(varchar,activity.StartTime,108) >= Convert(varchar,TimeDimension.starttime, 108) 
          And Convert(varchar,activity.StartTime,108) <= Convert(varchar,TimeDimension.endtime, 108)
And activity.Date = @DateParam
       )
Where               
    TimeDimension.isbusinesshours = 1