Extract after comparing set of consecutive rows from another view Oracle

Question

Hi I want to get the sequence id from a view after comparing the set of rows from another view. I can do listagg and group by sequence id and match that listagg with other view.But is there any other option. View 1 has seq_id in order 0,1,2,3,4....and pattern id is also is in order. We can have ROW_NUMBER()over() has the ordering for pattern id

WITH view1(seq_id, pattern_id,rnum) AS (SELECT 0 , 1 ,1 FROM dual UNION
SELECT 0 , 2,2 FROM dual UNION
SELECT 0 , 3,3 FROM dual UNION
SELECT 0 , 4,4 FROM dual UNION
SELECT 1 , 3,5 FROM dual UNION
SELECT 1 , 4,6 FROM dual UNION
SELECT 1 , 1,7 FROM dual UNION
SELECT 1 , 2,8 FROM dual UNION
SELECT 2 , 2,9 FROM dual UNION
SELECT 2 , 4,10 FROM dual UNION
SELECT 2 , 1,11 FROM dual UNION
SELECT 2 , 3,12 FROM dual )

SELECT * FROM view1 order by rnum;

Example :

View 1

    seq id   pattern
    ------   -------
    0          1
    0          2
    0          3
    0          4
    1          3
    1          4
    1          1
    1          2
    2          2
    2          3
    2          5
    2          1

View 2 has another column pk_id which is unique sequence like a rownum. Always start with 1

   pk_id   pattern id
  ------  ------------
    1        3
    2        4
    3        1
    4        2

Expected output from View 1

    seq id
    ------
    1

Answer 1

You can use the ROW_NUMBER analytic function to index each sequence in view1 and then compare it to view2 and find the sequence which matches all the rows of the sequence:

SELECT seq_id,
       MIN(rnum) AS pattern_start
FROM   ( SELECT v1.*,
                ROW_NUMBER() OVER (PARTITION BY seq_id ORDER BY rnum) AS rn,
                COUNT(*) OVER (PARTITION BY seq_id) AS num_patterns
         FROM   view1 v1
       ) v1
       INNER JOIN (
         SELECT v2.*,
                COUNT(*) OVER () AS num_patterns
         FROM   view2 v2
       ) v2
       ON     v1.rn           = v2.pk_id
          AND v1.pattern_id   = v2.pattern_id
          AND v1.num_patterns = v2.num_patterns
GROUP BY seq_id
HAVING COUNT(*) = MAX(v1.num_patterns);

Which, for the sample data:

CREATE VIEW view1 (seq_id, pattern_id, rnum) AS
  SELECT 0, 1,  1 FROM dual UNION ALL
  SELECT 0, 2,  2 FROM dual UNION ALL
  SELECT 0, 3,  3 FROM dual UNION ALL
  SELECT 0, 4,  4 FROM dual UNION ALL
  SELECT 1, 3,  5 FROM dual UNION ALL
  SELECT 1, 4,  6 FROM dual UNION ALL
  SELECT 1, 1,  7 FROM dual UNION ALL
  SELECT 1, 2,  8 FROM dual UNION ALL
  SELECT 2, 2,  9 FROM dual UNION ALL
  SELECT 2, 4, 10 FROM dual UNION ALL
  SELECT 2, 1, 11 FROM dual UNION ALL
  SELECT 2, 3, 12 FROM dual
  ORDER BY 3;

CREATE VIEW view2 (pk_id, pattern_id) AS
  SELECT 1, 3 FROM dual UNION ALL
  SELECT 2, 4 FROM dual UNION ALL
  SELECT 3, 1 FROM dual UNION ALL
  SELECT 4, 2 FROM dual
  ORDER BY 1;

Outputs:

SEQ_ID	PATTERN_START
1	5

fiddle