值发生变化时抓取最后一个日期

Question

我需要抓取最后一次运行日期，然后更改客户流失值。我需要返回以下结果：

> customer  billto   banner     rundate        churn    lastchurndate

> 976193     976193   GexPro     12/04/2019     true     11/26/2019
> 976193     976193   GexPro     11/26/2019     true     11/26/2019  
> 976193     976193   GexPro     11/19/2019     false    11/26/2019  

这是当前的原始数据集：

> customer  billto   banner     rundate        churn    

> 976193     976193  GexPro     12/04/2019     true      

>976193     976193   GexPro     11/26/2019     true     

>976193     976193   GexPro     11/19/2019     false

2019年11/26，流失值从False变为True。

如果我要查询截至今天的当前运行日期，我需要知道最后的日期是何时流失<>当前流失日期。

我有一个问题，我可以得到11/19/2019，但它不是正确的退出。

select
    v.st_cust_no
    ,v.bt_cust_no
    ,v.banner
    ,v.rundate
    ,v.churn
    ,case when c."last churn date" is null then v.rundate
        else c."last churn date"
     end as "last churn date" 
    ,c."last churn" 
    from 
     dwstage v
    left join 
    (    
    select 
     z.st_cust_no
    ,z.BT_CUST_NO
    ,z.banner
    ,z.rundate 
    ,z.churn as "current churn" 
    ,x.latest as "last churn date" 
    ,x.churn "last churn" 

    from dwstage    z

        inner join 
        (select 
             st_cust_no
            ,BT_CUST_NO
            ,banner
            ,churn
            ,max(rundate) as latest 
            from dwstage
            group by 
                 st_cust_no
                ,BT_CUST_NO
                ,banner
                ,churn) x

        on z.st_cust_no = x.st_cust_no
        and z.bt_cust_no = x.bt_cust_no
        and z.BANNER = x.BANNER
    where z.churn <> x.churn
    ) c
     on v.st_cust_no = c.st_cust_no
        and v.bt_cust_no = c.bt_cust_no
        and v.BANNER = c.BANNER
        and v.rundate = c.rundate
-- where v.st_cust_no = '14025'
order by 
    v.st_cust_no
   ,v.banner
   ,v.rundate desc 
;

Answer 1

您可以按如下方式利用分析函数：

Select * from
  (Select t.*,
          Row_number() over (partition by customer order by rundate desc nulls last) as rn
   from
     (Select t.*, 
             case when lag(chrun) over (partition by cutomer order by rundate) <> chrun then 1 end as chrunchanged
        From your_table t) t
  Where chrunchanged = 1)
Where rn = 1

干杯！！

Answer 2

您希望在每一行上显示日期，因此不需要任何过滤。这建议使用窗口函数：

select s.*,
       max(case when prev_churn <> churn then rundate) over (partition by customer, billto) as last_churndate
from (select s.*,
             lag(churn) over (order by customer, billto order by rundate) as prev_churn
      from dwstage s
     ) s;

Answer 3

Oracle12.1引入了match_recognize子句，它可以非常快速地解决这类问题。这里有一种方法。我首先创建一个小的测试表，使用两个不同的客户来测试查询在更一般的场景中是否正确工作。

表创建：

create table dwstage (customer, billto,  banner, rundate, churn) as
  select 976193, 976193, 'GexPro', to_date('12/04/2019', 'mm/dd/yyyy'), 'true'  from dual union all
  select 976193, 976193, 'GexPro', to_date('11/26/2019', 'mm/dd/yyyy'), 'true'  from dual union all
  select 976193, 976193, 'GexPro', to_date('11/19/2019', 'mm/dd/yyyy'), 'false' from dual union all
  select 999999, 999999, 'Banner', to_date('03/21/2019', 'mm/dd/yyyy'), 'false' from dual union all
  select 999999, 999999, 'Banner', to_date('04/21/2019', 'mm/dd/yyyy'), 'true'  from dual union all
  select 999999, 999999, 'Banner', to_date('08/03/2019', 'mm/dd/yyyy'), 'false' from dual union all
  select 999999, 999999, 'Other' , to_date('08/31/2019', 'mm/dd/yyyy'), 'false' from dual
;

查询和输出：

select customer, billto, banner, rundate, churn, lastchurndate
from   dwstage
match_recognize(
  partition by customer
  order     by rundate desc
  measures  final last(a.rundate) as lastchurndate
  all rows  per match
  pattern   (^ a+ b*)
  define    a as churn = first(churn)
)
;


  CUSTOMER     BILLTO BANNER RUNDATE    CHURN LASTCHURND
---------- ---------- ------ ---------- ----- ----------
    976193     976193 GexPro 12/04/2019 true  11/26/2019
    976193     976193 GexPro 11/26/2019 true  11/26/2019
    976193     976193 GexPro 11/19/2019 false 11/26/2019
    999999     999999 Other  08/31/2019 false 08/03/2019
    999999     999999 Banner 08/03/2019 false 08/03/2019
    999999     999999 Banner 04/21/2019 true  08/03/2019
    999999     999999 Banner 03/21/2019 false 08/03/2019