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

Откуда:
Сообщений: 297
В SQL2005 есть функции
ROW_NUMBER(), RANK()
которые помогают вернуть определённые строки (страницу)
из всего запроса.
Но мне вместе с определённой страницей необходимо вернуть
общее количество строк всего запроса как возвращаемый параметер
чтобы знать сколько страниц получилось
Как это лучше сделать ?
24 ноя 09, 12:56    [7969828]     Ответить | Цитировать Сообщить модератору
 Re: Paging by functions: ROW_NUMBER(), RANK()  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Salik
В SQL2005 есть функции
ROW_NUMBER(), RANK()
которые помогают вернуть определённые строки (страницу)
из всего запроса.
Пример того, как они Вам помогают, покажите, пожалуйста
24 ноя 09, 12:58    [7969855]     Ответить | Цитировать Сообщить модератору
 Re: Paging by functions: ROW_NUMBER(), RANK()  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
COUNT(*)OVER()
24 ноя 09, 13:05    [7969938]     Ответить | Цитировать Сообщить модератору
 Re: Paging by functions: ROW_NUMBER(), RANK()  [new]
Salik
Member

Откуда:
Сообщений: 297
Вот пример получения с 20 по 30 строки.
А мне ещё надо знать сколько всего строк в запросе.

select * from
(
	select 
	rank() OVER     (ORDER BY ContactID DESC) AS 'RowNumber',
	*

	from contacts
	where ContactName like 'd%'
) res
where res.RowNumber between 20 and 30
24 ноя 09, 13:16    [7970052]     Ответить | Цитировать Сообщить модератору
 Re: Paging by functions: ROW_NUMBER(), RANK()  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
А почему rank() а не row_number() ?
Какая-то особая логика пейджинга дубликатов?
24 ноя 09, 13:23    [7970112]     Ответить | Цитировать Сообщить модератору
 Re: Paging by functions: ROW_NUMBER(), RANK()  [new]
-=DiM@n=-
Member

Откуда: Москва
Сообщений: 1564
	select 
	count(*)
	from contacts
	where ContactName like 'd%'
24 ноя 09, 13:29    [7970177]     Ответить | Цитировать Сообщить модератору
 Re: Paging by functions: ROW_NUMBER(), RANK()  [new]
Salik
Member

Откуда:
Сообщений: 297
Паганель : rank() или не row_number() мне пока не важно .

-=DiM@n=- : так ты предлагаешь запускать запрос 2 раза:
один раз подсчитать , а второй раз выбрать то что надо?
24 ноя 09, 13:34    [7970228]     Ответить | Цитировать Сообщить модератору
 Re: Paging by functions: ROW_NUMBER(), RANK()  [new]
-=DiM@n=-
Member

Откуда: Москва
Сообщений: 1564
Ну можете еще так:
create table #t
(...);

insert into #t
select 
rank() OVER     (ORDER BY ContactID DESC) AS 'RowNumber',
*
from contacts
where ContactName like 'd%';

select * 
from #t
where RowNumber between 20 and 30;

select count(*)
from #t
Так ваш запрос один раз отработает )
24 ноя 09, 13:38    [7970256]     Ответить | Цитировать Сообщить модератору
 Re: Paging by functions: ROW_NUMBER(), RANK()  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
еще как вариант
;with res as
(
	select 
	row_number() OVER     (ORDER BY ContactID DESC) AS [RowNumber],
	*

	from contacts
	where ContactName like 'd%'
) 
select *, (select top 1 [RowNumber] from res order by [RowNumber] desc)
from res
where res.RowNumber between 20 and 30
ЗЫ не проверял
24 ноя 09, 13:39    [7970271]     Ответить | Цитировать Сообщить модератору
 Re: Paging by functions: ROW_NUMBER(), RANK()  [new]
iljy
Member

Откуда:
Сообщений: 8711
Salik,

чем вариант jap не подошел?
24 ноя 09, 13:41    [7970287]     Ответить | Цитировать Сообщить модератору
 Re: Paging by functions: ROW_NUMBER(), RANK()  [new]
Salik
Member

Откуда:
Сообщений: 297
iljy Ваш линк не работает

-=DiM@n=- При использовании временной таблицы запрос используется 1 раз а по временной 2
раза да и при использовании временной таблицы надобность в функции отпадает.

Паганель тогда уж лучше так

select RowNumber+RowNumberASC-1,* from
(
	select 
	rank() OVER     (ORDER BY ContactID DESC) AS 'RowNumber',
	rank() OVER     (ORDER BY ContactID ASC) AS 'RowNumberASC',
	*

	from contacts
	where ContactName like 'd%'
) res
where res.RowNumber between 20 and 30
24 ноя 09, 13:54    [7970404]     Ответить | Цитировать Сообщить модератору
 Re: Paging by functions: ROW_NUMBER(), RANK()  [new]
iljy
Member

Откуда:
Сообщений: 8711
Salik
iljy Ваш линк не работает

А ручками промотать чуть выше? Он сразу сказал про
count(*) over()

А "Паганель тогда уж лучше так" - не лучше, будет 2 сортировки, на большой таблице будет ой.
24 ноя 09, 14:14    [7970611]     Ответить | Цитировать Сообщить модератору
 Re: Paging by functions: ROW_NUMBER(), RANK()  [new]
Salik
Member

Откуда:
Сообщений: 297
Понял :
select * from
(
	select 
               count(*) OVER     () AS 'count',
	  rank() OVER     (ORDER BY ContactID DESC) AS 'RowNumber',
	  *

	from contacts
	where ContactName like 'd%'
) res
where res.RowNumber between 101 and 300
24 ноя 09, 15:16    [7971170]     Ответить | Цитировать Сообщить модератору
 Re: Paging by functions: ROW_NUMBER(), RANK()  [new]
GlebZ
Member

Откуда: USA
Сообщений: 284
Salik
Понял :
select * from
(
	select 
               count(*) OVER     () AS 'count',
	  rank() OVER     (ORDER BY ContactID DESC) AS 'RowNumber',
	  *

	from contacts
	where ContactName like 'd%'
) res
where res.RowNumber between 101 and 300


Самое смешное, что:
SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT count(*) OVER     () AS 'count',* FROM tbl_A

SELECT (SELECT count(*) FROM tbl_A) AS 'count',* FROM tbl_A


SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.

(4581 row(s) affected)
Table 'Worktable'. Scan count 3, logical reads 19069, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_А'. Scan count 1, logical reads 730, physical reads 0, read-ahead reads 0, lob logical reads 2, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 203 ms,  elapsed time = 8054 ms.

(4581 row(s) affected)
Table 'tbl_А'. Scan count 2, logical reads 1460, physical reads 0, read-ahead reads 0, lob logical reads 2, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 140 ms,  elapsed time = 7825 ms.

24 ноя 09, 16:37    [7972020]     Ответить | Цитировать Сообщить модератору
 Re: Paging by functions: ROW_NUMBER(), RANK()  [new]
iljy
Member

Откуда:
Сообщений: 8711
GlebZ,

ага, а если чуть посложнее - уже наоборот
SET STATISTICS IO ON
SET STATISTICS TIME ON
select * from
(
	SELECT count(*) OVER() AS 'count',*, row_number() over(order by id) N FROM StrLevel3
)t
where N between 201 and 210

select * from
(
	SELECT (SELECT count(*) FROM StrLevel3) AS 'count',*, row_number() over(order by id) N FROM StrLevel3
)t
where N between 201 and 210

(10 row(s) affected)
Таблица "Worktable". Число просмотров 3, логических чтений 20284, физических чтений 0,
упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих
чтений 0.
Таблица "StrLevel3". Число просмотров 1, логических чтений 64, физических чтений 0,
упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих
чтений 0.

(1 row(s) affected)

Время работы SQL Server:
Время ЦП = 47 мс, затраченное время = 165 мс.

(10 row(s) affected)
Таблица "StrLevel3". Число просмотров 2, логических чтений 66, физических чтений 0,
упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих
чтений 0.

(1 row(s) affected)

Время работы SQL Server:
Время ЦП = 0 мс, затраченное время = 184 мс.
24 ноя 09, 16:50    [7972172]     Ответить | Цитировать Сообщить модератору
 Re: Paging by functions: ROW_NUMBER(), RANK()  [new]
GlebZ
Member

Откуда: USA
Сообщений: 284
iljy,
Ну это на ваших таблицах. А на наших:

(10 row(s) affected)
Table 'Worktable'. Scan count 3, logical reads 18405, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_А'. Scan count 1, logical reads 730, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 125 ms,  elapsed time = 183 ms.

(10 row(s) affected)
Table 'tbl_А'. Scan count 2, logical reads 805, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 38 ms.
24 ноя 09, 16:54    [7972218]     Ответить | Цитировать Сообщить модератору
 Re: Paging by functions: ROW_NUMBER(), RANK()  [new]
GlebZ
Member

Откуда: USA
Сообщений: 284
Кстати, у вас тоже не наоборот.
Нa CPU time посмотрите. Elapsed зависит от кучи других факторов.
Водку? Водку - буду!
24 ноя 09, 16:56    [7972242]     Ответить | Цитировать Сообщить модератору
 Re: Paging by functions: ROW_NUMBER(), RANK()  [new]
iljy
Member

Откуда:
Сообщений: 8711
GlebZ
iljy,
Ну это на ваших таблицах. А на наших:

Все может быть. Я честно говоря тоже не очень понимаю, как у меня такие результаты получаются, да и план там странноватый... В принципе интуитивно понятно, что просто на таблице - вариант с подзапросом должен быть быстрее: Table spool нет, а таблица все равно в памяти.
24 ноя 09, 16:59    [7972271]     Ответить | Цитировать Сообщить модератору
 Re: Paging by functions: ROW_NUMBER(), RANK()  [new]
iljy
Member

Откуда:
Сообщений: 8711
GlebZ
Кстати, у вас тоже не наоборот.
Нa CPU time посмотрите. Elapsed зависит от кучи других факторов.
Водку? Водку - буду!

Elapsed у меня получается для второго запроса стабильно 190+-5мс, а для первого - 120-170мс, так что разброс есть, но первое всегда меньше.
24 ноя 09, 17:03    [7972316]     Ответить | Цитировать Сообщить модератору
 Re: Paging by functions: ROW_NUMBER(), RANK()  [new]
iljy
Member

Откуда:
Сообщений: 8711
GlebZ,

не, все нормально, если запросы поменять местами - время получается 95мс и 210мс а если еще построение плана убрать (ну забыл я!) - становится 1мс и 55мс
24 ноя 09, 17:07    [7972349]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить