CODEWITHSUNDEEP
sqloracle-databasewindow-functions
Eithan
Eithan

Reputation: 143

Oracle: Rank with condition and keeping history

I have this sample table:

enter image description here

I need to create a rank column that will order the id's based on is_true=1, meaning that every change in is_true from 0 to 1 will increase the index by 1. Something like the following:

enter image description here

I tried something like:

select id, customer_id, is_true,  
       rank() over (partition by customer_id order by (case when is_true=1 then id end)) index_rank 
from table

but it does not give what I need.

Any help will be much appreciated.

Upvotes: 2

Views: 180

Answers (2)

George Joseph
George Joseph

Reputation: 5932

improving upon the query provided by GMB

select x.*
       ,sum(case when (is_true=1 and lag_val=0) or (lag_val is null) then 1 end) over(partition by customer_id order by id) index_rank
  from (select *
               ,lag(is_true) over(partition by customer_id order by id) lag_val       
          from dbo.t 
        )x

OLD Query

select id
       ,customer_id
       ,is_true
       ,dense_rank() over(partition by customer_id order by customer_id,max_grp)
   from(
        select id
               ,customer_id
               ,is_true
               ,max(is_rank) over(partition by customer_id order by id) max_grp
          from (select id,customer_id,is_true
                       ,case when is_true=1 and lag(is_true) over(partition by customer_id order by id)=0 
                              or  lag(is_true) over(partition by customer_id order by id) is null then
                             DENSE_RANK() over(partition by customer_id order by id)
                        end as is_rank
                   from dbo.t
                 )x
      )y

Upvotes: 1

GMB
GMB

Reputation: 222632

You can do a window sum:

select
    t.*,
    1 + sum(is_true) over(partition by cutsomer_id order by id) index_rank
from mytable t

If you want to allow consecutive 1s without increasing the rank, then you can use lag() first:

select
    t.*,
    1 + sum(case when is_true = 1 and lag_is_true = 0 then 1 else 0 end) 
        over(partition by cutsomer_id order by id) index_rank
from (
    select 
        t.*,
        lag(is_true) over(partition by cutsomer_id order by id) lag_is_true
    from mytable t
) t

Upvotes: 3

Related Questions