loading..
Русский    English
16:12
листать

Агрегатная функция от агрегатной функции стр. 1

Давайте рассмотрим такую задачу:

Найти максимальное значение среди средних цен ПК, посчитанных для каждого производителя отдельно.


Посчитать средние значения стоимости по производителям труда не составляет:

Консоль
Выполнить
  1. SELECT AVG(price) avg_price
  2. FROM Product P JOIN PC ON P.model = PC.model 
  3. GROUP BY maker;

Однако стандарт запрещает использовать подзапрос в качестве аргумента агрегатной функции, т.е. нельзя решить задачу следующим способом:

Консоль
Выполнить
  1. SELECT MAX(
  2. SELECT AVG(price) avg_price
  3. FROM Product P JOIN PC ON P.model = PC.model 
  4. GROUP BY maker
  5. );

В подобных случаях используется подзапрос в предложении FROM:

Консоль
Выполнить
  1. SELECT MAX(avg_price) 
  2. FROM (SELECT AVG(price) avg_price
  3.       FROM Product P JOIN PC ON P.model = PC.model 
  4.       GROUP BY maker
  5.      ) X;

С помощью новых возможностей языка – оконных функций - эту задачу можно решить без подзапроса:

Консоль
Выполнить
  1. SELECT DISTINCT MAX(AVG(price)) OVER () max_avg_price
  2.       FROM Product P JOIN PC ON P.model = PC.model 
  3.      GROUP BY maker;

Обратите внимание, что оконные функции допускают использование агрегатной функции в качестве аргумента. Ключевое слово DISTINCT необходимо здесь, поскольку максимальное значение, подсчитанное по всему набору средних значений, будет «приписано» каждому производителю.

Стандарт также запрещает использовать агрегатную функцию как аргумент другой агрегатной функции. Т.е. мы не можем решить нашу задачу следующим образом:

    

  1. SELECT MAX(AVG(price)) max_avg_price
  2.     FROM Product P JOIN PC ON P.model = PC.model 
  3.     GROUP BY maker;

Но не бывает правил без исключений. Как ни странно, но в Oracle подобные конструкции работают, и вышеприведенный запрос даст результат:

MAX_AVG_PRICE
850

Чтобы убедиться в этом, зайдите на страницу задач обучающего этапа на сайте sql-ex.ru, выберите Oracle в списке СУБД и выполните запрос с флажком "Без проверки".

Кстати говоря, решение с использованием оконной функции также будет работать в Oracle. Могу предположить, что решение без оконной функции фактически её и использует, неявно подразумевая предложение OVER().

Bookmark and Share
Страницы: 1 2 3
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
месяц назад
продать waves . Как сделать фрезер своими руками?
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.