Объединение перекрывающихся интервалов для вычисления пропущенных интервалов может быть сложной задачей для опытных программистов. В этой статье мы представим новое решение, которое использует тип данных геометрии и специальные функции, предназначенные для геометрических объектов в SQL Server.
Рассмотрим следующие интервалы: [1, 5], [64, 70], [66, 72], [150, 200], [130, 300]. Вопрос: какие интервалы чисел отсутствуют?
Чтобы найти пропущенные интервалы, мы можем преобразовать каждый интервал (Начало, Конец) в ПОЛИГОН (пространственный объект), используя формат ПОЛИГОН((Начало 0, Конец 0, Конец 1, Начало 1, Начало 0)). Например, диапазон (1, 5) становится ПОЛИГОН((1 0,5 0,5 1,1 1,1 0)).
Эти полигоны затем могут быть объединены с помощью функции UnionAggregate() для вычисления неперекрывающихся интервалов. Результатом является объект MULTIPOLYGON, содержащий неперекрывающиеся полигоны.
Затем мы можем вычислить ограничивающий прямоугольник MULTIPOLYGON с помощью функции STEnvelope(), которая дает нам полигон, представляющий внешнюю границу.
Наконец, мы можем вычислить пропущенные полигоны из этого ограничения с помощью функции STSymDifference(). Результатом является новый пространственный объект MULTIPOLYGON, содержащий пропущенные интервалы.
Вот окончательный исходный код:
DECLARE @pol GEOMETRY
DECLARE @tab TABLE(Col1 GEOMETRY);
INSERT INTO @tab(Col1)
SELECT RangePol = GEOMETRY::STPolyFromText('POLYGON((1 0,5 0,5 1,1 1,1 0))', 0) UNION ALL
SELECT RangePol = GEOMETRY::STPolyFromText('POLYGON((64 0,70 0,70 1,64 1,64 0))', 0) UNION ALL
SELECT RangePol = GEOMETRY::STPolyFromText('POLYGON((66 0,72 0,72 1,66 1,66 0))', 0) UNION ALL
SELECT RangePol = GEOMETRY::STPolyFromText('POLYGON((150 0,200 0,200 1,150 1,150 0))', 0) UNION ALL
SELECT RangePol = GEOMETRY::STPolyFromText('POLYGON((130 0,300 0,300 1,130 1,130 0))', 0);
DECLARE @ranges GEOMETRY;
SELECT @pol = GEOMETRY::UnionAggregate(t.Col1)
FROM @tab t
SELECT @pol.STAsText();
SELECT @pol.STEnvelope().STAsText()
SELECT @ranges = @pol.STEnvelope().STSymDifference(@pol);
SELECT @ranges.STAsText();
SELECT
Range_N = val.number,
Range_Polygon = @ranges.STGeometryN(val.number),
Range_PolygonT = @ranges.STGeometryN(val.number).ToString(),
Range_Start = @ranges.STGeometryN(val.number).STPointN(1).STX+1,
Range_End = @ranges.STGeometryN(val.number).STPointN(2).STX-1
FROM master.dbo.spt_values val
WHERE val.type = N'P'
AND val.number >= 1
AND val.number <= @ranges.STNumGeometries();
В заключение, в этой статье было представлено новое решение для обнаружения пропущенных интервалов в коллекции перекрывающихся интервалов с использованием типа данных геометрии и специальных функций в SQL Server. Путем использования пространственных объектов POLYGON и MULTIPOLYGON, а также функций, таких как UnionAggregate, STEnvelope, STSymDifference, STNumGeometries() и STGeometryN(), мы можем эффективно вычислять пропущенные интервалы.