Непрерывный заказ tsql

Я ищу непрерывный алгоритм в tsql. Мои настройки данных, как показано ниже.

IF OBJECT_ID('Tempdb..#Area_Version') IS NOT NULL 
DROP TABLE #Area_Version
CREATE TABLE #Area_Version
(
  AreaVersionId INT ,
  AreaId VARCHAR(1) ,
  VersionId VARCHAR(2) ,
  PocketGroupId INT
)

INSERT  INTO #Area_Version
    ( AreaVersionId, AreaId, VersionId, PocketGroupId )
VALUES  ( 1, 'A', 'V5', 0 ),
    ( 2, 'B', 'V2', 0 ),
    ( 3, 'C', 'V2', 0 ),
    ( 4, 'D', 'V3', 0 ),
    ( 5, 'E', 'V2', 0 ),
    ( 6, 'F', 'V1', 0 ),
    ( 7, 'F', 'V4', 0 ),
    ( 8, 'G', 'V3', 0 );

Полученные результаты:

AreaVersionId AreaId VersionId PocketGroupId
------------- ------ --------- -------------
1             A      V5        0
2             B      V2        0
3             C      V2        0
4             D      V3        0
5             E      V2        0
6             F      V1        0
7             F      V4        0
8             G      V3        0

(8 row(s) affected)

Ниже представлено визуальное представление данных.

AreaId  V1  V2  V3  V4  V5
A                        x
B            x            
C            x            
D                x        
E            x            
F        x           x  
G                x

Заказывая по AreaId, я ищу смежные группы. Поскольку V2 находится в областях B, C и E, V2 и V3 в D они не могут располагаться в одном непрерывном порядке. Набор данных выше может привести к двум группам. (V5, V2, V1 и V3, V4) или (V5, V3, V4 и V2, V1). Я могу решить, какой набор имеет приоритет, на основе столбца количества, который выходит за рамки вопроса.

Основываясь на наборе результатов алгоритма, я обновлю PocketGroupId, чтобы я мог выполнять запросы на основе PocketGroupId и знать, что это непрерывная группа.

Желаемый результат должен быть примерно таким.

AreaVersionId AreaId VersionId PocketGroupId
------------- ------ --------- -------------
1             A      V5        1
2             B      V2        1
3             C      V2        1
4             D      V3        2
5             E      V2        1
6             F      V1        1
7             F      V4        3
8             G      V3        2

Любые идеи, как это может быть достигнуто. Заранее спасибо.


sql-server tsql contiguous
person S K    schedule 15.12.2014    source источник
comment
Какая версия sql сервера?   -  person T I    schedule 16.12.2014

Ответы (2)


arrow_upward
1
arrow_downward

Вот динамическая версия, использующая курсор и цикл while... и дополнительные временные таблицы. Это грубо, но работает.

IF OBJECT_ID('Tempdb..#Area_Version') IS NOT NULL
    DROP TABLE #Area_Version

CREATE TABLE #Area_Version
    (
      AreaVersionId INT ,
      AreaId VARCHAR(1) ,
      VersionId VARCHAR(2) ,
      PocketGroupId INT
    )

INSERT INTO #Area_Version (AreaVersionId, AreaId, VersionId, PocketGroupId) VALUES (1, 'A', 'V5', 0)
INSERT INTO #Area_Version (AreaVersionId, AreaId, VersionId, PocketGroupId) VALUES (2, 'B', 'V2', 0)
INSERT INTO #Area_Version (AreaVersionId, AreaId, VersionId, PocketGroupId) VALUES (3, 'C', 'V2', 0)
INSERT INTO #Area_Version (AreaVersionId, AreaId, VersionId, PocketGroupId) VALUES (4, 'D', 'V3', 0)
INSERT INTO #Area_Version (AreaVersionId, AreaId, VersionId, PocketGroupId) VALUES (5, 'E', 'V2', 0)
INSERT INTO #Area_Version (AreaVersionId, AreaId, VersionId, PocketGroupId) VALUES (6, 'F', 'V1', 0)
INSERT INTO #Area_Version (AreaVersionId, AreaId, VersionId, PocketGroupId) VALUES (7, 'F', 'V4', 0)    
INSERT INTO #Area_Version (AreaVersionId, AreaId, VersionId, PocketGroupId) VALUES (8, 'G', 'V2', 0)    
INSERT INTO #Area_Version (AreaVersionId, AreaId, VersionId, PocketGroupId) VALUES (9, 'H', 'V4', 0)    

IF OBJECT_ID('Tempdb..#WorkingArea_Version') IS NOT NULL
    DROP TABLE #WorkingArea_Version

CREATE TABLE #WorkingArea_Version
    (
      AreaVersionId INT ,
      AreaId VARCHAR(1) ,
      VersionId VARCHAR(2) ,
      PocketGroupId INT
    )

INSERT INTO #WorkingArea_Version
SELECT * FROM #Area_Version

IF OBJECT_ID('Tempdb..#resultset') IS NOT NULL
    DROP TABLE #resultset

CREATE TABLE #resultset
    (
      PocketGroupId INT ,
      VersionId NVARCHAR(MAX)
    )

DECLARE @areaCount INT 
SELECT  @areaCount = COUNT(1)
FROM    #WorkingArea_Version av

 DECLARE @PocketGroupId INT
SET @PocketGroupId = 1

WHILE ( @areaCount > 0 )
    BEGIN

        DECLARE @AreaVersionId INT ,
            @AreaId VARCHAR(1) ,
            @VersionId VARCHAR(2)

        DECLARE db_cursor CURSOR
        FOR
            SELECT  av.AreaVersionId ,
                    av.AreaId ,
                    av.VersionId
            FROM    #WorkingArea_Version av
            ORDER BY av.AreaId

        OPEN db_cursor   
        FETCH NEXT FROM db_cursor INTO @AreaVersionId, @AreaId, @VersionId


        WHILE @@FETCH_STATUS = 0
            BEGIN   

                DECLARE @Versions NVARCHAR(MAX) = ''
                IF NOT EXISTS ( SELECT TOP 1
                                        1
                                FROM    #resultset r
                                WHERE   r.PocketGroupId = @PocketGroupId )
                    BEGIN

                        INSERT  INTO #resultset
                                ( PocketGroupId, VersionId )
                        VALUES  ( @PocketGroupId, @VersionId )                
                    END
                ELSE
                    BEGIN

                        DECLARE @minAreaId NVARCHAR(1)
                        DECLARE @maxAreaId NVARCHAR(1)

            --current max
                        SELECT  @maxAreaId = MAX(AreaId)
                        FROM    #resultset r
                                JOIN #WorkingArea_Version av ON av.VersionId = r.VersionId

            --potentials min
                        SELECT  @minAreaId = MIN(AreaId)
                        FROM    #WorkingArea_Version av
                        WHERE   av.VersionId = @VersionId                           

                        IF ( @minAreaId > @maxAreaId )
                            BEGIN
                                INSERT  INTO #resultset
                                        ( PocketGroupId, VersionId )
                                VALUES  ( @PocketGroupId, -- PocketGroupId - int
                                          @VersionId  -- VersionId - nvarchar(max)
                                          )
                            END     
                    END

                FETCH NEXT FROM db_cursor INTO @AreaVersionId, @AreaId,
                    @VersionId
            END
        CLOSE db_cursor   
        DEALLOCATE db_cursor

        DELETE  FROM #WorkingArea_Version
        WHERE   VersionId IN ( SELECT   r.VersionId
                               FROM     #resultset r )

        SELECT  @areaCount = COUNT(1)
        FROM    #WorkingArea_Version av
        SET @PocketGroupId = @PocketGroupId + 1
    END



    SELECT av.AreaVersionId, av.AreaId, av.VersionId, r.PocketGroupId
    FROM #Area_Version av
    JOIN #resultset r ON r.VersionId = av.VersionId
    ORDER BY av.AreaVersionId
person meadisu    schedule 19.12.2014

arrow_upward

arrow_downward

person    schedule
comment
Спасибо. Как сделать это более динамично, если у меня более 2 PocketGroups - person S K; 17.12.2014
comment
@SK, приведите пример, как в вашем вопросе (визуальное представление) - person Vasily Ivoyzha; 17.12.2014
comment
Я обновил визуальное представление данных в вопросе. Теперь V3 должен быть в отдельной группе, а V4 в другой группе. Таким образом, запрос должен иметь возможность обрабатывать данные, чтобы группы определялись динамически. Если ряд данных не может попасть в группу, это должна быть новая группа. Если строка может попасть в существующую группу, ее можно добавить в группу. По сути, реальные данные, с которыми я работаю, содержат гораздо больше данных, чем я мог бы представить здесь. Спасибо за вашу помощь. - person S K; 17.12.2014