Определение интервала времени в днях между текущей и следующей записями

Задача

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

Решение

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

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

1 select x.*,

2 days(x.next hd) - days(x.hiredate) diff

3 from (

4 select e.deptno, e.ename, e.hiredate,

5 (select min(d.hiredate) from emp d

6 where d.hiredate > e.hiredate) next hd

7 from emp e

8 where e.deptno = 10

9 ) x

MySQL и SQL Server

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

1 select x.*,

2 datediff(day,x.hiredate,x.next hd) diff

3 from (

4 select e.deptno, e.ename, e.hiredate,

5 (select min(d.hiredate) from emp d

6 where d.hiredate > e.hiredate) next hd

7 from emp e

8 where e.deptno = 10

9 ) x

Пользователи MySQL могут исключить первый аргумент ( day ) и поменять порядок оставшихся двух аргументов:

2 datediff(x.next hd, x.hiredate) diff

Oracle

При работе с Oracle 8i Database или более новыми версиями для поиска следующего значения HIREDATE относительно текущего используйте оконную функцию LEAD OVER:

1 select ename, hiredate, next hd,

2 next hd - hiredate diff

3 from (

4 select deptno, ename, hiredate,

5 lead(hiredate)over(order by hiredate) next hd

6 from emp

8 where deptno=10

Для Oracle 8 Database и более ранних версий можно использовать в качестве альтернативы решение PostgreSQL.

PostgreSQL

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

1 select x.*,

2 x.next hd - x.hiredate as diff

3 from (

4 select e.deptno, e.ename, e.hiredate,

5 (select min(d.hiredate) from emp d

6 where d.hiredate > e.hiredate) as next hd

7 from emp e

8 where e.deptno = 10

9 ) x

Обсуждение

DB2, MySQL, PostgreSQL и SQL Server

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

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

Oracle

Здесь оконная функция LEAD OVER исключительно полезна, поскольку обеспечивает возможность доступа к последующим (относительно текущей строки согласно порядку, установленному оператором ORDER BY) строкам. Возможность доступа к близлежащим строкам без дополнительных объединений делает код более понятным и эффективным. При работе с оконными функциями необходимо помнить о том, что они обрабатываются после предиката WHERE, следовательно, в решении должен использоваться вложенный запрос. Если бы во вложенном запросе осуществлялась сортировка по столбцу DEPTNO, результаты были бы другими (рассматривались бы значения HIREDATE только для DEPTNO 10). Важно рассмотреть поведение Oracle-функций LEAD и LAG в присутствии дубликатов. В предисловии я говорил, что код данных рецептов незащищенный , потому что существует слишком много условий, которые разработчик должен предвидеть, чтобы сохранить целостность и работоспособность своего кода. А иногда, после того как все проблемы учтены, результирующий SQL становится абсолютно нечитабельным. Поэтому целью предлагаемого решения является представить технику, которая может использоваться при создании систем, но должна быть протестирована и многократно скорректирована для работы с реальными данными. Здесь мы обсудим ситуацию, подход к решению которой может быть не так очевиден, особенно для тех, кто работает не в системах Oracle. В данном примере в столбце HIREDATE таблицы EMP нет дублирующихся значений, но такой вариант, безусловно, возможен. Рассмотрим служащих 10-го отдела (DEPTNO 10) и их значения HIREDATE:

select ename, hiredate from emp where deptno=10 order by 2

ENAME HIREDATE CLARK 09-JUN-1981

KING 17-NOV-1981

MILLER 23-JAN-1982

Для данного примера вставим четыре дубликата, так чтобы в таблице было пять служащих (включая KING), которые были приняты на работу 17 ноября:

insert into emp (empno,ename,deptno,hiredate) values (1,ant,10,to date(17-NOV-1981))