Hi All! We have a table with sequences of records, which need to be merged into one record if the difference between them is 1. Let's have a sample table with data as below, then we expect to return 2 rows instead of original 4. Wonder about some recursion, but have no experience with it.
Thank you for your help
CREATE TABLE DB.T1 AS ( a INT NOT NULL , b INT NOT NULL ) PRIMARY INDEX (a) ;
INSERT INTO DB.T1 VALUES(1,2); INSERT INTO DB.T1 VALUES(3,4); INSERT INTO DB.T1 VALUES(5,6); INSERT INTO DB.T1 VALUES(8,9);
Expected returned values (a, b) after merge are: 1, 6 8, 9
SELECT MIN(a), MAX(b) FROM ( SELECT a,b, SUM(change_indicator) OVER (ORDER BY b ROWS UNBOUNDED PRECEDING) AS grp FROM ( SELECT a,b, CASE WHEN a - MIN(b) OVER (ORDER BY b ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) = 1 THEN 0 ELSE 1 END AS change_indicator -- difference of current and previous value <> 1? FROM t1 ) AS dt ) AS dt GROUP BY grp
This query might be further simplified to two nested OLAP functions without the aggregate, but it's too late for me now :-)