sql - dense_rank ()over ID values - 如何按顺序而不是值对 ID 编号进行排名
问题描述
我有一种情况,其中有按结束时间“ts_end_utc”排序的事务,并且每个事务都有一个分配给它的代理。我想在遵循时间顺序的同时为每个代理添加一个排名(它们在整个交易中发生变化)。不幸的是,当我使用dense_rank时,它会根据代理ID的值进行排名,并且不遵循“ts_end_utc”的顺序。有什么建议么?
理想情况下,我希望我的排名列“agent_number”以 1 而不是 2 开头,然后在第 8 行切换到 2
dense_rank() over(cte_v3.id_ticket_anchor order by agent 分区) agent_number,
代理等级 | 代理人 | ts_end_utc | id_ticket_anchor |
---|---|---|---|
2 | 200 | 16:53:28 | 95300807 |
2 | 200 | 17:01:36 | 95300807 |
2 | 200 | 17:06:26 | 95300807 |
2 | 200 | 18:09:58 | 95300807 |
2 | 200 | 18:17:50 | 95300807 |
2 | 200 | 18:20:14 | 95300807 |
2 | 200 | 18:51:52 | 95300807 |
3 | 201 | 18:53:24 | 95300807 |
3 | 201 | 19:13:48 | 95300807 |
1 | 199 | 19:15:47 | 95300807 |
期望的结果
代理等级 | 代理人 | ts_end_utc | id_ticket_anchor |
---|---|---|---|
1 | 200 | 16:53:28 | 95300807 |
1 | 200 | 17:01:36 | 95300807 |
1 | 200 | 17:06:26 | 95300807 |
1 | 200 | 18:09:58 | 95300807 |
1 | 200 | 18:17:50 | 95300807 |
1 | 200 | 18:20:14 | 95300807 |
1 | 200 | 18:51:52 | 95300807 |
2 | 201 | 18:53:24 | 95300807 |
2 | 201 | 19:13:48 | 95300807 |
3 | 199 | 19:15:47 | 95300807 |
第二个场景 - 当前结果
代理等级 | 代理人 | ts_end_utc | id_ticket_anchor |
---|---|---|---|
1 | 200 | 16:53:28 | 95300807 |
1 | 200 | 17:01:36 | 95300807 |
1 | 200 | 17:06:26 | 95300807 |
1 | 200 | 18:09:58 | 95300807 |
1 | 200 | 18:17:50 | 95300807 |
1 | 200 | 18:20:14 | 95300807 |
1 | 200 | 18:51:52 | 95300807 |
2 | 201 | 18:53:24 | 95300807 |
2 | 201 | 19:13:48 | 95300807 |
3 | 199 | 19:15:47 | 95300807 |
1 | 200 | 19:16:55 | 95300807 |
第二个场景 - 期望的结果
代理等级 | 代理人 | ts_end_utc | id_ticket_anchor |
---|---|---|---|
1 | 200 | 16:53:28 | 95300807 |
1 | 200 | 17:01:36 | 95300807 |
1 | 200 | 17:06:26 | 95300807 |
1 | 200 | 18:09:58 | 95300807 |
1 | 200 | 18:17:50 | 95300807 |
1 | 200 | 18:20:14 | 95300807 |
1 | 200 | 18:51:52 | 95300807 |
2 | 201 | 18:53:24 | 95300807 |
2 | 201 | 19:13:48 | 95300807 |
3 | 199 | 19:15:47 | 95300807 |
4 | 200 | 19:16:55 | 95300807 |
解决方案
由于 id_ticket_anchor 中的单个代理有不同的 ts_end_utc,我首先计算了每个具有min(ts_end_utc)over(partition by id_ticket_anchor, agent)
窗口函数的 id_ticket_anchor 明智代理的最小 ts_end_utc。
然后按顺序使用 的子句dense_rank()
。可能有多个代理具有相同的最小 ts_end_utc,因此agent
也按顺序使用。
DB-小提琴:
create table cte_v3(agent int, ts_end_utc time, id_ticket_anchor int);
insert into cte_v3 values(200 ,'16:53:28','95300807');
insert into cte_v3 values(200 ,'17:01:36','95300807');
insert into cte_v3 values(200 ,'17:06:26','95300807');
insert into cte_v3 values(200 ,'18:09:58','95300807');
insert into cte_v3 values(200 ,'18:17:50','95300807');
insert into cte_v3 values(200 ,'18:20:14','95300807');
insert into cte_v3 values(200 ,'18:51:52','95300807');
insert into cte_v3 values(201 ,'18:53:24','95300807');
insert into cte_v3 values(201 ,'19:13:48','95300807');
insert into cte_v3 values(199 ,'19:15:47','95300807');
询问:
With cte as
(
select agent,ts_end_utc,id_ticket_anchor,
min(ts_end_utc)over(partition by id_ticket_anchor, agent) min_ts_end_utc
from cte_v3
)
select dense_rank()over(partition by id_ticket_anchor order by min_ts_end_utc,agent)agent_rank,
agent,ts_end_utc,id_ticket_anchor
from cte
输出:
代理等级 | 代理人 | ts_end_utc | id_ticket_anchor |
---|---|---|---|
1 | 200 | 16:53:28.0000000 | 95300807 |
1 | 200 | 17:01:36.0000000 | 95300807 |
1 | 200 | 17:06:26.0000000 | 95300807 |
1 | 200 | 18:09:58.0000000 | 95300807 |
1 | 200 | 18:17:50.0000000 | 95300807 |
1 | 200 | 18:20:14.0000000 | 95300807 |
1 | 200 | 18:51:52.0000000 | 95300807 |
2 | 201 | 18:53:24.0000000 | 95300807 |
2 | 201 | 19:13:48.0000000 | 95300807 |
3 | 199 | 19:15:47.0000000 | 95300807 |
db<小提琴在这里