Pattern based sql query

user_13013828 0
Viewed 50

A Table:

col1          col2          col3           col4
17            5678          abc            mno
16            5678          abc            mno
15            5678          abc            mno
14            5678          abc            mno
13            5678          abc            mno
10            1234          def            pqr

I want to create a column output with either value 'a' or 'b'

 col1          col2          col3           col4           output
 17            5678          abc            mno            b
 16            5678          abc            mno            a
 15            5678          abc            mno            b
 14            5678          abc            mno            b
 13            5678          abc            mno            a 
 10            1234          def            pqr            a

For a partition by (or group) of columns namely col3, col4 and order by col1 and col2, output is marked the value of 'a' the first occurrence of that group (row 5 for example), this row marks an event which can last 3 consecutive times, after which again output column should be marked 'a' (example, row 2) if col1 is in sequence.

window-functions sql
2 Answers

With ROW_NUMBER() window function:

select t.*,
  case mod(row_number() over (partition by t.col3, t.col4 order by t.col1, t.col2), 3) 
    when 1 then 'a'
    else 'b'
  end output
from tablename t 
order by t.col1 desc

See the demo.
Results:

> COL1 | COL2 | COL3 | COL4 | OUTPUT
> ---: | ---: | :--- | :--- | :-----
>   17 | 5678 | abc  | mno  | b     
>   16 | 5678 | abc  | mno  | a     
>   15 | 5678 | abc  | mno  | b     
>   14 | 5678 | abc  | mno  | b     
>   13 | 5678 | abc  | mno  | a     
>   10 | 1234 | def  | pqr  | a
user_104988280

This is a type of gaps-and-islands problem. You need to enumerate the rows in the islands and then some conditional logic:

select t.*,
       (case when mod(row_number() over (partition by col3, col4, seqnum - seqnum_2 order by col1, col2), 3) = 1
             then 'a' else 'b'
        end) as
from (select t.*,
             row_number() over (order by col1, col2) as seqnum,
             row_number() over (partition by col3, col4 order by col1, col2) as seqnum_2
     from t
     ) t;
user_11440350
Related