SQL如何在时间段组(SQL how to group in time periods)

2019-09-28 07:16发布

我想组数据的时间段。 每个周期为5分钟,我想看看发生了什么,每5分钟从08:00发生在18:00。

我创建了具有在该时间范围内的所有时间段的表。 例如:

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但随后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子句中的条件


CTE格式

  • 该CTE还去掉了开始时间下降到只有一个TimePart
  • 该DATEADD / DATEDIFF业务只需要如果开始时间包括日期,以及
  • 如果它已经只是时间,只需使用开始时间


独家VS包容结束时间

  • < EndTime ,而不是<= EndTime
  • 该承担的形式,间隔08:00 to 08:0508:05 to 08:10 ,等
  • 有“第一时间,你不想有”的结束时间可以让事情变得更容易
  • 没有更多的四舍五入Activity.StartTime向下调整至最接近分钟,例如
  • 和不奇怪的间隔08:00 to 08:04 ,等


使用独家结束时间值,另一种方法是圆你Activity.StartTime值最接近的分钟。 而不是使用字符串,如下因素它使用日期时间函数...
- DATEADD(minute, DATEDIFF(minute, 0, Activity.StartTime), 0)



Answer 2:

你说你要组的结果,但你是不是应用GROUP到查询。

但是,如果您汇总结果,你就失去了截然不同的信息,你也想( DescriptionStartDate ,除非他们在组中配合其他记录)。

作为Scorpi0评论,你想输出的样本将是有益的。



Answer 3:

您对活动表中的过滤器: activity.Date = @DateParam

它可以防止获取TimeDimension表的每一行。 将过滤器联接子句中,你会看到所有的DATAS。

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子句为加入条件 ,就像这样:

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


文章来源: SQL how to group in time periods