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

Если в результирующем множестве возвращается нечетное число строк, первая часть предиката WHERE (строки 11-13) не выполняется. Если известно, что количество строк в результирующем множестве всегда будет нечетным, запрос можно упростить до:

select avg(sal)

from ( select sal,

count(*)over() total,

ceil(cast(count(*)over() as decimal)/2) next, row number()over(order by sal) rn from emp where deptno = 20 ) x

where rn = next

К сожалению, если в результирующем множестве четное количество строк, упрощенное решение не годится. В исходном решении четное количество строк обрабатывается с помощью значений столбца MID. Рассмотрим результаты вложенного представления Х для 30-го отдела (DEPTNO 30), в котором шесть служащих:

select sal,

count(*)over() total, cast(count(*)over() as decimal)/2 mid, ceil(cast(count(*)over() as decimal)/2) next, row number()over(order by sal) rn from emp where deptno = 30

SAL TOTAL MID NEXT RN

950 6 3 3 1

1250 6 3 3 2

1250 6 3 3 3

1500 6 3 3 4

1600 6 3 3 5

2850 6 3 3 6

Поскольку возвращается четное число строк, медиана вычисляется путем нахождения среднего из значений двух строк: строки, где RN равно MID, и строки, где RN равно MID + 1.

MySQL и PostgreSQL

Вычисление медианы начинается с рефлексивного объединения таблицы EMP, в результате которого возвращается декартово произведение всех заработных плат (группировка по столбцу E.SAL предотвратит

возвращение дубликатов). В конструкции HAVING с помощью функции SUM подсчитывается, сколько в столбцах E.SAL и D.SAL равных значений. Если это количество больше или равно числу раз, когда значение E.SAL больше значения D.SAL, значит, данная строка является медианой. Увидеть это можно, переместив SUM в список SELECT:

select e.sal,

sum(case when e.sal=d.sal

then 1 else 0 end) as cnt1, abs(sum(sign(e.sal - d.sal))) as cnt2 from emp e, emp d where e.deptno = d.deptno

and e.deptno = 20 group by e.sal

SAL CNT1 CNT2

800 1 4

1100 1 2

2975 1 0

3000 4 6

Oracle

При работе с Oracle10 Database или Oracle 9i Database задачу по вычислению медианы можно переложить на плечи функций, предоставляемых Oracle. Для Oracle 8i Database можно использовать решение DB2. Для всех других версий используется решение PostgreSQL. Очевидно, что функция MEDIAN вычисляет медиану, тогда как с функцией PERCENTILE CONT все не так явно, хотя она делает то же самое. Передаваемый в PERCENTILE CONT аргумент, 0,5 - это процентиль. Конструкция WITHIN GROUP (ORDER BY SAL) определяет множество сортированных строк, с которым будет работать PERCENTILE CONT (помним, что медиана - это середина множества упорядоченных значений). Возвращаемое значение соответствует заданной процентили в сортированном множестве строк (в данном случае, это 0,5; т. е. середина множества, поскольку граничными значениями являются 0 и 1).

Вычисление доли от целого в процентном выражении Задача

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

Решение

В общем, вычисление процента от целого в SQL ничем не отличается от того, как это делается на бумаге: делим, затем умножаем. В данном примере требуется найти, какой процент от всех заработных плат таблицы EMP составляют заработные платы служащих 10-го отдела (DEPTNO 10). Для этого просто находим заработные платы служащих 10-го отдела и делим их сумму на общую сумму заработных плат в таблице. В качестве заключительного шага умножаем полученное значение на 100, чтобы представить результат в процентном выражении.

MySQL и PostgreSQL

Делим сумму заработных плат 10-го отдела (DEPTNO 10) на сумму всех заработных плат:

1 select (sum(

2 case when deptno = 10 then sal end)/sum(sal)

3 ).100 as pct

4 from emp

DB2, Oracle и SQL Server

С помощью вложенного запроса и оконной функции SUM OVER находим суммы всех заработных плат и заработных плат 10-го отдела. Затем во внешнем запросе выполняем деление и умножение:

1 select distinct (d10/total)*100 as pct

2 from (

3 select deptno,

4 sum(sal)over() total,

5 sum(sal)over(partition by deptno) d10

6 from emp

7 ) x

8 where deptno=10

Обсуждение

MySQL и PostgreSQL

Выражение CASE позволяет без труда выбрать заработные платы служащих 10-го отдела (DEPTNO 10). После этого они суммируются и делятся на сумму всех заработных плат. Агрегатные функции игнорируют значения NULL, поэтому конструкция ELSE в выражении CASE не нужна. Чтобы увидеть, какие именно значения участвуют в делении, выполните запрос без этой операции:

select sum(case when deptno = 10 then sal end) as d10, sum(sal) from emp

D10 SUM(SAL)

8750 29025