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

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

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

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

SELECT AVG(price) avg_price  
FROM Product P 
    JOIN PC ON P.model = PC.model   
GROUP BY maker;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

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

SELECT MAX(  
           SELECT AVG(price) avg_price  
           FROM Product P 
                JOIN PC ON P.model = PC.model   
           GROUP BY maker  
          );

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

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

SELECT MAX(avg_price)   
FROM (SELECT AVG(price) avg_price  
      FROM Product P 
           JOIN PC ON P.model = PC.model   
      GROUP BY maker  
     ) X;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

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

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

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

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

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

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

oracle

🚫

[[ error ]]

[[ column ]]
[[ value ]]

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

MAX_AVG_PRICE
850

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

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

Наверняка, вам встретятся решения подобных задач на основе сортировки с ограничением на число строк результирующего набора. Однако такие решения не являются легитимными с точки зрения стандарта языка и, как следствие, имеют различный синтаксис в разных реализациях. В качестве примера приведу решения нашей задачи в диалектах SQL Server и MySQL.

SQL Server

SELECT TOP 1 AVG(price) avg_price  
FROM Product P 
    JOIN PC ON P.model = PC.model   
GROUP BY maker  
ORDER BY avg_price DESC;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

MySQL

SELECT AVG(price) avg_price  
FROM Product P 
    JOIN PC ON P.model = PC.model  
GROUP BY maker  
ORDER BY avg_price DESC  
LIMIT 1;

mysql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Оба этих решения берут только первую строку из отсортированного по убыванию набора средних цен.

У начинающих изучать язык SQL зачастую вызывает проблему определение производителя, для которого достигается искомый максимум/минимум. Другими словами, требуется найти максимальную среднюю цену и производителя, средняя цена ПК которого совпадает с этой максимальной средней ценой.

Нестандартными средствами эта задача решается фактически рассмотренным выше запросом:

SELECT TOP 1 maker, AVG(price) avg_price  
FROM Product P 
    JOIN PC ON P.model = PC.model   
GROUP BY maker  
ORDER BY avg_price DESC;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Использование maker в списке столбцов предложения SELECT вполне допустимо, т.к. по этому столбцу выполняется группировка. Однако тут имеется одна «ловушка». Она связана с тем, что максимум может достигаться для нескольких производителей, и в данной постановке задачи их нужно выводить всех, в то время как мы ограничиваем выборку только одной (первой) строкой. На этот случай диалект T-SQL имеет дополнительную конструкцию WITH TIES. Логически правильное решение будет иметь вид:

SELECT TOP 1 WITH TIES maker, AVG(price) avg_price  
FROM Product P 
    JOIN PC ON P.model = PC.model   
GROUP BY maker  
ORDER BY avg_price DESC;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Однако, если иметь в виду проблему переносимости кода, то следует предпочесть решение, использующее стандартные средства.

Замечание

На сайте SQL-EX.RU проблема переносимости кода возникла в связи с нашим намерением реализовать упражнения для различных СУБД. Реализация потребовала бы минимальных средств, если бы тестовые решения, используемые для проверки, работали бы на всех предполагаемых СУБД без изменения своего кода. Поэтому следование стандарту может являться одним из требований тех. задания на проект. Приведем ниже несколько стандартных решений рассматриваемой задачи.

1. Использование предложения ALL в предложении WHERE

SELECT maker, avg_price   
FROM (SELECT maker, AVG(price) avg_price  
      FROM Product P 
        JOIN PC ON P.model=PC.model   
      GROUP BY maker  
     ) X  
WHERE avg_price >= ALL(SELECT AVG(price) avg_price  
                       FROM Product P 
                           JOIN PC ON P.model=PC.model   
                       GROUP BY maker  
                      );

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

На естественном языке этот запрос звучит следующим образом: «Найти производителей, средняя цена на ПК у которых не меньше, чем средние цены у КАЖДОГО из производителей ПК».

2. Использование внутреннего соединения

SELECT maker, avg_price   
FROM (SELECT maker, AVG(price) avg_price  
      FROM Product P 
          JOIN PC on P.model=PC.model   
      GROUP BY maker  
      ) X 
    JOIN   
     (SELECT MAX(avg_price) max_price   
      FROM (SELECT maker, AVG(price) avg_price  
            FROM Product P 
                JOIN PC on P.model=PC.model   
            GROUP BY maker  
           ) X  
     ) Y ON avg_price = max_price;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Здесь мы соединяем подзапрос, определяющий производителей и средние цены на их ПК, с подзапросом, в котором определяется максимальная средняя цена. Соединение выполняется по условию равенства средней цены из первого подзапроса с максимальной ценой из второго.

3. Использование предиката ALL в предложении HAVING

SELECT maker, AVG(price) avg_price  
FROM Product P 
    JOIN PC on P.model=PC.model   
GROUP BY maker  
HAVING AVG(price) >= ALL(SELECT AVG(price)   
                         FROM Product P 
                             JOIN PC ON P.model=PC.model   
                         GROUP BY maker  
                        );

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Это решение отличается от первого варианта отсутствием «лишнего» запроса, который пришлось написать лишь затем, чтобы была возможность использовать алиас avg_price в предложении WHERE (смотри порядок обработки предложений оператора SELECT); с другой стороны, использование в предложении WHERE агрегатной функции также запрещено правилами языка.

Все приведенные стандартные решения выглядят тяжеловесными, хотя и будут работать практически во всех СУБД. Эта громоздкость объясняется повторением в коде фактически одного и того же запроса. Однако общие табличные выражения – CTE, которые были введены в последних версиях стандарта, позволяют многократно ссылаться на один раз сформулированный запрос. Например, решения 1, 3 с помощью CTE можно записать в таком виде:

WITH cte(maker, avg_price)  
AS (SELECT maker, AVG(price) avg_price  
    FROM Product P 
        JOIN PC on P.model=PC.model   
    GROUP BY maker  
    )  
SELECT *  
FROM cte   
WHERE avg_price >= ALL(SELECT avg_price   
                       FROM cte  
                      );

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Замечу, что поддержка общих табличных выражений появилась в Oracle 9.2, в SQL Server 2005, в PostgreSQL 8.4 и в MySQL 8.0.

#AVG #MAX #DISTINCT #Oracle #Агрегатные Функции #Оконные Функции #MySQL #TOP N WITH TIES #LIMIT #HAVING #Внутреннее Соединение #ALL #PostgreSQL #CTE #Общие Табличные Выражения

Сортировка и NULL-значения Произведение значений столбца