sql-server - 通过聚合和分组接收新的格式化表格

Question

我在这里遇到一个 SQL Server 查询的大问题，我真的不知道如何继续。

目标是接收一个由从 00:00 - 00:29 到 23:30 - 23:59 的不同时间间隔区分的表格。在每个时间间隔中，我想总结在这些时间段内等待的实体的总分钟数。此信息可以通过 starttime 和 endtime 以及实体的状态接收，如下所示:

startdate                    | finishdate                   | resourcestatus | id
2015-03-19 10:22:56.8490000  | 2015-03-19 10:32:56.8490000  | 8              | asdsdasdsad

如您所见，这样的实体从一个时间间隔 (10:00 - 10:30) 到另一个时间间隔 (10:30 - 11:00) 的状态可以为 8。

到目前为止，我通过定义 4 组时间间隔来解决这个问题(完成和开始都在间隔内，开始在间隔内但结束在，开始在间隔内但结束，开始和结束都在间隔内)这 4组按时间间隔加入。

我会在这里发布代码，但它太多了。我的结果看起来像这样。以下是查询不同部分的开头:

select  zr.nr, 
        zr.interval, 
        case when outOfInterval.waittime is not null
                    then SUM(outOfInterval.waittime) 
                    else 0
               end 
               + 
               case when inInterval.waittime is not null 
                    then SUM(inInterval.waittime)
                    else 0
               end 
               +
               case when startInInterval.waittime is not null 
                    then SUM(startInInterval.waittime) 
                    else 0
               end 
               +
               case when finishInInterval.waittime is not null 
                    then sum(finishInInterval.waittime)
                    else 0
               end
               as waitingMinutes
From    (select 1 as nr,'00:00 - 00:29' as interval, 0 as waittime
        union  select 2,'00:30 - 00:59', 0 
        union  select 3,'01:00 - 01:29', 0 ...
        ) zr
left join (select case when CONVERT(time, rt.startedat, 8) < '00:00' and CONVERT(time, rt.finishedat , 8) > '00:30'  then '00:00 - 00:29' end as inter, 30 as waittime from T_resourcetracking rt where rt.resource_id is not null and rt.resourcestatus = 8 AND  CONVERT(Date, rt.startedat) >= '02.02.2015' AND  CONVERT(Date, rt.finishedat) < DateAdd(day,1,CONVERT ( datetime , '08.05.2015', 120 ))           
...
) outOfInterval on outOfInterval.inter = zr.interval

left join (select case when CONVERT(time, rt.startedat, 8) >= '00:00' and CONVERT(time, rt.finishedat , 8) <= '00:30'  then '00:00 - 00:29' end as inter, SUM(DATEDIFF(minute, rt.STARTEDAT, rt.FINISHEDAT)) as waittime from T_resourcetracking rt where rt.resource_id is not null and rt.resourcestatus = 8 AND  CONVERT(Date, rt.startedat) >= '02.02.2015' AND  CONVERT(Date, rt.finishedat) <= DateAdd(day,1,CONVERT ( datetime , '08.05.2015', 120 )) group by rt.startedat, rt.finishedat        
...
) inInterval on inInterval.inter = zr.interval

left join (select case when CONVERT(time, rt.startedat, 8) >= '00:00' and CONVERT(time, rt.startedat, 8) < '00:30'and CONVERT(time, rt.finishedat , 8) >= '00:30'  then '00:00 - 00:29' end as inter, (30-DATEPART(minute, rt.STARTEDAT)) as waittime from T_resourcetracking rt where rt.resource_id is not null and rt.resourcestatus = 8 AND  CONVERT(Date, rt.startedat) >= '02.02.2015' AND  CONVERT(Date, rt.finishedat) <= DateAdd(day,1,CONVERT ( datetime , '08.05.2015', 120 )) group by rt.startedat, rt.finishedat       
...
) startInInterval on startInInterval.inter = zr.interval

left join (select case when CONVERT(time, rt.startedat, 8) >= '00:00' and CONVERT(time,rt.finishedat, 8) < '00:30'and CONVERT(time, rt.STARTEDAT , 8) < '00:00'  then '00:00 - 00:29' end as inter,  DATEPART(minute, rt.finishedat) as waittime from T_resourcetracking rt where rt.resource_id is not null and rt.resourcestatus = 8 AND  CONVERT(Date, rt.startedat) >= '02.02.2015' AND  CONVERT(Date, rt.finishedat) <= DateAdd(day,1,CONVERT ( datetime , '08.05.2015', 120 )) group by rt.startedat, rt.finishedat        
...
) finishInInterval on finishInInterval.inter = zr.interval
group by zr.interval, outOfInterval.waittime, inInterval.waittime, startInInterval.waittime, finishInInterval.waittime, zr.nr   

这是结果:

    nr | interval      | waitingMinutes
    1  | 00:00 - 00:29 | 2
    2  | 00:30 - 00:59 | 7
...
    24 | 11:30 - 11:59 | 8
    24 | 11:30 - 11:59 | 51
...

如您所见，我的结果集中有多个间隔。

你知道如何将小组加入到一个小组并总结 session 记录吗？我真的受够了，每种聚合函数都不适合我。

提前致谢!

@EDIT:如果这还不够困难，我们需要第二个规范，我忘了解释:我们不想看到 48 个时间间隔内的所有等待时间，而是特定日期间隔内所有等待时间的总和。

假设我们想知道上个月的总分钟数。那么结果集应该是这样的:

    nr | interval      | waitingMinutes
    1  | 00:00 - 00:29 | 0
    2  | 00:30 - 00:59 | 0
...
    20 | 09:30 - 09:59 | 0
    21 | 10:00 - 10:29 | 8
    22 | 10:30 - 10:59 | 73
    23 | 11:00 - 11:29 | 20
...

上个月所有时间间隔的分钟数汇总。例如，在过去 30 天的 11:00 - 11:29，实体总共等待了 20 分钟(例如昨天 10 分钟和前一天 10 分钟)。

这太难了，我真的不知道这对 SQL 来说太难了......

有什么建议吗？

Answer 1

我会把你的问题分解成这样。我这里可能有一些因素略有偏差，但希望您能明白我要解决的问题。

我将用评论分解脚本，但实际的事情应该作为一个查询运行:

declare @StartDate date
declare @EndDate date

select @StartDate = '20150202',@EndDate='20150508'

我已经打破了开始和结束日期作为参数，因为我猜这些可能会发生变化，所以这给了我们一个地方来改变它们而不是很多

;With Dates as (
    select CAST(@StartDate as datetime) as Day
    union all
    select DATEADD(day,1,Day) from Dates where Day < @EndDate
)

第一个 CTE，Dates，生成感兴趣期间内的所有日期。如果您的数据库中有日历表，只需从中选择即可

, PMNs as (
    select ROW_NUMBER() OVER (ORDER BY number)-1 as n
    from master..spt_values
)

下一个 CTE，PMNs 是我的“穷人数字表”——如果您的数据库中有一个实数表，您可以用它代替

, DateTimes as (
    select
        n+1 as nr,
        DATEADD(minute,30*n,Day) as StartInclusive,
        DATEADD(minute,30*(n+1),Day) as EndExclusive
    from
        Dates d
            inner join
        PMNs p
            on
                p.n between 0 and 47
)

现在，真正有趣的是。我们结合前两个 CTE 生成 DateTimes - 所有感兴趣日期的所有半小时长周期的完整集合

select
    nr,
    CAST(time,StartInclusive) as StartTime,
    CAST(time,EndInclusive) as EndTime,
    SUM(
        DATEDIFF(minute,
            CASE WHEN dt.StartInclusive < rt.StartedAt THEN rt.StartedAt
                ELSE dt.StartInclusive END,
            CASE WHEN dt.EndExclusive > rt.finishedAt THEN rt.FinishedAt
                ELSE dt.EndExclusive END
        )) as TotalMinutes
from
    DateTimes dt
        inner join
    T_resourcetracking rt
        on
            dt.StartInclusive < rt.finishedAt and
            rt.startedAt < dt.EndExclusive
group by
    nr,
    CAST(time,StartInclusive),
    CAST(time,EndInclusive)

最后，我们将数据组合在一起。我们发现 resourceTracking 周期与我们的 DateTimes 周期之一重叠的位置(注意 join 的 on 子句标识所有重叠)。然后在一些 CASE 表达式中进行一些操作，以计算出两个开始日期时间中的较晚日期时间和两个结束日期时间中较早的日期时间 - 这就是我们要减去的两个值。

如果您的 T_resourcetracking 也不是(与我的 DateTimes 一样)计算具有半开放时间间隔(包括开始时间，不包括结束时间)的时间间隔，您可能需要进行一些调整，使其看起来确实如此。