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

Откуда:
Сообщений: 5
Доброго времени суток! Требуется помошь знающих людей.

http://www.imageup.ru/img62/fuckin_sql231320.png

Первая таблица на рисунке создана запросом:
select * from Tours
join Themes on Tours.theme_id = Themes.id

Она исходная..

Требуется выбрать экскурсии по каждой тематике, которые с наибольшей посещаемостью.

Вторая таблица создана запросом:
select Tours.name, Themes.name as Тематика, MAX(Tours.attendance) as Посещаемость from Tours
join Themes on Tours.theme_id = Themes.id
group by Tours.name, Themes.name

Но результат не такой, какой надо. Тематика повторяется, экскурсия -Памятники и достопримечательности- - лишняя, т.к. там не макс. посещаемость.

Результат нормальный, если убрать их группировки Tours.name, но мне нужно как-то выводить это поле все равно.

Need help!
19 дек 09, 15:17    [8090397]     Ответить | Цитировать Сообщить модератору
 Re: Трудности группировки!  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
Тортег,

для 2005 и выше попробуйте так:
1) test data:
+
declare @tr table(id int identity, name varchar(20), cost int,dur int, dt datetime, attd int, theme_id int, type_id int)
declare @th table(id int, name varchar(20))
insert @tr select 'Водопады и озёра', 420,180,'20091217 23:30',5,1,1
insert @tr select 'Памятники',        552,120,'20091217 10:00',4,1,1
insert @tr select 'Бетонные джунгли',1200, 60,'20100420 12:00',3,5,2
insert @tr select 'Нерукотв. пам-ки', 100, 30,'20091217 23:00',5,4,3

insert @th select 1,'Историческая' union all select 5,'Архитектура' union all select 4,'Литература'

-- some more test data:
insert @th select 10,'Музыкальная' union all select 11,'Технологическая'

insert @tr select 'Deep Purple',    444,180,'20091217 23:30',  10, 10,1
insert @tr select 'Led Zeppelin',   560,120,'20091217 10:00',  14, 10,1
insert @tr select 'Black Sabbath', 1550, 60,'20100420 12:00',  30, 10,2
insert @tr select 'AC/DC',          120, 30,'20091217 23:00',  14, 10,3
insert @tr select 'Slade',          120, 30,'20091217 23:00',  19, 10,3
insert @tr select 'Queen',          120, 30,'20091217 23:00',  29, 10,3
insert @tr select 'Metallica',     1100, 60,'20100420 12:00',  30, 10,2
insert @tr select 'Строительство',   420,180,'20091217 23:30', 25, 11,1
insert @tr select 'Металлургия',     552,120,'20091217 10:00', 24, 11,1
insert @tr select 'Химпром',        1200, 60,'20100420 12:00', 23, 11,2
insert @tr select 'Ядерная энергия', 100, 30,'20091217 23:00', 25, 11,3
2) query:
select top 1 with ties *
from @th th
left join @tr tr on th.id=tr.theme_id
order by dense_rank()over(partition by th.id order by attd desc)
3) result:
idnameidnamecostdurdtattdtheme_idtype_id
4Литература4Нерукотв. пам-ки100302009-12-17 23:00:00.000543
5Архитектура3Бетонные джунгли1200602010-04-20 12:00:00.000352
10Музыкальная7Black Sabbath1550602010-04-20 12:00:00.00030102
10Музыкальная11Metallica1100602010-04-20 12:00:00.00030102
11Технологическая15Ядерная энергия100302009-12-17 23:00:00.00025113
11Технологическая12Строительство4201802009-12-17 23:30:00.00025111
1Историческая1Водопады и озёра4201802009-12-17 23:30:00.000511
Обратите внимание: если внутри одной и то же темы есть экскурсии с одинаковыми значениями посещаемости и эти значения - максимальные среди остальных экскурсий для этой темы, то выводить надо все такие строки.
19 дек 09, 19:18    [8090852]     Ответить | Цитировать Сообщить модератору
 Re: Трудности группировки!  [new]
Тортег
Member

Откуда:
Сообщений: 5
Большое спасибо за помощь! Но..

Я не понимаю как оно работает. Там кстати NULL'ы вылезли, но я их убрал, добавив
where Tours.attendance is not null
19 дек 09, 22:22    [8091332]     Ответить | Цитировать Сообщить модератору
 Re: Трудности группировки!  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
Тортег,

уберите сначала "top 1 with ties" и посмотрите, что будет выведено: строки будут "выстроены" в соотв-вии с некоторым приоритетом, который задан в аргументе order by. Этот аргумент есть одна из оконных функций - см. BOL, ranking functions. Что касается "top 1 with ties", то эта инструкция просто заставляет оставить в резалтсете только те строки, которые имеют (каждая в своей группе) "наивысший приоритет".
ЗЫ. На будущее: потрудитесь снабжать свои запросы скриптами, как это было сделано мной для вашей ситуации. Удовольствия от набивки DDL + вставок (за того, кому это надо), поверьте, немного.
19 дек 09, 22:34    [8091363]     Ответить | Цитировать Сообщить модератору
 Re: Трудности группировки!  [new]
Тортег
Member

Откуда:
Сообщений: 5
Почитал про функции, вроде стало понятнее. Переделал запрос вот так:
select top 1 with ties Tours.name 'Экскурсия', Themes.name 'Тематика', Tours.attendance 'Посещаемость' from Tours
join Themes on Tours.theme_id = Themes.id
order by dense_rank() over(partition by Themes.id order by attendance desc)

Вроде разницы нет..
20 дек 09, 15:07    [8092321]     Ответить | Цитировать Сообщить модератору
 Re: Трудности группировки!  [new]
iap
Member

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

dense_rank() в данном случае то же самое, что и rank()
(ибо остаются строки, для которых эти функции вернули 1).
Подозреваю, что rank() эффективнее, хотя на глаз не заметно.
20 дек 09, 15:56    [8092374]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить