SQL Server: новое решение задачи упаковки интервалов
Содержание:
1. Новое решение с использованием оконных агрегатов (Вы читаете данный раздел);
2. Сравнение со старыми решениями.
Смотрим программный код, представленный ниже. Как мы видим, каждый сеанс был активен в течение временного интервала с временем начала и конца, хранящимся в столбцах starttime и endtime соответственно. Задача — упаковать группы пересекающихся интервалов для каждой учетной записи. Таким образом, для каждой группы пересекающихся интервалов предполагается вернуть один непрерывный интервал; в качестве начального и конечного времени упакованного интервала предполагается использовать минимальное начальное время и максимальное конечное время в группе соответственно.
DDL и тестовые данные (малый набор)
Большой набор тестовых данных
Для тестовых данных в представленном коде желаемый результат показан в таблице ниже. Для проверки производительности решений необходимы более крупные наборы тестовых данных. Используйте программный код, представленный выше, для заполнения таблицы Accounts 5 тысячами учетных записей, а таблицы Sessions — двумя сотнями сеансов для каждой учетной записи (всего 1 млн сеансов). Вы можете изменить эти значения, чтобы протестировать решения с другим числом строк по вашему выбору.
Выходные данные полного решения
Чтобы повысить производительность, можно создать единый вспомогательный индекс, который упорядочивает данные по идентификатору учетной записи, времени начала сеанса, времени завершения сеанса и, наконец, идентификатору сеанса (если имеется два сеанса с одинаковой учетной записью, которые начались и завершились в одно и то же время). Используйте следующий программный код для формирования рекомендуемого индекса:
Следующий программный код реализует первый шаг решения:
Для каждого сеанса вычисляется текущий максимум, чтобы определить максимальное время завершения до предшествующего сеанса той же учетной записи (назовем результат prvend). В предшествующем сеансе используется следующий порядок: время начала, время завершения и идентификатор сеанса. Выходные данные на шаге 1 для малого набора тестовых данных показаны в таблице ниже. Естественно, первый сеанс учетной записи не имеет соответствующего предыдущего сеанса, поэтому значение prvend для первого сеанса равно NULL.
Выходные данные шага 1
1.
2. Сравнение со старыми решениями.
Тестовые данные для задачи упаковки интервалов
Смотрим программный код, представленный ниже. Как мы видим, каждый сеанс был активен в течение временного интервала с временем начала и конца, хранящимся в столбцах starttime и endtime соответственно. Задача — упаковать группы пересекающихся интервалов для каждой учетной записи. Таким образом, для каждой группы пересекающихся интервалов предполагается вернуть один непрерывный интервал; в качестве начального и конечного времени упакованного интервала предполагается использовать минимальное начальное время и максимальное конечное время в группе соответственно.
DDL и тестовые данные (малый набор)
Большой набор тестовых данных
Для тестовых данных в представленном коде желаемый результат показан в таблице ниже. Для проверки производительности решений необходимы более крупные наборы тестовых данных. Используйте программный код, представленный выше, для заполнения таблицы Accounts 5 тысячами учетных записей, а таблицы Sessions — двумя сотнями сеансов для каждой учетной записи (всего 1 млн сеансов). Вы можете изменить эти значения, чтобы протестировать решения с другим числом строк по вашему выбору.
Выходные данные полного решения
Новое решение с использованием оконных агрегатов
Чтобы повысить производительность, можно создать единый вспомогательный индекс, который упорядочивает данные по идентификатору учетной записи, времени начала сеанса, времени завершения сеанса и, наконец, идентификатору сеанса (если имеется два сеанса с одинаковой учетной записью, которые начались и завершились в одно и то же время). Используйте следующий программный код для формирования рекомендуемого индекса:
CREATE UNIQUE INDEX idx_start_end ON dbo.Sessions(actid, starttime, endtime, sessionid);Следующий программный код реализует первый шаг решения:
SELECT *,
MAX(endtime) OVER(PARTITION BY actid ORDER BY starttime, endtime, sessionid ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prvend
FROM dbo.Sessions;Для каждого сеанса вычисляется текущий максимум, чтобы определить максимальное время завершения до предшествующего сеанса той же учетной записи (назовем результат prvend). В предшествующем сеансе используется следующий порядок: время начала, время завершения и идентификатор сеанса. Выходные данные на шаге 1 для малого набора тестовых данных показаны в таблице ниже. Естественно, первый сеанс учетной записи не имеет соответствующего предыдущего сеанса, поэтому значение prvend для первого сеанса равно NULL.
Выходные данные шага 1
