将表分成15分钟的时间间隔 [英] Group table into 15 minute intervals

问题描述

给出一个示例表

 
  StatusSetDateTime |用户ID |状态| StatusEndDateTime | StatusDuration（以秒为单位）
 ========================================= =================================== 
 2012-01-01 12:00:00 | myID |可用| 2012-01-01 13:00:00 | 3600 
  

我需要将它分解为以15分钟为间隔的视图：

  IntervalStart |用户ID |状态|持续时间
 
 ========================================= == 
 
 2012-01-01 12:00:00 | myID |可用| 900 
 
 2012-01-01 12:15:00 | myID |可用| 900 
 
 2012-01-01 12:30:00 | myID |可用| 900 
 
 2012-01-01 12:45:00 | myID |可用| 900 
 
 2012-01-01 13:00:00 | myID |可用| 0 
 
 etc ... 
  

现在我已经能够搜索周围，并找到一些查询，将打破
我发现了一些类似的MySql Here ：

还有一些T-SQL Here

但在第二个例子中，他们总结了结果，而我需要按用户的状态将总时长除以用户间隔时间（900秒）。

我能够调整第二个链接中的示例以将所有内容拆分为间隔总持续时间会返回，我不能完全弄清楚如何获得间隔持续时间来分割（并且仍然总计到原始持续时间总和）。

感谢您的宝贵意见！

edit：Fi第一次尝试

 ; cte为
（选择MIN（StatusDateTime）作为MinDate 
 ，以StartDate 
，DATEPART（minute，StatusDateTime）/ 15作为GroupID 
，UserID 
，将MAX（StatusDateTime）作为MaxDate 
，convert（varchar（14），StatusDateTime，120） ，StatusKey 
，avg（StateDuration）作为AvgAmount 
从AgentActivityLog 
组转换（varchar（14），StatusDateTime，120）
，DATEPART（minute，StatusDateTime）/ 15 
，Userid，StatusKey）
 
选择dateadd（分钟，15 * GroupID，CONVERT（datetime，StartDate + '00'））
为[开始日期] 
，UserID ，StatusKey，AvgAmount as [平均数量] 
 from cte 
  

edit：Second尝试

 ;用cte As 
（选择DateAdd（分钟
，15 *（ DateDiff（分钟，'20000101'，StatusDateTime）/ 15） 
，'20000101'）作为StatusDateTime 
，userid，statuskey，StateDuration 
从AgentActivityLog）
 
选择StatusDateTime，userid，statuskey，Avg（StateDuration）
从cte 
 Group By StatusDateTime，userid，statuskey; 
  

解决方案

 ; with cte_max作为
（
）作为StartTime 
从AgentActivityLog 
中选择dateadd（mi，-15，max（StatusEndDateTime））作为EndTime，min（StatusSetDateTime），次数为
（
）选择StartTime作为cte_max的时间
 union all 
 select dateadd（mi，15，c.Time）
从时间开始c 
交叉加入cte_max为cm 
其中c.Time <= cm.EndTime 
）
选择
 t.Time，A.UserID，A.Status，
 case 
 when t.Time = A.StatusEndDateTime然后0 
 else A.StatusDuration /（count（*）over（由A.StatusSetDateTime，A.UserID，A.Status分区） -  1）
 end as Duration $来自AgentActivityLog的b $ b作为A 
离开外部联接时间t作为t.Time> = A.StatusSetDateTime和t.Time< = A.StatusEndDateTime 
  pre> 
 
 
    sql小提琴演示   
 
T-SQL, SQL Server 2008 and up

Given a sample table of
 StatusSetDateTime   | UserID | Status    | StatusEndDateTime   | StatusDuration(in seconds)
============================================================================
 2012-01-01 12:00:00 | myID   | Available | 2012-01-01 13:00:00 | 3600
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 : 

And something for T-SQL Here

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).

Thanks in advance for any insight!

edit : First Attempt
 ;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
edit : Second Attempt
;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 fiddle demo

                        
这篇关于将表分成15分钟的时间间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！