我想在某些条件下分配排名

发布于 2025-02-01 02:20:36 字数 837 浏览 3 评论 0原文

我想根据数据集中每个'detter_id'的顺序，将等级分配给“ drug_name”。（在这里，为了描述问题，我只在图像中显示一个患者_id）

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;

使用此查询，我将获得以下输出，

尽管我想要这样的东西（如下图所示，如下图所示）

请帮助我了解查询中我错过的内容以及如何解决此问题。谢谢！！

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

岁月流歌 2025-02-08 02:20:36

使用此cte进行数据：


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')
)

并使用 conditonal_change_event 想要

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;

：

deration_id	svcdate	drug_name	等级
110	2018-08-09	Acid	1
110	2020-05-28	takhzyro	2
110	2020-06-10	icatibant	3
110	2020-06-24	给出	tranexamic
		的	您
110	2020-07-24	ICATIBANT	5
110	2020-08-31	ICATIBANT	5
110	2020-08-31	TAKHZYRO	6

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:

PATIENT_ID	SVCDATE	DRUG_NAME	RANK
110	2018-08-09	TRANEXAMIC ACID	1
110	2020-05-28	TAKHZYRO	2
110	2020-06-10	ICATIBANT	3
110	2020-06-24	TAKHZYRO	4
110	2020-07-22	TAKHZYRO	4
110	2020-07-24	ICATIBANT	5
110	2020-08-31	ICATIBANT	5
110	2020-08-31	TAKHZYRO	6

回复收藏 0 原文

分開簡單 2025-02-08 02:20:36

我们可以尝试在子查询中使用lag窗口函数以获取每个先前的drug_name，然后按条件汇总窗口函数比较rank列。

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;

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;

回复收藏 0 原文

~没有更多了~