您需要一组子查询来生成所需的结果-

with grp_starts as (
  select device_id, product, start_date, end_date,
  case
    when start_date > max(end_date) over(
      partition by DEVICE_ID, product order by start_date, end_date
      rows between unbounded preceding and 1 preceding
    )
    then 1 else 0
  end grp_start
  from DATA
)
, grps as (
  select device_id, product, start_date, end_date,
  sum(grp_start) over(
    partition by device_id, product order by start_date, end_date
  ) grp
  from grp_starts
)
select device_id, product,
min(start_date) start_date,
max(end_date) end_date
from grps
group by device_id, product, grp
order by 3, 4;

演示。

对我来说，它看起来不像一个LEAD，而是一种 * 差距和岛屿 * 问题。
样本数据：

SQL> with test (device_id, valid_from, valid_to, product) as
  2    (select 'Y0001', date '2012-01-01', date '2012-01-10', 'Y' from dual union all
  3     select 'Y0001', date '2012-01-10', date '2012-01-20', 'Y' from dual union all
  4     select 'Y0001', date '2013-01-10', date '2013-01-20', 'Z' from dual union all
  5     select 'Y0001', date '2013-01-21', date '2013-01-30', 'Y' from dual union all
  6     select 'Y0001', date '2013-02-12', date '2013-02-21', 'X' from dual
  7    ),

查询从这里开始，首先查找数据组（grp列）：

8  grp as
  9    (select device_id, valid_from, valid_to, product,
 10       rownum - row_number() over (partition by device_id, product order by valid_From) grp
 11     from test
 12    )

最后，获取每个器械、产品和组的最小和最大日期值：

13  select device_id,
 14         min(valid_from) valid_from,
 15         max(valid_to)   valid_to,
 16         product
 17  from grp
 18  group by device_id, product, grp
 19  order by valid_from;

DEVICE_ID  VALID_FROM VALID_TO   PRODUCT
---------- ---------- ---------- ----------
Y0001      01.01.2012 20.01.2012 Y
Y0001      10.01.2013 20.01.2013 Z
Y0001      21.01.2013 30.01.2013 Y
Y0001      12.02.2013 21.02.2013 X

SQL>

型

从Oracle 12开始，您可以使用MATCH_RECOGNIZE简单高效地解决逐行模式匹配问题：

SELECT device_id, valid_from, valid_to, product
FROM   table_name
MATCH_RECOGNIZE (
  PARTITION BY device_id, product
  ORDER BY valid_from
  MEASURES
    FIRST(valid_from) AS valid_from,
    MAX(valid_to) AS valid_to
  PATTERN ( overlapping* any_row )
  DEFINE
    overlapping AS MAX(valid_to) >= NEXT(valid_from)
)
ORDER BY device_id, valid_from

其中，对于示例数据：

CREATE TABLE table_name (DEVICE_ID, VALID_FROM, VALID_TO, PRODUCT) AS
SELECT 'Y0001', DATE '2012-01-01', DATE '2012-01-10', 'Y' FROM DUAL UNION ALL
SELECT 'Y0001', DATE '2012-01-10', DATE '2012-01-20', 'Y' FROM DUAL UNION ALL
SELECT 'Y0001', DATE '2013-01-10', DATE '2013-01-20', 'Z' FROM DUAL UNION ALL
SELECT 'Y0001', DATE '2013-01-21', DATE '2013-01-30', 'Y' FROM DUAL UNION ALL
SELECT 'Y0001', DATE '2013-02-12', DATE '2013-02-21', 'X' FROM DUAL;

输出：
| 器械ID|有效_自|有效期至|产品|
| - ------|- ------|- ------|- ------|
| Y0001| 2012年01月01日00时00分|2012年1月20日上午00时00分|是|
| Y0001| 2013年1月10日上午00时00分|2013年1月20日上午00时00分|Z型|
| Y0001| 2013年1月21日上午00时00分|2013年1月30日上午00时00分|是|
| Y0001| 2013年2月12日00：00：00| 2013年2月21日上午00时00分|十|
fiddle