СТАТИСТИЧЕСКИЕ ФУНКЦИИ

 

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

 








Дата добавления: 2015-08-26; просмотров: 1478;


Поиск по сайту:

При помощи поиска вы сможете найти нужную вам информацию.

Поделитесь с друзьями:

Если вам перенёс пользу информационный материал, или помог в учебе – поделитесь этим сайтом с друзьями и знакомыми.
helpiks.org - Хелпикс.Орг - 2014-2024 год. Материал сайта представляется для ознакомительного и учебного использования. | Поддержка
Генерация страницы за: 0.004 сек.