Решение

Оконные функции упрощают решение этой задачи. Если в вашем распоряжении нет оконных функций, можно использовать скалярные подзапросы.

DB2, Oracle и SQL Server

Используйте оконную функцию COUNT OVER, задавая разные сегменты или группы данных, для которых проводится агрегация:

select ename, deptno,

count(*)over(partition by deptno) deptno cnt, job,

count(*)over(partition by job) job cnt, count(*)over() total from emp

PostgreSQL и MySQL

Для выполнения операций агрегации COUNT разных групп строк используйте скалярные подзапросы в списке оператора SELECT:

1 select e.ename,

2 e.deptno,

3 (select count(*) from emp d

4 where d.deptno = e.deptno) as deptno cnt,

5 job,

6 (select count(*) from emp d

7 where d.job = e.job) as job cnt,

8 (select count(*) from emp) as total

9 from emp e

Обсуждение

DB2, Oracle и SQL Server

Данный пример действительно показывает мощь и преимущества оконных функций. Всего лишь задавая различные сегменты или группы данных, подлежащих агрегации, можно создавать чрезвычайно подробные отчеты без бесконечных рефлексивных объединений и без громоздких и, возможно, низкопроизводительных подзапросов в списке SELECT. Всю работу выполняет оконная функция COUNT OVER. Чтобы понять полученный результат, остановимся на операторе OVER каждой операции COUNT:

count(*)over(partition by deptno) count(*)over(partition by job) count(*)over()

Вспомним основные части оператора OVER: сегмент, определяемый ключевым словом PARTITION BY, и кадр или окно данных, определяемое ORDER BY. Посмотрим на первый оператор COUNT, в котором задано сегментирование по DEPTNO. Строки таблицы EMP будут сгруппированы по DEPTNO, и операция COUNT будет выполнена над всеми строками каждой группы. Поскольку кадр или окно данных не определено (нет оператора ORDER BY), пересчитываются все строки группы. Оператор PARTITION BY находит все уникальные значения DEPTNO, для каждого из них функция COUNT подсчитывает количество строк, имеющих это значение. В конкретном примере COUNT(*)OVER(PAR-TITION BY DEPTNO) оператор PARTITION BY выделяет сегменты или группы по значениям 10, 20 и 30.

То же самое присходит для второй функции COUNT с сегментированием по JOB. В последней COUNT сегменты не определены, просто указаны пустые круглые скобки. Пустые круглые скобки подразумевают всю таблицу . Таким образом, тогда как две предыдущие операции COUNT обрабатывают заданные группы или сегменты данных, последняя COUNT подсчитывает все строки таблицы EMP.

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

PostgreSQL и MySQL

Для проведения различных подсчетов для каждого отдела и должности используйте несколько скалярных подзапросов в списке SELECT, обрабатывая каждую строку, возвращаемую основным запросом (строки из EMP E). Чтобы получить значение TOTAL, просто с помощью другого скалярного подзапроса пересчитайте всех служащих таблицы EMP.

Агрегация скользящего множества значений Задача

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

Решение

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

DB2 и Oracle

Для DB2 и Oracle используйте оконную функцию SUM OVER и сортировку по HIREDATE. В операторе сегментирования задайте диапазон 90 дней, чтобы в сумму были включены заработные платы всех служащих, принятых на работу в течение предыдущих 90 дней. Поскольку DB2 не позволяет задавать HIREDATE в операторе ORDER BY оконной функции (строка 3 в фрагменте кода ниже), можно сортировать по

DAYS(HIREDATE):

1 select hiredate,

2 sal,

3 sum(sal)over(order by days(hiredate)

4 range between 90 preceding

5 and current row) spending pattern

6 from emp e

расходов для каждого 90-дневного периода между датами приема на работу первого и последнего служащих. Должно быть получено следующее результирующее множество: