SQL查找模式并计算出现多少次

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

您应该使用铅来获取下一行的值，以查看它是否是22，并使用它来摆脱列中的所有额外22s。为此效果：

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 |
+-------------+--------+------+----------------+