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

Откуда:
Сообщений: 53
[b][/b]Добрый Э - Эх,
Жалко мало времени на обучение T-SQL (интересный и мощный язык).
Итак прежде чем разобраться с кодом пришлось на бумаге рисовать варианты инверсии для bit-данных выяснил конечные варианты для разных цепочек четных, нечетных (хоть и решал через циклы но финиш всегда один и строго детерминирован т.е определен и здесь вы оказались правы здесь циклы не нужны), после этого код стал понятен, тема реально оказалась не простой и интересной (говорили мне не увлекайся bit и NULL)...

Я немного оптимизировал Ваш код для простоты получилось:
with
 t0 (id, bit) as (select * from (values (1,0),(2,1),(3,0),(4,1),(5,0),(6,1),(7,0),(8,1))v(i,j))
,t1 as (select id, [bit]=convert(BIT,bit) from t0)
--
select id, bit
,[~bit]=iif(count(*)over()%2 = 1, (select top 1 bit from t1),
        iif(count(*)over()/2>=id, (select top 1 bit from t1),~(select top 1 bit from t1)))
from t1 order by 1

кстати top 1 можно заменить на "offset 0 rows fetch next 1 rows only"
Выдает:
IDBIT~BIT
100
210
300
410
501
611
701
811


ВОПРОС: Можно ли изначально определять типы данных для CTE (например BIT), чтобы потом не использовать конвертацию (у меня в коде для этого пришлось вводить таблицу t1) с уважением Neosan.

P.S. на счет разрывов цепочек, код постараюсь выложить позже...
22 дек 12, 01:46    [13669769]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Добрый Э - Эх
Guest
Neosan
Я немного оптимизировал Ваш код для простоты получилось:
with
 t0 (id, bit) as (select * from (values (1,0),(2,1),(3,0),(4,1),(5,0),(6,1),(7,0),(8,1))v(i,j))
,t1 as (select id, [bit]=convert(BIT,bit) from t0)
--
select id, bit
,[~bit]=iif(count(*)over()%2 = 1, (select top 1 bit from t1),
        iif(count(*)over()/2>=id, (select top 1 bit from t1),~(select top 1 bit from t1)))
from t1 order by 1
TOP n без ORDER BY - та ещё бомба замедленного действия.
попробуй "перепутать" исходный порядок строк в тестовом наборе данных и посмотри на то, как изменится результат:
with
t0 (id, bit) as (select * from (values (4,1),(5,0),(6,1),(7,0),(1,0),(2,1),(3,0),(8,1))v(i,j))
,t1 as (select id, [bit]=convert(BIT,bit) from t0)
--
select id, bit
,[~bit]=iif(count(*)over()%2 = 1, (select top 1 bit from t1),
        iif(count(*)over()/2>=id, (select top 1 bit from t1),~(select top 1 bit from t1)))
from t1 order by 1
22 дек 12, 14:01    [13670532]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Neosan
Member

Откуда:
Сообщений: 53
Добрый Э - Эх,
действительно top без order by при нарушении исходного порядка строк дает ошибку я этого не знал, правда второй вариант о котором я писал ранее был верный (это замена "top 1" на "offset 0 rows fetch next 1 rows only"), кстати оказывается count(*)over() можно заменить на count(1)over()...
_______________________________
вопрос о изначальном определять типов данных для CTE (например BIT, чтобы потом не использовать конвертацию), я так понимаю типы определяются автоматически и напрямую в шапке нет возможности изменить (в инете нечего не нашел)?
22 дек 12, 22:18    [13671691]     Ответить | Цитировать Сообщить модератору
 Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
Добрый Э - Эх
Guest
Neosan
второй вариант о котором я писал ранее был верный (это замена "top 1" на "offset 0 rows fetch next 1 rows only"),
А ничего, что для работы второго варианта требуется обязательное наличие фразы ORDER BY, на отсутствие которого тебе и было указано в первом варианте (с TOP N) ?
B.O.L.
Ограничения при использовании OFFSET-FETCH
  • Для использования предложения OFFSET и FETCH требуется ORDER BY.
  • С FETCH нужно обязательно использовать предложение OFFSET. Нельзя использовать ORDER BY … FETCH.
  • TOP нельзя объединять с OFFSET и FETCH в одном и том же выражении запроса.
  • Выражение подсчета строк OFFSET/FETCH может быть арифметическим, константным или выражением с параметрами, которое возвращает целое значение. Выражение подсчета строк не поддерживает скалярные вложенные запросы.
  • 24 дек 12, 06:01    [13674790]     Ответить | Цитировать Сообщить модератору
     Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
    Neosan
    Member

    Откуда:
    Сообщений: 53
    Итак резюмирую тему которая должна была называться: "инверсия знака при <> с соседними для периодических bit-цепочек с разрывами". Как всегда большой респект "Доброму Э - Эх" за показанный код 13662630 (ценный изюм :) и потраченное время, без него этого код скорей всего не было!
    + код
    --ОБУЧЕНИЕ: инверсия знака при <> с соседними для периодических bit-цепочек с разрывом (РПЦ)
    --выделение (маркировка) и нумерация групп "неразрывных периодических bit-цепочек" (НПЦ)
    with
    t0 (bit) as (select * from (values (1),(0),(1),(0),(0),(0),(1),(0),(0),(1),(1),(0),(1),(0))v(a))--тестовые данные
    --
    ,t1 as (select [id]=row_number()over(order by (select 1)), [bit]=convert(BIT,bit) from t0)--приводим таблицу к нормальному виду
    ,t2 as (select * ,[mark]=iif(bit= lag (bit,1,bit)over(order by id) AND bit=~lead(bit,1)over(order by id) AND bit= lead(bit,2)over(order by id), bit--начало НПЦ
                            ,iif(bit=~lag (bit,1)over(order by id) AND bit= lag (bit,2)over(order by id)--маркер (верх)
                              OR bit=~lag (bit,1)over(order by id) AND bit=~lead(bit,1)over(order by id)--маркер (центр)
                              OR bit=~lead(bit,1)over(order by id) AND bit= lead(bit,2)over(order by id), NULL, bit)) from t1)--маркер (низ)
    ,t3 as (select * ,[numOut]=count(mark)over(order by id) from t2)--нумерация (внешняя)
    ,t4 as (select * ,[numIn] =row_number()over(partition by numOut order by id)--нумерация (внутри)
                     ,[numGrp]=count(1)over(partition by numOut)--для расчета четных и нечетных групп
                     ,[topUp] =convert(bit, max(convert(tinyint, mark))over(partition by numOut order by id)) from t3)
    --
    select id, bit, [Target]=iif(numGrp%2=1, topUp, iif(numGrp/2>=numIn, topUp, ~topUp))
    from t4 order by id
    

    C наилучшими пожеланиями и с наступающим Новым 2013!!!
    24 дек 12, 18:52    [13679103]     Ответить | Цитировать Сообщить модератору
     Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
    Добрый Э - Эх
    Guest
    Neosan, тут придумал, как прикрутить рекурсию к твоей задаче...
    Суть таково: в качестве одного входного параметра запрашиваем от пользователя стартовое значение бит-поля (0 или 1), в качестве второго - длину бит-последовательности. При помощи рекурсивного СТЕ генерируем битовый вектор нужно длины. Далее - делаем "инверсию" полученного вектора
    24 дек 12, 19:08    [13679193]     Ответить | Цитировать Сообщить модератору
     Re: ОБУЧЕНИЕ "Использование CTE-рекурсии для инверсии BIT-значения"  [new]
    Neosan
    Member

    Откуда:
    Сообщений: 53
    Добрый Э - Эх,
    Интересное решение, я так понимаю, что "вектор" это как перевернутый столбец записанный в строку, который потом обратно вписываем, куда надо? аналогично функции PIVOT - UNPIVOT... если сможешь напиши простейший пример, а я попробую понять (с данным примером "вектор" не разу не встречался),
    с уважением neosan...
    25 дек 12, 08:57    [13680660]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: Ctrl  назад   1 [2]      все
    Все форумы / Microsoft SQL Server Ответить