使用SQL显示一天的会议系统使用量

Question

目标:创建一个报告，根据历史数据提供一天中任何时间会议系统的平均行数

给定:会议唯一ID，会议开始时间(Time)，会议结束时间(Time)，会议持续时间(基本上是结束时间-开始时间的整型)，每个会议的行数。这些列是SQL表中的~250,000行，代表2年的数据。

问题:一开始看起来很简单。只需运行一个查询，查找行和并按小时和分钟分组，如下所示:

Select Datepart(hour,[Actual Conference Begin Time]) as 'Hour',
       Datepart(minute,[Actual Conference Begin Time]) as 'Minute', 
       SUM([Actual Total Lines]) as 'Lines'
From Filtered
Group By DatePart(hour,[Actual Conference Begin Time]),
         Datepart(minute,[Actual Conference Begin Time])
Order By DatePart(hour,[Actual Conference Begin Time]),
         Datepart(minute,[Actual Conference Begin Time])

然而这个查询只考虑"在那个时间开始"的调用的行数总和。它没有捕捉到这样一个事实，即在一天中的任何给定分钟都有多个呼叫正在进行，这些呼叫都在不同的时间开始，并且运行的持续时间不同。

建议解决方案:我解决这个问题的最初计划是创建另一个表，并使用一个循环来填充该小时内的每小时和每分钟的shell。然后基本上逐行"解析"我的主数据表，找到开始时间、持续时间和行数，然后转到小时/分钟表，从开始时间开始，按与持续时间相同的行数依次填充。它将通过添加单元格中当前的值(而不是替换)，在另一列中填充"# of lines"。小时/分钟表本质上是一个大的记分牌，记录了系统在任何给定的小时/分钟内的线路。

建议的解决方案2:我还想到了另一个可能的解决方案。查看数据库中的每一行，并在开始和结束时间戳之间的每一分钟创建更多相同的行。例如，如果您有一个10分钟的电话，那么将创建另外9行，每列中都有相同的信息，只是时间戳将每行多一分钟。现在表中有足够的数据来运行上面的聚合查询。

我已经创建了小时/分钟的shell表，但我正在达到一个点，我认为上面的解决方案可能不可能在SQL中。我也尝试过在Tableau中操作数据，但没有结果。

请让我知道，如果你知道一个解决方案，以提供这些信息是否在SQL或其他方法。我有更多的代码来分享，以显示进度。

谢谢。

Answer 1

我不能忍受你的列名，因为它们需要转义，所以我正在简化它们。

是这样的。为每个开始和结束创建一个新记录。然后在每个时间点，将开始和结束的次数加起来，就得到了任意时间点的数字:

with be as (
      select thetime, sum(inc) as in
      from (select begintime as thetime, 1 as inc
            from filtered
            union all
            select endtime,  -1 as inc
            from filtered
           ) be
     )
select thetime, sum(inc) over (order by thetime) as numConcurrentLines
from be;

显示每次会议开始和结束时的值。这应该会为你指明正确的方向。

Answer 2

DECLARE @t1 time(0)= '00:00:00'
DECLARE @t2 time(0)= '00:01:00'
DECLARE @counter int=1
While @counter<=1440
Begin
SELECT @t1 as 'Time (24hr)', SUM([Actual Total Lines]) as 'Total Lines' 
FROM Filtered 
WHERE @t2 >= [Actual Conference Begin Time] AND [Actual Conference End Time]>= @t1
Set @t1= Dateadd(minute,1,@t1)
Set @t2= Dateadd(minute,1,@t2)
Set @counter= @counter+1
End;