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

Откуда:
Сообщений: 91
Всем доброго времени суток.

MSSQL 2005.

Допустим, имеем таблицу с такими данными:

DECLARE
   @Temp table
      (
      ID     int,
      String varchar(20)
      )

DECLARE
   @Field     int,
   @Direction varchar(5)

INSERT INTO @temp VALUES (6, 'g')
INSERT INTO @temp VALUES (3, 'b')
INSERT INTO @temp VALUES (1, 'a')
INSERT INTO @temp VALUES (5, 'e')
INSERT INTO @temp VALUES (2, 'c')
INSERT INTO @temp VALUES (4, 'd')
INSERT INTO @temp VALUES (7, 'f')

Задача: на основе входных параметров @Field (задает номер столбца, по к-рому надо сортировать) и @Direction (направление сортировки - ASC/DESC) получить все данные из таблички @temp, причем к этим данным необходимо добавить номер строки (для paging-a).

Сразу оговорюсь, что задачу необходимо решить без динамического sql-я.

Допустим такой код отрабатывает нормально:

SET @Field       = 1
SET @Direction = 'ASC'

SELECT
   RowNum = CASE
               WHEN (@Field = 1 AND @Direction = 'ASC')
                  THEN ROW_NUMBER() OVER (ORDER BY ID ASC)
            END,
   ID,
   String
FROM
   @Temp

И возвращает такие результаты:
Row - ID - String
1 - 1 - a
2 - 2 - c
3 - 3 - b
4 - 4 - d
5 - 5 - e
6 - 6 - g
7 - 7 - f

Стоит в CASE добавить еще одно условие:
SET @Field       = 1
SET @Direction = 'ASC'

SELECT
   RowNum = CASE
               WHEN (@Field = 1 AND @Direction = 'ASC')
                  THEN ROW_NUMBER() OVER (ORDER BY ID ASC)
               WHEN (@Field = 1 AND @Direction = 'DESC')
                  THEN ROW_NUMBER() OVER (ORDER BY ID DESC)
            END,
   ID,
   String
FROM
   @Temp
и на выходе получаем не то, что надо. При неизменных входных параметрах, отработал 2-ой WHEN
Row - ID - String
7 - 7 - f
6 - 6 - g
5 - 5 - e
4 - 4 - d
3 - 3 - b
2 - 2 - c
1 - 1 - a

На деле, сколько бы я в CASE не добавлял условий с ROW_NUMBER-ом, всегда отрабытывает последнее условие. Это так и должно или я что-то не правильно делаю?
4 ноя 09, 16:22    [7881639]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER() в CASE-e  [new]
-=FlinT=-
Member

Откуда:
Сообщений: 91
Насчет результатов 2-го select-a - там вообще получилось непонятно что, т.к. по идее, если бы отработал именно 2-ой WHEN, то вместо
7 - 7 - f
6 - 6 - g
5 - 5 - e
4 - 4 - d
3 - 3 - b
2 - 2 - c
1 - 1 - a

должно было бы получится:
1 - 7 - f
2 - 6 - g
3 - 5 - e
4 - 4 - d
5 - 3 - b
6 - 2 - c
7 - 1 - a

А так видно, что отсортировано по [ID DESC], но почему-то также и отсортировался по убыванию RowNum.
4 ноя 09, 16:27    [7881660]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER() в CASE-e  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
-=FlinT=-
И возвращает такие результаты:
Row - ID - String
1 - 1 - a
2 - 2 - c
3 - 3 - b
4 - 4 - d
5 - 5 - e
6 - 6 - g
7 - 7 - f
-=FlinT=-
При неизменных входных параметрах, отработал 2-ой WHEN
Row - ID - String
7 - 7 - f
6 - 6 - g
5 - 5 - e
4 - 4 - d
3 - 3 - b
2 - 2 - c
1 - 1 - a
Что-то я отличий не вижу
4 ноя 09, 16:29    [7881670]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER() в CASE-e  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
А где же ORDER BY??
C чего Вы взяли, что результат должен быть в каком-то порядке?
SELECT
   RowNum = CASE
               WHEN (@Field = 1 AND @Direction = 'ASC')
                  THEN ROW_NUMBER() OVER (ORDER BY ID ASC)
               WHEN (@Field = 1 AND @Direction = 'DESC')
                  THEN ROW_NUMBER() OVER (ORDER BY ID DESC)
            END,
   ID,
   String
FROM
   @Temp
ORDER BY RowNum;
4 ноя 09, 16:30    [7881683]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER() в CASE-e  [new]
-=FlinT=-
Member

Откуда:
Сообщений: 91
iap
А где же ORDER BY??
C чего Вы взяли, что результат должен быть в каком-то порядке?


Эмм... ну, то, что результат должен быть в каком-то порядке, я решил на основе того, что у меня уже присутствует ORDER BY внутри ROW_NUMBER(), причем когда у меня в CASE-e один WHEN (или вообще просто ROW_NUMBER без всяких CASE-ов), результирующие данные отображаются отсортированными.

По крайней мере ORDER BY по RowNum залечил, спасибо :)

