В зависимости от того, как определен SAL, при делении могут понадобиться явные приведения типов. Например, для DB2, SQL Server и PostgreSQL, если значения SAL хранятся как целые, их можно привести к десятичному типу для получения более точного результата, как показано ниже:

select (cast(

sum(case when deptno = 10 then sal end)

as decimal)/sum(sal) )*100 as pct from emp

DB2, Oracle и SQL Server

В качестве альтернативы традиционному подходу данное решение для вычисления процента от целого использует оконные функции. Для DB2 и SQL Server, если значения SAL хранятся как целые, перед делением понадобится выполнить приведение типов:

select distinct

cast(d10 as decimal)/total*100 as pct from ( select deptno,

sum(sal)over() total, sum(sal)over(partition by deptno) d10 from emp ) x

where deptno=10

Важно помнить, что оконные функции обрабатываются после предиката WHERE. Таким образом, сортировка по DEPTNO не может осуществляться во вложенном запросе Х. Рассмотрим результаты вложенного запроса Х без и с фильтром по DEPTNO. Сначала без:

select deptno,

sum(sal)over() total, sum(sal)over(partition by deptno) d10 from emp

Поскольку оконные функции обрабатываются после предиката WHERE, значение столбца TOTAL представляет сумму всех зарплат только 10-го отдела. Но для решения задачи TOTAL должен представлять сумму всех заработных плат. Вот почему фильтр по DEPTNO должен располагаться вне вложенного запроса Х.

Агрегация столбцов, которые могут содержать NULL-значения

Задача

Требуется выполнить агрегацию столбца, но в нем могут содержаться NULL-значения. Хочется сохранить точность, но тревожит то, что агрегатные функции игнорируют NULL-значения. Например, поставлена задача определить среднюю сумму комиссионных для служащих 30-го отдела (DEPTNO 30), но некоторые из них не получают комиссионных (для этих служащих в столбце COMM располагаются NULL-значения). Агрегатные функции игнорируют NULL-значения, поэтому точность результата под вопросом. Хотелось бы каким-то образом учесть NULL-значения при агрегации.

Решение

С помощью функции COALESCE преобразуйте NULL-значения в 0, тогда они будут включены в агрегацию:

1 select avg(coalesce(comm,0)) as avg comm

2 from emp

3 where deptno=30

Обсуждение

При работе с агрегатными функциями следует помнить об игнориров-нии NULL-значений. Рассмотрим результат выполнения решения без

функции COALESCE:

30 29025 9400 30 29025 9400

а теперь с фильтром:

select deptno,

sum(sal)over() total, sum(sal)over(partition by deptno) d10

select avg(comm) from emp where deptno=30

AVG(COMM)

В результате данного запроса получаем, что средняя сумма комиссионных для 30-го отдела (DEPTNO 30) составляет 550, но беглый взгляд на строки:

select ename, comm from emp where deptno=30 order by comm desc

ENAME COMM

BLAKE

JAMES

MARTIN 1400

WARD 500

ALLEN 300

TURNER 0

показывает, что только четверо из шести служащих могут получать комиссионные. Сумма всех комиссионных 30-го отдела составляет 2200, среднее значение должно вычисляться как 2200/6, а не 2200/4. Исключая функцию COALESCE, мы отвечаем на вопрос Каково среднее значение комиссионных служащих 30-го отдела, которые могут получать комиссионные? , а не на вопрос Каково среднее значение комиссионных всех служащих 30-го отдела? При работе с агрегатными функциями не забывайте обрабатывать NULL-значения соответствующим образом.

Вычисление среднего без учета наибольшего и наименьшего значений

Задача

Требуется вычислить среднее, но без учета наибольшего и наименьшего значений, чтобы (будем надеятся) уменьшить асимметрию распределения. Например, необходимо найти среднюю заработную плату служащих, исключая из расчета наибольшую и наименьшую из них.

Решение

MySQL и PostgreSQL

Исключаем наибольшее и наименьшее значения с помощью подзапросов: