找到一个SQL记录并发用户数(Find number of concurrent users in

2019-07-30 02:15发布

站内文章 / 后端开发

23 0

神经病院院长

女 | 书童

私信

我有以下结构的表：

UserID   StartedOn          EndedOn
1        2009-7-12T14:01    2009-7-12T15:01 
2        2009-7-12T14:30    2009-7-12T14:45
3        2009-7-12T14:47    2009-7-12T15:30
4        2009-7-12T13:01    2009-7-12T17:01
5        2009-7-12T14:15    2009-7-12T18:01
6        2009-7-12T11:01    2009-7-12T19:01
1        2009-7-12T16:07    2009-7-12T19:01

我需要找到那名在线并发用户的最大数量。另外，在上述表中的结果将是5，因为用户SET1 = {1,2,4,5,6}和SET2 = {1,3,4,5,6}中的在线在同一时期。

你有一个想法，一个怎么可能只计算此使用T-SQL？

Answer 1:

显然并发用户的数目，当用户或者开始或结束一个周期只改变，因此它是足够的，以确定何时开始和结束期间，并发用户的数目。所以，把通过瑞摩斯提供的测试数据（谢谢你，莱姆斯）：

DECLARE @Table TABLE 
(
  UserId int, 
  StartedOn datetime,
  EndedOn datetime
);

insert into @table (UserId, startedOn, EndedOn)
select 1, '2009-7-12 14:01', '2009-7-12 15:01'
union all select 2, '2009-7-12 14:30', '2009-7-12 14:45'
union all select 3, '2009-7-12 14:47', '2009-7-12 15:30'
union all select 4, '2009-7-12 13:01', '2009-7-12 17:01'
union all select 5, '2009-7-12 14:15', '2009-7-12 18:01'
union all select 6, '2009-7-12 11:01', '2009-7-12 19:01'
union all select 1, '2009-7-12 16:07', '2009-7-12 19:01';

SELECT MAX(ConcurrentUsers) FROM(
SELECT COUNT(*) AS ConcurrentUsers FROM @table AS Sessions 
JOIN 
(SELECT DISTINCT StartedOn AS ChangeTime FROM @table
) AS ChangeTimes
ON ChangeTime >= StartedOn AND ChangeTime < EndedOn 
GROUP BY ChangeTime
) AS ConcurrencyAtChangeTimes
-------
5

BTW使用DISTINCT本身并没有错 - 只是滥用DISTINCT是。 DISTINCT只是一个工具，在这种情况下使用它是完全正确的。

编辑：我回答OP的问题：“一个能如何计算仅此使用T-SQL”。请注意，这个问题并没有提到性能。

如果问题是这样的：“什么是确定最大并发最快的方法，如果数据被存储在SQL Server”，我会提供一个不同的答案，是这样的：

考虑以下方案

写光标
写CLR光标
写在客户端上环
使用RDBMS体面的游标，如Oracle或PostgreSQL
为了获得最佳性能，设计你的表格不同，这样就可以检索一个索引答案寻找。这是我在我的系统做，如果我需要提供最佳的性能。

如果问题是“什么是最快的方式确定最大并发使用T-SQL查询”，我可能不会在所有回答。其原因是：如果我需要真正好的表现，我不会解决在T-SQL查询这个问题。

Answer 2:

您可以在订购日期顺序所有事件和计算登录现有用户的运行聚合：

DECLARE @Table TABLE 
(
  UserId int, 
  StartedOn datetime,
  EndedOn datetime
);

insert into @table (UserId, startedOn, EndedOn)
select 1, '2009-7-12 14:01', '2009-7-12 15:01'
union all select 2, '2009-7-12 14:30', '2009-7-12 14:45'
union all select 3, '2009-7-12 14:47', '2009-7-12 15:30'
union all select 4, '2009-7-12 13:01', '2009-7-12 17:01'
union all select 5, '2009-7-12 14:15', '2009-7-12 18:01'
union all select 6, '2009-7-12 11:01', '2009-7-12 19:01'
union all select 1, '2009-7-12 16:07', '2009-7-12 19:01';

with cte_all_events as (
select StartedOn as Date
    , +1 as Users
    from @Table
union all 
select EndedOn as Date
    , -1 as Users
    from @Table),
cte_ordered_events as (
select Date
    , Users
    , row_number() over (order by Date asc) as EventId
    from cte_all_events)
, cte_agg_users as (
  select Date
    , Users
    , EventId
    , (select sum(Users) 
        from cte_ordered_events agg
        where agg.EventId <= e.EventId) as AggUsers
    from cte_ordered_events e)
select * from cte_agg_users


2009-07-12 11:01:00.000 1   1   1
2009-07-12 13:01:00.000 1   2   2
2009-07-12 14:01:00.000 1   3   3
2009-07-12 14:15:00.000 1   4   4
2009-07-12 14:30:00.000 1   5   5
2009-07-12 14:45:00.000 -1  6   4
2009-07-12 14:47:00.000 1   7   5
2009-07-12 15:01:00.000 -1  8   4
2009-07-12 15:30:00.000 -1  9   3
2009-07-12 16:07:00.000 1   10  4
2009-07-12 17:01:00.000 -1  11  3
2009-07-12 18:01:00.000 -1  12  2
2009-07-12 19:01:00.000 -1  13  1
2009-07-12 19:01:00.000 -1  14  0

一旦你有这样的地方，发现最大并发会话数是微不足道的。正如你看到你有两个时刻，当你有5个用户，在14:30时（用户2登录）和14:47（当用户3登录）。只需更换从CTE选择以获得实际最大上次查询：

select top(1) AggUsers 
    from cte_agg_users
    order by AggUsers desc

该解决方案采用热膨胀系数，因此只能在SQL 2K5工作，如果你仍然在SQL 2000，你就必须使用派生表的热膨胀系数，而不是重写一遍。

Answer 3:

我试图AlexKuznetsov的解决方案，但结果却是49 :(

我的解决方案：

/* Create temporary table and set all dates into 1 column,
so we can sort by this one column */
DECLARE @tmp table (
    Dates datetime,
    IsStartedDate bit )

INSERT INTO @tmp
    SELECT StartedOn, 1 FROM stats
    UNION ALL
    SELECT EndedOn, 0 FROM stats

DECLARE @currentlogins int, @highestlogins int, @IsStartedDate bit;
SET @currentlogins = 0;
SET @highestlogins = 0;

DECLARE tmp_cursor CURSOR FOR 
SELECT IsStartedDate FROM @tmp
ORDER BY Dates ASC

OPEN tmp_cursor

/* Step through every row, if it's a starteddate increment @currentlogins else decrement it
When @currentlogins is higher than @highestlogins set @highestlogins to the new highest value */
FETCH NEXT FROM tmp_cursor 
INTO @IsStartedDate

WHILE @@FETCH_STATUS = 0
BEGIN
    IF (@IsStartedDate = 1)
    BEGIN
        SET @currentlogins = @currentlogins + 1;
        IF (@currentlogins > @highestlogins)
            SET @highestlogins = @currentlogins;
    END
    ELSE
        SET @currentlogins = @currentlogins - 1;

    FETCH NEXT FROM tmp_cursor 
    INTO @IsStartedDate
END

CLOSE tmp_cursor
DEALLOCATE tmp_cursor

SELECT @highestlogins AS HighestLogins

Answer 4:

我没有使用整数而不是日期时间领域的工作，但我相信下面的SQL代码片段得到你想要的东西。

基本上，我比较反对对方的每个用户使用一个自连接的开始和结束日期。如果用户A之前或同时为用户B和用户B之前或在为用户A结束的同时开始启动，它们同时运行。因此，我发现并发用户的最大数量的用户（以及加入1为自己，因为我在自联接排除它们。）

我注意到你有每个用户的多行。请注意下面的SQL假设同一用户不能同时运行多个实例（兼）。如果这个假设不成立，我希望你有一个附加列是每行唯一的。使用此列，而不是用户ID在整个SQL例程。

我已经得到了你非常接近。我希望这有帮助。祝您好运。

DECLARE @Table TABLE 
(
  UserId int, 
  StartedOn int,
  EndedOn int
)

Insert Into @Table
Select 1, 1, 3
union
Select 2, 2, 4
union
Select 3, 3, 5
union
Select 4, 4, 6
union
Select 5, 7, 8
union
Select 6, 9, 10
union
Select 7, 9, 11
union
Select 8, 9, 12
union
Select 9, 10, 12
union
Select 10, 10, 13

--Select * from @Table

Select 
    A.UserId, 
    Count(B.UserId) + 1 as 'Concurrent Users'
FROM @Table A, @Table B
WHERE A.StartedOn <= B.StartedOn
AND B.StartedOn <= A.EndedOn
AND A.UserId != B.UserId
Group By A.UserId
Order By Count(B.UserId) Desc

Answer 5:

天真的方法：
您可以测试如果另一个用户B当前登录的用户登录时与

a.StartedOn BETWEEN b.StartedOn AND b.EndedOn

而有人是“最后登录”设定的“最并发用户”的。
如果你现在经历的所有记录（作为），并检查有多少其他用户（b）凡在当时登录，然后对列表进行排序（递减）的第一个结果是并发用户的最大数量。

SELECT
  a.id, a.UserId, a.StartedOn, a.EndedOn,  
  (  
    SELECT    
      Count(*)      
    FROM    
      logons as b      
    WHERE    
      a.StartedOn BETWEEN b.StartedOn AND b.EndedOn            
  ) as c
FROM
  logons as a 
ORDER BY
  c desc

现在读通过应用程序开发者所做的数据库开发的错误，看看如何低效的（甚至是错误的），这是;-)
例如，你有一个大的临时表的顺序进行操作，没有任何指标，以帮助在SQL Server。

（和BTW：我使用MySQL测试这一点，因为我没有在手的SQL Server现在）

Answer 6:

这是不是一个解决方案。因为，在此张贴的时间，最upvoted的解决方案有一个很讨厌的CROSS JOIN的行数量较少以及真是可恶三角JOIN较大的行，我想我会发布一些代码，以做出更数测试数据的大量的人做他们与测试。让比赛开始。 ;-)

DROP TABLE #Table
GO
WITH
cteStartedOn AS
(
 SELECT TOP 100000 --LOOK!  Change this number to vary the number of rows you're testing with.
        UserID = ABS(CHECKSUM(NEWID()))%1000,
        StartedOn = RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'2012','2013')+CAST('2012' AS DATETIME)
   FROM sys.all_columns ac1, sys.all_columns ac2
)
 SELECT UserID, StartedOn,
        EndedOn = DATEADD(ss,ABS(CHECKSUM(NEWID()))%36000,StartedOn) --10 hours max
   INTO #Table
   FROM cteStartedOn;