Агрегатные функции и Вложенные агрегатные функции или подзапрос в предложении FROM, Предложение HAVING, порядок выполнения запроса кратко

Практика

агрегатные функции

Агрегатные функции (англ. aggregate function) — это математические функции, применяемые к набору входных данных и возвращающие по ним одно результирующее значение.

Агрегатные функции используются для обобщения данных в языках программирования (включая языки управления данными в СУБД), электронных таблицах и реляционной алгебе.

К числу агрегатных функций относят, например, следующие функции:

SUM (сумма)
MAX(максимальное значение)
MIN (минимальное значение)
COUNT (количество значений)
AVG (среднее значение, обычно среднее арифметическое)
MODE (мода)
MEDIAN (медиана)

Агрегатные функции и Вложенные агрегатные функции или подзапрос в предложении FROM, Предложение HAVING, порядок выполнения запроса

Примеры использования

В следующем примере рассматривается использование агрегатных функций в языке запросов SQL в реляционной базе данных.

Например, мы можем найти наиболее высокую низкую температуру, создав запрос:

  SELECT max(temp_lo) FROM weather;

Представим структуру нашей таблицы Orders в таком виде:

num	amt	date
1	100	2036-01-01
2	578	2038-08-01
3	200	2036-08-10

1)Получить сумму всех заказов из таблицы Orders, которые были совершены в 2016 году.

  SELECT SUM(amt) 
FROM Orders 
WHERE odate BETWEEN '2036-01-01' and '2036-12-31';

В результате получим:

sum(amt)
300

2)Вывести среднюю стоимость заказа из таблицы Orders.

 SELECT AVG(amt) FROM Orders;

В результате получим:

avg(amt)
292.6

Вложенные агрегатные функци

Вложенные агрегатные функции или подзапрос в предложении FROM

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

средние значения стоимости по производителям не сложно

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

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

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

как это можно решить?
используйте подзапрос в предложении 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;

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

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

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

предложение having

Если предложение WHERE определяет предикат для фильтрации строк, то предложение HAVING применяется после группировки для определения аналогичного предиката, фильтрующего группы по значениям агрегатных функций. Это предложение необходимо для проверки значений, которые получены с помощью агрегатной функции не из отдельных строк источника записей, определенного в предложении FROM, а из групп таких строк. Поэтому такая проверка не может содержаться в предложении WHERE.

Обратите внимание, что HAVING предложение применяет условие фильтрации к каждой группе строк, а WHEREпредложение применяет условие фильтрации к каждой отдельной строке.

Предложение HAVING полезно только тогда, когда вы используете его с GROUP BYпредложением для генерации вывода высокоуровневых отчетов. Например, вы можете использовать это HAVING предложение, чтобы ответить на такие вопросы, как определение количества заказов в этом месяце, этом квартале или этом году, у которых общий объем продаж превышает 10 тысяч.

WHERE предложение вводит условие для отдельных строк ; HAVING Предложение вводит условие для агрегирования , т. е. результатов выбора, когда один результат, такой как количество, среднее, минимальное, максимальное или сумма, был получен из нескольких строк. Ваш запрос требует второго типа условия (то есть условия агрегирования), следовательно, HAVING работает правильно.

Как правило, используйте WHERE до GROUP BY и HAVING после GROUP BY. Это довольно примитивное правило, но оно полезнов большинстве случаев.

Пример 5.5.5

Получить количество ПК и среднюю цену для каждой модели, средняя цена которой менее $800

SELECT model, COUNT(model) AS Qty_model,
AVG(price) AS Avg_price
FROM PC
GROUP BY model
HAVING AVG(price) < 800;

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

model	Qty_model	Avg_price
1232	4	425
1260	1	350

порядок выполнения запроса

Заметим, что в предложении HAVING нельзя использовать псевдоним (Avg_price), используемый для именования значений агрегатной функции в предложении SELECT. Дело в том, что предложение SELECT, формирующее выходной набор запроса, выполняется предпоследним перед предложением ORDER BY. Ниже приведен порядок обработки предложений в операторе SELECT:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

Этот порядок не соответствует синтаксическому порядку общего представления оператора SELECT, который ближе к естественному языку:

SELECT [DISTINCT | ALL]{*
| [<выражение для столбца> [[AS] <псевдоним>]] [,…]}
FROM <имя таблицы> [[AS] <псевдоним>] [,…]
[WHERE <предикат>]
[[GROUP BY <список столбцов>]
[HAVING <условие на агрегатные значения>] ]
[ORDER BY <список столбцов>]

Следует отметить, что предложение HAVING может использоваться и без предложения GROUP BY. При отсутствии предложения GROUP BY агрегатные функции применяются ко всему выходному набору строк запроса, т.е. в результате мы получим всего одну строку, если выходной набор не пуст.

Таким образом, если условие на агрегатные значения в предложение HAVING будет истинным, то эта строка будет выводиться, в противном случае мы не получим ни одной строки. Рассмотрим такой пример.

Пример 5.5.6

Найти максимальную, минимальную и среднюю цену на персональные компьютеры.

Решение этой задачи дает следующий запрос:

SELECT MIN(price) AS min_price,
MAX(price) AS max_price, AVG(price) avg_price
FROM PC;

результатам которого будет

min_price	max_price	avg_price
350.00	980.00	675.00

Если же мы добавим в условие ограничение, скажем, на среднюю цену:

Найти максимальную, минимальную и среднюю цену на персональные компьютеры при условии, что средняя цена не превышает $600:

SELECT MIN(price) AS min_price,
MAX(price) AS max_price, AVG(price) avg_price
FROM PC
HAVING AVG(price) <= 600;

то в результате получим пустой результирующий набор, т.к. 675.00 > 600.

Есть ли разница в производительности между HAVING и без использования HAVING?

Без использования

SELECT x + y AS z, t.*  FROM t
WHERE 
x = 1 and
x+y = 2

С HAVING

SELECT x + y AS z, t.*  FROM t
WHERE 
x = 1
HAVING
z = 2

HAVING используется для запросов, содержащих GROUP BY, или возвращает одну строку, содержащую результат агрегатных функций. Например, SELECT SUM(scores) FROM t HAVING SUM(scores) > 100 возвращает либо одну строку, либо строку вообще.

Второй запрос считается недействительным стандартом SQL и не принят некоторыми системами баз данных.

Да, есть разница, ожидается, чтопервый запрос будет быстрее.

Убедиться, что сначала выполняется основной запрос, а затем применяется фильтр-получатель - поэтому он в основном работает с набором данных, возвращаемым (запрос минус с).

Первый запрос должен быть предпочтительным, поскольку он вообще не выбирает эти записи.

Ответы на вопросы для самопроверки пишите в комментариях, мы проверим, или же задавайте свой вопрос по данной теме.

Агрегатные функции и Вложенные агрегатные функции или подзапрос в предложении FROM, Предложение HAVING, порядок выполнения запроса кратко