I want to assign the rank to 'drug_name' as per the order of 'svcdate' for each 'patient_id' in a dataset. (here, to describe the issue I'm only showing one patient_id in the image)

select patient_id
    ,svcdate
    ,drug_name
    ,dense_rank() over(partition by patient_id order by first_date) as rank
from (
    select *
        ,first_value(svcdate) over (
           partition by patient_id, drug_name 
           order by svcdate) as first_date
    from table
)
order by 1,2;

With this query I'm getting the following output,

Although, I want something like this (as shown in image below)

Please help me understand what I'm missing out in the query and how to address this issue. Thanks!!

CodePudding user response：

We can try to use LAG window function in the subquery to get each previous drug_name, then compare by condition aggregate window function to make rank column.

select patient_id
    ,svcdate
    ,drug_name
    ,SUM(CASE WHEN prev_drug_name <> drug_name THEN 1 ELSE 0 END) over(partition by patient_id order by first_date) as rank
from (
    select *,LAG(drug_name) OVER(partition by patient_id ORDER BY svcdate) prev_drug_name
    from table
)
order by 1,2;

CodePudding user response：

using this CTE for the data:

with data(patient_id, svcdate, drug_name) as (
    select * from values
    (110, '2018-08-09'::date, 'TRANEXAMIC ACID'),
    (110, '2020-05-28'::date, 'TAKHZYRO'),
    (110, '2020-06-10'::date, 'ICATIBANT'),
    (110, '2020-06-24'::date, 'TAKHZYRO'),
    (110, '2020-07-22'::date, 'TAKHZYRO'),
    (110, '2020-07-24'::date, 'ICATIBANT'),
    (110, '2020-08-31'::date, 'ICATIBANT'),
    (110, '2020-08-31'::date, 'TAKHZYRO')
)

And using CONDITONAL_CHANGE_EVENT gives you what you want

select patient_id
    ,svcdate
    ,drug_name
    ,CONDITIONAL_CHANGE_EVENT( drug_name ) OVER ( 
        PARTITION BY patient_id ORDER BY svcdate ) 1 as rank
from data
order by 1,2;

gives: