11. Статистические функции

Для подведения итогов по информации, содержащейся в базе данных, в 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 или таблица пуста. Поэтому, прежде чем полагаться на стандартные правила, следует проверить используемую СУБД.