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

Откуда:
Сообщений: 111
Уважаемый All,

Возник следующий вопрос. Имеется таблица:
declare @t table (f1 int, f2 int, f3 int)
insert into @t
select 1, 1, 10 union all
select 1, 2, 20 union all
select 1, 3, 20 union all
select 1, 4, 50 union all
select 1, 5, 60 union all
select 1, 6, 60 union all
select 1, 7, 60 union all
select 1, 8, 20 union all
select 1, 9, 20
Хотелось бы получить результат вида:
f1 f2 f3 rank
1  1  10 1
1  2  20 2
1  3  20 2
1  4  50 3
1  5  60 4
1  6  60 4
1  7  60 4
1  8  20 5
1  9  20 5
Вопрос, сами понимаете, как этого добиться ?
4 авг 09, 16:18    [7496712]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с ранжированием  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
rank()over(partition by ... order by ...)

для спящего время бодрствования равносильно сну
4 авг 09, 16:24    [7496759]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с ранжированием  [new]
Сергей Мишин
Member

Откуда:
Сообщений: 376
a2d,
dense_rank() ...
4 авг 09, 16:31    [7496802]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с ранжированием  [new]
a2d
Member

Откуда:
Сообщений: 111
Алексей2003
rank()over(partition by ... order by ...)

тогда уж DENSE_RANK(), только "не выходит каменный цветок..."
4 авг 09, 16:31    [7496806]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с ранжированием  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
SELECT DENSE_RANK()OVER(ORDER BY f3)
FROM @t
ORDER BY f1,f2;
4 авг 09, 16:34    [7496828]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с ранжированием  [new]
a2d
Member

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

Спасибо, но это не совсем то! 20-е и 60-е строки повторяются и имеют во втором случае другой ранг.
4 авг 09, 16:37    [7496854]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с ранжированием  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iap
SELECT DENSE_RANK()OVER(ORDER BY f3)
FROM @t
ORDER BY f1,f2;
Точнее
declare @t table (f1 int, f2 int, f3 int)
insert into @t
select 1, 1, 10 union all
select 1, 2, 20 union all
select 1, 3, 20 union all
select 1, 4, 50 union all
select 1, 5, 60 union all
select 1, 6, 60 union all
select 1, 7, 60 union all
select 1, 8, 20 union all
select 1, 9, 20

SELECT f1,f2,f3, DENSE_RANK()OVER(ORDER BY f3)rank
FROM @t
ORDER BY f3;
4 авг 09, 16:37    [7496855]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с ранжированием  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
;with numbered as (
   select f1, f2, f3, row_number() over(order by f2) as rn
     from @t
), cte as (
   select f1, f2, f3, rn, 1 as rank
     from numbered
    where rn = 1
   union all
   select n.f1, n.f2, n.f3, n.rn, c.rank + case when n.f3 = c.f3 then 0 else 1 end
     from numbered as n
     join cte as c on c.rn + 1 = n.rn
)
select f1, f2, f3, rank from cte
option(maxrecursion 0)

f1          f2          f3          rank
----------- ----------- ----------- -----------
1           1           10          1
1           2           20          2
1           3           20          2
1           4           50          3
1           5           60          4
1           6           60          4
1           7           60          4
1           8           20          5
1           9           20          5

(9 row(s) affected)
4 авг 09, 16:40    [7496871]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с ранжированием  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
declare @t table (f1 int, f2 int, f3 int)
insert into @t
select 1, 1, 10 union all
select 1, 2, 20 union all
select 1, 3, 20 union all
select 1, 4, 50 union all
select 1, 5, 60 union all
select 1, 6, 60 union all
select 1, 7, 60 union all
select 1, 8, 20 union all
select 1, 9, 20;

WITH CTE(f1,f2,f3,GN) AS(SELECT f1,f2,f3, ROW_NUMBER()OVER(ORDER BY f2)-ROW_NUMBER()OVER(PARTITION BY f3 ORDER BY f2) FROM @t)
SELECT f1,f2,f3, DENSE_RANK()OVER(ORDER BY GN)[rank]
FROM CTE
ORDER BY f2;
4 авг 09, 16:45    [7496913]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с ранжированием  [new]
Сергей Мишин
Member

Откуда:
Сообщений: 376
SELECT
	*
	,dense_rank() OVER(ORDER BY IsNull((SELECT TOP 1 f2 FROM @t b WHERE b.f2>a.f2 AND a.f3<>b.f3 ORDER BY f2),99))
