I have a table looking like this:

Column "GR" is the info I want to use for grouping.
Column "I" holds some none important info.
Column "VON" holds originally a date value - for readability I used a number here.
The same goes for column "BIS".

So the records hold some information that is valid for the interval which is specified by "VON" and "BIS" and should be grouped by "GR".

I'm trying to build a query to get the result like this:

That means, I want to merge rows with the same grouping information - in the example by merging records 3 and 4.

What I came up with was this query - but this only works for distinct groups:

select distinct  gr
      ,first_value(i) over(partition by gr order by von asc) i
      ,first_value(von) over(partition by gr order by von asc) von
      ,first_value(bis) over(partition by gr order by von desc) bis
  from table_test
;

With our test data it leads to this result, which is obviously wrong:

Any ideas how to solve this with a query? (I know I could do it with PL/SQL, but it needs to be pure SQL ...)

CodePudding user response：

From Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row processing:

SELECT *
FROM   table_test
MATCH_RECOGNIZE(
  ORDER BY von
  MEASURES
    FIRST(gr) AS gr,
    FIRST(i) AS i,
    FIRST(von) AS von,
    LAST(bis) AS bis
  PATTERN (same_gr )
  DEFINE same_gr AS FIRST(gr) = gr
)

Which, for your sample data:

CREATE TABLE table_test (GR, I, VON, BIS) AS
SELECT 1, 'a', 1, 2 FROM DUAL UNION ALL
SELECT 2, 'b', 2, 3 FROM DUAL UNION ALL
SELECT 1, 'c', 3, 4 FROM DUAL UNION ALL
SELECT 1, 'd', 4, 5 FROM DUAL UNION ALL
SELECT 3, 'e', 5, 6 FROM DUAL;

Outputs:

GR	I	VON	BIS
1	a	1	2
2	b	2	3
1	c	3	5
3	e	5	6

db<>fiddle here

CodePudding user response：

You can also try below solution if you are not using Oracle 12c and later.

1. I built a useful inline colonne "gaps" in the first inline view "t".
2. Then, In the outer inline view "tt", I built another inline column "group_id" based on the previous "gaps" column. The purpose of that column is to fill in the gaps of the "gaps" column.
3. Finally, I grouped all the rows by the "GR" and "group_id" columns, before applying min()keep() clause to get the desired ouput

SELECT   GR
       , MIN(I)KEEP(dense_rank FIRST ORDER BY VON ASC) AS I
       , MIN(VON)KEEP(dense_rank FIRST ORDER BY VON ASC) AS VON
       , MIN(VON)KEEP(dense_rank LAST ORDER BY VON ASC) AS BIS         
FROM (
  SELECT  GR, I, VON, BIS, last_value( GAPS IGNORE NULLS )OVER( ORDER BY von ) group_id
   FROM (
     select t.*
     , CASE WHEN GR != LAG(GR, 1, -GR) OVER ( ORDER BY VON ) 
                 THEN ROW_NUMBER()OVER(ORDER BY VON)
            ELSE NULL
       END AS gaps      
    from your_tab t
  ) t
) tt
GROUP BY GR, group_id
ORDER BY VON
;

db<>fiddle here