sql - 每分钟计算两列的行数

Question

我有一个包含 session 的 SQL Server 2012 表，开始时间和结束时间为 2。我现在需要知道每分钟有多少个 session 处于事件状态，这意味着 >= 开始时间且 <= 结束时间。

示例

Order_strUserId | Order_dtmInitiated      | Order_dtmLastUpdated
s2ir1f8vqx      | 2016-12-13 15:06:17.993 | 2016-12-13 15:06:59.723
4839m6lnjchn    | 2016-12-13 15:09:42.807 | 2016-12-13 15:12:21.220
y7k3u6q1wjn     | 2016-12-13 15:11:40.173 | 2016-12-13 15:12:01.630
sdc74a0ahid     | 2016-12-13 15:14:06.013 | 2016-12-13 15:14:28.703
2pgj2ixpta9     | 2016-12-13 15:17:41.567 | 2016-12-13 15:17:42.063
qlfv4vmxdb      | 2016-12-13 15:18:17.750 | 2016-12-13 15:18:47.227
y9jd24i59x5v    | 2016-12-13 15:19:30.160 | 2016-12-13 15:19:30.607
9vb2d6u90hn     | 2016-12-13 15:22:09.257 | 2016-12-13 15:22:09.743

第 1 行仅计算 2016-12-13 15:06，第 2 行计算 2016-12-13 15:09 和 2016-12-13 15:12 之间的每分钟等。

我需要的是类似的东西

Time             | ActiveSessions
2016-12-13 15:06 | 1
2016-12-13 15:09 | 1
2016-12-13 15:10 | 1
2016-12-13 15:11 | 2
2016-12-13 15:12 | 2

如何做到这一点？

到目前为止，我的想法是按分钟计算 UserId，然后将所需的时间添加到计数中。但我不知道如何计算这个。

SELECT COUNT(t.UserSessionId) ,
    DATEPART( yyyy , t.TimeInitiated) AS 'Year', 
    DATEPART(MM, t.TimeInitiated) 'Month', 
    DATEPART(DD, t.TimeInitiated) 'Day', 
    DATEPART(HH, t.TimeInitiated) 'Hour', 
    DATEPART(MI, t.TimeInitiated) 'Minute', 
    t.TimeNeeded
FROM ( SELECT DATEDIFF(MI, Order_dtmInitiated, Order_dtmLastUpdated) AS 'TimeNeeded',
        Order_strUserId AS 'UserSessionId',
        Order_dtmInitiated AS 'TimeInitiated'
    FROM tblOrder ) t
GROUP BY DATEPART( yyyy , t.TimeInitiated), DATEPART(MM, t.TimeInitiated), DATEPART(DD, t.TimeInitiated), DATEPART(HH, t.TimeInitiated), DATEPART(MI, t.TimeInitiated), t.TimeNeeded

Answer 1

您需要一个带有分钟的日历表

;WITH cte
     AS (SELECT Min(Cast(Dateadd(minute, Datediff(minute, 0, Order_dtmInitiated), 0) AS SMALLDATETIME))   AS st_date,
                Max(Cast(Dateadd(minute, Datediff(minute, 0, Order_dtmLastUpdated), 0) AS SMALLDATETIME)) ed_date
         FROM   Yourtable
         UNION ALL
         SELECT Dateadd(minute, 1, st_date),
                ed_date
         FROM   cte
         WHERE  st_date < ed_date)
SELECT st_date,
       Count(1)
FROM   cte a
       LEFT JOIN Yourtable b
              ON a.st_date >= Cast(Dateadd(minute, Datediff(minute, 0, Order_dtmInitiated), 0) AS SMALLDATETIME)
                 AND a.st_date <= Cast(Dateadd(minute, Datediff(minute, 0, Order_dtmLastUpdated), 0) AS SMALLDATETIME)
GROUP  BY st_date
OPTION (maxrecursion 0) 

我已经使用Recursive CTE来生成日期，但我更喜欢在我的数据库中创建一个Calendar表并在这样的查询中使用它

• Live Demo