Найти шаблон и посчитать, сколько раз он появился

Question

Данные дают

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

Это столбец в таблице. Используя SQL-запрос, мы должны выяснить, что шаблон 22 36 54 40 является первым шаблоном, затем 22 36 является вторым и 22 54 является третьим шаблоном.

Answer 1

Вы должны использовать LEAD, чтобы получить значение следующей строки, чтобы увидеть, является ли оно 22, и использовать это, чтобы избавиться от всех лишних 22 в столбце. Что-то на этот счет:

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

Это выводит:

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

Answer 2

PostgreSQL

Предполагая, что:

• Есть столбец, который определяет порядок элементов
• Все шаблоны начинаются с 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 |
+-------------+--------+------+----------------+