避免在日期时间间隔与CTE和开始和结束数据时间间隔 [英] Avoiding gaps in datetime intervals with CTE and start and end datetimes

问题描述

由于某些原因，我在使用此查询的时间间隔中看到空白。当我使用基本数据时，我已经得到了工作。但是，当加入我的表并指定WHERE子句时，我看到我的时间间隔有间隙。我还需要在我的间隔中并入S.SessionEndTime，以找到与ResponseTime和SessionEndTime之间给定的1分钟间隔重叠的记录计数。

这是我正在使用的查询。通过使用派生表，我每小时得到一个MAX，每COUNT一分钟。

解决方案

这里澄清的是一些TSQL计算：
- 每小时活动的会话总数和
- 每小时内活动的最大并发会话数。

编辑：来自更新问题的示例数据已被使用，显示并发会话的最后一个查询的输出现在包括会话ID，并且修正了先前优化中的错误

/ code>值因行而异。对所有行使用 1 的值将导致令人失望的结果。因此， SessionId 列中的 IDENTITY 属性。

   - 参数。 
 declare @Start as DateTime ='20120901 00:00:00'
 declare @End as DateTime ='20120901 12:00:00'
 declare @Interval as Time = '01：00 ：00.00' - 一小时。 
 select @Start as [Start]，@End as [End]，@Interval as [Interval] 
 
  - 样本数据。 
将@Sessions声明为表（SessionId Int Identity，SessionStart DateTime，SessionEnd DateTime）
插入到@Sessions（SessionStart，SessionEnd）值
（'20120901 00:00:00'，'20120901 05:59:59'）， - 在一个会话中几个小时。 
（'20120901 01:01:00'，'20120901 01:01:30'）， - 重叠的分类... 
（'20120901 01:02:00'，'20120901 01 ：03：30'）， - 在一个小时内的会议。 
（'20120901 00：00：05.077'，'20120901 00：04：02.280'），
（'20120901 00：00：14.687'，'20120901 00：06：05.947'），
（'20120901 00：00：17.857'，'20120901 00：07：34.757'），
（'20120901 00：00：25.843'，'20120901 00：07：38.720'），
 （'20120901 00：00：29.427'，'20120901 00：01：58.180'），
（'20120901 00：00：31.853'，'20120901 00：05：10.733'），
 20120901 00：00：40.693'，'20120901 00：00：44.237'），
（'20120901 00：00：58.773'，'20120901 00：06：14.667'），
（'20120901 00 ：00：59.457'，'20120901 00：01：01.310'），
（'20120901 00：01：16.390'，'20120901 00：11：18.383'）
 select * from @Sessions 
 
  - 每小时内任何时间活动的会话汇总。 
;使用SampleWindows as（
 select @Start as WindowStart，@Start + @Interval as WindowEnd 
 union all 
 select SW.WindowStart + @Interval，SW.WindowEnd + @Interval 
 from SampleWindows as SW 
其中SW.WindowEnd< @End 
）
将SWWWStart，Count（S.SessionStart）作为[Sessions] 
从SampleWindows作为SW左外连接
 @Sessions as S on SW.WindowStart< = S.SessionEnd and S.SessionStart< SW.WindowEnd 
 group by SW.WindowStart 
 
  - 每小时内活动的最大并发会话摘要。 
;以SampleWindows为例（
选择1作为SampleWindowId，@Start为WindowStart，@Start + @Interval为WindowEnd 
 union all 
选择SW.SampleWindowId + 1，SW.WindowStart + @Interval，SW .WindowEnd + @Interval 
从SampleWindows as SW 
其中SW.WindowEnd< @End 
），
 ActiveSessionsDuringWindow as（
 select SW.SampleWindowId，SW.WindowStart ，SW.WindowEnd，S.SessionId，S.SessionStart，S.SessionEnd，
  - 窗格是对窗口和会话开始/结束时间的限制。
当SW。 WindowStart< = S.SessionStart然后S.SessionStart else SW.WindowStart结束为PaneStart，
当SW.WindowEnd> = S.SessionEnd然后S.SessionEnd else SW.WindowEnd结束为PaneEnd 
从SampleWindows as SW left outer join 
 @Sessions as S on SW.WindowStart< = S.SessionEnd and S.SessionStart< SW.WindowEnd 
），
 ConcurrentSearch as（
选择SampleWindowId，WindowStart ，WindowEnd，SessionId，SessionStart，SessionEnd，PaneStart，PaneEnd，
 Cast（'|'+ Right（复制（'0'，3）+ Cast（SessionId作为VarChar（4）），4）+'|'作为VarChar（1024））作为SessionIds，
 Cast（当SessionId为空的情况下为0，否则为1结束为Int）作为Sessions 
从ActiveSessionsDuringWindow 
 union all 
选择CS.SampleWindowId ，CS.WindowStart，CS.WindowEnd，ASDW.SessionId，CS.SessionStart，CS.SessionEnd，
 case当CS.PaneStart< = ASDW.PaneStart然后ASDW.PaneStart其他CS.PaneStart结束为PaneStart，
 case当CS.PaneEnd> = ASDW.PaneEnd然后ASDW.PaneEnd else CS.PaneEnd结束为PaneEnd，
 Cast（CS.SessionIds + Right（复制（'0'，3）+ Cast（ASDW。 SessionId作为VarChar（4）），4）+'|'作为VarChar（1024）），
 CS.Sessions + 1 
从ConcurrentSearch作为CS内部连接
 ActiveSessionsDuringWindow作为ASDW ASDW。 SampleWindowId = CS.SampleWindowId和
  - 我们没有沿着这个路径访问这个会话。 
 CS.SessionId< ASDW.SessionId和 - 编辑：减小搜索树的大小。 
 CharIndex（'|'+ Right（Replicate（'0'，3）+ Cast（ASDW.SessionId as VarChar（4）），4）+'|'，CS.SessionIds）= 0和
  - 会话的窗格与并发搜索窗格重叠。 
 CS.PaneStart< = ASDW.PaneEnd和ASDW.PaneStart< = CS.PaneEnd 
）
选择WindowStart，Max（Sessions）作为会话，
（选择顶部1来自ConcurrentSearch的SessionIds，其中Sessions = Max（CS.Sessions））作为SessionIds 
从ConcurrentSearch作为CS 
通过WindowStart 
  

以下是最后一个查询的变体，不会使用 @Sessions 表中的行ID值。而是使用 Row_Number（）在查询期间分配合适的值。这也改变了以下假设： SessionId 值不超过四位数，假设在任何给定小时内没有超过9,999个会话活动。

   - 每小时内活动的最大并发会话摘要。 
;以SampleWindows为例（
选择1作为SampleWindowId，@Start为WindowStart，@Start + @Interval为WindowEnd 
 union all 
选择SW.SampleWindowId + 1，SW.WindowStart + @Interval，SW .WindowEnd + @Interval 
从SampleWindows as SW 
其中SW.WindowEnd< @End 
），
 ActiveSessionsDuringWindow as（
 select SW.SampleWindowId，SW.WindowStart ，SW.WindowEnd，S.SessionStart，S.SessionEnd，
  - 窗格是窗口和会话开始/结束时间的限制条件
当SW.WindowStart <= S.SessionStart then S.SessionStart else SW.WindowStart end as PaneStart，
 case when SW.WindowEnd> = S.SessionEnd then S.SessionEnd else SW.WindowEnd end as PaneEnd，
 Row_Number（）over （SW.SampleWindowId由S.SessionStart排序）作为SampleId 
从SampleWindows作为SW左外连接
 @Sessions作为S在SW.WindowStart< = S.SessionEnd和S.SessionStart< ; （
）选择SampleWindowId，WindowStart，WindowEnd，SampleId，SessionStart，SessionEnd，PaneStart，PaneEnd，
 Cast（'|'+右（复制） （'0'，3）+ Cast（SampleId as VarChar（4）），4）+'|'作为VarChar（1024））作为SampleIds，
 Cast（当SampleId为NULL时为0，否则为1结束为Int）as Sessions 
从ActiveSessionsDuringWindow 
 union all 
选择CS.SampleWindowId，CS.WindowStart，CS.WindowEnd，ASDW.SampleId，CS.SessionStart，CS.SessionEnd，
 case当CS.PaneStart< = ASDW.PaneStart然后ASDW.PaneStart其他CS.PaneStart结束为PaneStart，
的情况下，当CS.PaneEnd> = ASDW.PaneEnd然后ASDW.PaneEnd其他CS.PaneEnd结束为PaneEnd，
 Cast（CS.SampleIds + Right（复制（'0'，3）+ Cast（ASDW.SampleId为VarChar（4）），4）+'|'为VarChar（1024）），
 CS .Sessions + 1 
从ConcurrentSearch作为CS内部连接
 ActiveSessionsDuringWindow as ASDW on ASDW.SampleWindowId = CS.SampleWindowId and 
  - 我们没有通过此路径访问此会话。 
 CS.SampleId< ASDW.SampleId和 - 编辑：减小搜索树的大小。 
 CharIndex（'|'+ Right（Replicate（'0'，3）+ Cast（ASDW.SampleId as VarChar（4）），4）+'|'，CS.SampleIds）= 0和
  - 会话的窗格与并发搜索窗格重叠。 
 CS.PaneStart< = ASDW.PaneEnd和ASDW.PaneStart< = CS.PaneEnd 
）
从ConcurrentSearch中将WindowStart，Max（Sessions）作为会话
作为CS 
 group by WindowStart 
  

这应该很容易修改以对现有表进行运行。 SessionStart 升序， SessionEnd 升序中的单个索引应提高性能。

For some reason I am seeing gaps in my time intervals using this query. I have gotten it to work when just using basic data. However, when joining my tables and specifying a WHERE clause, I see gaps in my time interval. I also need to incorporate the S.SessionEndTime in my intervals to find a count of records where there is overlap with a given 1 minute interval between the ResponseTime and SessionEndTime.

Here is the query I am using. By using a derived table, I get a MAX per hour based on the COUNT for 1 minute intervals.

解决方案

Okay, lacking a clarification here is some TSQL that computes both: - the total number of sessions active in each hour and - the maximum number of concurrent sessions active in each hour.

EDIT: The sample data from the updated question has been used, the output of the last query which shows concurrent sessions now includes the session ids, and a bug in the prior optimization was corrected which greatly improves performance.

NB: These queries work best when the SessionId values vary from row to row. Using a value of 1 for all rows will cause disappointing results. Hence the IDENTITY property on the SessionId column.

-- Parameters.
declare @Start as DateTime = '20120901 00:00:00'
declare @End as DateTime = '20120901 12:00:00'
declare @Interval as Time = '01:00:00.00' -- One hour.
select @Start as [Start], @End as [End], @Interval as [Interval]

-- Sample data.
declare @Sessions as Table ( SessionId Int Identity, SessionStart DateTime, SessionEnd DateTime )
insert into @Sessions ( SessionStart, SessionEnd ) values
  ( '20120901 00:00:00', '20120901 05:59:59' ), -- Several hours in a single session.
  ( '20120901 01:01:00', '20120901 01:01:30' ), -- An assortment of overlapping ... 
  ( '20120901 01:02:00', '20120901 01:03:30' ), -- ... sessions during a single hour. 
  ( '20120901 00:00:05.077', '20120901 00:04:02.280' ),
  ( '20120901 00:00:14.687', '20120901 00:06:05.947' ),
  ( '20120901 00:00:17.857', '20120901 00:07:34.757' ),
  ( '20120901 00:00:25.843', '20120901 00:07:38.720' ),
  ( '20120901 00:00:29.427', '20120901 00:01:58.180' ),
  ( '20120901 00:00:31.853', '20120901 00:05:10.733' ),
  ( '20120901 00:00:40.693', '20120901 00:00:44.237' ),
  ( '20120901 00:00:58.773', '20120901 00:06:14.667' ),
  ( '20120901 00:00:59.457', '20120901 00:01:01.310' ),
  ( '20120901 00:01:16.390', '20120901 00:11:18.383' )
