Найти количество одновременных пользователей в записях SQL

Ответ 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

Кстати, использование DISTINCT само по себе не является ошибкой - только злоупотребление DISTINCT. DISTINCT - это просто инструмент, использующий его в этом контексте совершенно правильно.

Изменить: Я отвечал на вопрос OP: "как можно вычислить это, используя только T-SQL". Обратите внимание, что в вопросе не упоминается производительность.

Если бы вопросы были такими: "какой самый быстрый способ определить максимальный concurrency, если данные хранятся в SQL Server", я бы предложил другой ответ, примерно такой:

Рассмотрим следующие альтернативы

• Введите курсор
• Введите курсор CLR
• Напишите цикл на клиенте
• Используйте СУБД с достойными курсорами, такими как Oracle или PostgreSql
• Для максимальной производительности создайте свою таблицу по-разному, чтобы вы могли получить ответ в одном запросе индекса. Это то, что я делаю в своей системе, если мне нужно обеспечить наилучшую производительность.

Если возник вопрос: "Какой самый быстрый способ определить максимальный concurrency с помощью запроса T-SQL", я бы, вероятно, вообще не ответил. Причина: если мне нужна действительно хорошая производительность, я бы не решил эту проблему в запросе T-SQL.

Ответ 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

В этом решении используются CTE, поэтому он будет работать только на SQL 2k5, если вы все еще на SQL 2000, вам придется переписать его с использованием производных таблиц вместо CTE.

Ответ 3

Я попробовал решение Алексея Кузнецова, но результат был 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 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

Ответ 4

Я выполнял работу с использованием целых чисел, а не полей datetime, но я считаю, что следующий фрагмент sql доставит вам то, что вы хотите.

В принципе, я сравнивал дату начала и конца каждого пользователя друг с другом, используя самосоединение. Если пользователь А запускался до или в то же время, что и пользователь B И пользователь B, запускаемый до или в то же время, когда пользователь A закончил, они запускаются одновременно. Таким образом, я нашел пользователя с максимальным количеством одновременных пользователей (и добавил 1 для себя, так как я исключил их в самосоединение.)

Я заметил, что у вас есть несколько строк для каждого пользователя. Обратите внимание, что нижеприведенный sql предполагает, что один и тот же пользователь не может одновременно запускать несколько экземпляров (одновременно). Если это предположение не выполняется, я надеюсь, что у вас есть дополнительный столбец, который является уникальным для каждой строки. Используйте этот столбец, а не UserId во всей процедуре 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

Ответ 5

Наивный подход:
Вы можете проверить, зарегистрирован ли другой пользователь b, когда пользователь регистрируется с помощью

a.StartedOn BETWEEN b.StartedOn AND b.EndedOn

И кто-то должен быть "окончательным входом" в набор "самых одновременных пользователей".
Если вы теперь просматриваете все записи (как а) и проверяете, сколько других пользователей (б), где во время входа в систему, а затем заказывать список (desc), первым результатом является максимальное количество одновременных пользователей.

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.

(и btw: я тестировал это с помощью MySQL, потому что у меня сейчас нет сервера sql)

Ответ 6

Это НЕ РЕШЕНИЕ. Так как во время этой публикации наиболее подходящее решение имеет очень неприятный CROSS JOIN для меньшего количества строк и действительно неприятный TRIANGULAR 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;

Ответ 7

вы присоединяетесь к этой таблице