Решение для Oracle более понятное, чем для DB2, потому что в Oracle в оконных функциях можно проводить сортировку по типам даты-времени:

1 select hiredate,

2 sal,

3 sum(sal)over(order by hiredate

4 range between 90 preceding

5 and current row) spending pattern

6 from emp e

MySQL, PostgreSQL и SQL Server

Чтобы для каждого служащего просуммировать заработные платы сотрудников, принятых на работу в течение 90 дней до дня найма рассматриваемого сотрудника, используйте скалярный подзапрос:

1 select e.hiredate,

2 e.sal,

3 (select sum(sal) from emp d

4 where d.hiredate between e.hiredate-90

5 and e.hiredate) as spending pattern

6 from emp e

7 order by 1

Обсуждение

DB2 и Oracle

Для DB2 и Oracle используется одно и то же решение. Единственное небольшое отличие в том, как задается HIREDATE в операторе ORDER BY оконной функции. На момент написания данной книги DB2 не допускает применения значений типа DATE в ORDER BY, если для определения окна данных используется числовое значение. (Например, если задано RANGE BETWEEN UNBOUNDED PRECEDING AND

CURRENT ROW, сортировка по дате допускается, а если RANGE BETWEEN 90 PRECEDING AND CURRENT ROW - нет.)

Чтобы понять, что делает запрос, представленный в решении, необходимо просто разобраться, как работает оператор сегментирования. Задаваемое окно данных обусловливает упорядочение заработных плат всех служащих по HIREDATE. Затем функция вычисляет сумму. Сумма вычисляется не для всех заработных плат. Происходит следующее:

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

2. Определяется заработная плата следующего (соответственно HIRE-DATE) служащего. Она включается в скользящую сумму вместе с заработными платами других сотрудников, принятых на работу в течение 90 дней до дня найма рассматриваемого сотрудника.

Дата HIREDATE первого служащего - 17 декабря 1980 года, а HIRE-DATE служащего, поступившего следующим, - 20 февраля 1981. Второй служащий был принят на работу раньше, чем через 90 дней после первого служащего, таким образом, скользящая сумма для второго сотрудника - 2400 (1600 + 800). Чтобы не было трудностей с пониманием, откуда берутся значения поля SPENDING PATTERN, рассмотрим следующий запрос и результирующее множество:

select distinct

dense rank()over(order by e.hiredate) window, e.hiredate current hiredate, d.hiredate hiredate within 90 days, d.sal sals used for sum from emp e, emp d

where d.hiredate between e.hiredate-90 and e.hiredate

WINDOW CURRENT HIREDATE HIREDATE WITHIN 90 DAYS SALS USED FOR SUM

13 12-JAN-1983 09-DEC-1982 3000

13 12-JAN-1983 12-JAN-1983 1100

В каждой сумме участвуют только строки с одинаковым значением поля WINDOW. Возьмем, к примеру, WINDOW 3. При вычислении суммы для этого окна используются заработные платы 800, 1600 и 1250, в сумме дающие 3650. Если взглянуть на окончательное результирующее множество в разделе Задача , мы увидим, что значение SPEN-DING PATTERN для 22 февраля 1981 (WINDOW 3) равно 3650. Убедиться в том, что приведенное выше рефлексивное объединение обеспечивает выбор соответствующих заработных плат для заданных окон, можно, просто просуммировав значения SALS USED FOR SUM и проведя группировку по CURRENT DATE. Результаты должны быть аналогичны результирующему множеству, представленному в разделе Задача (без дублирующейся строки для 3 декабря 1981):

select current hiredate,

sum(sals used for sum) spending pattern from ( select distinct

dense rank()over(order by e.hiredate) window,

e.hiredate current hiredate,

d.hiredate hiredate within 90 days,

d.sal sals used for sum from emp e,

emp d

where d.hiredate between e.hiredate-90 and e.hiredate ) x

group by current hiredate

MySQL, PostgreSQL и SQL Server

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