Еще вопросик: насколько запрос с таким CASE-ом будет тяжелым, будет ли это значить, что для каждой результирующей строки в select-e будет заново отрабатывать ORDER By или еще что-нибудь подобное?
4 ноя 09, 16:47    [7881748]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER() в CASE-e  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
-=FlinT=-
я решил на основе того, что у меня уже присутствует ORDER BY внутри ROW_NUMBER()
А если Вы в запросе закажете вычислять два поля, и в обоих ROW_NUMBER(), но с разными ORDER BY, как тогда серверу быть?
4 ноя 09, 16:51    [7881767]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER() в CASE-e  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
-=FlinT=-
iap
А где же ORDER BY??
C чего Вы взяли, что результат должен быть в каком-то порядке?


Эмм... ну, то, что результат должен быть в каком-то порядке, я решил на основе того, что у меня уже присутствует ORDER BY внутри ROW_NUMBER(), причем когда у меня в CASE-e один WHEN (или вообще просто ROW_NUMBER без всяких CASE-ов), результирующие данные отображаются отсортированными.

По крайней мере ORDER BY по RowNum залечил, спасибо :)

Еще вопросик: насколько запрос с таким CASE-ом будет тяжелым, будет ли это значить, что для каждой результирующей строки в select-e будет заново отрабатывать ORDER By или еще что-нибудь подобное?
Какое отношение ORDER BY внутри OVER() имеет к порядку записей результата запроса?
Это всего лишь внутренняя структура оконной (windowed) функции.
Если какой-то порядок и просматривался, то это было случайное совпадение, так как ORDER BY не был задан.
"Тяжесть" запроса надо выяснять, анализируя план.
4 ноя 09, 16:53    [7881772]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER() в CASE-e  [new]
-=FlinT=-
Member

Откуда:
Сообщений: 91
Паганель
-=FlinT=-
я решил на основе того, что у меня уже присутствует ORDER BY внутри ROW_NUMBER()
А если Вы в запросе закажете вычислять два поля, и в обоих ROW_NUMBER(), но с разными ORDER BY, как тогда серверу быть?


Честно говоря, не подумал.

Насчет:
jap
Если какой-то порядок и просматривался, то это было случайное совпадение, так как ORDER BY не был задан.


Не знаю, на случайное совпадение не похоже. Если Вы на на моих тестовых данных позапускаете такие запросы, как:
SELECT
   ROW_NUMBER() OVER (ORDER BY String DESC),
   ID,
   String
FROM
   @temp
или
SELECT
   ROW_NUMBER() OVER (ORDER BY ID DESC),
   ID,
   String
FROM
   @temp
и тому подобное, Вы увидите, что результирующие данные отсортированы в соответствии с условием в ORDER BY внутри ROW_NUMBER.
4 ноя 09, 17:09    [7881816]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER() в CASE-e  [new]
iljy
Member

Откуда:
Сообщений: 8711
-=FlinT=-

Не знаю, на случайное совпадение не похоже. Если Вы на на моих тестовых данных позапускаете такие запросы, как:
SELECT
   ROW_NUMBER() OVER (ORDER BY String DESC),
   ID,
   String
FROM
   @temp
или
SELECT
   ROW_NUMBER() OVER (ORDER BY ID DESC),
   ID,
   String
FROM
   @temp
и тому подобное, Вы увидите, что результирующие данные отсортированы в соответствии с условием в ORDER BY внутри ROW_NUMBER.

Разумеется это не случайное совпадение, и если вы глянете на план своего запроса с CASE- увидите там ДВЕ сортировки, и сортировка по убыванию идет второй. Но если где-то вмешается например параллельное выполнение запроса - сортировка уедет запросто. Гарантию дает ТОЛЬКО order by в запросе.
И еще. Как я уже сказал - в вашем плане 2 сортировки. Сделаете больше ветвлений - будет больше сортировок. Работать будет конечно, но быстродействие может стать аховым. Сравните такие варианты:
SET @Field       = 1
SET @Direction = 'DESC'

SELECT
   RowNum = CASE
               WHEN (@Field = 1 AND @Direction = 'ASC')
                  THEN ROW_NUMBER() OVER (ORDER BY ID ASC)
               WHEN (@Field = 1 AND @Direction = 'DESC')
                  THEN ROW_NUMBER() OVER (ORDER BY ID DESC)            END,
   ID,
   String
FROM
   @Temp
   
SET @Field       = 1
SET @Direction = 'DESC'
   
SELECT
   RowNum = ROW_NUMBER() over(order by    
			 CASE WHEN (@Field = 1 AND @Direction = 'ASC')
				  THEN ID 
				  WHEN (@Field = 1 AND @Direction = 'DESC')
				  THEN -ID 
			 END),
   ID,
   String
FROM
   @Temp
order by RowNum
4 ноя 09, 17:29    [7881878]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER() в CASE-e  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
-=FlinT=-
Не знаю, на случайное совпадение не похоже.
Я не имел в виду нормальное распределение вероятности!
Чаще всего получится что-то похожее на упорядоченность,
но иногда может получиться и неправильно упорядоченный результат.
Вас это устраивает? Или нужен гарантированный порядок?
4 ноя 09, 17:49    [7881945]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER() в CASE-e  [new]
-=FlinT=-
Member

Откуда:
Сообщений: 91
iap
-=FlinT=-
Не знаю, на случайное совпадение не похоже.
Я не имел в виду нормальное распределение вероятности!
Чаще всего получится что-то похожее на упорядоченность,
но иногда может получиться и неправильно упорядоченный результат.
Вас это устраивает? Или нужен гарантированный порядок?

Конечно, гарантированный порядок меня устроит больше, чем случайный, спасибо.
4 ноя 09, 18:04    [7881982]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить