时间戳

首页 > 解决方案 > SQL 按行连续重复值

sql - SQL 按行连续重复值

问题描述

我有下表：

id            calltime                   call_end 
1001   2020-04-05 12:00:00.000            answered
1002   2020-05-05 13:23:02.000            not answered
1002   2020-05-05 13:25:02.000            not answered
1002   2020-05-05 13:28:02.000            answered
1002   2020-05-06 13:23:02.000            not answered
...

我正在尝试添加一个按 id 对 call_end 列进行排名的新列，但我希望在 call_end 值更改时将排名重置为 1。例如：

id            calltime                   call_end       rank
1001   2020-04-05 12:00:00.000            answered       1
1002   2020-05-05 13:23:02.000            not answered   1
1002   2020-05-05 13:25:02.000            not answered   2
1002   2020-05-05 13:28:02.000            answered       1
1002   2020-05-06 13:23:02.000            not answered   1
...

但是，当我使用 RANK() OVER（按 id 分区，按呼叫时间的 call_end 顺序）时，它给了我以下排名：

id            calltime                   call_end       rank
1001   2020-04-05 12:00:00.000            answered       1
1002   2020-05-05 13:23:02.000            not answered   1
1002   2020-05-05 13:25:02.000            not answered   2
1002   2020-05-05 13:28:02.000            answered       1
1002   2020-05-06 13:23:02.000            not answered   3
...

当连续值停止时，有没有办法将排名重置为 1？

标签： sql

解决方案

这是一个可能的解决方案，lag()用于比较上一行并sum over识别差异：

有样本数据

create table t (id int, calltime datetime, call_end varchar(20))
insert into t values
(1001,'20200405 12:00:00.000','answered'),
(1002,'20200505 13:23:02.000','not answered'),
(1002,'20200505 13:25:02.000','not answered'),
(1002,'20200505 13:28:02.000','answered'),
(1002,'20200506 13:23:02.000','not answered')


with c as (
    select Id, CallTime, Call_End, sum(case when call_end = call_endPrev then 0 else 1 end)  over(order by calltime rows unbounded preceding) as diff 
    from (
        select *, Lag(call_end) over(partition by id order by call_end) call_endPrev
        from t
    )x
)
select Id, CallTime, Call_End, row_number() over(partition by id, diff order by call_end) as [Rank]
from c
order by id, calltime

推荐阅读