Статистические функции в списке возвращаемых столбцов

Назначение простого запроса, в котором участвуют статистические функции, можно понять достаточно легко. Однако если в список возвращаемых столбцов входит несколько таких функций или если аргументом функции является сложное выражение, понять запрос становится значительно сложнее. В табл. 8.1 приведены правила выполнения SQL-запросов из табл. 7.1, расщиренные с учетом применения статистических функций. Как и ранее, эти правила являются точным определением того, что означает запрос, а не описанием того, как СУБД на самом деле получает результаты запроса.

7сгбЩ<о 8.1. Правила выполнения SQjlnp навыборЩа> i

Таблица результатов запроса на выборку генерируется следующим образом:

1. Если запрос представляет собой запрос на объединение (union) инструкций select, для каждой из этих инструкций выполнить действия 2-5 и получить отдельную таблицу результатов.

2. Сформировать произведение таблиц, перечисленных в предложении from. Если в предложении from указана только одна таблица, то произведением будет она сама.

3. Если имеется предложение where, применить заданное в нем условие отбора к каждой строке таблицы произведения и оставить в ней только те строки, для которых это условие выполняется, т.е. имеет значение true; строки, для которых условие отбора имеет значение false или null, - отбросить.

4. Для каждой из оставшихся строк вычислить значение каждого элемента в списке возвращаемых столбцов и создать одну строку таблицы результатов запроса. При любой ссылке на столбец берется значение столбца для текущей строки. В качестве аргумента статистической функции используется весь набор строк.

5. Если указан предикат distinct, удалить из таблицы результатов запроса все повторяюшиеся строки.

6. Если запрос является запросом на объединение (union) инструкций select, объединить результаты выполнения отдельных инструкций в одну таблицу результатов запроса. Удалить из нее повторяющиеся строки, если не указан предикат all.

7. Если имеется предложение order by, отсортировать результаты запроса.

Один из лучших способов понять, как выполняются итоговые запросы со статистическими функциями, - представить запрос разбитым на два этапа. Сначапа подумайте, как работал бы запрос без статистических функций, возвращая несколько строк результатов. Затем представьте, как СУБД применяет статистические функции к результатам запроса, возвращая одну итоговую строку. Например, рассмотрлм следующий сложный запрос:

Найти среднюю стоимость заказов, общую стоимость заказов, среднюю стоимость заказов в процентах от лимитов кредита клиентов, а также среднюю стоимость заказов в процентах от плановых объемов продаж служащих.

SELECT AVG(AMOUNT) , SUM(AMOUNT), (100 * AVG(AMOUNT/CREDIT LIMIT)) ,

(100 * AVG(AMOUNT/QUOTA))

from orders, customers, salesreps where cust = cust num and rep = empl num

avg(amount) sum(amount) (100*avg(amount/credit limit)) (100*avg(amount/quota)) $8,254.17 $247,625.00 24.44 2.68

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

select amount, amount, amount/credit limit, amount/quota from orders, customers, salesreps where cust = cust num and rep = empl num

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

В списке возвращаемых столбцов вместо имени любого столбца можно указывать статистическую функцию. Например, она может входить в выражение, в котором суммируются или вычитаются значения двух статистических функций. Однако статистическая функция не может быть аргументом для другой такой функции, поскольку получающееся в подобном случае выражение не имеет смысла. Иногда это правило формулируют таким образом: Нельзя вкладывать статистртеские функции одна в другую .

Кроме того, в списке возвращаемых столбцов нельзя одновременно использовать статистические функции и обычные имена столбцов, поскольку в этом также нет смысла. Например, рассмотрим следующий запрос:

select name, sum(sales) from salesreps

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

Статистические функции и значения NULL

функции sum (), avg (), min (), max () и count () в качестве аргумента принимают столбец значений и возвращают в качестве результата одно значение. А что происходит, когда в столбце встречается одно или несколько значений null? В стандарте ANSI/ISO сказано, что значения null статистическими функциями игнорируются.

Следующий запрос показывает, что статистическая функция count () игнорирует все значения null, содержащиеся в столбце:

SELECT COUNT(*), COUNT(SALES), COUNT(QUOTA) FROM SALESREPS

COUNT(*) COUNT(SALES) COUNT(QUOTA)

10 10 9

В таблице salesreps содержится десять строк, поэтому функция count (*) возвращает число 10. В столбце sales содержится десять значений, причем ни одно из них не равно null, поэтому функция count (sales) также возвращает число 10. А вот в столбце quota содержится одно значение null - для служащего, принятого совсем недавно. Функция count (quota) игнорирует это значение и возвращает число 9. Именно из-за таких расхождений вместо функции count () для подсчета строк почти всегда используется функция count (*). Исключение составляют случаи, когда необходимо не учитывать строки, содержащие значения null в определенном столбце.

Игнорирование значений null не оказывает влияния на результаты, возвращаемые статистическими функциями min() и мах(). Однако оно может привести к проблемам при использовании функций sum() и avg(), что иллюстрирует следующий запрос:

SELECT SUM(SALES), SUM(QUOTA), (SUM(SALES) - SUM(QUOTA)), SUM(SALES-QUOTA) FROM SALESREPS

SUM(SALES) SDM(QUOTA) {SUM{SALES) -SUM(QDOTA)) SUM(SALES-QUOTA)

$2,893,532.00 $2,700,000.00 $193,532.00 $117,547.00

Можно ожидать, что выражения

(SUM(SALES) - SUM(QDOTA)), SUM(SALES-QUOTA)

вернут одинаковые результаты, однако пример показывает, что так не происходит. И снова причиной является строка со значением null в столбце quota. Выражение

зим(SALES)

вычисляет сумму продаж для всех десяти служащих, а выражение

SUM(QUOTA)

вычисляет сумму только девяти значений и не учитывает значение null. Следующее выражение вычисляет разницу между ними:

SUM(SALES) - SUM(QUOTA)

В то же время выражение

SUM(SALES-QUOTA)

принимает в качестве аргументов только девять значений, которые не равны null. В строке, где значение планового объема продаж равно null, операция вычитания возвращает значение null, которое функция sum() игнорирует. Таким образом, из