Консолидация смежных, перекрывающихся и встроенных диапазонов во взаимоисключающие диапазоны
Среда является SQL Server 2014.
Я имею дело с сокращением многих деталей регистрации в страховании (небольшие диапазоны первого и последнего) в гораздо большие взаимоисключающие (ME) диапазоны непрерывной регистрации.
Для ясности проблема сводится к выборочным данным, отсортированным по id, сначала, последним. F(n) и L(n) - это первое и последнее значения в записи n в id.
Наиболее подробные диапазоны являются типичными
- смежный, F(n) = L(n-1) + 1
Но в деталях есть дьявол - добро пожаловать в данные реального мира.
- подключен несмежно, F(n) <= L(n-1)
- вложенный, L(n) <= L(n-1)
- перекрытие, L(n) > L(n-1)
- отключен несмежный
- Разрыв определяет границы консолидированных диапазонов, которые являются взаимоисключающими
- ME(i).last = максимум предыдущего L
Эта картина демонстрирует большинство случаев
Have
1 30 60 90 120
+-------+--------+--------+--------+
1 +-------+ (1:30)
2 +-------+ (31:60) adjacent
3 +--+ (40:50) embedded
4 + (61:61) adjacent some earlier
5 +-+ (61:65) adjacent some earlier
6 +--+ (61:75) adjacent some earlier
7 +--+ (65:80) overlap
8 +---------+ (85:120) gap, boundaries of ME ranges located
9 +-------+ (91:120)
10 +--+ (110:120)
Want
1 30 60 90 120
+-------+--------+--------+--------+
1 +----------------------+ (1:80)
2 +---------+ (85:120)
There are other unusual cases, such as embed followed by gap
.....
..
....
AAAAA BBBB
DROP TABLE #Details
CREATE TABLE #Details (id int, first int, last int);
insert into #Details values (1, 1, 30);
insert into #Details values (1, 31, 60);
insert into #Details values (1, 40, 50);
insert into #Details values (1, 61, 75);
insert into #Details values (1, 65, 80);
insert into #Details values (1, 85, 120);
insert into #Details values (1, 91, 120);
insert into #Details values (1, 110, 120);
Я прочитал некоторые ответы по стекам и диапазонам рефакторинга, но не смог сделать скачок в моем расположении данных.
- Для jpw--
Типичный анализ может включать 20000 идентификаторов с 200 подробными записями. Эти случаи были обработаны путем загрузки на локальный компьютер и обработаны (в виде курсора) на шаге SAS Data. В худшем случае это порядка 650K идентификаторов и 150M детализации - слишком много данных для загрузки, что приводит к проблемам с другими ресурсами. Я считаю, что все детали могут быть в диапазоне 1,2 млрд. Строк. В любом случае, если все это можно сделать на SQL-сервере, весь процесс упрощается.
1 ответ
Хорошо, этот ответ приблизит вас. Чувствую себя немного испеченным для меня, но определенно на правильном пути. Я уверен, что вы можете адаптировать это к вашим потребностям. Суть проблемы заключается в создании семей перекрытия. Я использовал рекурсивный cte после создания родительского списка. Пожалуйста, смотрите мое объяснение ниже для более подробной информации.
Начальные данные
USERID RangeStart RangeEnd
----------- ----------- -----------
1 1 2
1 2 4
1 3 5
1 6 7
2 1 3
2 5 9
2 11 14
2 14 15
запрос
DECLARE @USERID TABLE (USERID INT, RangeStart INT, RangeEnd INT)
INSERT INTO @USERID (USERID, RangeStart,RangeEnd) VALUES
(1,1,2),(1,2,4),(1,3,5),(1,6,7),
(2,1,3),(2,5,9),(2,11,14),(2,14,15)
;WITH Data AS (
SELECT ROW_NUMBER() OVER (ORDER BY USERID, RangeStart) AS MasterOrdering,
USERID,
RangeStart,
RangeEnd,
LAG(RangeStart) OVER (PARTITION BY USERID ORDER BY RangeStart ASC) AS PreviousStart,
LAG(RangeEnd) OVER (PARTITION BY USERID ORDER BY RangeStart ASC) AS PreviousEnd
FROM @USERID
), ParentChild AS (
SELECT *,
Parent = CASE
WHEN PreviousStart IS NULL AND PreviousEnd IS NULL THEN MasterOrdering
WHEN PreviousEnd NOT BETWEEN RangeStart AND RangeEnd THEN MasterOrdering
ELSE 0
END
FROM Data
), Family AS (
SELECT MasterOrdering,
USERID,
RangeStart,
RangeEnd,
PreviousStart,
PreviousEnd,
Parent
FROM ParentChild
WHERE Parent > 0
UNION ALL
SELECT A.MasterOrdering,
A.USERID,
A.RangeStart,
A.RangeEnd,
A.PreviousStart,
A.PreviousEnd,
F.Parent
FROM ParentChild AS A
INNER JOIN Family AS F ON ( A.MasterOrdering = F.MasterOrdering + 1
AND A.parent = 0)
)
SELECT USERID,
MIN(RangeStart) AS RangeStart,
MAX(RangeEnd) AS RangeEnd,
MIN(MasterOrdering) AS MasterOrdering
FROM Family
GROUP BY UserID,Parent
ORDER BY MIN(MasterOrdering)
Результаты
USERID RangeStart RangeEnd MasterOrdering
----------- ----------- ----------- --------------------
1 1 5 1
1 6 7 4
2 1 3 5
2 5 9 6
2 11 15 7
Query Walk Through
Предположения
- SQL Server 2014
- Разумное понимание оконных функций
- Твердое понимание CTE и рекурсивных CTE в частности.
Шаг за шагом
- Начинается с запроса данных. При этом используется функция ROW_NUMBER для установки последовательного ORDER на основе USERID и Ascending RangeStarts. Он используется позже, чтобы организовать список обратно в этот порядок. Функции LAG извлекают даты предыдущих строк PreviousStart и PreviousEnd. Это также используется при установлении родителей, и номер используется в качестве идентификатора семьи на основе этого идентификатора родителя.
- ParentChild заполняет столбец Parent на основе 2 правил. Если значения previousstart и previousend равны NULL, это означает, что в разделе они являются первым элементом. Мы назначаем их автоматически как родительский ряд. Затем, если PreviousEnd не находится между начальным и конечным диапазоном, мы также используем его как родительский.
- Семья - это то, где настоящая магия. Используя рекурсивный CTE, мы запрашиваем всех родителей и затем объединяем всех не-родителей в их связанный главный порядок + 1. Плюс + 1 гарантирует, что мы заполняем все 0, а предикат A.parent = 0 гарантирует, что мы присоединяемся только к неназначенным Члены семьи в родительском диапазоне.
- В конечном итоге у нас просто есть группа min и max по идентификатору пользователя и родительскому столбцу (который теперь является уникальным числом для перекрывающегося семейства).
Посмотри. Отличный вопрос и немного забавной головоломки.
ура
Matt