FROM @t	a
4 авг 09, 16:45    [7496916]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с ранжированием  [new]
a2d
Member

Откуда:
Сообщений: 111
Паганель,

Конечно!!! Обобщенное табличное выражение!!! Спасибо!
4 авг 09, 16:46    [7496921]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с ранжированием  [new]
Сергей Мишин
Member

Откуда:
Сообщений: 376
iap,
не прокатит на:
select 1, 1, 10 union all
select 1, 2, 20 union all
select 1, 3, 20 union all
select 1, 4, 50 union all
select 1, 5, 60 union all
select 1, 6, 70 union all
select 1, 7, 60 union all
select 1, 8, 20 union all
select 1, 9, 20;
4 авг 09, 16:56    [7496998]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с ранжированием  [new]
a2d
Member

Откуда:
Сообщений: 111
Спасибо всем!
4 авг 09, 16:56    [7497003]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с ранжированием  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Сергей Мишин
iap,
не прокатит на:
select 1, 1, 10 union all
select 1, 2, 20 union all
select 1, 3, 20 union all
select 1, 4, 50 union all
select 1, 5, 60 union all
select 1, 6, 70 union all
select 1, 7, 60 union all
select 1, 8, 20 union all
select 1, 9, 20;
Тогда придётся так:
declare @t table (f1 int, f2 int, f3 int)
insert into @t
select 1, 1, 10 union all
select 1, 2, 20 union all
select 1, 3, 20 union all
select 1, 4, 50 union all
select 1, 5, 60 union all
select 1, 6, 70 union all
select 1, 7, 60 union all
select 1, 8, 20 union all
select 1, 9, 20;

WITH
 Numbered(f1,f2,f3,N,G) AS(SELECT f1,f2,f3, ROW_NUMBER()OVER(ORDER BY f2), ROW_NUMBER()OVER(PARTITION BY f3 ORDER BY f2) FROM @t)
,Ranked(f1,f2,f3,R) AS(SELECT f1,f2,f3, MIN(N)OVER(PARTITION BY f3,N-G)+RANK()OVER(ORDER BY N-G) FROM Numbered)
SELECT f1,f2,f3, DENSE_RANK()OVER(ORDER BY R)
FROM Ranked
ORDER BY f2;
Зато без рекурсии!
4 авг 09, 17:44    [7497403]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с ранжированием  [new]
Сергей Мишин
Member

Откуда:
Сообщений: 376
iap, извиняюсь, но опять не прокатит-))
select 1, 1, 10 union all
select 1, 2, 20 union all
select 1, 3, 30 union all
select 1, 4, 30 union all
select 1, 5, 20 union all
select 1, 6, 70 union all
select 1, 7, 20 union all
select 1, 8, 30 union all
select 1, 9, 20;
4 авг 09, 18:11    [7497550]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с ранжированием  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Сергей Мишин
iap, извиняюсь, но опять не прокатит-))
select 1, 1, 10 union all
select 1, 2, 20 union all
select 1, 3, 30 union all
select 1, 4, 30 union all
select 1, 5, 20 union all
select 1, 6, 70 union all
select 1, 7, 20 union all
select 1, 8, 30 union all
select 1, 9, 20;
Пока ехал в метро, всё понял.
Чего-то все идеи приходят в метро! Давно заметил.
Действительно, что я к этому RANK() привязался? Не нужен он:
SET NOCOUNT ON;

declare @t table (f1 int, f2 int, f3 int)
insert into @t
select 1, 1, 10 union all
select 1, 2, 20 union all
select 1, 3, 30 union all
select 1, 4, 30 union all
select 1, 5, 20 union all
select 1, 6, 70 union all
select 1, 7, 20 union all
select 1, 8, 30 union all
select 1, 9, 20;

WITH
 Numbered AS(SELECT f1,f2,f3, ROW_NUMBER()OVER(ORDER BY f2)N, ROW_NUMBER()OVER(PARTITION BY f3 ORDER BY f2)G FROM @t)
,Ranked AS(SELECT f1,f2,f3, MIN(N)OVER(PARTITION BY f3,N-G)R FROM Numbered)
SELECT f1,f2,f3, DENSE_RANK()OVER(ORDER BY R)
FROM Ranked
ORDER BY f2;
Сергей, следующего раза не будет!
4 авг 09, 19:25    [7497746]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить