sql Find the pattern and count how many times it appeared

Question

Data Give

22
22
22
22
22
36
54
40
22
22
22
22
36
22
22
54
22
22

This is the column in table. Using an sql query we need to find out the pattern such as 22 36 54 40 is first pattern then 22 36 is second and 22 54 is third pattern.

Answer 1

PostgreSQL

Assuming:

• There is a column that determines the order of the elements
• All patterns start with 22

---

select      array_to_string(array_agg(val order by i),',')  as pattern
           ,min  (i)                                        as from_i
           ,max  (i)                                        as to_i
           ,count(*)                                        as pattern_length           

from       (select  i,val
                  ,count(case when val = 22 then 1 end) over
                   (
                        order by i
                        rows unbounded preceding
                    ) as pattern_id

            from    mytable
            ) t

 group by   pattern_id

 having     count(*)>1
;

---

+-------------+--------+------+----------------+
|   pattern   | from_i | to_i | pattern_length |
+-------------+--------+------+----------------+
| 22,36,54,40 |      5 |    8 |              4 |
| 22,36       |     12 |   13 |              2 |
| 22,54       |     15 |   16 |              2 |
+-------------+--------+------+----------------+

Answer 2

You should use LEAD to get the value of the next row to see whether it's 22, and use that to get rid of all the extra 22s in the column. Something to this effect:

declare @t table (id int identity(1,1) not null, n int)
insert into @t 
select 22 union all 
select 22 union all 
select 22 union all 
select 22 union all 
select 22 union all 
select 36 union all 
select 54 union all 
select 40 union all 
select 22 union all 
select 22 union all 
select 22 union all 
select 22 union all 
select 36 union all 
select 22 union all 
select 22 union all 
select 54 union all 
select 22 union all 
select 22

select id,n from (select id,n ,lead(n) over (order by id) 
as lead_val from @t ) t where n<>22 or lead_val<>22

This outputs:

5   22
6   36
7   54
8   40
12  22
13  36
15  22
16  54