Есть такой набор данных:

2014-11-01 21:26:00
2014-11-01 22:20:00
2014-11-02 04:30:00
2014-11-02 06:15:00
2014-11-02 07:42:00
2014-12-01 20:30:00
2014-12-01 22:20:00
2014-12-02 04:25:00
2014-12-02 06:15:00
2014-12-02 07:40:00
2015-01-01 21:24:00
2015-01-01 21:31:00
2015-01-01 22:20:00
2015-01-02 04:30:00
2015-01-02 06:15:00
2015-01-02 07:43:00

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

на выходе надо получить такое:

2014-11-01 21:26:00    2014-11-01 21:26:00    2014-11-02 07:42:00
2014-11-01 22:20:00    2014-11-01 21:26:00    2014-11-02 07:42:00
2014-11-02 04:30:00    2014-11-01 21:26:00    2014-11-02 07:42:00
2014-11-02 06:15:00    2014-11-01 21:26:00    2014-11-02 07:42:00
2014-11-02 07:42:00    2014-11-01 21:26:00    2014-11-02 07:42:00
2014-12-01 20:30:00    2014-12-01 20:30:00    2014-12-02 07:40:00
2014-12-01 22:20:00    2014-12-01 20:30:00    2014-12-02 07:40:00
2014-12-02 04:25:00    2014-12-01 20:30:00    2014-12-02 07:40:00
2014-12-02 06:15:00    2014-12-01 20:30:00    2014-12-02 07:40:00
2014-12-02 07:40:00    2014-12-01 20:30:00    2014-12-02 07:40:00
2015-01-01 21:24:00    2015-01-01 21:24:00    2015-01-02 07:43:00
2015-01-01 21:31:00    2015-01-01 21:24:00    2015-01-02 07:43:00
2015-01-01 22:20:00    2015-01-01 21:24:00    2015-01-02 07:43:00
2015-01-02 04:30:00    2015-01-01 21:24:00    2015-01-02 07:43:00
2015-01-02 06:15:00    2015-01-01 21:24:00    2015-01-02 07:43:00
2015-01-02 07:43:00    2015-01-01 21:24:00    2015-01-02 07:43:00

попытался так

    select
        d."operationDate"
        first_value(d."operationDate") over (partition by d."trainIndex" order by d."operationDate" range between 18/24 preceding and 18/24 following) f_date,
        last_value(d."operationDate") over (partition by d."trainIndex" order by d."operationDate" range between 18/24 preceding and 18/24 following) l_date
    from "TrainData" d

но он на тестовых данных возвращает неправильный результат, поскольку 18 часов считаются от даты в первой колонке.

2015-02-26 16:16:00    2015-02-26 16:16:00    2015-02-27 08:35:00
2015-02-26 16:17:00    2015-02-26 16:16:00    2015-02-27 08:35:00
2015-02-26 16:35:00    2015-02-26 16:16:00    2015-02-27 08:35:00
2015-02-26 16:52:00    2015-02-26 16:16:00    2015-02-27 08:35:00
2015-02-26 17:31:00    2015-02-26 16:16:00    2015-02-27 08:35:00
2015-02-26 17:36:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-26 18:06:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-26 18:45:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-26 20:32:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-27 00:44:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-27 02:40:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-27 05:10:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-27 08:35:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-27 11:35:00    2015-02-26 17:36:00    2015-02-27 11:35:00

а мне нужно чтобы даты последовательно сравнивались и всё считалось одной пачкой, т.е. выглядело так:

2015-02-26 16:16:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-26 16:17:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-26 16:35:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-26 16:52:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-26 17:31:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-26 17:36:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-26 18:06:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-26 18:45:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-26 20:32:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-27 00:44:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-27 02:40:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-27 05:10:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-27 08:35:00    2015-02-26 16:16:00    2015-02-27 11:35:00
2015-02-27 11:35:00    2015-02-26 16:16:00    2015-02-27 11:35:00

субд оракл.

Здравствуйте, Neco, Вы писали:

WITH t AS (

SELECT to_date('2014-11-01 21:26:00','yyyy-mm-dd hh24:mi:ss') oper_date FROM dual UNION ALL
SELECT to_date('2014-11-01 22:20:00','yyyy-mm-dd hh24:mi:ss') oper_date FROM dual UNION ALL
SELECT to_date('2014-11-02 04:30:00','yyyy-mm-dd hh24:mi:ss') oper_date FROM dual UNION ALL
SELECT to_date('2014-11-02 06:15:00','yyyy-mm-dd hh24:mi:ss') oper_date FROM dual UNION ALL
SELECT to_date('2014-11-02 07:42:00','yyyy-mm-dd hh24:mi:ss') oper_date FROM dual UNION ALL
SELECT to_date('2014-12-01 20:30:00','yyyy-mm-dd hh24:mi:ss') oper_date FROM dual UNION ALL
SELECT to_date('2014-12-01 22:20:00','yyyy-mm-dd hh24:mi:ss') oper_date FROM dual UNION ALL
SELECT to_date('2014-12-02 04:25:00','yyyy-mm-dd hh24:mi:ss') oper_date FROM dual UNION ALL
SELECT to_date('2014-12-02 06:15:00','yyyy-mm-dd hh24:mi:ss') oper_date FROM dual UNION ALL
SELECT to_date('2014-12-02 07:40:00','yyyy-mm-dd hh24:mi:ss') oper_date FROM dual UNION ALL
SELECT to_date('2015-01-01 21:24:00','yyyy-mm-dd hh24:mi:ss') oper_date FROM dual UNION ALL
SELECT to_date('2015-01-01 21:31:00','yyyy-mm-dd hh24:mi:ss') oper_date FROM dual UNION ALL
SELECT to_date('2015-01-01 22:20:00','yyyy-mm-dd hh24:mi:ss') oper_date FROM dual UNION ALL
SELECT to_date('2015-01-02 04:30:00','yyyy-mm-dd hh24:mi:ss') oper_date FROM dual UNION ALL
SELECT to_date('2015-01-02 06:15:00','yyyy-mm-dd hh24:mi:ss') oper_date FROM dual UNION ALL
SELECT to_date('2015-01-02 07:43:00','yyyy-mm-dd hh24:mi:ss') oper_date FROM dual 
)

SELECT oper_date, 
       first_value(oper_date) OVER(PARTITION BY grp ORDER BY oper_date) f_start, 
       first_value(oper_date) OVER(PARTITION BY grp ORDER BY oper_date DESC) f_finish 
FROM (       
  SELECT oper_date, SUM(start_of_group) OVER(ORDER BY oper_date) grp 
    FROM (
     SELECT oper_date, 
           CASE WHEN oper_date > nvl(lag(oper_date + 18/24) OVER(ORDER BY oper_date)  , oper_date-1) THEN 1 ELSE 0 END start_of_group
     FROM t 
    ) 
)