Команда SQL, чтобы получить поле максимального значения, не делая два выбора

Я начинаю изучать SQL и работаю над этим упражнением: у меня есть таблица «книги», в которой содержится информация о каждой книге (включая цену и идентификатор жанра). Мне нужно получить название жанра с самой высокой средней ценой. Я полагаю, что мне сначала нужно сгруппировать цены по жанрам, а затем получить название самой высокой.

Я знаю, что могу получить результаты GENRE VS COST следующим образом:

select b.genre, 
       round(avg(b.price),2) as cost 
from books b 
group by b.genre;

Мой вопрос: чтобы получить жанр с самой высокой ценой AVG из этого результата, мне нужно сделать:

select aux.genre 
from (
   select b.genre, 
          round(avg(b.price),2) as cost 
   from books b 
   group by b.genre
) aux 
where aux.cost = (select max(aux.cost) 
                  from (
                      select b.genre, 
                             round(avg(b.price),2) as cost 
                      from books l 
                      group by b.genre
                  ) aux);

Это плохая практика или нет другого пути? Я получаю правильный результат, но мне неудобно создавать два раза один и тот же выбор.

Я не использую PL SQL, поэтому не могу использовать переменные или что-то в этом роде.

Любая помощь будет оценена. Заранее спасибо!


sql oracle max
person A. Capelo    schedule 08.11.2012    source источник

Ответы (4)


arrow_upward
5
arrow_downward

На сервере Sql вы можете использовать avg aggragate внутри оконной функции row_number.

 with m as(
    select genre, 
         avg(price) cost, 
         row_number() over(order by avg(price) desc) rw
    from books
    group by genre
 )
 select * from m
 where rw=1
person jmoreno    schedule 08.11.2012
comment
Вы уверены, что можете вложить агрегат в предложение over оконной функции? - person a_horse_with_no_name; 09.11.2012
comment
@a_horse_with_no_name — отлично работает. скрипт SQL - person Martin Smith; 09.11.2012
comment
@MartinSmith: Это круто. Кажется, он также работает с Oracle и PostgreSQL. И при использовании rank() вместо row_number() связи также обрабатываются. Очень элегантное решение. - person a_horse_with_no_name; 09.11.2012
comment
Да. Обратите внимание, что в отличие от решений, использующих top, это, естественно, включает ВСЕ связи, так что если у вас есть 3 жанра с одним и тем же средним вторым местом, и вы хотите получить 3 лучших средних, вы получите 5 строк назад (уникальное 1-е место, 3 2 место, уникальное 3 место). - person jmoreno; 09.11.2012
comment
@jmoreno: но только при использовании rank() (или даже dense_rank()) - person a_horse_with_no_name; 09.11.2012
comment
Я не знаком с командой over, но из того, что я прочитал, это кажется хорошим решением. По сути, он устанавливает номер строки в порядке убывания средней цены, поэтому, когда вы пишете, где rw=1, это похоже на выбор первой позиции в MS-SQL, верно? - person A. Capelo; 09.11.2012
comment
Верно. Основное различие заключается в том, что существует более 1 жанра с одинаковым средним значением. - person jmoreno; 09.11.2012

arrow_upward
1
arrow_downward 

with avg_price as (
   select b.genre, 
          round(avg(b.price),2) as cost 
   from books b 
   group by b.genre
)
select genre 
from avg_price
where cost = (select max(cost) from avg_price);

(Это стандартный SQL ANSI и работает во всех современных СУБД)

person a_horse_with_no_name    schedule 08.11.2012
comment
Вау, я пробовал это с .. частью раньше, но безрезультатно. Это как вид?? - person A. Capelo; 09.11.2012
comment
@AntónioCapelo: это называется общим табличным выражением, и да, вы можете думать об этом как о представлении на лету. - person a_horse_with_no_name; 09.11.2012
comment
Хороший! можете ли вы сказать мне, является ли это самым легким решением, или есть более быстрый способ получить эти результаты? Просто чтобы убедиться, что я на правильном пути :) - person A. Capelo; 09.11.2012
comment
@AntónioCapelo: может быть (!) Решение jmoreno может быть быстрее. Но вам нужно будет проверить свои данные. Сравните планы выполнения для обоих решений, и вы увидите, какое из них более эффективно. Но я не ожидаю действительно большой разницы. - person a_horse_with_no_name; 09.11.2012

arrow_upward
0
arrow_downward

В MS-SQL вы можете сделать это:

SELECT TOP 1 genre, ROUND(AVG(price),2) AS cost 
FROM books
GROUP BY genre
ORDER BY ROUND(AVG(price),2) DESC

(предложение TOP специфично для MS-SQL, но аналогичные конструкции есть и в других СУБД)

person pkmiec    schedule 08.11.2012
comment
На самом деле, я использую Oracle в качестве СУБД, поэтому это не сработало. Я обнаружил, что вместо этого мне нужно использовать условие rownum = 1. Но я не уверен, куда его поместить, так как я использую группу по... Но я посмотрю на это. Спасибо! - person A. Capelo; 09.11.2012

arrow_upward

arrow_downward

person    schedule
comment
Это вернет только одну строку, даже если два жанра имеют одинаковую максимальную стоимость (и это не работает с Oracle, но мы пока не знаем этого наверняка). - person a_horse_with_no_name; 09.11.2012
comment
@a_horse_with_no_name — если SQL Server, то можно использовать TOP 1 WITH TIES. - person Martin Smith; 09.11.2012
comment
ребята, спасибо за упоминание об этой функции. Моя беда, забыл. - person pkuderov; 09.11.2012
comment
будет ли в этом случае работать where rownum = 1 вместо top 1? - person pkuderov; 09.11.2012