Смежный Орден ЦКЛ
Я ищу непрерывный алгоритм в ЦКЛ. Мои данные приведены ниже.
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
Любые идеи, как этого можно достичь. Заранее спасибо.
3 ответа
Решение
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 );
WITH cte
AS ( SELECT AVLeft.AreaVersionId AS [LEFT] ,
AVLeft.AreaId ,
AVLeft.VersionId ,
AVLeft.PocketGroupId ,
COALESCE(MAX(AVRight.VersionId),
( SELECT MIN(AV.VersionId)
FROM #Area_Version AS AV
)) AS maxVersion ,
COALESCE(MIN(AVRight.VersionId),
( SELECT MAX(AV.VersionId)
FROM #Area_Version AS AV
)) AS minVersion
FROM #Area_Version AS AVLeft
LEFT JOIN #Area_Version AS AVRight ON AVLeft.AreaVersionId > AVRight.AreaVersionId
GROUP BY AVLeft.AreaVersionId ,
AVLeft.AreaId ,
AVLeft.VersionId ,
AVLeft.PocketGroupId
)
SELECT cte.[LEFT] ,
cte.AreaId ,
cte.VersionId ,
CASE WHEN cte.VersionId > cte.minVersion
AND cte.VersionId < cte.maxVersion THEN 2
ELSE 1
END AS PocketGroupId
FROM cte
Вот динамическая версия с использованием курсора и цикла 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
SK, пожалуйста, посмотрите на картинку ниже, это мое понимание логики, которая вам требуется. мое понимание верно?
это еще одна визуализация в соответствии с моим пониманием
