SQL如何在时间段内进行分组 [英] SQL how to group in time periods

问题描述

我试图在时间段内对数据进行分组。每段时间为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 
  
 
 
 
等。
 选择
 TimeDimension 。[StartTime]，
 TimeDimension。[EndTime]，
 activity。[Description]，
 activity。[StartTime] 
 From 
 TimeDimension 
 Full Outer加入活动
 on（
 Convert（varchar，activity.StartTime，108）> = Convert（varchar，TimeDimension.starttime，108）
 And Convert（varchar，activity.StartTime，108） < = Convert（varchar，TimeDimension.endtime，108）
）
其中
 activity.Date = @DateParam 
 And TimeDimension.isbusinesshours = 1 
  
我希望将数据按5分钟的时间段分组，但我得到的是：
 
 
  08：20：00.0000000 08：25：00.0000000某些活动
 08:30： 00.0000000 08：35：00.0000000某些活动
 08：45：00.0000000 08：50：00.0000000在这段时间内有三项活动。首先
 08：45：00.0000000 08：50：00.0000000在此期间的三项活动。第二个
 08：45：00.0000000 08：50：00.0000000这段时间内的三项活动。第三个
  
当我想看到的是：
  08：20：00.0000000 08：25：00.0000000有些活动
 08：25：00.0000000 08：30：00.0000000 NULL 
 08：30：00.0000000 08：35：00.0000000有些活动
 08：35：00.0000000 08：40：00.0000000 NULL 
 08：45：00.0000000 08：50：00.0000000这段时间内有三项活动。首先
 08：45：00.0000000 08：50：00.0000000在此期间的三项活动。第二个
 08：45：00.0000000 08：50：00.0000000这段时间内的三项活动。第三个
  
这意味着我显示了一些活动发生的时间段，而不是所有时间段在那个范围内。我已经调用了TimeDimension表 - 但我不确定这是否正确。直觉告诉我，这与分析服务有关。 
 
 
 
谢谢 注解1：使用VARCHAR进行DATETIME算术注意2：你有一个OUTER JOIN，但是一个WHERE子句不包含NULL。
 
 
 
 
 
 
 
 
这就是我要用的... 
  WITH 
 FilteredActivity AS 
（
 SELECT 
描述，
 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业务
 
 
如果已经是刚刚开始的时间，只需使用StartTime 
 
 
 
 
 
 独占与包容结束时间 
 
 
 
 
 
我有< 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） 
 
I'm trying to group data in time periods. Each period is 5 minutes and I'd like to see what was happening every 5 minutes from 08:00 to 18:00.

I have created a table that has all the time periods in that time range. E.g.:
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
etc.
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
I expect to have data grouped by 5 minute time periods, but what I get is:
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
When what I'd like to see is:
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
This means that I'm displaying time periods when some activities took place, rather than all time periods in that range. I have called a table TimeDimension - but I'm not sure whether this is correct. Gut feeling tells me that this is something to do with analysis services. 

Thank you
解决方案
Note 1: Doing DATETIME arithmetic using VARCHAR yields poor performance.

Note 2: You have an OUTER JOIN but then a WHERE clause that doesn't account for NULLs.




This is what I'd use...
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 Filtering  


The CTE at the start filters Activity to just one date  
This avoids the condition in your WHERE clause that plays poorly with OUTER JOINs  





CTE Formatting  


The CTE also strips the StartTime down to just a TimePart
The DATEADD/DATEDIFF business is only need if StartTime includes the DATE as well  
If it is already just the time, just use StartTime  





Exclusive vs Inclusive EndTime  


I have < EndTime rather than <= EndTime  
This assume intervals in the form of 08:00 to 08:05 and 08:05 to 08:10, etc  
Having the EndTime as "the first time you don't want to include" can make things easier  
No more rounding Activity.StartTime down to the nearest minute, for example  
And no strange intervals of 08:00 to 08:04, etc  





An alternative to using Exclusive EndTime values is to round your Activity.StartTime values to the nearest minute.  Rather than using strings, the folowing does it using DateTime functions...

- DATEADD(minute, DATEDIFF(minute, 0, Activity.StartTime), 0)

                        
这篇关于SQL如何在时间段内进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！