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

Откуда: KZ
Сообщений: 41
 		Всем привет товарищи!
Хочу выразить спасибо тем, кто решил помочь мне в решении данной задачи
А именно я пытаюсь разобраться все в том же CTE,
я знаю, желание убивать у посвященных людей на этом форуме только увеличивается! ))

меня озадачили такой фишкой с CTE, имеется таблица - с индексом и строкой
которая "якобы"
показывает бинарную последовательность 0 или 1

+
with binaries as 
(
	SELECT 1 as id, '1101101' as bin
	UNION 
	SELECT 2 as id, '0111011' as bin
),


так вот, суть задачи: Провести "инверсию" в строке посимвольно, т.е.
следующий символ это инверсия от предыдущего если этот символ равен 1.
Если символ равен 0, то копируем предыдущий
Например:
'1101101' ->
первый равен 1 - первая позиция - не трогаем (1)
второй 1 - равен инверсией от первого (10)
третий 0 - копируем предыдущий (100)
4 - 1 инверсия 3-ого = (1001)
5 - 1 инверсия 4 -ого = (10010)
6 - 0 копируем пред (100100)
7 - 1 инверс 6 ого (1001001)

казалось бы вообще фигня
я понял как работает CTE

[url=]https://www.sql.ru/forum/1295380/sql-i-matrica-realno-li-reshit-takuu-zadachu[/url]
[url=]https://www.sql.ru/forum/1295471/i-opyat-vopros-pro-cte[/url]


пишу вот такой запрос:

+
T(id,str_in, i, s_str) AS
(
		
 SELECT id, bin as str_in, 0 as i, '' as s_out	From binaries
 UNION ALL
 SELECT id,str_in, i + 1, case 
		when i=0  then  SUBSTRING(str_in, i, 1) --если это первый символ - не трогаем
		when i>0 and SUBSTRING(str_in, i, 1) = '1' and SUBSTRING(str_in, i-1, 1) = '0' then '1' -- если предыдущий равен 0 то 1
		when i>0 and SUBSTRING(str_in, i, 1) = '1' and SUBSTRING(str_in, i-1, 1) = '1' then '0' -- наоборот
		when i>0 and SUBSTRING(str_in, i, 1) = '0' then SUBSTRING(str_in, i-1, 1) -- если равен 0, то копируем
		end 
		FROM T 
	WHERE i<=LEN(str_in)
)

select id, stuff((select s_str 
            FROM T 
			Where binaries.id = t.id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') ,1,0,'') str_out FROM binaries



но тут то уже мой воспаленный мозг понял, что данные берущие из binaries
не меняются в CTE. В этом и есть загвоздка, как придумать некий "буфер"
и ложить туда данные для выполнения промежуточных операции со строками?

или вообще возможно ли такое в принципе?

я пробую многократно cross joinить с предусловиями... Но и это мне кажется обречено на позорное фиаско)

PS: Решение только при помощи with, select, CTE

Или этими средствами можно ли выполнить как то итерацию по строкам c учетом изменении "на лету"?
11 июн 18, 10:34    [21484368]     Ответить | Цитировать Сообщить модератору
 Re: CTE и промежуточные результаты выполнения  [new]
aleks222
Member

Откуда:
Сообщений: 1299
 		Это нереляционная задача.

1. Разобрать строку бит в столбец бит и порядковый номер.
2. Запросом с LEAD сгенерировать новый столбец бит.
3. Сложить все обратно в бинари.

А так-то изложенный алгоритм = 
b(0) = 0
for i:=1 to N
  b(i) = b(i) or b(i-1)
11 июн 18, 11:40    [21484430]     Ответить | Цитировать Сообщить модератору
 Re: CTE и промежуточные результаты выполнения  [new]
aleks222
Member

Откуда:
Сообщений: 1299
 		пардон, не or, а xor.
11 июн 18, 11:42    [21484434]     Ответить | Цитировать Сообщить модератору
 Re: CTE и промежуточные результаты выполнения  [new]
swd1986
Member

Откуда: KZ
Сообщений: 41
aleks222
Это нереляционная задача.

1. Разобрать строку бит в столбец бит и порядковый номер.
2. Запросом с LEAD сгенерировать новый столбец бит.
3. Сложить все обратно в бинари.

А так-то изложенный алгоритм = 
b(0) = 0
for i:=1 to N
  b(i) = b(i) or b(i-1)



Спасибо! LAG и LEAD, это классная идея! Попробую сейчас....
11 июн 18, 11:54    [21484455]     Ответить | Цитировать Сообщить модератору
 Re: CTE и промежуточные результаты выполнения  [new]
invm
Member

Откуда: Москва
Сообщений: 9693
 		swd1986,

with binaries as 
(
	SELECT 1 as id, '1101101' as bin
	UNION 
	SELECT 2 as id, '0111011' as bin
),
t as
(
 select
  1 as p, id, bin,
  left(bin, 1) as c from binaries
 union all
 select
  t.p + 1, t.id, t.bin,
  case
   when a.c = '0' then t.c
   when a.c = '1' and t.c = '0' then '1'
   when a.c = '1' and t.c = '1' then '0'
  end
 from
  t join
  binaries b on b.id = t.id cross apply
  (select substring(b.bin, t.p + 1, 1)) a(c)
 where
  t.p < len(b.bin)
)
select
 b.*, a.x.value('.', 'varchar(30)')
from
 binaries b cross apply
 (
  select
   c as [*]
  from
   t
  where
   id = b.id
  order by
   p
  for xml path(''), type
 ) a(x);
11 июн 18, 12:05    [21484472]     Ответить | Цитировать Сообщить модератору
 Re: CTE и промежуточные результаты выполнения  [new]
swd1986
Member

Откуда: KZ
Сообщений: 41
+
invm
swd1986,

with binaries as 
(
	SELECT 1 as id, '1101101' as bin
	UNION 
	SELECT 2 as id, '0111011' as bin
),
t as
(
 select
  1 as p, id, bin,
  left(bin, 1) as c from binaries
 union all
 select
  t.p + 1, t.id, t.bin,
  case
   when a.c = '0' then t.c
   when a.c = '1' and t.c = '0' then '1'
   when a.c = '1' and t.c = '1' then '0'
  end
 from
  t join
  binaries b on b.id = t.id cross apply
  (select substring(b.bin, t.p + 1, 1)) a(c)
 where
  t.p < len(b.bin)
)
select
 b.*, a.x.value('.', 'varchar(30)')
from
 binaries b cross apply
 (
  select
   c as [*]
  from
   t
  where
   id = b.id
  order by
   p
  for xml path(''), type
 ) a(x);


invm,

Большущее спасибо! Это как раз та ситуация, когда ученик идет в гору,
на вершине которого сидит мастер. Даже не мастер, а учитель.
Вообщем то, для учителя выгода получить удовлетворение от безвозмездно
переданных знании ученику. А ученик, знания которого в руках, дает учителю
признание. Ну и осознание ученику, какой же он "д****б" ))

я честно ждал такого запроса, правильнее было мучиться, париться, но добиться
своего или потерпеть фи.sql.аско. А тут и разобрать код, и понять как работает, и
главное - сэкономленное время. На блюдечке в голубой каемочке...

Еще раз спасибо invm и сайту)
11 июн 18, 12:31    [21484498]     Ответить | Цитировать Сообщить модератору
 Re: CTE и промежуточные результаты выполнения  [new]
aleks222
Member

Откуда:
Сообщений: 1299
invm
swd1986,

with binaries as 
(
	SELECT 1 as id, '1101101' as bin
	UNION 
	SELECT 2 as id, '0111011' as bin
),
t as
(
 select
  1 as p, id, bin,
  left(bin, 1) as c from binaries
 union all
 select
  t.p + 1, t.id, t.bin,
  case
   when a.c = '0' then t.c
   when a.c = '1' and t.c = '0' then '1'
   when a.c = '1' and t.c = '1' then '0'
  end
 from
  t join
  binaries b on b.id = t.id cross apply
  (select substring(b.bin, t.p + 1, 1)) a(c)
 where
  t.p < len(b.bin)
)
select
 b.*, a.x.value('.', 'varchar(30)')
from
 binaries b cross apply
 (
  select
   c as [*]
  from
   t
  where
   id = b.id
  order by
   p
  for xml path(''), type
 ) a(x);

Очень забавно городить целый case для банального xor.

Ваще то, если напрячь фантазию:

 new_b(i) = old_b(i) xor new_b(i-1)

 new_b(i) = old_b(i) xor ( old_b(i-1) xor new_b(i-2) )
...

 new_b(i) = old_b(i) xor old_b(i-1) xor ... xor old_b(1)


и задача становится почти реляционной, т.е. новое значение бита может быть рассчитано исключительно из текущих значений бит.
11 июн 18, 12:37    [21484503]     Ответить | Цитировать Сообщить модератору
 Re: CTE и промежуточные результаты выполнения  [new]
swd1986
Member

Откуда: KZ
Сообщений: 41
aleks222
+
invm
swd1986,

with binaries as 
(
	SELECT 1 as id, '1101101' as bin
	UNION 
	SELECT 2 as id, '0111011' as bin
),
t as
(
 select
  1 as p, id, bin,
  left(bin, 1) as c from binaries
 union all
 select
  t.p + 1, t.id, t.bin,
  case
   when a.c = '0' then t.c
   when a.c = '1' and t.c = '0' then '1'
   when a.c = '1' and t.c = '1' then '0'
  end
 from
  t join
  binaries b on b.id = t.id cross apply
  (select substring(b.bin, t.p + 1, 1)) a(c)
 where
  t.p < len(b.bin)
)
select
 b.*, a.x.value('.', 'varchar(30)')
from
 binaries b cross apply
 (
  select
   c as [*]
  from
   t
  where
   id = b.id
  order by
   p
  for xml path(''), type
 ) a(x);

Очень забавно городить целый case для банального xor.

Ваще то, если напрячь фантазию:

 new_b(i) = old_b(i) xor new_b(i-1)

 new_b(i) = old_b(i) xor ( old_b(i-1) xor new_b(i-2) )
...

 new_b(i) = old_b(i) xor old_b(i-1) xor ... xor old_b(1)


и задача становится почти реляционной, т.е. новое значение бита может быть рассчитано исключительно из текущих значений бит.


Спасибо! Но invm показал запрос, который может применяться не только к битовым значениям. Это первое, второе - это как раз таки тот случай "как меняются данные на лету" средствами CTE (один из вариантов решения)... Порой мне кажется человек, который дал мне задачу и invm - это один и тот же чел )
11 июн 18, 12:44    [21484511]     Ответить | Цитировать Сообщить модератору
 Re: CTE и промежуточные результаты выполнения  [new]
swd1986
Member

Откуда: KZ
Сообщений: 41
 		swd1986,

PS: Тапак еще выручал, он тоже умеет отжигать)
11 июн 18, 12:45    [21484513]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить