В SQL имеется множество предопределенных агрегатных функций, которые можно использовать для написания запросов для получения именно такой информации. Предложение GROUP BY указывает, как группировать строки из таблицы данных при агрегировании информации, тогда как предложение HAVING отфильтровывает строки, которые не принадлежат указанные группы.
Агрегатные функции выполняют различные действия, такие как подсчет всех строк в таблице, усреднение данных столбца и суммирование числовых данных. Агрегаты также могут искать в таблице, чтобы найти самые высокие значения «MAX» или самые низкие значения «MIN» в столбце. Как и в случае других типов запросов, вы можете ограничить или отфильтровать строки, с которыми работают эти функции, с помощью предложения WHERE. Например, если менеджеру необходимо знать, сколько сотрудников работает в организации, для получения этой информации можно использовать агрегатную функцию COUNT (*). Функция COUNT (*), показанная в приведенном ниже операторе SELECT, подсчитывает все строки в Таблица.
SELECT COUNT(*) FROM employees; COUNT(*) ---------- 24
Таблица результатов для функции COUNT (*) представляет собой один столбец из одной строки, известный как скалярный результат или значение. Обратите внимание, что таблица результатов имеет заголовок столбца, который соответствует имени агрегатной функции, указанной в предложении SELECT.
Некоторые из часто используемых агрегатных функций приведены ниже:
SUM( [ALL | DISTINCT] expression ) AVG( [ALL | DISTINCT] expression ) COUNT( [ALL | DISTINCT] expression ) COUNT(*) MAX(expression) MIN(expression)
Ключевые слова ALL и DISTINCT являются необязательными и выполняются так же, как и с предложениями SELECT, которые вы научились писать. Ключевое слово ALL является значением по умолчанию, где эта опция разрешена. Выражение, указанное в синтаксисе, может быть константой, функцией, или любая комбинация имен столбцов, констант и функций, связанных арифметическими операторами. Однако агрегатные функции чаще всего используются с именем столбца. За исключением функции COUNT, все агрегатные функции не учитывают значения NULL.
Есть два правила, которые вы должны понимать и соблюдать при использовании агрегатов:
-
Агрегатные функции могут использоваться как в предложениях SELECT, так и в предложениях HAVING (предложение HAVING будет описано далее в этой главе).
-
Агрегатные функции нельзя использовать в предложении WHERE. Его нарушение приведет к тому, что групповая функция Oracle ORA-00934 не допускает здесь сообщения об ошибке.
Агрегатные функции могут использоваться как в предложениях SELECT, так и в предложениях HAVING (предложение HAVING будет описано далее в этой главе).
Агрегатные функции нельзя использовать в предложении WHERE. Его нарушение приведет к тому, что групповая функция Oracle ORA-00934 не допускает здесь сообщения об ошибке.
иллюстрации
Приведенный ниже запрос SELECT подсчитывает количество сотрудников в организации.
SELECT COUNT(*) Count FROM employees; COUNT ----- 24
Приведенный ниже запрос SELECT возвращает среднюю зарплату сотрудников в организации.
SELECT AVG(Salary) average_sal FROM employees; AVERAGE_SAL ----------- 15694
Приведенный ниже запрос SELECT возвращает сумму зарплат сотрудников в организации.
SELECT SUM(Salary) total_sal FROM employees; TOTAL_SAL --------- 87472
Приведенный ниже запрос SELECT возвращает самые старые и самые последние даты приема сотрудников в организации.
SELECT MIN (hire_date) oldest, MAX (hire_date) latest FROM employees; OLDEST LATEST --------- ----------- 16-JAN-83 01-JUL-2012
ГРУППА ПО
Агрегатные функции обычно используются вместе с предложением GROUP BY. Предложение GROUP BY позволяет использовать агрегатные функции для ответа на более сложные вопросы управления, такие как:
Какова средняя зарплата сотрудников в каждом отделе?
Сколько сотрудников работает в каждом отделе?
Сколько сотрудников работает над конкретным проектом?
Группировка за функцией устанавливает группы данных на основе столбцов и объединяет информацию только внутри группы. Критерий группировки определяется столбцами, указанными в предложении GROUP BY. Следуя этой иерархии, данные сначала организуются в группы, а затем предложение WHERE ограничивает строки в каждой группе.
Правила использования предложения GROUP BY
(1) Все зависимые столбцы или столбцы, используемые в функции GROUP BY, должны формировать основу группировки, следовательно, они также должны быть включены в предложение GROUP BY.
SELECT DEPARTMENT_ID, SUM(SALARY) FROM employees; DEPARTMENT_ID, * ERROR at line 2: ORA-00937: not a single-group group function
(2) Предложение GROUP BY поддерживает не псевдоним столбца, а фактические имена.
(3) Предложение GROUP BY может использоваться только с агрегатными функциями, такими как SUM, AVG, COUNT, MAX и MIN. Если оно используется с однорядными функциями, Oracle выдает исключение как «ORA-00979: не выражение GROUP BY» ,
(4) Агрегатные функции нельзя использовать в предложении GROUP BY. Oracle вернет сообщение об ошибке «ORA-00934: групповая функция не разрешена».
Ниже запроса перечислены количество сотрудников, работающих в каждом отделе.
SELECT DEPARTMENT_ID, COUNT (*) FROM employees GROUP BY DEPARTMENT_ID;
Аналогичным образом, ниже запрос, чтобы найти сумму зарплат для соответствующих идентификаторов работы в каждом отделе. Обратите внимание, что группа создана на основе отдела и идентификатора работы. Таким образом, они появляются в предложении GROUP BY.
SELECT DEPARTMENT_ID, JOB_ID, SUM (SAL) FROM employees GROUP BY DEPARTMENT_ID, JOB_ID;
Приведенный ниже запрос также дает тот же результат. Обратите внимание, что группировка основана на столбцах идентификатора отдела и идентификатора задания, но не используется для отображения.
SELECT SUM (SALARY) FROM employees GROUP BY DEPARTMENT_ID, JOB_ID;
Использование DISTINCT, ВСЕХ ключевых слов с агрегатными функциями
Задав ключевое слово DISTINCT во входном параметре, функция group by учитывает только уникальное значение столбца для агрегации. Указывая ключевое слово ALL с входным параметром, group by function учитывает все значения столбца для агрегации, включая нули и дубликаты. ALL является спецификацией по умолчанию.
Пункт HAVING
Предложение HAVING используется для агрегатных функций так же, как предложение WHERE используется для имен столбцов и выражений. По существу, предложения HAVING и WHERE выполняют то же самое, то есть фильтруют строки из включения в таблицу результатов на основе условия. , Хотя может показаться, что предложение HAVING отфильтровывает группы, это не так. Скорее, предложение HAVING фильтрует строки.
Когда все строки для группы удаляются, так же как и для группы. Подводя итог, можно отметить важные различия между предложениями WHERE и HAVING:
Предложение WHERE используется для фильтрации строк перед действием GROUPING (т. Е. Перед вычислением агрегатных функций).
Предложение HAVING фильтрует строки ПОСЛЕ действия GROUPING (т. Е. После вычисления агрегатных функций).
SELECT JOB_ID, SUM (SALARY) FROM employees GROUP BY JOB_ID HAVING SUM (SALARY) > 10000;
Предложение HAVING является условным параметром, который непосредственно связан с параметром предложения GROUP BY, поскольку предложение HAVING удаляет строки из таблицы результатов на основе результата предложения GROUP BY.