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

Откуда:
Сообщений: 53
Приветствую,
тормознул на простом запросе по нумерации (ранжиру) BIT-столбца, пример таблицы:
Задача: пронумеровать строки-NULL (начало нумерации от "1" или "0")
и получить новый столбец "NEW"

idid_bitNEW
110
2NULL1
3NULL2
4NULL3
500
6NULL1
7NULL2
810
9NULL1
10NULL2
1100
12NULL1
13NULL2

Использовал ранжир DENSE_RANK но он нумерует без обнуления
--временная таблица с данными необходимо получить новый столбец "NEW"
CREATE TABLE #boo(id SMALLINT IDENTITY(1,1), id_bit BIT);
(1),(NULL),(NULL),(NULL),(0),(NULL),(NULL),(1),(NULL),(NULL),(0),(NULL),(NULL)

--формируем новый столбец NEW (нумерация NULL)
SELECT * 
,DENSE_RANK () OVER (ORDER BY aa) as NEW 
FROM #boo
ORDER BY id

DROP TABLE #boo

Думаю нужно использовать вложенный запрос, если кто знаком с данной темой просьба откликнутся (p.s. MS SQL 2012)...

К сообщению приложен файл. Размер - 4Kb
12 ноя 12, 15:43    [13459578]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
WITH CTE AS
(
 SELECT GN=ROW_NUMBER()OVER(ORDER BY id)-ROW_NUMBER()OVER(PARTITION BY id_bit ORDER BY id),*
 FROM #boo
)
SELECT id,id_bit,[NEW]=ISNULL(0*id_bit,ROW_NUMBER()OVER(PARTITION BY GN,id_bit ORDER BY id))
FROM CTE
ORDER BY id;
12 ноя 12, 16:02    [13459751]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
Neosan
Member

Откуда:
Сообщений: 53
Да красивый код получился, с рекурсией еще я не работал, спасибо :)
А то мне здесь прислали код на пол-страницы, короче опыт это единственное мерило нашей жизни...
p.s. интересное решение по ISNULL и умножения столбца на 0 (иначе пришлось использовать CASE).
12 ноя 12, 16:24    [13459956]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
Glory
Member

Откуда:
Сообщений: 104760
Neosan
Да красивый код получился, с рекурсией еще я не работал

только тут нет рекурсии
12 ноя 12, 16:45    [13460159]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31429
Neosan
рекурсией еще я не работал, спасибо
Это обычный подзапрос:
SELECT id,id_bit,[NEW]=ISNULL(0*id_bit,ROW_NUMBER()OVER(PARTITION BY GN,id_bit ORDER BY id))
FROM (
 SELECT GN=ROW_NUMBER()OVER(ORDER BY id)-ROW_NUMBER()OVER(PARTITION BY id_bit ORDER BY id),*
 FROM #boo
) CTE
ORDER BY id;
12 ноя 12, 17:44    [13460609]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
Neosan
Member

Откуда:
Сообщений: 53
В этом запросе для получения столбца "NEW" используется 3-расчетных столбца, сейчас пробую оптимизировать до 2-х расчетных столбцов.

На счет рекурсии вы правы это WITH "временно именованный результирующий набор, называемый обобщенным табличным выражением", я привык использовать временные таблицы, нужно привыкать к WITH запросам (говорят они и работают быстрее, да и сам MS рекомендует использовать их вместо временных таблиц)
12 ноя 12, 18:02    [13460766]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
Neosan
да и сам MS рекомендует использовать их вместо временных таблиц

а можно ссылку?
12 ноя 12, 18:53    [13460995]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
Neosan
Member

Откуда:
Сообщений: 53
Зайцев Фёдор,
В октябре видел ссылку и пост на одном международном форуме, по самой ссылке не ходил просто инфа запомнилось т.к. давно хочу перейти на WITH CTE, попробуй поискать.
12 ноя 12, 19:50    [13461291]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
Neosan
Member

Откуда:
Сообщений: 53
Итак, 1-вариант Задачи "Нумерация строк NULL после первого значение стоящим выше и не являющимся NULL"
Спасибо за код 13459751 iap (member, г.Москва 22624-сообщений)
--временная таблица с данными
CREATE TABLE #boo(id SMALLINT IDENTITY(1,1), null_bit bit);
INSERT INTO #boo VALUES (1),(NULL),(NULL),(NULL),(0),(NULL),(NULL),(1),(NULL),(NULL),(0),(NULL),(NULL),(NULL),(NULL)
--нумерация строк null_bit после первого значение стоящим выше и не являющимся 'NULL'
SELECT *
FROM (SELECT id,null_bit,[num_bit]=ISNULL(0*[null_bit],ROW_NUMBER()OVER(PARTITION BY TMP,null_bit ORDER BY id))
FROM (SELECT * ,[TMP]=[id]-ROW_NUMBER()OVER(PARTITION BY null_bit ORDER BY id) 
FROM #boo) N1) N2
ORDER BY id
13 ноя 12, 12:39    [13464049]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
Neosan
Member

Откуда:
Сообщений: 53
Переписал код для наглядного обучения (как создается нумерация)
--нумерация 'NULL' [bool] после первого значения стоящим выше и не являющимся 'NULL'
--создаем и заполняем таблицу
CREATE TABLE #boo(ID SMALLINT IDENTITY(1,1), BOOL bit);
INSERT INTO #boo VALUES (1),(NULL),(NULL),(NULL),(0),(NULL),(NULL),(NULL)
--показательный запрос CTE (для обучения) со всеми столбцами участвующих в расчете
;WITH CTE AS
(
SELECT *
,[NUMBER]=ISNULL(0*[bool],ROW_NUMBER()OVER(PARTITION BY tmp,bool ORDER BY id))
FROM (SELECT * ,[tmp]=[row1]-[row2]
FROM (SELECT * 
,[row1]=ROW_NUMBER()OVER(ORDER BY id)
,[row2]=ROW_NUMBER()OVER(PARTITION BY bool ORDER BY id)
FROM #boo) N1) N2
)
--смотрим результат
SELECT *
FROM CTE
ORDER BY id

DROP TABLE #boo
14 ноя 12, 19:05    [13474430]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
Neosan
Member

Откуда:
Сообщений: 53
то-же самое но добавляем дополнительный столбец [COD] как измениться код SQL ?
пример таблицы:

К сообщению приложен файл. Размер - 6Kb
14 ноя 12, 19:26    [13474560]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
Neosan
Member

Откуда:
Сообщений: 53
небольшие изменения в PARTITION BY остальное то-же самое из-за нового столбца [COD]
--нумерация 'NULL' [bool] с дополнительным столбцом [COD] после первого значения стоящим выше и не являющимся 'NULL'
--создаем и заполняем таблицу
CREATE TABLE #boo(ID INT IDENTITY(1,1), COD VARCHAR (2), BOOL BIT);
INSERT INTO #boo VALUES 
('AA',1),('XX',0),('AA',NULL),('AA',NULL),('AA',NULL),('AA',0),('AA',NULL),('AA',NULL),
('XX',NULL),('XX',NULL),('XX',NULL),('XX',1),('XX',NULL),('AA',NULL),('XX',NULL),('XX',NULL)
--показательный запрос CTE (для обучения) со всеми столбцами участвующих в расчете
;WITH CTE AS
(
SELECT *
,[NUMBER]=ISNULL(0*[bool],ROW_NUMBER()OVER(PARTITION BY tmp,bool,cod ORDER BY id))
FROM (SELECT * ,[tmp]=[row1]-[row2]
FROM (SELECT * 
,[row1]=ROW_NUMBER()OVER(PARTITION BY cod ORDER BY id)
,[row2]=ROW_NUMBER()OVER(PARTITION BY cod,bool ORDER BY id)
FROM #boo) N1) N2
)
--смотрим результат
SELECT *
FROM CTE
ORDER BY id--cod, row1, row2--для наглядности работы сортировки

DROP TABLE #boo
14 ноя 12, 19:28    [13474585]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
Neosan
Member

Откуда:
Сообщений: 53
По вопросу преимуществ CTE - Джон Папа (John Papa) старший консультант по .NET в компании ASPSOFT (aspsoft.com)
http://msdn.microsoft.com/ru-ru/magazine/cc163346.aspx

Выражения CTE предоставляют способ написания T-SQL в гораздо более удобочитаемом виде по сравнению с ситуациями, в которых внутри запроса используются сложные производные таблицы или осуществляются ссылки на представление, определение которого является внешним по отношению к пакету T-SQL. Помимо этого, выражения CTE предоставляют значительно усовершенствованный инструмент для устранения трудностей, возникающих при использовании рекурсивных алгоритмов. Независимо от того, какие выражения CTE (нерекурсивные или рекурсивные) используются, вы убедитесь, что выражения CTE оказываются полезными во многих распространенных задачах разработки и повышают удобочитаемость, не жертвуя производительностью.
16 ноя 12, 17:28    [13485785]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
CTE vc #t
Guest
Neosan
По вопросу преимуществ CTE - Джон Папа (John Papa) старший консультант по .NET в компании ASPSOFT (aspsoft.com)
http://msdn.microsoft.com/ru-ru/magazine/cc163346.aspx

Выражения CTE предоставляют способ написания T-SQL в гораздо более удобочитаемом виде по сравнению с ситуациями, в которых внутри запроса используются сложные производные таблицы или осуществляются ссылки на представление, определение которого является внешним по отношению к пакету T-SQL. Помимо этого, выражения CTE предоставляют значительно усовершенствованный инструмент для устранения трудностей, возникающих при использовании рекурсивных алгоритмов. Независимо от того, какие выражения CTE (нерекурсивные или рекурсивные) используются, вы убедитесь, что выражения CTE оказываются полезными во многих распространенных задачах разработки и повышают удобочитаемость, не жертвуя производительностью.

и где здесь предложение использовать CTE вместо временных таблиц?
16 ноя 12, 17:38    [13485881]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
Добрый Э - Эх
Guest
Neosan,

в этой задаче также можно использовать [SUM | COUNT] over() для получения идентификатора групп. А на следующем уровне запроса делать нумерацию ROW_NUMBER-ом с секционированием по этому идентификатору. В итоге уложишься в желаемых два вычисляемых столбца...
28 ноя 12, 15:01    [13543335]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
Добрый Э - Эх
Guest
+ < Ну или всё вышесказанное мною в коде:
--
-- Тестовые данные:
with
  b$m_test(id, cod, bool) as 
    (
      select * 
        from (
               VALUES 
                 (1,'AA',1),
                 (2,'XX',0),
                 (3,'AA',NULL),
                 (4,'AA',NULL),
                 (5,'AA',NULL),
                 (6,'AA',0),
                 (7,'AA',NULL),
                 (8,'AA',NULL),
                 (9,'XX',NULL),
                 (10,'XX',NULL),
                 (11,'XX',NULL),
                 (12,'XX',1),
                 (13,'XX',NULL),
                 (14,'AA',NULL),
                 (15,'XX',NULL),
                 (16,'XX',NULL)
             ) as v(q,a,z)
    )
--
-- Основонй запрос:
select v.id, v.cod,
       row_number() 
             over(partition by v.cod, v.grp_id 
                      order by v.id) - 1 as rn
  from (
         select t.*,
                count(bool) over(partition by cod order by id) as grp_id
           from b$m_test as t
       ) as v
 order by id


on-line- проверка на sqlfiddle.com
28 ноя 12, 15:18    [13543478]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Neosan
(говорят они и работают быстрее, да и сам MS рекомендует использовать их вместо временных таблиц)


CTE - это лишь удобный вид записи кода. Сам по себе код не может работать быстрее потому что он написан через CTE. Я бы даже сказал, что код написанный через CTE всегда работает медленнее(или равно) чем без CTE. А вот эта удобная запись кода без реального осознания, что же в нем происходит - это уже реальная проблема. Задайте вопрос своим разработчикам, как разворачивается код CTE в execution plan-е?
30 ноя 12, 01:13    [13553319]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
так зашёл
Guest
Павел-П
CTE всегда работает медленнее(или равно) чем без CTE.
...
как разворачивается код CTE в execution plan-е?


СТЕ работает абсолютно так же как и подзапросы:)
Планы идентичны.
Без рекурсии СТЕ - это просто структуирование и повышение читаемости большого запроса.

По теме:

У человека 2012 сервер, там кажется можно обойтись одним ROW_NUMBER, сейчас попробую
30 ноя 12, 07:37    [13553566]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
Добрый Э - Эх
Guest
так зашёл
У человека 2012 сервер, там кажется можно обойтись одним ROW_NUMBER, сейчас попробую
А чем row_number в версии 2012 принципиально отличается от себя же в версии 2005? ORDER BY из него не выкинешь, конструкцию окна не прикрутишь...
30 ноя 12, 08:35    [13553676]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
так зашёл
Guest
Добрый Э - Эх
так зашёл
У человека 2012 сервер, там кажется можно обойтись одним ROW_NUMBER, сейчас попробую
А чем row_number в версии 2012 принципиально отличается от себя же в версии 2005? ORDER BY из него не выкинешь, конструкцию окна не прикрутишь...

Согласен, не row_number:).. Но кажется как-то накопительным можно прикрутить.. Врят ли конечно, я ещё не баловался с 2012(читал только), сейчас ставится))
30 ноя 12, 08:41    [13553700]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
Добрый Э - Эх
Guest
так зашёл,

Как и что можно прикрутить - я показал выше.
Потренироваться на версии MS SQL Server 2012 можно на он-лайн ресурсе sqlfiddle.com
30 ноя 12, 09:13    [13553808]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование BIT-столбца  [new]
так зашёл
Guest
Добрый Э - Эх
так зашёл,

Как и что можно прикрутить - я показал выше.
Потренироваться на версии MS SQL Server 2012 можно на он-лайн ресурсе sqlfiddle.com


Ага, проглядел order by. За ссылку спасибо, но я уже поставил:)
У меня дома куча БД для тестов/развлечений, ща перекину на новый сервер.
30 ноя 12, 09:18    [13553835]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить