Блог

Главная - Вопросы по программированию - T-SQL: острова и пробелы, начало / конец

T-SQL: острова и пробелы, начало / конец

#tsql #subquery #sql-server-2014 #gaps-and-islands

#tsql #подзапрос #sql-server-2014 #пробелы и острова

Вопрос:

У меня есть набор данных, в котором я должен объединить диапазоны для двух конкретных полей. Во время исследования я видел, что эта проблема называется «Острова и пробелы». К сожалению, большинство IAG, касающихся слияния между одним столбцом («StartDate») или что-то в этом роде. В то время как в моей проблеме у меня есть два столбца, которые составляют диапазон (PlzVon- PlzBis).

Я нашел множество примеров, но все они, как правило, повсюду со ссылкой на снятие шкуры с кошки. Некоторые из них используют CTE, другие используют ОТСТАВАНИЕ / ОПЕРЕЖЕНИЕ, о которых я никогда не слышал до сих пор.

Я попытался перепрофилировать один скрипт, который я нашел, в основном потому, что я могу понять, что он говорит, но без кубиков. Я вижу все «части» проблемы (поиск верхних / нижних конечных точек, сопряжение двух записей), но я не могу понять, как сформировать с ней согласованное утверждение. Пример

На фотографии выше я хотел бы объединить выделенные строки так, чтобы они были PlzVon-73000 и PlzBis-74999. Я могу сказать, что мне нужен либо CTE, либо коррелированный подзапрос, и у меня есть условие b.PlzVon = a.PlzBis 1. Но одного соединения будет недостаточно, поскольку эта новая запись может затем соединиться с другой, что приведет нас к неприятному пути рекурсии и курсоров.

Любая помощь в выяснении того, как объединить эти острова, была бы весьма признательна.

Комментарии:

1. Что уникального в двух выделенных строках, которые можно использовать для определения того, что эти две строки должны быть объединены в одну строку? Что такого в первой строке с PlzVon-38000, что может привести к исключению ее из объединения с двумя другими строками?

2. Какую версию SQL Server вы используете? Пожалуйста, добавьте соответствующий тег.

3. Это не похоже gaps-and-islands на проблему. Взгляните на интервалы упаковки Ицика Бен-Гана. Если вы предоставите некоторые образцы данных в виде текста или в виде INSERT утверждений (не изображений) в вопросе и вашем ожидаемом результате, есть большая вероятность, что кто-нибудь напишет рабочий запрос.

4. @VladimirBaranov Это версия 2014, и я добавил тег, как вы просили.

5. @JohnH Поля ID_FI и ID_PE используются для идентификации «сущности», на которую они указывают. Я должен был добавить это к своему вопросу.

Ответ №1:

Если я правильно понимаю вопрос, то это проблема с островом данных, но в диапазоне, а не в ключе. Следующий запрос демонстрирует, как эта проблема может быть решена с использованием подхода острова данных.

 DECLARE @SourceData TABLE 
(
     ID         INT
    ,PlzVon     INT
    ,PlzBis     INT
)
INSERT INTO @SourceData 
VALUES
(1,38000,38999),
(2,73000,73999),
(3,74000,74999),
(4,75000,75999),
(5,85000,85999);


;WITH CTE_DataIslands  -- First CTE determine the start of each new data island
AS
(
    SELECT           [Main].ID
                    ,[Main].[PlzVon]
                    ,[Main].[PlzBis]
                    ,(
                        CASE
                            WHEN (LAG([Main].[PlzBis], 1) OVER  (ORDER BY [Main].[PlzVon] ASC)   1 ) <> ([Main].[PlzVon]) THEN 1 -- If prev record's value for ([PlzBis]   1)  is not equal to current record [PlzVon] value then it is the start of a new data island.
                            ELSE 0
                        END
                     ) AS [IsNewDataIsland]
    FROM            @SourceData [Main]
), CTE_GenerateGroupingID
AS
(
    SELECT  ID
            ,[PlzVon]
            ,[PlzBis]
            ,SUM([IsNewDataIsland]) OVER (ORDER BY [PlzVon] ROWS UNBOUNDED PRECEDING) AS GroupingID -- Create a running total of the IsNewDataIsland column this will create a grouping id we can now group on
    FROM    CTE_DataIslands
)
SELECT      MIN([PlzVon]) AS [PlzVon]       -- Min [PlzVon] will give the lower range
            ,MAX([PlzBis]) AS [PlzBis]      -- Max [PlzBis] will give the upper range (use min or max for any other column that should be included in the return result)
FROM        CTE_GenerateGroupingID
GROUP BY    GroupingID

Ответ №2:

Мне было неясно, что такое PK, поэтому я добавил идентификатор в свою примерную таблицу

 Declare @YourTable Table (ID int,PlzVon int,PlzBis int)
Insert Into @YourTable values
(1,38000,38999),
(1,73000,73999),
(1,74000,74999)

;with cte0(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N))
     ,cteN(N) As (Select Top (Select max(PlzBis)-min(PlzVon) 1 From @YourTable) 
                         N=(Select min(PlzVon)-1 from @YourTable) Row_Number() over (Order By (Select NULL)) 
                   From  cte0 N1, cte0 N2, cte0 N3, cte0 N4, cte0 N5, cte0 N6) 
     ,cteBase As (Select A.ID
                        ,A.PlzVon
                        ,A.PlzBis
                        ,PosNr = N.N
                        ,RowNr = N.N - Row_Number() Over (Partition By A.ID Order By N.N) 
                   From  cteN N
                   Join  @YourTable A on N.N Between A.PlzVon and A.PlzBis
                 )
Select ID
      ,PlzVon = min(PosNr)
      ,PlzBis = max(PosNr)
 From  cteBase
 Group By ID,RowNr
 Order By ID,min(PosNr)

ВОЗВРАТ

 ID  PlzVon  PlzBis
1   38000   38999
1   73000   74999