Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Neosan
Member

Откуда:
Сообщений: 53
В общем задача на BIT-столбцом: Инвертировать значение если вокруг находятся значения с другим знаком (010 или 101) на выходе получить 000 или 111.
При решении этой простой задачи столкнулся с тем, что она решается в несколько циклов (не за один проход), можно ли ее решить при помощи рекурсии CTE (раньше рекурсию не использовал), есть набросок не работающего кода,
--ОБУЧЕНИЕ "Инверсия BIT-значения окруженного противоположенными BIT-значениями (т.е. комбинации 010 или 101) через ЦИКЛ-рекурсию получить 000 или 111
;WITH T0 (id, bitA) AS--Тестовый набор данных
(SELECT * FROM (VALUES (1,1),(2,0),(3,1),(4,0),(5,1),(6,0),(7,1),(8,0))
v(a, b))
--
--выполнять инверсию если вокруг BIT-значения распологаются BIT-значениями с другим знаком
,T1 AS (SELECT * ,[bitB]=IIF(bitA<>LAG(bitA,1)OVER(ORDER BY id) AND bitA<>LEAD(bitA,1)OVER(ORDER BY id),~bitA,bitA)
UNION ALL 
SELECT --здесь должно быть описание условия рекурсивной части
FROM T0
--условие выполнения цикла - пока есть в солбце комбинация "010" или "101" выполнять
WHERE bitB<>LAG(bitB,1)OVER(ORDER BY id) AND bitB<>LEAD(bitB,1)OVER(ORDER BY id))
--
SELECT * FROM T1

если кто знает просьба помочь разобраться.

К сообщению приложен файл. Размер - 2Kb
20 дек 12, 14:57    [13660199]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
сиквел сервер у вас какой?
20 дек 12, 15:25    [13660442]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
если 12ый то можно смотреть на lag и lead
20 дек 12, 15:35    [13660546]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Дедушка
если 12ый то можно смотреть на lag и lead
Дык у него и так IIF и LEAD с LAGом! :))
20 дек 12, 16:24    [13660978]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
qwrqwr
Member

Откуда: Msk
Сообщений: 1684
Neosan
Инвертировать значение если вокруг находятся значения с другим знаком (010 или 101) на выходе получить 000 или 111.

Для исходной последовательности:
in
1
0
1
0

Должно получится на выходе
out1
1
1
1
0

или
out2
1
0
0
0
??

Вам надо еще поработать над ТЗ. :-)
20 дек 12, 16:55    [13661225]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Neosan
Member

Откуда:
Сообщений: 53
Да точно 12 версия MSSQL, запрос больше рассчитан на цикл WHILE, но хотелось бы научится работать с циклом через рекурсию...
20 дек 12, 16:55    [13661226]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Neosan
Member

Откуда:
Сообщений: 53
qwrqwr,
в задаче приоритет идет по id т.е цепочка вида 0101 дает 0001, в моем коде это определено через оконную функцию LAG и LEAD OVER (ORDER BY id)...
20 дек 12, 17:00    [13661273]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
qwrqwr
Member

Откуда: Msk
Сообщений: 1684
Neosan
qwrqwr,
в задаче приоритет идет по id т.е цепочка вида 0101 дает 0001, в моем коде это определено через оконную функцию LAG и LEAD OVER (ORDER BY id)...


1. Определить значения, не равные последующему в сортировке по id.
2. Определить протяженность и начало непрерывного интервала значений, удовлетворяющих п.1.
3. Если в таком интервале 2 или более значений - заменить все значения в интервале первым значением для этого интервала.

Решается оконными функциями, рекурсия и тем более процедурные циклы не нужны.
20 дек 12, 17:15    [13661399]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
iap
Дедушка
если 12ый то можно смотреть на lag и lead
Дык у него и так IIF и LEAD с LAGом! :))

действительно был не внимателен
20 дек 12, 17:18    [13661413]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
qwrqwr
Member

Откуда: Msk
Сообщений: 1684
qwrqwr
3. Если в таком интервале 2 или более значений - заменить все значения в интервале первым значением для этого интервала.

Зачеркнутое не нужно. ;-)
20 дек 12, 17:21    [13661431]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Neosan
Member

Откуда:
Сообщений: 53
qwrqwr,
Смысл задачи: Инвертировать значение если вокруг находятся значения с другим знаком т.е. сравнение идет не только с вышестоящим но и нижестоящим значением одновременно в коде это определенно как
IIF(bitA<>LAG(bitA,1)OVER(ORDER BY id) AND bitA<>LEAD(bitA,1)OVER(ORDER BY id),~bitA,bitA)

так же в этой задачи BIT-значения идут сменяя друг друга 0101010101010 т.е. нет повторяющихся цепочек типа 00... или 11..,
после Рекурсии вместо например 10101010 должно остаться типа 11110000 для данного решения нужно 3-цикла:
Данные1-цикл2-цикл3-цикл
1111
0111
1011
0101
1010
0100
1000
0000

для этой задачи интересно решение именно через рекурсию...
20 дек 12, 17:39    [13661561]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
qwrqwr
Member

Откуда: Msk
Сообщений: 1684
Neosan
после Рекурсии вместо например 10101010 должно остаться типа 11110000


В прошлом посте вы утверждали, что последовательность 10101010 должна привестись к виду 11111110.

Это было бы хотя бы объяснимо, пусть при дополнительных уточнениях, типа направления просмотра последовательности.

Всего хорошего.
20 дек 12, 17:43    [13661585]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Neosan
Member

Откуда:
Сообщений: 53
внимательно читайте шапку задачи, там все описано,
повторюсь смысл задачи: если между одинаковых 2-значений находится иное логическое значение то оно инвертируется.
20 дек 12, 17:59    [13661654]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Добрый Э - Эх
Guest
Neosan,

Задача решается за один проход. Рекурсию, конечно, в академических целях прикрутить можно, но тут она будет сродни забиванию гвоздей микроскопом. В целом же, как уже правильно отметили, задача не на рекурсию, а на итерацию...
20 дек 12, 19:12    [13662016]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх
В целом же, как уже правильно отметили, задача не на рекурсию, а на итерацию...
Упс... Это же ты сам и высказал совершенно верную мысль, что задача на цикл while :)
20 дек 12, 19:13    [13662027]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Neosan
Member

Откуда:
Сообщений: 53
Добрый Э - Эх,
Добрый вечер,
к сожалению циклами не приходилось пользоваться, если приведете краткий пример WHILE в CTE буду благодарен,
я так понимаю что цикл переписывает столбец через UPDATE SET или MERGE when matched...
20 дек 12, 19:46    [13662229]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Добрый Э - Эх
Guest
Рекурсия здесь не к месту.

+ < Простой линейный запрос и никакой рекурсии...
with
  t(id, bit) as
    (
      select * 
        from (
               values (1,1)
                     ,(2,0)
                     ,(3,1)
                     ,(4,0)
                     ,(5,1)
                     ,(6,0)
                     ,(7,1)
                     ,(8,0)
             ) v(i,j)
    )
--
--
select all
       v.id
     , v.bit
     , case 
         when total_cnt%2 = 1 then lag(bit,rn_asc - 1,bit) over(order by id)
         else case
                when rn_asc <= total_cnt/2 then lag(bit,rn_asc - 1,bit) over(order by id)
                else lag(bit,rn_desc - 1,bit) over(order by id desc)
              end
       end as inversed_bit
 from (
select t.*
     , row_number() over(order by id) rn_asc
     , row_number() over(order by id desc) rn_desc
     , count(*) over() as total_cnt
  from t) v
order by id

on-line проверка на sqlfiddle.com

З.Ы.
Если получение первого / последнего значения в группе отсортированных по ID строк заменить на трюковый метод (я тебе его уже как-то показывал), то запрос станет вообще простым до безобразия, без вложенных запросов.
20 дек 12, 20:13    [13662357]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Добрый Э - Эх
Guest
+ < Слегка упрощенный вариант...
select all
       v.id
     , v.bit
     , case 
         when total_cnt%2 = 1 then lag(bit,rn_asc - 1,bit) over(order by id)
         else case
                when rn_asc <= total_cnt/2 then lag(bit,rn_asc - 1,bit) over(order by id)
                else 1 - lag(bit,rn_asc - 1,bit) over(order by id)
              end
       end as inversed_bit
 from (
        select t.*
             , row_number() over(order by id) rn_asc
             , count(*) over() as total_cnt
          from t
       ) v
 order by id
+ < Однопроходный вариант...
select t.*
     , case 
         when count(*) over()%2 = 1
           then cast(substring(min(right(replicate('0',39)+cast(id as varchar),39)
                     +cast(bit as varchar)) over(),40,100) as int)
         else case
                when row_number() over(order by id) <= count(*) over()/2 
                  then cast(substring(min(right(replicate('0',39)+cast(id as varchar),39)
                     +cast(bit as varchar)) over(),40,100) as int)
                else 1-cast(substring(min(right(replicate('0',39)+cast(id as varchar),39)
                     +cast(bit as varchar)) over(),40,100) as int)
              end
       end as inversed_bit  
, cast(substring(min(right(replicate('0',39)+cast(id as varchar),39)
                     +cast(bit as varchar)) over(),40,100) as int) x_bit
  from t
 order by id
20 дек 12, 21:22    [13662630]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Neosan
Member

Откуда:
Сообщений: 53
Добрый Э - Эх,
Спасибо за пример :), начал изучать код,
задача: "Если между одинаковых 2-значений находится иное логическое значение то оно инвертируется".
При изменении входных данных например на: "10100101" на выходе должно "11000011", код выдает "11111111",
в таблице понятней:
ДанныеДолжноПолучилось
111
011
101
001
001
101
011
111

думаю все таки без цикла никак, хотя могу ошибаться,
разбираюсь с вашим кодом...
20 дек 12, 22:20    [13662822]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Neosan
Member

Откуда:
Сообщений: 53
Добрый Э - Эх,
кстати что значит значек "%2" в названии столбца и over()%2, в инете не нашел...
20 дек 12, 22:51    [13662943]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Добрый Э - Эх
Guest
Neosan
При изменении входных данных например на: "10100101" на выходе должно "11000011", код выдает "11111111",
В первоначальной постановке задачи утверждалось иное:
Neosan
так же в этой задачи BIT-значения идут сменяя друг друга 0101010101010 т.е. нет повторяющихся цепочек типа 00... или 11..
21 дек 12, 05:42    [13663561]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Добрый Э - Эх
Guest
Neosan
кстати что значит значек "%2" в названии столбца и over()%2, в инете не нашел...

R.T.F.M. - остаток от целочисленного деления...
21 дек 12, 05:48    [13663565]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
kurai
Member

Откуда:
Сообщений: 127
Добрый Э - Эх
Neosan
При изменении входных данных например на: "10100101" на выходе должно "11000011", код выдает "11111111",
В первоначальной постановке задачи утверждалось иное:
Neosan
так же в этой задачи BIT-значения идут сменяя друг друга 0101010101010 т.е. нет повторяющихся цепочек типа 00... или 11..


При такой постановке задачи тут вообще ни циклы не рекурсия не нужны и все банально сводится к case )))))
CREATE TABLE #t(id int, b bit)
INSERT INTO #t
VALUES  (1,1),
		(2,0),
		(3,1),
		(4,0),
		(5,1),
		(6,0)
SELECT * FROM #t
UPDATE #t
SET b = CASE WHEN (SELECT TOP 1 b FROM #t ORDER BY id DESC) = (SELECT TOP 1 b FROM #t ORDER BY id) THEN (SELECT TOP 1 b FROM #t ORDER BY id)
			 WHEN (SELECT TOP 1 b FROM #t ORDER BY id) = CAST (0 AS BIT) AND id <= (SELECT COUNT(*)/2 FROM #t) 
			 OR   (SELECT TOP 1 b FROM #t ORDER BY id) = CAST (1 AS BIT) AND id > (SELECT COUNT(*)/2 FROM #t) THEN 0
			 WHEN (SELECT TOP 1 b FROM #t ORDER BY id) = CAST (0 AS BIT) AND id > (SELECT COUNT(*)/2 FROM #t) 
			 OR   (SELECT TOP 1 b FROM #t ORDER BY id) = CAST (1 AS BIT) AND id <= (SELECT COUNT(*)/2 FROM #t) THEN 1
			 
		END
SELECT * FROM #t
DROP TABLE #t
21 дек 12, 11:05    [13664522]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Добрый Э - Эх
Guest
kurai,

спойлер раскрыть не догадался?
Там тоже один линейный запрос, без рекурсий и многократных елозаний по исходным данным.
21 дек 12, 11:11    [13664559]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
kurai
Member

Откуда:
Сообщений: 127
Добрый Э - Эх,

Не смотрел, каюсь) однако вдруг кому-то понадобится сие без использования row_number CTE и прочего, позволяющих избежать "многократного елозонья"
так что считаю вариант отличающимся, имеющим правильное решение, а следовательно, и имеющим право быть
21 дек 12, 11:29    [13664679]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить