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

Откуда:
Сообщений: 182
Предыстория:
есть документы, показывающие из чего состоит то или иное изделие. Необходимо проранжировать документы, имеющие одинаковую спецификацию.
Пример: исходная таблица:
d1	aaa
d1 bbb
d2 bbb
d3 bbb
d4 aaa
d4 ccc
d5 aaa
d6 ccc
d7 bbb
d7 ccc
d8 aaa
d8 bbb
d8 ccc
d9 ccc
d10 ccc
Конечный результат:
1	aaa	d1
1 bbb d1
2 bbb d2
2 bbb d3
3 aaa d4
3 ccc d4
4 aaa d5
5 ccc d6
5 ccc d9
5 ccc d10
6 bbb d7
6 ccc d7
7 aaa d8
7 bbb d8
7 ccc d8
Первая колонка конечной таблицы показывает ранг - перечень документов одинаковой спецификации. Как можно ранжировать по строкам?
19 дек 09, 19:20    [8090858]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
Glory
Member

Откуда:
Сообщений: 104760
Чего уж проще. Специально в SQL2005 добавили функция ранжирования RANK/DENSE_RANK/NTILE.Выьирайте
19 дек 09, 19:28    [8090876]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
gigadedushka
Member

Откуда:
Сообщений: 182
Я знаю и пользуюсь вунциями RANK/DENSE_RANK, но они не подходят, или я не вижу как ими воспользоваться в моем примере. Здесь надо смотреть уникальность не только по определенным полям (их я объединил для примера в одно, типа aaa), но и по строкам (чтобы была одинаковая совокупность строк для разных документов). Может вы код для текущего примера подскажите? Был бы очень благодарен
19 дек 09, 19:36    [8090890]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
Glory
Member

Откуда:
Сообщений: 104760
А строка это уже не определенные поля, по которым надо уникальность ?
19 дек 09, 19:37    [8090896]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
gigadedushka
Member

Откуда:
Сообщений: 182
Уникальность должна быть не только по полям, но и по совокупности строк: для документов одинаковой спецификации - одного и того же состава с одним и тем же количеством и т.п. - один ранг
19 дек 09, 19:44    [8090916]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
Glory
Member

Откуда:
Сообщений: 104760
gigadedushka
Уникальность должна быть не только по полям, но и по совокупности строк: для документов одинаковой спецификации - одного и того же состава с одним и тем же количеством и т.п. - один ранг

А как записи могут быть неуникальны, если составлющие их поля уникальны ?
19 дек 09, 19:46    [8090924]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
gigadedushka
Member

Откуда:
Сообщений: 182
К примеру в одном доке есть две составляющие aaa и bbb - это две строчки, в другом доке есть aaa но нет bbb - третья строка. две строчки aaa разных доков уникальны по всем полям, но совокупность всех строк документов - различна, должен быть разный ранг
19 дек 09, 19:52    [8090941]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
gigadedushka,

но в каком порядке сделана нумерация?
19 дек 09, 20:05    [8090977]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
gigadedushka
Member

Откуда:
Сообщений: 182
Давайте еще раз на абстрактных примерах. Допустим у нас есть people в каком-нибудь замкнутом помещении. На каждом из них разная одежда: на ком-то футболка, шорты, кроссовки; на ком-то пиджак, брюки, туфли, шапка; а кто-то совсем голый))). Необходимо классифицировать всех людей в этом помещении, то есть присвоить определенной группе с общими атрибутами один ранг. При этом: люди у которых есть шорты, кроссовки, но нет футболки должны иметь свой ранг, то есть смотреть на уникальность всех элементов.
19 дек 09, 20:15    [8090994]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
gigadedushka
Member

Откуда:
Сообщений: 182
Типов одежды - бесконечное множество. Дак как можно решить такую задачу?
19 дек 09, 20:16    [8090996]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
Glory
Member

Откуда:
Сообщений: 104760
gigadedushka
Давайте еще раз на абстрактных примерах.

лучше на конкретных. Так, как это предложено в Рекомендациях по оформлению сообщений п.6
19 дек 09, 20:35    [8091047]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
iljy
Member

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

Объясните, почему
2 bbb d2
2 bbb d3

но
6 bbb d7
?

5 ccc d6
5 ccc d9
5 ccc d10
но
6 ccc d7
7 ccc d8
?
По какому алгоритму присваевается этот ранг?
19 дек 09, 20:47    [8091086]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iljy
gigadedushka,

Объясните, почему
2 bbb d2
2 bbb d3

но
6 bbb d7
?

5 ccc d6
5 ccc d9
5 ccc d10
но
6 ccc d7
7 ccc d8
?
По какому алгоритму присваевается этот ранг?
А я понял! А я понял!
d2 состоит только из bbb. И d3 состоит только из bbb. ==> попадают в один класс (получают один номер).
d7 состоит из bbb и ccc. Больше таких наборов нет ==> в этот класс попадает только одна запись.
d6 состоит только из ccc. Точно так же, как и d9 и d10 ==> все они попадают в один класс.
И так далее.
Но как нумеруются эти классы (автор называет их почему-то рангами) - вот вопрос.
19 дек 09, 22:33    [8091362]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
iap
Но как нумеруются эти классы (автор называет их почему-то рангами) - вот вопрос.
пусть он сам скажет; я решил довериться номерам, которые возвращает dense_rank()
Очевидный вариант - с составлением списков через for xml, ранжированием их и последующей разбивкой на слова (т.е. на "виды одежды" - шорты, майки етц). Число "видов одежд" - до 2048 (по числу строк в счетчике master..spt_values). Разумеется, на больших объёмах надо делать по-другому, этот вариант - так, "тряпочный":
declare @t table(d char(3), s char(3))
insert @t
select 'd1', 'aaa' union all
select 'd1', 'bbb' union all
select 'd2', 'bbb' union all
select 'd3', 'bbb' union all
select 'd4', 'aaa' union all
select 'd4', 'ccc' union all
select 'd5', 'aaa' union all
select 'd6', 'ccc' union all
select 'd7', 'bbb' union all
select 'd7', 'ccc' union all
select 'd8', 'aaa' union all
select 'd8', 'bbb' union all
select 'd8', 'ccc' union all
select 'd9', 'ccc' union all
select 'd10', 'ccc'

;with 
x as(select distinct d from @t)
,r as
(
select d,ss,rk=dense_rank()over(order by ss,d),nw=len(ss)-len(replace(ss,',',''))
from(
select d,(select xs.s+',' from @t xs where xs.d=x.d order by xs.s for xml path(''))ss
from x
)w
)
select r.rk,r.d
      ,s=left(substring(r.ss,4*m.number+1,4*m.number+4),3)
from r
join master..spt_values m on m.type='P' and m.number<r.nw
order by r.rk
rkds
1d5 aaa
2d1 aaa
2d1 bbb
3d8 aaa
3d8 bbb
3d8 ccc
4d4 aaa
4d4 ccc
5d2 bbb
6d3 bbb
7d7 bbb
7d7 ccc
8d10ccc
9d6 ccc
10d9 ccc
19 дек 09, 23:08    [8091448]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
Anddros
Member

Откуда:
Сообщений: 1077
Проще простого. Две строчки. :)

declare @t table (d varchar(10), a varchar(10))
insert @t select 'd1',	'aaa'
union all select 'd1',	'bbb'
union all select 'd2',	'bbb'
union all select 'd3',	'bbb'
union all select 'd4',	'aaa'
union all select 'd4',	'ccc'
union all select 'd5',	'aaa'
union all select 'd6',	'ccc'
union all select 'd7',	'bbb'
union all select 'd7',	'ccc'
union all select 'd8',	'aaa'
union all select 'd8',	'bbb'
union all select 'd8',	'ccc'
union all select 'd9',	'ccc'

;with q as (select d, a, (select a+',' as 'text()' from @t t2 where t1.d=t2.d order by a for xml path(''))s from @t t1) 
select dense_rank()over(order by dm)r,a,d from(select d,a,s,min(d)over(partition by s)dm from q)t
19 дек 09, 23:09    [8091451]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
gigadedushka
Member

Откуда:
Сообщений: 182
По поводу того, что я их называю рангами. Я бы назвал их классами, но термина такого, думаю, никто не поймет))). iap, вы абсолютно правильно поняли мою мысль. Сейчас попробую разобраться в вашем коде. По поводу числа видов одежд. Разумеется в моей задаче - их бесконечное множество, которое нельзя заранее описать. Но, на одном человеке число одежд небольшое число. Порядка 10, однако что это за одежда угадать нельзя, ввиду того, что ее бесконечное множество и оно постоянно растет...
20 дек 09, 13:44    [8092179]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
gigadedushka
Member

Откуда:
Сообщений: 182
Я вас правильно понимаю: вы группируйте строки, выводя все виды в одну, разделенную через запятую, а затем ранжируйте по ней? В принципе такое решение, думаю, мне подойдет (хотя работа со строковыми типами в SQL - задача медленная, но в виду малого кооличества записей, он справится с ней относительно быстро). Сейчас попробую переписать его на мою задачу. Спасибо.
20 дек 09, 13:49    [8092193]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
gigadedushka
Member

Откуда:
Сообщений: 182
Что означает text() в куске: select a+',' as 'text()'? Я обычно всегда data() писал))), но никогда не придавал этому смысла
20 дек 09, 15:09    [8092323]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
gigadedushka
Что означает text() в куске: select a+',' as 'text()'? Я обычно всегда data() писал))), но никогда не придавал этому смысла
совсем ничего там не пишите, и дело с концом!
20 дек 09, 15:41    [8092351]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
gigadedushka
Member

Откуда:
Сообщений: 182
Если ничего не писать, он теги добавит)))
В общем вроде работает, переписал на свой лад, ща проверять буду для всех случаев. Спасибо всем за помощь.
20 дек 09, 15:43    [8092353]     Ответить | Цитировать Сообщить модератору
 Re: Ранжирование  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
gigadedushka
Если ничего не писать, он теги добавит)))
Да что Вы говорите!
20 дек 09, 15:51    [8092367]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить