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.
Asked
Active
Viewed 310 times
-3
David דודו Markovitz
- 42,900
- 6
- 64
- 88
Himanshu Dhingra
- 317
- 3
- 7
-
1What is your definition of a "pattern" ? is there another column in the table that will define the sequence ? – Squirrel Apr 19 '17 at 04:43
-
1So what is it? Microsoft SQL Server or PostgreSQL? Those are two very different DBMS. – Apr 19 '17 at 04:51
-
1Also: what defines the sort order of those rows? Rows in a relational database are not "sorted", so if you need a specific order of rows you need a column to sort by. – Apr 19 '17 at 05:33
-
Voted to close as unclear what the question is asking. – Giorgos Altanis Apr 19 '17 at 05:59
-
We are using redshift – Himanshu Dhingra Apr 19 '17 at 07:38
-
It is sorted by date – Himanshu Dhingra Apr 19 '17 at 07:39
-
@HimanshuDhingra - change your data sample accordingly – David דודו Markovitz Apr 19 '17 at 12:10
2 Answers
1
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
Max Szczurek
- 4,324
- 2
- 20
- 32
-
This is a bad solution for 2 reasons: **(1)** The patterns can't be seen in this result format. E.g. there is nothing that indicates that id 12 and id 13 belongs to the same pattern. **(2)** This result can be achieved by a much simpler code `select i,val from (select i,val ,lead(val) over (order by i) as lead_val from mytable ) t where val<>22 or lead_val<>22` – David דודו Markovitz Apr 19 '17 at 12:22
-
Yes, that's a simpler query - I updated my answer in acknowledgment. – Max Szczurek Apr 20 '17 at 04:53
0
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 |
+-------------+--------+------+----------------+
David דודו Markovitz
- 42,900
- 6
- 64
- 88