时间戳

首页 > 解决方案 > 拉取日期记录在当前状态是第一个

sql - 拉取日期记录在当前状态是第一个

问题描述

我有一个包含当前记录信息的表和一个历史表。每次更新记录时，都会在当前表中创建一条新记录，并将前一条记录移动到历史表中。我想返回当前客户端、连接、状态首次设置的日期。关键是连接可能会移出并返回当前状态，因此我不能只是一般地在历史表中查找与 ConnectionStatus 表中的客户端、连接、状态相匹配的第一个日期。在下面的示例中，我希望从历史记录表中的第二条记录返回 DateChanged - 1-19-2021。

连接状态

客户 联系 地位 日期更改
A B C D 1234 4 2021 年 1 月 27 日

连接状态历史

客户 联系 地位 日期更改
A B C D 1234 4 2021 年 1 月 24 日
A B C D 1234 4 2021 年 1 月 19 日
A B C D 1234 3 2021 年 1 月 16 日
A B C D 1234 3 2021 年 1 月 12 日
A B C D 1234 4 2021 年 1 月 8 日

标签： sqlsql-servertsql

解决方案

这有点棘手。一种方法是获取与另一个状态相关联的最新日期，然后只选择大于该日期的行：

select csh.client, csh.connection, csh.status, min(csh.date)
from (select t.*,
             max(case when most_recent_status <> status then date end) over (partition by client, connection) as other_date
      from (select csh.*,
                   first_value(csh.status) over (partition by client, connection order by date desc) as most_recent_status
            from ConnectionStatusHistory csh 
           ) csh
     ) csh
where date > other_date or other_date is null
group by client, connection, status;

更聪明的方法使用row_number()：

select client, connection, status, min(date)
from (select csh.*,
             row_number() over (partition by client, connection order by date desc) as seqnum,
             row_number() over (partition by client, connection, status order by date desc) as seqnum_s
      from ConnectionStatusHistory csh 
     ) csh
where seqnum = seqnum_s
group by client, connection, status;

这更令人费解。但是如果您查看子查询的结果，您会看到它seqnum从最近的 1 开始枚举所有行。然后seqnum_s按状态枚举行。仅当状态为最新状态时，这些值才相等。

推荐阅读