Для подведения итогов по информации, содержащейся в базе данных, в SQL предусмотрены статистические (агрегатные) функции. Статистическая функция принимает в качестве аргумента какой-либо столбец данных целиком, а возвращает одно значение, которое определенным образом подытоживает этот столбец, исключая все пустые значения.
В стандарте SQL указано пять статистических функций, которые позволяют получать различные виды итоговой информации:
- SUM() – для вычисления суммы всех значений столбца-аргумента;
- AVG() – для вычисления среднего значения столбца;
- MIN() – определяет минимальное значение столбца;
- MAX() – определяет максимальное значение столбца;
- COUNT() – подсчитывает число всех определенных значений столбца;
- COUNT(*) – подсчитывает число строк таблицы.
Аргументом статистической функции может быть простое имя столбца или выражение.
В Oracle применяется более 20 статистических функций, например, добавлены такие функции, как:
CORR() - определяет коэффициент корреляции пар;
KEEP() - используется для работы со множеством значений, отобранных SUM(), MIN(), MAX();
PERCENT_RANK() - вычисляет относительную позицию строки;
RANK() - вычисляет ранг строки по отношению к другим строкам в группе (используется с ORDER BY) и др.
Приведем примеры запросов со стандартными статистическими функциями:
Определить среднемесячную и среднегодовую зарплату сотрудников предприятия
SELECT AVG(salary) AS per_month, AVG(12*salary) AS per_year
FROM staff;
В примере в списке возвращаемых столбцов результирующего множества используются псевдонимы столбцов, иначе значения в возвращаемой строке были бы представлены под именами avg(salary) и avg(12*salary).
Тип данных столбца-аргумента функции COUNT() может быть любым (не обязательно числовым, в отличие от других статистических функций), а возвращает она всегда целое число независимо от типа данных столбца. Например, следующий запрос подсчитывает количество работающих на предприятии менеджеров:
SELECT count (sno)
FROM staff
WHERE position=‘менеджер’;
Следует заметить, что для функции COUNT() важны не конкретные значения ячеек, а количество ячеек, удовлетворяющих заданному критерию.
В отличие от функции COUNT() статистическая функция COUNT(*) подсчитывает строки, а не значения в них. Функция COUNT() не учитывает NULL-значений в столбцах, также как и любая другая статистическая функция.
В SQL существует ряд ограничений на применение статистических функций в инструкциях SELECT:
1. в списке возвращаемых столбцов можно указывать любую статистическую функцию, она также может входить в выражение, в котором суммируются или вычитаются значения двух и более статистических функций, однако статистическая функция не может быть аргументом для другой такой функции, поскольку получающееся в таком случае выражение не имеет смысла.
2. в списке возвращаемых столбцов нельзя одновременно указывать статистическую функцию и обычные имена столбцов, поскольку в этом также нет смысла.
3. разрешается использовать предикат DISTINCT во всех статистических функциях, но в одном запросе можно употребить только один раз. Если он применяется вместе с аргументом одной из статистических функций, его нельзя использовать ни с одним другим аргументом. Если он указан перед списком возвращаемых столбцов, его нельзя употреблять ни в одной статистической функции. Единственным исключением из этого правила является случай, когда предикат DISTINCT используется внутри подчиненного запроса, входящего в запрос, в котором он уже применяется.
В целом статистические функции могут использоваться только в SELECT и HAVING, при чем, если они есть в SELECT, а в GROUP BY нет, то никакие другие столбцы в списке SELECT быть не могут.
В стандарте ANSI/ISO сказано, что значения NULL статистическими функциями игнорируются и определены следующие точные правила обработки значений NULL в статистических функциях:
- если какие-либо из значений, содержащихся в столбце, равны NULL, при вычислении результата функции они исключаются;
- если все значения в столбце равны NULL, то функции SUM(), AVG(), MIN() и MAX() возвращают значение NULL; функция COUNT() возвращает ноль;
- если в столбце нет значений (т.е. столбец пустой), то функции SUM(), AVG(), MIN() и MAX() возвращают значение NULL; функция COUNT() возвращает ноль;
- функция COUNT(*) подсчитывает количество строк и не зависит от наличия или отсутствия в столбце значений NULL; если строк в таблице нет, эта функция возвращает ноль.
Хотя эта часть стандарта определена достаточно четко, коммерческие СУБД могут выдавать результаты, отличающиеся от стандарта, особенно если все значения, содержащиеся в столбце, равны NULL или таблица пуста. Поэтому, прежде чем полагаться на стандартные правила, следует проверить используемую СУБД.
