将表格分组为 15 分钟间隔

2021-12-27 00:00:00 sql group-by tsql sql-server-2008 sql-server

T-SQL、SQL Server 2008 及更高版本

T-SQL, SQL Server 2008 and up

给定一个样本表

 StatusSetDateTime   | UserID | Status    | StatusEndDateTime   | StatusDuration(in seconds)
============================================================================
 2012-01-01 12:00:00 | myID   | Available | 2012-01-01 13:00:00 | 3600

我需要将其分解为使用 15 分钟间隔的视图，例如:

I need to break that down into a view that uses 15 minute intervals for example:

IntervalStart       | UserID | Status | Duration

===========================================

2012-01-01 12:00:00 | myID | Available | 900 

2012-01-01 12:15:00 | myID | Available | 900

2012-01-01 12:30:00 | myID | Available | 900 

2012-01-01 12:45:00 | myID | Available | 900 

2012-01-01 13:00:00 | myID | Available | 0

etc....

现在我已经能够四处搜索并找到一些会崩溃的查询我在这里找到了类似的东西:

Now I've been able to search around and find some queries that will break down I found something similar for MySql Here :

T-SQL 的一些东西这里

And something for T-SQL Here

但在第二个示例中，他们对结果求和，而我需要将总持续时间除以用户按状态的间隔时间(900 秒).

But on the second example they are summing the results whereas I need to divide the total duration by the interval time (900 seconds) by user by status.

我能够调整第二个链接中的示例以将所有内容拆分为间隔，但返回了总持续时间，我无法弄清楚如何拆分间隔持续时间(并且仍然总结为原始总持续时间).

I was able to adapt the examples in the second link to split everything into intervals but the total duration time is returned and I cannot quite figure out how to get the Interval durations to split (and still sum up to the total original duration).

提前感谢您的见解！

第一次尝试

 ;with cte as 
    (select MIN(StatusDateTime) as MinDate
          , MAX(StatusDateTime) as MaxDate
          , convert(varchar(14),StatusDateTime, 120) as StartDate
          , DATEPART(minute, StatusDateTime) /15 as GroupID
          , UserID
          , StatusKey
          , avg(StateDuration) as AvgAmount
     from AgentActivityLog
     group by convert(varchar(14),StatusDateTime, 120)
         , DATEPART(minute, StatusDateTime) /15
         , Userid,StatusKey)

  select dateadd(minute, 15*GroupID, CONVERT(datetime,StartDate+'00'))
         as [Start Date]
       , UserID, StatusKey, AvgAmount as [Average Amount]
  from cte

第二次尝试

;With cte As
   (Select DateAdd(minute
                   , 15 * (DateDiff(minute, '20000101', StatusDateTime) / 15)
                   , '20000101') As StatusDateTime
         , userid, statuskey, StateDuration
    From AgentActivityLog)

 Select StatusDateTime, userid,statuskey,Avg(StateDuration)
 From cte
 Group By StatusDateTime,userid,statuskey;

推荐答案

;with cte_max as 
(
   select dateadd(mi, -15, max(StatusEndDateTime)) as EndTime, min(StatusSetDateTime) as StartTime
   from AgentActivityLog
), times as
(
    select StartTime as Time from cte_max
    union all
    select dateadd(mi, 15, c.Time)
    from times as c
        cross join cte_max as cm
    where c.Time <= cm.EndTime
)
select
    t.Time, A.UserID, A.Status,
    case
        when t.Time = A.StatusEndDateTime then 0
        else A.StatusDuration / (count(*) over (partition by A.StatusSetDateTime, A.UserID, A.Status) - 1)
    end as Duration
from AgentActivityLog as A
    left outer join times as t on t.Time >= A.StatusSetDateTime and t.Time <= A.StatusEndDateTime

sql 小提琴演示

相关文章