SQL Server: новое решение задачи упаковки интервалов. Продолжение

Цель второго шага — выяснить, начинается ли новый упакованный интервал с текущего интервала. На этом шаге реализована важная часть решения, поэтому обязательно разберитесь в его логике. Интервалы I1 и I2 пересекаются, если выполняются два условия: I2.endtime >= I1.starttime AND 12. starttime <= I1.endtime. Очевидно, в сеансах вплоть до предшествующего текущему для последнего упакованного интервала в качестве времени завершения будет использоваться значение prvend. Благодаря оконному предложению порядка в вычислении prvend по определению время завершения текущего сеанса больше или равно времени начала последнего упакованного интервала. Это объясняется тем, что время начала текущего сеанса больше или равно времени начала любого предшествующего сеанса, а время завершения текущего сеанса больше или равно времени начала текущего сеанса. Другими словами, одно из условий пересечения между текущим сеансом и последним упакованным интервалом всегда подразумевается. Поэтому остается явно проверить лишь другое условие. А именно, если время начала текущего сеанса меньше или равно значению prvend, то оно не начинает новый упакованный интервал; в противном случае — начинает. На основе этой логики в следующем программном коде рассчитывается флаг с именем isstart, которому присваивается значение NULL, если текущий сеанс не начинает новый упакованный интервал, и значение 1, если начинает:

WITH C1 AS
(
  SELECT *,
    MAX(endtime) OVER(PARTITION BY actid
                      ORDER BY starttime, endtime, sessionid
                      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prvend
  FROM dbo.Sessions
)
SELECT *
FROM C1
  CROSS APPLY ( VALUES(CASE WHEN starttime <= prvend THEN NULL ELSE 1 END) ) AS A(isstart);

Третий шаг решения — формирование группового идентификатора упакованных интервалов (назовем его grp) с вычислением простой суммы нарастающим итогом флага isstart от начала раздела до текущей строки. Ниже приводится программный код, реализующий этот шаг:

WITH C1 AS
(
  SELECT *,
    MAX(endtime) OVER(PARTITION BY actid
                      ORDER BY starttime, endtime, sessionid
                      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prvend
  FROM dbo.Sessions
)
SELECT *,
  SUM(isstart) OVER(PARTITION BY actid
                    ORDER BY starttime, endtime, sessionid
                    ROWS UNBOUNDED PRECEDING) AS grp
FROM C1
  CROSS APPLY ( VALUES(CASE WHEN starttime <= prvend THEN NULL ELSE 1 END) ) AS A(isstart);

Наконец, чтобы получить упакованные интервалы, на четвертом шаге строки результаты шага 3 группируются по идентификатору учетной записи и групповому идентификатору. Возвращаются минимальное время начала в качестве времени начала упакованного интервала и максимальное время завершения в качестве времени завершения упакованного интервала. Программный код, который вы видите выше, представляет полное решение.



План выполнения этого решения очень эффективен. На рисунке выше показан мой план (построенный с использованием обозревателя планов SQL Sentry (http://www.sqlsentry.com/products/plan-explorer)) для больших наборов тестовых данных. Как мы видим, выполняется только один упорядоченный просмотр вспомогательного индекса. Обе оконные функции полагаются на порядок индекса, а не на явную сортировку. Я получил следующие статистические данные о производительности для этого запроса на своем компьютере: время центрального процессора = 12 545 мс, истекшее время = 4277 мс, число логических операций чтения = 3300.

---

Ваша корпоративная сеть дышит на ладан и постоянно становится вашей головной болью? Значит, вам определенно точно следует посетить страничку http://pks-alteko.ru/uslugi/resheniya_dlya_gos_struktur/ (http://pks-alteko.ru/uslugi/resheniya_dlya_gos_struktur/). Здесь вы найдете опытных специалистов по IT, которые отлично разбираются как SQL Server, так и в любых других сетевых средах, служащих для управления базами данных.