Количество количества 'перекрывающихся' строк в SQL Server
Меня попросили посмотреть на базу данных, которая записывает пользовательское действие входа в систему и выхода из системы - существует столбец в течение времени входа в систему и затем другой столбец для записи выхода из системы, обоих в формате OLE. Я должен сплотить некоторую информацию о пользовательском параллелизме - т.е. сколько пользователей было зарегистрировано одновременно каждый день.
Кто-либо знает, как сделать это в SQL? Я не должен действительно знать деталь, просто количество в день.
Заранее спасибо.
3 ответа
Думаю, это сработает.
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
Самый простой способ - сделать times_table из вспомогательной таблицы чисел (добавив от 0 до 24 * 60 минут к базовому времени), чтобы получить каждое время в определенный 24-часовой период:
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)
Не отмечен ... но теряются значения даты, отсчитывается время между ними, используется «конец дня», если вы все еще вошли в систему.
Предполагается, что «время входа» - это дата и время. В противном случае производную таблицу можно удалить (хотя ISNULL все еще требуется). конечно, у SQL Server 2008 есть «время» сделать и это проще.
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