select * from @Sessions 

-- Summary of sessions active at any time during each hour. 
; with SampleWindows as ( 
  select @Start as WindowStart, @Start + @Interval as WindowEnd 
  union all 
  select SW.WindowStart + @Interval, SW.WindowEnd + @Interval 
    from SampleWindows as SW 
    where SW.WindowEnd < @End 
  ) 
  select SW.WindowStart, Count( S.SessionStart ) as [Sessions] 
    from SampleWindows as SW left outer join 
      @Sessions as S on SW.WindowStart <= S.SessionEnd and S.SessionStart < SW.WindowEnd 
    group by SW.WindowStart 

-- Summary of maximum concurrent sessions active during each hour. 
; with SampleWindows as ( 
  select 1 as SampleWindowId, @Start as WindowStart, @Start + @Interval as WindowEnd 
  union all 
  select SW.SampleWindowId + 1, SW.WindowStart + @Interval, SW.WindowEnd + @Interval 
    from SampleWindows as SW 
    where SW.WindowEnd < @End 
  ), 
  ActiveSessionsDuringWindow as ( 
  select SW.SampleWindowId, SW.WindowStart, SW.WindowEnd, S.SessionId, S.SessionStart, S.SessionEnd, 
    -- A "pane" is the more restrictive of the window and the session start/end times. 
    case when SW.WindowStart <= S.SessionStart then S.SessionStart else SW.WindowStart end as PaneStart, 
    case when SW.WindowEnd >= S.SessionEnd then S.SessionEnd else SW.WindowEnd end as PaneEnd
    from SampleWindows as SW left outer join 
      @Sessions as S on SW.WindowStart <= S.SessionEnd and S.SessionStart < SW.WindowEnd 
  ), 
  ConcurrentSearch as ( 
  select SampleWindowId, WindowStart, WindowEnd, SessionId, SessionStart, SessionEnd, PaneStart, PaneEnd, 
    Cast( '|' + Right( Replicate( '0', 3 ) + Cast( SessionId as VarChar(4) ), 4 ) + '|' as VarChar(1024) ) as SessionIds, 
    Cast( case when SessionId is NULL then 0 else 1 end as Int ) as Sessions 
    from ActiveSessionsDuringWindow 
  union all 
  select CS.SampleWindowId, CS.WindowStart, CS.WindowEnd, ASDW.SessionId, CS.SessionStart, CS.SessionEnd, 
    case when CS.PaneStart <= ASDW.PaneStart then ASDW.PaneStart else CS.PaneStart end as PaneStart, 
    case when CS.PaneEnd >= ASDW.PaneEnd then ASDW.PaneEnd else CS.PaneEnd end as PaneEnd, 
    Cast( CS.SessionIds + Right( Replicate( '0', 3 ) + Cast( ASDW.SessionId as VarChar(4) ), 4 ) + '|' as VarChar(1024) ), 
    CS.Sessions + 1 
    from ConcurrentSearch as CS inner join 
      ActiveSessionsDuringWindow as ASDW on ASDW.SampleWindowId = CS.SampleWindowId and 
        -- We haven't visited this session along this path. 
        CS.SessionId < ASDW.SessionId and -- EDIT: Reduce the size of the search tree. 
        CharIndex( '|' + Right( Replicate( '0', 3 ) + Cast( ASDW.SessionId as VarChar(4) ), 4 ) + '|', CS.SessionIds ) = 0 and 
        -- The session's pane overlaps the concurrent search pane. 
        CS.PaneStart <= ASDW.PaneEnd and ASDW.PaneStart <= CS.PaneEnd 
  )
  select WindowStart, Max( Sessions ) as Sessions,
    ( select top 1 SessionIds from ConcurrentSearch where Sessions = Max( CS.Sessions ) ) as SessionIds
    from ConcurrentSearch as CS
    group by WindowStart 

