代码之家 › 专栏 › 技术社区 › KenD Abbas Hadavandi

计算SQL Server中“重叠”行的数目

sql-server

KenD Abbas Hadavandi · 技术社区 · 16 年前

我被要求查看一个记录用户登录和注销活动的数据库—有一列记录登录时间,还有一列记录注销,都是OLE格式的。我需要收集一些关于用户并发性的信息—例如,每天有多少用户同时登录。

3 回复 | 直到 16 年前

JohnFx 16 年前

我想这会管用的。

Select C.Day, Max(C.Concurrency) as MostConcurrentUsersByDay
FROM 
(
   SELECT convert(varchar(10),L1.StartTime,101) as day, count(*) as Concurrency
   FROM login_table L1
   INNER JOIN login_table L2 
      ON (L2.StartTime>=L1.StartTime AND L2.StartTime<=L1.EndTime) OR
         (L2.EndTime>=L1.StartTime AND L2.EndTime<=L1.EndTime)
    WHERE (L1.EndTime is not null) and L2.EndTime Is not null)  AND (L1.ID<>L2.ID)
    GROUP BY convert(varchar(10),L1.StartTime,101)
) as C    
Group BY C.Day

Cade Roux 16 年前

SELECT MAX(simul) FROM (
    SELECT test_time
        ,COUNT(*) AS simul
    FROM your_login_table
    INNER JOIN times_table -- a table/view/subquery of all times during the day
        ON your_login_table.login_time <= times_table.test_time AND times_table.test_time <= your_login_table.logout_time
    GROUP BY test_time
) AS simul_users (test_time, simul)

gbn 16 年前

未选中。。。但丢失日期值,计算时间间隔,使用“一天结束”表示仍然登录。

这假设“logintime”是一个日期和时间。如果不是,则可以删除派生表(但仍然需要ISNULL)。当然,SQLServer2008也有“时间”让这变得更容易。

SELECT
    COUNT(*)
FROM
    (
    SELECT
         DATEADD(day, DATEDIFF(day, logintime, 0), logintime) AS inTimeOnly,
         ISNULL(DATEADD(day, DATEDIFF(day, logouttime, 0), logintime), '1900-01-01 23:59:59.997') AS outTimeOnly
    FROM
         mytable
    ) foo
WHERE
    inTimeOnly >= @TheTimeOnly AND outTimeOnly <= @TheTimeOnly

推荐文章