Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Свертка последовательностей  [new]
опять свёртка
Guest
Помогите, пожалуйста.

Есть такой набор
declare @temptable table(Group1 int,Group2 int,N int,Val int )
INSERT @temptable(Group1,Group2,N,Val)
VALUES
	(1, 0, 1, 5503 ), 
	(1, 0, 2, 5504 ), 
	(1, 0, 3, 5505 ), 
	(1, 0, 4, 5506 ), 
	(1, 0, 5, 5507 ), 
	(1, 0, 6, 5508 ), 
	(1, 3, 7, 5509 ), 
	(1, 3, 8, 5510 ), 
	(1, 0, 9, 5511 ), 
	(1, 0, 10, 5512 ), 
	(2, 0, 11, 2655 ), 
	(2, 0, 12, 2656 ), 
	(2, 0, 13, 2657 ), 
	(2, 0, 14, 2658 ), 
	(4, 0, 15, 73 ), 
	(4, 0, 16, 74 ), 
	(4, 0, 17, 75 ), 
	(4, 0, 18, 76 ), 
	(4, 0, 19, 77 ), 
	(4, 0, 20, 78 ), 
	(4, 4, 21, 79 ), 
	(4, 11, 22, 80 ), 
	(1, 0, 23, 5513 ), 
	(1, 0, 24, 5514 ), 
	(1, 0, 25, 5515 ), 
	(1, 0, 26, 5516 ), 
	(1, 0, 27, 5517 ), 
	(1, 0, 28, 5518 ), 
	(3, 0, 29, 5 ), 
	(3, 0, 30, 6 ), 
	(3, 0, 31, 1 ), 
	(3, 0, 32, 2 ), 
	(3, 0, 33, 3 ), 
	(3, 0, 34, 4 ), 
	(3, 0, 35, 9 ), 
	(3, 0, 36, 10 ), 
	(3, 0, 37, 7 ), 
	(3, 0, 38, 8 ), 
	(3, 0, 39, 11 ), 
	(3, 0, 40, 12 ), 
	(4, 0, 41, 83 ), 
	(4, 0, 42, 84 ), 
	(3, 0, 43, 13 ), 
	(3, 0, 44, 14 ), 
	(4, 0, 45, 81 ), 
	(4, 0, 46, 82 ), 
	(4, 0, 47, 89 ), 
	(4, 0, 48, 90 ), 
	(4, 0, 49, 85 ), 
	(4, 0, 50, 86 ), 
	(4, 0, 51, 87 ), 
	(4, 0, 52, 88 )
	;


Хотелось бы на выходе получить следующее
Group1 Group2 ValFrom ValTo
1 0 5503 5508
1 3 5509 5510
1 0 5511 5512
2 0 2655 2658
4 0 73 78
4 4 79 79
4 11 80 80
1 0 5513 5518
3 0 5 6
3 0 1 4
3 0 9 10
3 0 7 8
3 0 11 12
4 0 83 84
3 0 13 14
4 0 81 82
4 0 89 90
4 0 85 88

+

SELECT *
FROM (VALUES
		(1,0,5503,5508),
		(1,3,5509,5510),
		(1,0,5511,5512),
		(2,0,2655,2658),
		(4,0,73,78),
		(4,4,79,79),
		(4,11,80,80),
		(1,0,5513,5518),
		(3,0,5,6),
		(3,0,1,4),
		(3,0,9,10),
		(3,0,7,8),
		(3,0,11,12),
		(4,0,83,84),
		(3,0,13,14),
		(4,0,81,82),
		(4,0,89,90),
		(4,0,85,88)
	) as q(Group1,Group2,ValFrom,ValTo)
	;



Сумел получить
SELECT q.Group1,
		q.Group2,
		min(q.Val) as ValFrom,
		max(q.Val) as ValTo
	FROM (
			SELECT q1.Group1,
				q1.Group2,
				q1.N,
				q1.Val,
				q1.Val - row_number() OVER(PARTITION BY q1.Group1,q1.Group2 ORDER BY q1.Val) as gr1,
				row_number() OVER(PARTITION BY q1.Group1,q1.Group2 ORDER BY q1.Val) - row_number() OVER(PARTITION BY q1.Group1,q1.Group2 ORDER BY q1.N) as gr2,
				q1.N - row_number() OVER(PARTITION BY q1.Group1,q1.Group2 ORDER BY q1.N) as gr3
			FROM @temptable as q1
			) as q
	GROUP BY q.Group1,q.Group2,q.gr1,q.gr2,q.gr3
	ORDER BY min(q.N);

но этот селект неправильно склеивает интервалы с N в диапазоне 31-38 и 45-52
20 июл 16, 20:03    [19432824]     Ответить | Цитировать Сообщить модератору
 Re: Свертка последовательностей  [new]
опять свёртка
Guest
P.S. версия 2008R2
20 июл 16, 20:05    [19432830]     Ответить | Цитировать Сообщить модератору
 Re: Свертка последовательностей  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
Это для версий 2012 и выше:

select group1,group2,min(val) Valfrom,max(val) Valto
from(
select *
,sum(num) over(order by n) as s
from(
select *
,case when group1 <> lag(group1) over(order by n) or
			group2 <> lag(group2) over(order by n) or
			abs(val - lag(val) over(order by n)) > 1
			then 1
			else 0 end num
from @temptable
) a
) b
group by group1,group2,s
20 июл 16, 21:40    [19433161]     Ответить | Цитировать Сообщить модератору
 Re: Свертка последовательностей  [new]
Добрый Э - Эх
Guest
опять свёртка,

нужно было чуть хитрее:
--
-- Тестовые данные:
declare @temptable table(Group1 int,Group2 int,N int,Val int )
INSERT @temptable(Group1,Group2,N,Val)
VALUES
	(1, 0, 1, 5503 ), 
	(1, 0, 2, 5504 ), 
	(1, 0, 3, 5505 ), 
	(1, 0, 4, 5506 ), 
	(1, 0, 5, 5507 ), 
	(1, 0, 6, 5508 ), 
	(1, 3, 7, 5509 ), 
	(1, 3, 8, 5510 ), 
	(1, 0, 9, 5511 ), 
	(1, 0, 10, 5512 ), 
	(2, 0, 11, 2655 ), 
	(2, 0, 12, 2656 ), 
	(2, 0, 13, 2657 ), 
	(2, 0, 14, 2658 ), 
	(4, 0, 15, 73 ), 
	(4, 0, 16, 74 ), 
	(4, 0, 17, 75 ), 
	(4, 0, 18, 76 ), 
	(4, 0, 19, 77 ), 
	(4, 0, 20, 78 ), 
	(4, 4, 21, 79 ), 
	(4, 11, 22, 80 ), 
	(1, 0, 23, 5513 ), 
	(1, 0, 24, 5514 ), 
	(1, 0, 25, 5515 ), 
	(1, 0, 26, 5516 ), 
	(1, 0, 27, 5517 ), 
	(1, 0, 28, 5518 ), 
	(3, 0, 29, 5 ), 
	(3, 0, 30, 6 ), 
	(3, 0, 31, 1 ), 
	(3, 0, 32, 2 ), 
	(3, 0, 33, 3 ), 
	(3, 0, 34, 4 ), 
	(3, 0, 35, 9 ), 
	(3, 0, 36, 10 ), 
	(3, 0, 37, 7 ), 
	(3, 0, 38, 8 ), 
	(3, 0, 39, 11 ), 
	(3, 0, 40, 12 ), 
	(4, 0, 41, 83 ), 
	(4, 0, 42, 84 ), 
	(3, 0, 43, 13 ), 
	(3, 0, 44, 14 ), 
	(4, 0, 45, 81 ), 
	(4, 0, 46, 82 ), 
	(4, 0, 47, 89 ), 
	(4, 0, 48, 90 ), 
	(4, 0, 49, 85 ), 
	(4, 0, 50, 86 ), 
	(4, 0, 51, 87 ), 
	(4, 0, 52, 88 )
	;
--
-- Основной запрос:
select Group1, Group2
     , MIN(n) as beg_n, MAX(n) as end_n
     , MIN(val) as beg_val, MAX(val) as end_val
  from (
         select *
              , ROW_NUMBER() over(PARTITION by group1, group2, group3 order by n) - val as group4
           from (
                  select *
                       , n - ROW_NUMBER() over(PARTITION by group1, group2 order by val) as group3
                    from @temptable
                ) v0
       )v1
 group by Group1, Group2, group3, group4
 order by MIN(n);

З.Ы.
И работать будет от версии 2005 и выше...
21 июл 16, 05:54    [19433933]     Ответить | Цитировать Сообщить модератору
 Re: Свертка последовательностей  [new]
опять свёртка
Guest
Добрый Э - Эх,

огромное спасибо, возвращает что надо, буду тестить на других входных наборах.

Сам до еще одного уровня вложенности не дошел, всё пытался ограничиться одним :(
21 июл 16, 06:05    [19433937]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить