时间戳

首页 > 解决方案 > Oracle SQL - 返回当前月份组中至少有一行并且上个月的班级发生变化的行

sql - Oracle SQL - 返回当前月份组中至少有一行并且上个月的班级发生变化的行

问题描述

我正在尝试输出满足以下条件的行：

我的数据每个客户每月可以有多行，我只对每个客户每月的最新行感兴趣。

这是我的数据示例：

ID      Client ID  Class    Date
14609   87415      C        04/DEC/18
13859   87415      X        16/AUG/18
11906   87415      C        27/FEB/17
10667   87415      B        23/JAN/17
14538   132595     D        03/DEC/18
14567   141805     C        04/DEC/18
14411   141805     A        27/NOV/18

基于上述的期望输出是：

ID      Client ID  Class    Date
14609   87415      C        04/DEC/18
13859   87415      X        16/AUG/18
14567   141805     C        04/DEC/18
14411   141805     A        27/NOV/18

我对此进行了多次尝试，但成功率为零。任何帮助将不胜感激。我的尝试无法找到前一行。：/

select * from
(
select drh.defaultriskhistid, drh.clientid, cv.description, 
drh.updatetimestamp
from default_risk_history drh 
inner join code_values cv on drh.defaultriskcodeid = cv.codevalueid
where
defaultriskhistid in
(select max(defaultriskhistid) from default_risk_history
group by clientid, ltrim(TO_CHAR(updatetimestamp,'mm-yyyy'),'0'))
) t
where
(
Select count(*) from default_risk_history drh1 where drh1.clientid = 
t.clientid and ltrim(TO_CHAR(drh1.updatetimestamp,'mm-yyyy'),'0') = 
ltrim(TO_CHAR(current_date,'mm-yyyy'),'0')
) >=1
order by clientid, updatetimestamp desc

标签： sqloracle

解决方案

您似乎想要最近的两行，如果它们有不同的类并且最近的行是在当前月份。如果是这样：

select t.*
from (select t.*,
             max(date) over (partition by clientid) as max_date,
             lag(class) over (partition by client_id order by date) as prev_class,
             lead(class) over (partition by client_id order by date) as next_class,
             row_number() over (partition by clientid order by date desc) as seqnum
      from t
     ) t
where max_date >= trunc(sysdate, 'MON') and
      ( (seqnum = 1 and prev_class <> class) or
        (seqnum = 2 and next_class <> class)
      );

推荐阅读