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

Откуда:
Сообщений: 7
Ну вот никак не могу себя заставить думать "по-SQL-ному".
Такой простой запрос, но я не пойму как мне его осуществить...
Суть такая: мне нужно выбрать из таблицы записи, погруппировав их по одному полю, например:
SELECT type, MAX(price)
FROM titles
WHERE royalty = 10 AND advance < 8000
GROUP BY type
Но мне нужно получить ещё и другие поля той записи, у которой значение поля "price" максимально в группе.
Если я пишу так:
SELECT type, MAX(price), title_id
FROM titles
WHERE royalty = 10 AND advance < 8000 AND price=MAX(price)
GROUP BY type
получаю ошибку, что функцию MAX нельзя использовать в WHERE (а только в HAVING).
13 ноя 09, 10:15    [7924005]     Ответить | Цитировать Сообщить модератору
 Re: Как получить "неагрегированные" поля из групп?  [new]
pacha
Member

Откуда:
Сообщений: 239
Что непонятно? Группировать надо по всем полям по которым нет агрегатов
13 ноя 09, 10:23    [7924075]     Ответить | Цитировать Сообщить модератору
 Re: Как получить "неагрегированные" поля из групп?  [new]
iap
Member

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

решение зависит от версии сервера, а Вы её не написали.
SQL2005/2008
Вариант 1
WITH Prices AS(SELECT RANK()OVER(PARTITION BY type ORDER BY price DESC) N,* FROM titles)
SELECT * FROM Prices WHERE N=1;
Вариант 2
SELECT TOP(1) WITH TIES *
FROM titles
ORDER BY RANK()OVER(PARTITION BY type ORDER BY price DESC);
13 ноя 09, 10:25    [7924084]     Ответить | Цитировать Сообщить модератору
 Re: Как получить "неагрегированные" поля из групп?  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
poli-smen версия какая?
--------------------------------------------------------------
Дьявол кроется в деталях.
13 ноя 09, 10:26    [7924091]     Ответить | Цитировать Сообщить модератору
 Re: Как получить "неагрегированные" поля из групп?  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
SQL2000
SELECT *
FROM titles T
WHERE T.price=(SELECT MAX(TT.price) FROM titles TT WHERE TT.type=T.type);
13 ноя 09, 10:27    [7924098]     Ответить | Цитировать Сообщить модератору
 Re: Как получить "неагрегированные" поля из групп?  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
iap :)
осталось ещё только для
4.2
4.21
6.0
6.5
7.0
--------------------------------------------------------------
Дьявол кроется в деталях.
13 ноя 09, 10:31    [7924118]     Ответить | Цитировать Сообщить модератору
 Re: Как получить "неагрегированные" поля из групп?  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Дедушка
iap :)
осталось ещё только для
4.2
4.21
6.0
6.5
7.0
--------------------------------------------------------------
Дьявол кроется в деталях.
А тот, который для 2000-го, не подойдёт ли?
13 ноя 09, 10:33    [7924130]     Ответить | Цитировать Сообщить модератору
 Re: Как получить "неагрегированные" поля из групп?  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
iap
А тот, который для 2000-го, не подойдёт ли?

ну если гипотетически предположить, что автору жутко нужно видеть сообщение о проходе агрегатной ф-ии через null то наверное не подойдёт :)
хотя я не спец по ранним версиям...
13 ноя 09, 10:50    [7924262]     Ответить | Цитировать Сообщить модератору
 Re: Как получить "неагрегированные" поля из групп?  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Дедушка
iap
А тот, который для 2000-го, не подойдёт ли?

ну если гипотетически предположить, что автору жутко нужно видеть сообщение о проходе агрегатной ф-ии через null то наверное не подойдёт :)
хотя я не спец по ранним версиям...
Упс!
А про
royalty = 10 AND advance < 8000 
я как-то забыл!
SQL2005/2008
Вариант 1
WITH Prices AS(SELECT RANK()OVER(PARTITION BY type ORDER BY price DESC) N,* FROM titles WHERE royalty=10 AND advance<8000)
SELECT * FROM Prices WHERE N=1;
Вариант 2
SELECT TOP(1) WITH TIES *
FROM titles
WHERE royalty=10 AND advance<8000
ORDER BY RANK()OVER(PARTITION BY type ORDER BY price DESC);

SQL2000
SELECT *
FROM titles T
WHERE T.royalty=10 AND T.advance<8000
  AND T.price=(SELECT MAX(TT.price) FROM titles TT WHERE TT.royalty=10 AND TT.advance<8000 AND TT.type=T.type);
Что касается price IS NULL для некоторого гипотетического type, то мне кажется, что это в данном случае вряд ли будет иметь место.
На всякий случай можно выполнить
SET ANSI_WARNINGS OFF;
перед запросом
13 ноя 09, 11:08    [7924422]     Ответить | Цитировать Сообщить модератору
 Re: Как получить "неагрегированные" поля из групп?  [new]
poli-smen
Member

Откуда:
Сообщений: 7
iap
решение зависит от версии сервера, а Вы её не написали.

Извините, забыл. Версия сервера MSDE 2000. И ещё забыл написать, что в примерах экспериментировал над стандартной таблицей titles из каталога pubs.
13 ноя 09, 11:11    [7924459]     Ответить | Цитировать Сообщить модератору
 Re: Как получить "неагрегированные" поля из групп?  [new]
poli-smen
Member

Откуда:
Сообщений: 7
Дедушка

SQL2000
SELECT *
FROM titles T
WHERE T.royalty=10 AND T.advance<8000
  AND T.price=(SELECT MAX(TT.price) FROM titles TT WHERE TT.royalty=10 AND TT.advance<8000 AND TT.type=T.type);

Вот, так и я написал... :) Но вот сдвоенный запрос режет глаза... :(
Тоесть, если критериев запроса будет много (типа WHERE field1='a' AND field2='b' AND field3='c' AND ...), то их, получается, нужно дважды писать в тексте запроса? А если какой-то критерий изменится, его нужно править в двух местах? И по-другому на MSDE 2000 нельзя? :(
13 ноя 09, 11:28    [7924611]     Ответить | Цитировать Сообщить модератору
 Re: Как получить "неагрегированные" поля из групп?  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
вы однако так провели цитирование что создаётся впечатление будто это я вам написал... хотя на самом деле это вариант iap. соблюдайте копирайт :)
а по сути вопроса - замените явные значения на переменные.
--------------------------------------------------------------
Дьявол кроется в деталях.
13 ноя 09, 11:51    [7924833]     Ответить | Цитировать Сообщить модератору
 Re: Как получить "неагрегированные" поля из групп?  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
poli-smen
сдвоенный запрос режет глаза... :(
Тоесть, если критериев запроса будет много (типа WHERE field1='a' AND field2='b' AND field3='c' AND ...), то их, получается, нужно дважды писать в тексте запроса?
если SS 2000, то попробуйте соединение полей в строку с последующим "парсингом" этой строки для вытряхивания из неё значений и приведением их к исходному типу (изврат, конечно; но зато критерий запроса будет упомянут только один раз :-))
declare @t table(type varchar(10),price numeric(12,2),royalty numeric(12,2),advance numeric(12,2))
insert @t select 'aaa',15200,10,7570
insert @t select 'aaa',15300,10,7520
insert @t select 'aaa',15400,10,7530
insert @t select 'aaa',15500,10,7540
insert @t select 'aaa',15250,10,7550
insert @t select 'aaa',15100,10,7560

select 
   type
  ,maxPrice=cast(substring(maxs,2,14) as numeric(12,2))
  ,royalty2maxPrice=cast(substring(maxs,17,14) as numeric(12,2))
  ,advance4maxPrice=cast(substring(maxs,32,14) as numeric(12,2))
from(
  select type, maxs=max( cast(1000000000+price as char(15)) + cast(1000000000+royalty as char(15)) + cast(1000000000+advance as char(15)) )
  from @t
  where royalty = 10 and advance < 8000
  group by type
)t
result:
typemaxPriceroyalty2maxPriceadvance4maxPrice
aaa15500.0010.007540.00
13 ноя 09, 13:35    [7925805]     Ответить | Цитировать Сообщить модератору
 Re: Как получить "неагрегированные" поля из групп?  [new]
poli-smen
Member

Откуда:
Сообщений: 7
Дедушка
вы однако так провели цитирование что создаётся впечатление будто это я вам написал... хотя на самом деле это вариант iap. соблюдайте копирайт :)

Ой! Действительно! :) На этом форуме цитирование немного неудобно сделано (или я не разобрался)...
Дедушка
а по сути вопроса - замените явные значения на переменные.

Ну это сути особо не меняет. Под выражением "какой-то критерий изменится" я имел ввиду не только значения полей, а полностью содержимое WHERE...
16 ноя 09, 12:51    [7933239]     Ответить | Цитировать Сообщить модератору
 Re: Как получить "неагрегированные" поля из групп?  [new]
poli-smen
Member

Откуда:
Сообщений: 7
Ozzy-Osbourne
poli-smen
сдвоенный запрос режет глаза... :(
Тоесть, если критериев запроса будет много (типа WHERE field1='a' AND field2='b' AND field3='c' AND ...), то их, получается, нужно дважды писать в тексте запроса?
если SS 2000, то попробуйте соединение полей в строку с последующим "парсингом" этой строки для вытряхивания из неё значений и приведением их к исходному типу (изврат, конечно; но зато критерий запроса будет упомянут только один раз :-))
declare @t table(type varchar(10),price numeric(12,2),royalty numeric(12,2),advance numeric(12,2))
insert @t select 'aaa',15200,10,7570
insert @t select 'aaa',15300,10,7520
insert @t select 'aaa',15400,10,7530
insert @t select 'aaa',15500,10,7540
insert @t select 'aaa',15250,10,7550
insert @t select 'aaa',15100,10,7560

select 
   type
  ,maxPrice=cast(substring(maxs,2,14) as numeric(12,2))
  ,royalty2maxPrice=cast(substring(maxs,17,14) as numeric(12,2))
  ,advance4maxPrice=cast(substring(maxs,32,14) as numeric(12,2))
from(
  select type, maxs=max( cast(1000000000+price as char(15)) + cast(1000000000+royalty as char(15)) + cast(1000000000+advance as char(15)) )
  from @t
  where royalty = 10 and advance < 8000
  group by type
)t
result:
typetmaxPricetroyalty2maxPricetadvance4maxPrice
aaat15500.00t10.00t7540.00

Это действительно изврат... Таким вариантом я стараюсь не злоупотреблять, даже если это оправданно, например если в таблице фамилия, имя и отчество в разных полях, то я предпочту получить их именно в трёх полях (т.е. необъединяя), даже если в конечном счёте мне нужна объединённая строка из фамилии, имени и отчества...
16 ноя 09, 12:59    [7933300]     Ответить | Цитировать Сообщить модератору
 Re: Как получить "неагрегированные" поля из групп?  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
poli-smen
Таким вариантом я стараюсь не злоупотреблять
дык никто и не настаивает; но Вы спросили, как избежать в 2000 повторения кода. Что мешает перейти на 2005 или 2008, ведь на дворе скоро смена десятилетия будет...
16 ноя 09, 13:21    [7933479]     Ответить | Цитировать Сообщить модератору
 Re: Как получить "неагрегированные" поля из групп?  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Ozzy-Osbourne
ведь на дворе скоро смена десятилетия будет...
Целый год и полтора месяца ещё!
Простите за оффтоп.
16 ноя 09, 13:27    [7933533]     Ответить | Цитировать Сообщить модератору
 Re: Как получить "неагрегированные" поля из групп?  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
iap
Ozzy-Osbourne
ведь на дворе скоро смена десятилетия будет...
Целый год и полтора месяца ещё!
Простите за оффтоп.
ну, неважно: имхо, возможности 2005 по сравнению с 2000 заслуживают того, чтобы перейти на него и не париться с "повторами критериев"
16 ноя 09, 13:37    [7933626]     Ответить | Цитировать Сообщить модератору
 Re: Как получить "неагрегированные" поля из групп?  [new]
poli-smen
Member

Откуда:
Сообщений: 7
Ozzy-Osbourne
poli-smen
Таким вариантом я стараюсь не злоупотреблять
дык никто и не настаивает; но Вы спросили, как избежать в 2000 повторения кода. Что мешает перейти на 2005 или 2008, ведь на дворе скоро смена десятилетия будет...

Ничего не мешает. Просто моё приложение должно работать на любой версии, начиная с 2000...
16 ноя 09, 13:47    [7933708]     Ответить | Цитировать Сообщить модератору
 Re: Как получить "неагрегированные" поля из групп?  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
poli-smen
приложение должно работать на любой версии, начиная с 2000...
ну, попробуйте еще так:
declare @t table(type varchar(10),price numeric(12,2),royalty numeric(12,2),advance numeric(12,2))
insert @t select 'aaa',15200,10,7570
insert @t select 'aaa',15300,10,7520
insert @t select 'aaa',15400,10,7530
insert @t select 'aaa',15500,10,7540 -- first row with max price for type='aaa'
insert @t select 'aaa',15250,10,7550
insert @t select 'aaa',15100,10,7560
insert @t select 'aaa',15500,10,7090 --second row with max price for type='aaa'

insert @t select 'bbb',13400,10,7540  -- first row with max price for type='bbb'
insert @t select 'bbb',13250,10,7550
insert @t select 'bbb',13297,10,7550
insert @t select 'bbb',13325,10,7550
insert @t select 'bbb',13400,10,7560 -- second row with max price for type='bbb'
insert @t select 'bbb',13250,10,7090

select t0.t,t1.maxPrice,r4maxPrice=t0.r,a4maxPrice=t0.a
from(
select t=type,r=royalty,a=advance,p=price from @t
)t0
join (select t=type,maxPrice=max(price) from @t where royalty=10 group by type) t1 
  on t0.t=t1.t and t0.p=t1.maxPrice
result:
tmaxPricer4maxPricea4maxPrice
aaa15500.0010.007540.00
aaa15500.0010.007090.00
bbb13400.0010.007560.00
bbb13400.0010.007540.00
16 ноя 09, 14:06    [7933913]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить