how to combine product with Period of validity using LEAD function in oracle?

Question

:)

How to combine product with Period of validity using LEAD function?

Any idea how i can select device_id, valid_from,valid_to,product in the sense that if the next product / record is the same as the previous one, we take this product for the entire validity period?

DEVICE_ID| VALID_FROM | VALID_TO | PRODUCT
Y0001     01.01.2012   10.01.2012    Y
Y0001     10.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

The expected result of the query I would like to get is as follows:

returns only 4 records : (withhout changing order)

DEVICE_ID| VALID_FROM | VALID_TO | PRODUCT
Y0001      01.01.2012   20.01.2012    Y    -- IF next row was the same row - Y then combine period of validity
Y0001      10.01.2013   20.01.2013    Z
Y0001      21.01.2013   30.01.2013    Y
Y0001      12.02.2013   21.02.2013    X

Details:

!*! When next record is the same product, then it should return one product with combine period of validity (taking min valid_from (default value of first row?) and max valid_to of second Y)

Example:

DEVICE_ID| VALID_FROM | VALID_TO | PRODUCT | NEXT_ROW
Y0001     01.01.2012   10.01.2012    Y          Y
Y0001     10.01.2012   20.01.2012    Y          Z -- NOT Y
Y0001     10.01.2013   20.01.2013    Z          0 

Result:

DEVICE_ID| VALID_FROM | VALID_TO | PRODUCT | NEXT_ROW
Y0001     01.01.2012   20.01.2012    Y        Z       
Y0001     10.01.2013   20.01.2013    Z        0 

For now i have start code:

Select device_id
       ,valid_from
       ,valid_to
       ,LEAD(product,1,0) OVER (order by 1) as next_row
from test

and it returns:

DEVICE_ID| VALID_FROM | VALID_TO | PRODUCT | NEXT_ROW
Y0001      01.01.2012   10.01.2012    Y         Y
Y0001      10.01.2012   20.01.2012    Y         Z
Y0001      10.01.2013   20.01.2013    Z         Y
Y0001      21.01.2013   30.01.2013    Y         X
Y0001      12.02.2013   21.02.2013    X         0

Is there any way with subquery or something else to return like i show before?

Answer 1

From Oracle 12, you can solve row-by-row pattern matching problems simply and efficiently using 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

or:

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

Which, for the sample data:

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;

Both output:

DEVICE_ID	VALID_FROM	VALID_TO	PRODUCT
Y0001	2012-01-01 00:00:00	2012-01-20 00:00:00	Y
Y0001	2013-01-10 00:00:00	2013-01-20 00:00:00	Z
Y0001	2013-01-21 00:00:00	2013-01-30 00:00:00	Y
Y0001	2013-02-12 00:00:00	2013-02-21 00:00:00	X

fiddle