Найти последнюю строку в каждой группе из 2 строк
Есть пример таблицы
select *
from ( values
(1,15,'stage_1'),
(12,15,'stage_11'),
(13,15,'stage_11'),
(28,15,'stage_3'),
(30,15,'stage_11'),
(35,15,'stage_3'),
(77,23,'stage_1'),
(98,23,'stage_3'),
(101,23,'stage_4'),
(1021,34,'stage_1'),
(1031,34,'stage_11'),
(1035,34,'stage_11'),
(1039,34,'stage_11'),
(1059,34,'stage_11'),
(1021,34,'stage_9'),
(21506,89,'stage_17'),
(21521,89,'stage_2'),
(21543,89,'stage_11'),
(21568,89,'stage_8'),
(2221,314,'stage_1'),
(2311,314,'stage_11'),
(2352,314,'stage_11'),
(2355,314,'stage_11')
) as stages(id, number, stage)
Необходимо найти (пометить) последний stage_11 в каждой группе из 2, а также все элементы, которые не имеют группы. Группой из 2 считаются только 2 следующих друг за другом stage_11 в группе по полю number с сортировкой по id, number. Групп может быть несколько.
|-------ИСХОДНИК--------| |--------ЧТО ТРЕБУЕТСЯ--------|
|id |number|stage | |id |number|stage |cnt |
|------|------|---------| |------|------|---------|-----|
|1 |15 |stage_1 | |1 |15 |stage_1 |NULL |
|12 |15 |stage_11 | |12 |15 |stage_11 |NULL |
|13 |15 |stage_11 | |13 |15 |stage_11 |1 | -- последний id из (12,13)
|28 |15 |stage_3 | |28 |15 |stage_3 |NULL |
|30 |15 |stage_11 | |30 |15 |stage_11 |1 | -- нет пары
|35 |15 |stage_3 | |35 |15 |stage_3 |NULL |
|------|------|---------| |------|------|---------|-----|
|77 |23 |stage_1 | |77 |23 |stage_1 |NULL |
|98 |23 |stage_3 | |98 |23 |stage_3 |NULL |
|101 |23 |stage_4 | |101 |23 |stage_4 |NULL |
|------|------|---------| |------|------|---------|-----|
|1021 |34 |stage_1 | |1021 |34 |stage_1 |NULL |
|1021 |34 |stage_9 | |1021 |34 |stage_9 |NULL |
|1031 |34 |stage_11 | |1031 |34 |stage_11 |NULL |
|1035 |34 |stage_11 | |1035 |34 |stage_11 |1 | -- последний id из (1031,1035)
|1039 |34 |stage_11 | |1039 |34 |stage_11 |NULL |
|1059 |34 |stage_11 | |1059 |34 |stage_11 |1 | -- последний id из (1039,1059)
|------|------|---------| |------|------|---------|-----|
|21506 |89 |stage_17 | |21506 |89 |stage_17 |NULL |
|21521 |89 |stage_2 | |21521 |89 |stage_2 |NULL |
|21543 |89 |stage_11 | |21543 |89 |stage_11 |1 | -- нет пары
|21568 |89 |stage_8 | |21568 |89 |stage_8 |NULL |
|------|------|---------| |------|------|---------|-----|
|2221 |314 |stage_1 | |2221 |314 |stage_1 |NULL |
|2311 |314 |stage_11 | |2311 |314 |stage_11 |NULL |
|2352 |314 |stage_11 | |2352 |314 |stage_11 |1 | -- последний id из (2311,2352)
|2355 |314 |stage_11 | |2355 |314 |stage_11 |1 | -- нет пары
Пробовал сделать через row_number() + left join, а также lag() и lead(), но ничего не вышло. Когда всего одна пара и одна без пары, то норм, но когда появляется 2 и более пар, то все ломается.
Ответы (2 шт):
Автор решения: maksimski
→ Ссылка
Сделал такое себе решение
if object_id('tempdb..#tmp_values') is not null drop table #tmp_values
select *
into #tmp_values
from ( values
(1,15,'stage_1'),
(12,15,'stage_11'),
(13,15,'stage_11'),
(28,15,'stage_3'),
(30,15,'stage_11'),
(35,15,'stage_3'),
(77,23,'stage_1'),
(98,23,'stage_3'),
(101,23,'stage_4'),
(1021,34,'stage_1'),
(1031,34,'stage_11'),
(1035,34,'stage_11'),
(1039,34,'stage_11'),
(1059,34,'stage_11'),
(1021,34,'stage_9'),
(21506,89,'stage_17'),
(21521,89,'stage_2'),
(21543,89,'stage_11'),
(21568,89,'stage_8'),
(2221,314,'stage_1'),
(2311,314,'stage_11'),
(2352,314,'stage_11'),
(2355,314,'stage_11')
) as stages(id, number, stage)
if object_id('tempdb..#tmp_reassigment') is not null drop table #tmp_reassigment
select id, number, stage
into #tmp_reassigment
from #tmp_values
if object_id('tempdb..#tmp_stages') is not null drop table #tmp_stages
select *
into #tmp_stages
from #tmp_reassigment
where 1=0
DECLARE @id int
, @number int
, @stage varchar(50)
, @id_next int
, @number_next int
, @stage_next varchar(50)
DECLARE stages_cur CURSOR FOR
SELECT id, number, stage FROM #tmp_reassigment
OPEN stages_cur
FETCH NEXT FROM stages_cur INTO @id, @number, @stage
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM stages_cur INTO @id_next, @number_next, @stage_next
IF @stage = 'stage_11' and @stage_next = 'stage_11'
BEGIN
insert into #tmp_stages(id, number, stage)
select @id_next, @number_next, @stage_next
FETCH NEXT FROM stages_cur INTO @id_next, @number_next, @stage_next
END ELSE IF @stage = 'stage_11' BEGIN
insert into #tmp_stages(id, number, stage)
select @id, @number, @stage
END
select @id = @id_next, @number = @number_next, @stage = @stage_next
END
CLOSE stages_cur
DEALLOCATE stages_cur
select * from #tmp_stages
Автор решения: Akina
→ Ссылка
WITH cte1 AS (
SELECT *,
CASE WHEN stage = 'stage_11'
THEN ROW_NUMBER() OVER (PARTITION BY number ORDER BY stage, id)
END rn1,
CASE WHEN stage = 'stage_11'
THEN ROW_NUMBER() OVER (PARTITION BY number ORDER BY id)
END rn2
FROM stages
),
cte2 AS (
SELECT *,
CASE WHEN stage = 'stage_11'
THEN ROW_NUMBER() OVER (PARTITION BY number, rn2-rn1 ORDER BY id)
END rngrp
FROM cte1
)
SELECT id, number, stage, -- *,
CASE WHEN rngrp IS NULL
THEN NULL
WHEN rngrp % 2 = 0
THEN 1
WHEN LEAD(rngrp) OVER (PARTITION BY number ORDER BY id) IS NULL
THEN 1
END cnt
FROM cte2
ORDER BY 2,1
| id | number | stage | cnt |
|---|---|---|---|
| 1 | 15 | stage_1 | null |
| 12 | 15 | stage_11 | null |
| 13 | 15 | stage_11 | 1 |
| 28 | 15 | stage_3 | null |
| 30 | 15 | stage_11 | 1 |
| 35 | 15 | stage_3 | null |
| 77 | 23 | stage_1 | null |
| 98 | 23 | stage_3 | null |
| 101 | 23 | stage_4 | null |
| 1021 | 34 | stage_1 | null |
| 1021 | 34 | stage_9 | null |
| 1031 | 34 | stage_11 | 1 |
| 1035 | 34 | stage_1 | null |
| 1039 | 34 | stage_11 | null |
| 1059 | 34 | stage_11 | 1 |
| 21506 | 89 | stage_17 | null |
| 21521 | 89 | stage_2 | null |
| 21543 | 89 | stage_11 | 1 |
| 21568 | 89 | stage_8 | null |
| 2221 | 314 | stage_1 | null |
| 2311 | 314 | stage_11 | null |
| 2352 | 314 | stage_11 | 1 |
| 2355 | 314 | stage_11 | 1 |