我想组数据的时间段。 每个周期为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:05
和08:05 to 08:10
,等 - 有“第一时间,你不想有”的结束时间可以让事情变得更容易
- 没有更多的四舍五入Activity.StartTime向下调整至最接近分钟,例如
- 和不奇怪的间隔
08:00 to 08:04
,等
使用独家结束时间值,另一种方法是圆你Activity.StartTime值最接近的分钟。 而不是使用字符串,如下因素它使用日期时间函数...
- DATEADD(minute, DATEDIFF(minute, 0, Activity.StartTime), 0)
Answer 2:
你说你要组的结果,但你是不是应用GROUP
到查询。
但是,如果您汇总结果,你就失去了截然不同的信息,你也想( Description
, StartDate
,除非他们在组中配合其他记录)。
作为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