Following is a variation on the last query which does not use row id values from the @Sessions table. Instead it uses Row_Number() to assign suitable values for the duration of the query. This also changes the assumption that the SessionId values do not exceed four digits to an assumption that there are not more than 9,999 sessions active within any given hour.

-- Summary of maximum concurrent sessions active during each hour.
; with SampleWindows as (  
  select 1 as SampleWindowId, @Start as WindowStart, @Start + @Interval as WindowEnd  
  union all  
  select SW.SampleWindowId + 1, SW.WindowStart + @Interval, SW.WindowEnd + @Interval  
    from SampleWindows as SW  
    where SW.WindowEnd < @End  
  ),  
  ActiveSessionsDuringWindow as (  
  select SW.SampleWindowId, SW.WindowStart, SW.WindowEnd, S.SessionStart, S.SessionEnd,  
    -- A "pane" is the more restrictive of the window and the session start/end times.  
    case when SW.WindowStart <= S.SessionStart then S.SessionStart else SW.WindowStart end as PaneStart,  
    case when SW.WindowEnd >= S.SessionEnd then S.SessionEnd else SW.WindowEnd end as PaneEnd,
    Row_Number() over ( partition by SW.SampleWindowId order by S.SessionStart ) as SampleId
    from SampleWindows as SW left outer join  
      @Sessions as S on SW.WindowStart <= S.SessionEnd and S.SessionStart < SW.WindowEnd  
  ),  
  ConcurrentSearch as (  
  select SampleWindowId, WindowStart, WindowEnd, SampleId, SessionStart, SessionEnd, PaneStart, PaneEnd,  
    Cast( '|' + Right( Replicate( '0', 3 ) + Cast( SampleId as VarChar(4) ), 4 ) + '|' as VarChar(1024) ) as SampleIds,  
    Cast( case when SampleId is NULL then 0 else 1 end as Int ) as Sessions  
    from ActiveSessionsDuringWindow  
  union all  
  select CS.SampleWindowId, CS.WindowStart, CS.WindowEnd, ASDW.SampleId, CS.SessionStart, CS.SessionEnd,  
    case when CS.PaneStart <= ASDW.PaneStart then ASDW.PaneStart else CS.PaneStart end as PaneStart,  
    case when CS.PaneEnd >= ASDW.PaneEnd then ASDW.PaneEnd else CS.PaneEnd end as PaneEnd,  
    Cast( CS.SampleIds + Right( Replicate( '0', 3 ) + Cast( ASDW.SampleId as VarChar(4) ), 4 ) + '|' as VarChar(1024) ),  
    CS.Sessions + 1  
    from ConcurrentSearch as CS inner join  
      ActiveSessionsDuringWindow as ASDW on ASDW.SampleWindowId = CS.SampleWindowId and  
        -- We haven't visited this session along this path.  
        CS.SampleId < ASDW.SampleId and -- EDIT: Reduce the size of the search tree.  
        CharIndex( '|' + Right( Replicate( '0', 3 ) + Cast( ASDW.SampleId as VarChar(4) ), 4 ) + '|', CS.SampleIds ) = 0 and  
        -- The session's pane overlaps the concurrent search pane.  
        CS.PaneStart <= ASDW.PaneEnd and ASDW.PaneStart <= CS.PaneEnd  
  ) 
  select WindowStart, Max( Sessions ) as Sessions
    from ConcurrentSearch as CS 
    group by WindowStart  

This should be easy to modify to run against an existing table. A single index on SessionStart ascending, SessionEnd ascending should improve performance.

这篇关于避免在日期时间间隔与CTE和开始和结束数据时间间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！