How to identify pattern in SQL

This is my table. It does consist of A,B and C columns. Only one column value will be true at one time.

My task is to identify pattern based on latest five rows. For example

I need to search entire table to find whenever these five values were repeated.

If they were repeated, what was the next value avilable for these pattern and show how many times does A, B and C values were found after the pattern.

How this can be done in SQL? I am using oracle 11g. Thanks.

Answer

You can convert your a, b, c value to a trinary number and then calculate a value for that row and the previous 4 as if the trinary values for the rows comprised a 5-digit trinary number and then use analytic functions to find the next occurrence and to count the occurrences:

SELECT id,
       a,
       b,
       c,
       CASE
       WHEN grp_value IS NULL
       THEN NULL
       ELSE MIN(id) OVER (
              PARTITION BY grp_value
              ORDER     BY id
              ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
            ) + 1
       END  AS row_after_next_match,
       CASE
       WHEN grp_value IS NULL
       THEN 0
       ELSE COUNT(id) OVER ( PARTITION BY grp_value )
       END  AS num_matches
FROM   (
  SELECT id,
         a,
         b,
         c,
         value,
         81 * LAG(value,4) OVER ( ORDER BY id ) +
         27 * LAG(value,3) OVER ( ORDER BY id ) +
          9 * LAG(value,2) OVER ( ORDER BY id ) +
          3 * LAG(value,1) OVER ( ORDER BY id ) +
          1 * value AS grp_value
  FROM   (
    SELECT id,
           a,
           b,
           c,
           DECODE(1,a,0,b,1,c,2) AS value
    FROM   table_name
  )
)
ORDER BY id

Which, for the sample data:

CREATE TABLE table_name (
  id PRIMARY KEY,
  a,
  b,
  c,
  CHECK (a IN (0,1)),
  CHECK (b IN (0,1)),
  CHECK (c IN (0,1)),
  CHECK (a+b+c = 1)
) AS
SELECT  1, 1, 0, 0 FROM DUAL UNION ALL
SELECT  2, 1, 0, 0 FROM DUAL UNION ALL
SELECT  3, 0, 1, 0 FROM DUAL UNION ALL
SELECT  4, 1, 0, 0 FROM DUAL UNION ALL
SELECT  5, 0, 1, 0 FROM DUAL UNION ALL
SELECT  6, 0, 0, 1 FROM DUAL UNION ALL
SELECT  7, 1, 0, 0 FROM DUAL UNION ALL
SELECT  8, 0, 1, 0 FROM DUAL UNION ALL
SELECT  9, 1, 0, 0 FROM DUAL UNION ALL
SELECT 10, 0, 1, 0 FROM DUAL UNION ALL
SELECT 11, 0, 0, 1 FROM DUAL UNION ALL
SELECT 12, 1, 0, 0 FROM DUAL UNION ALL
SELECT 13, 1, 0, 0 FROM DUAL UNION ALL
SELECT 14, 1, 0, 0 FROM DUAL UNION ALL
SELECT 15, 1, 0, 0 FROM DUAL UNION ALL
SELECT 16, 1, 0, 0 FROM DUAL UNION ALL
SELECT 17, 1, 0, 0 FROM DUAL UNION ALL
SELECT 18, 1, 0, 0 FROM DUAL UNION ALL
SELECT 19, 1, 0, 0 FROM DUAL UNION ALL
SELECT 20, 1, 0, 0 FROM DUAL

Outputs:

ID	A	B	C	ROW_AFTER_NEXT_MATCH	NUM_MATCHES
1	1	0	0		0
2	1	0	0		0
3	0	1	0		0
4	1	0	0		0
5	0	1	0		1
6	0	0	1	12	2
7	1	0	0	13	2
8	0	1	0		1
9	1	0	0		1
10	0	1	0		1
11	0	0	1		2
12	1	0	0		2
13	1	0	0		1
14	1	0	0		1
15	1	0	0		1
16	1	0	0	18	5
17	1	0	0	19	5
18	1	0	0	20	5
19	1	0	0	21	5
20	1	0	0		5

db<>fiddle here