逐分钟分解日志数据

I have a table (Excel sheet, whatever...) containing PeopleSoft login information with three columns: Login Time, Logout Time, Time Spent.

我有一个表(Excel表，随便什么…)，包含PeopleSoft的登录信息，有三列:登录时间、注销时间、花费的时间。

My manager has been asked to use that to provide a minute by minute run down of concurrent logins for the entire data set (day).

我的经理被要求使用它来为整个数据集(天)提供一分钟一分钟的并发登录。

So, the SELECT for the table is like so:

因此，表的选择如下:

SELECT LOGIN.Login, LOGIN.[Log out], LOGIN.[Time in]
FROM LOGIN;

Output looks like this:

输出是这样的:

Login               Log out             Time in
11/1/10 12:36 AM    11/1/10 12:42 AM    0:06
...

What I need is:

我需要的是:

Time                    Concurrent_Logins  
11/1/10 12:36 AM        16

...

…

So, this is quite complicated. Any thoughts?

这很复杂。任何想法吗?

3 个解决方案

#1

What you'll want to do is build a table that contains every minute for the day. There are a number of ways you can do this, just search for "tally table", etc.

您要做的是构建一个包含一天中每一分钟的表。有很多方法可以做到这一点，只要搜索“计数表”等等。

Once you have a table containing all of your minutes (in datetime format), it should be straightforward.

一旦您有了一个包含所有分钟(以datetime格式)的表，那么它应该是简单的。

Join your login table to the minutes table on minute between login/logout and do a count(*) for each minute.

在登录/注销之间每分钟将登录表连接到分钟表，每分钟做一次计数(*)。

#2

Derek's solution is the way to go.

德里克的解决方案是正确的。

http://www.ridgway.co.za/archive/2007/11/23/using-a-common-table-expression-cte-to-generate-a-date.aspx explain a way to generate on the fly the timetable

http://www.ridgway.co.za/archive/2007/11/23/ use -a common table express - cteto -generate-a-date.aspx解释了一种动态生成时间表的方法

SET DATEFORMAT DMY
GO
DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME

SELECT @STARTDATE = '02/01/2011 01:00', @ENDDATE = '03/01/2011 01:00'
;

WITH DateRange(MyDateTime) AS
(
    SELECT
      @STARTDATE AS MyDateTime
    UNION ALL
    SELECT
        DATEADD(minute, 1, MyDateTime) AS MyDateTime
    FROM
        DateRange
    WHERE
        MyDateTime < @ENDDATE
)
SELECT MyDateTime, ConcurrentConnections = COUNT(*) 
       FROM DateRange INNER JOIN [LOGIN] ON MyDateTime >= [LogIn] AND MyDateTime <= [Log Out]
   OPTION (MaxRecursion 10000);

#3

To solve this problem I would first get the min and the max datetime values from the dataset. This would provide the time range from which we will need to determine the count of concurrent logins. After getting the time range, I would do a loop to populate a table with each minute in the range and the count of concurrent logins for that minute. After populating the table I would select from it where concurrent login count > 0, this would be my result set. I use SQL Server, you may need to convert some of the syntax to another DBMS.

为了解决这个问题，我首先从数据集中获取最小值和最大时间值。这将提供我们需要确定并发登录计数的时间范围。在获得时间范围之后，我将执行一个循环，用范围内的每一分钟填充一个表，并在那一分钟填充并发登录的次数。填充完表后，我将从其中选择并发登录计数> 0的表，这将是我的结果集。

-- To get the min and max of the time range
DECLARE @min datetime, @max datetime
SELECT @min = MIN(l.[Login]), @max = MAX(l.[Log out])
  FROM [LOGIN] l

-- now make a table to how the minutes and the counts
CREATE TABLE #Result
(
    [Time] datetime,
    [count] int
)

-- now do a loop to fill each minute between @min and @max
DECLARE @currentTime datetime, @count int
SELECT @currentTime = @min, @count = 0
-- go from @min to @max
WHILE @currentTime < @max
BEGIN
    -- get the count of concurrent logins for @currentTime
    SELECT @count = COUNT(*)
      FROM [LOGIN] l
     WHERE @currentTime between l.[Login] and l.[Log out]

    -- insert into our results table
    INSERT #Result ([Time], [count]) VALUES (@currentTime, @count)

    -- increment @currentTime for next pass
    SELECT @currentTime = DATEADD(minute, 1, @currentTime) 
END

-- select final result (where count > 0)
SELECT *
  FROM #Result
 WHERE [count] > 0

-- clean up our temp table
DROP TABLE #Result

#1