Oracle 查找违反规则的号码和日期

askTom 2017-05-30

128

问题描述

亲爱的克里斯，

我有这样的表

Account_number        Opening_date

1234                  16/05/2017
1235                  16/05/2017
1236                  23/04/2017
1237                  25/04/2017
1238                  25/04/2017
1239                  26/05/2017
1240                  11/05/2017复制

这里的账号是用一个开业日期生成的。规则是，当您开户时，账号和开户日期应该大于之前的账号和日期。但是顺序已经打破了一些方式。所以我需要一个sql查询，找到违反规则的帐号和日期。

例如，我需要通过查询输出1236，1237，1238，1240帐号和打开日期，因为这些帐户违反了规则。我在表中有数千个帐号和开放日期。所以我需要找到那些违反规则的账户和开户日期。

我已经使用了您给出的查询。查询为:

WITH RAW_DATA AS (
 SELECT  * FROM ACC3 
), PREV_DATA AS (
 SELECT R.*, LAG(ACCOUNT_OPENDATE,1) OVER (ORDER BY ACCOUNT_NUMBER) PREV_DATE
 FROM   RAW_DATA R
)
 SELECT * FROM PREV_DATA
 WHERE  ACCOUNT_OPENDATE < PREV_DATE;复制

但问题是这个查询只返回不匹配开始的地方。此查询仅给出->> 1236 23/04/2017。

但是我也需要找到另一个帐户。请帮帮我。

专家解答

所以你想找到第一个打破序列之后的所有行？

如果是这样，您可以有一个大小写表达式来检查当前日期何时小于先前按id排序的日期。然后返回这些的最小值。

最后返回当前id为> = 以上计算值的所有行:

with rws as (
  select 1 id, date'2015-01-01' dt from dual union all 
  select 2 id, date'2017-01-01' dt from dual union all 
  select 3 id, date'2016-01-01' dt from dual union all 
  select 4 id, date'2018-01-01' dt from dual 
), prev as (
  select r.*, lag(dt) over (order by id) prev_dt
  from   rws r
), diff as (
  select p.*, min(case when dt < prev_dt then id end) over () diff_id from prev p
)
  select * from diff
  where  id >= diff_id;

ID  DT                    PREV_DT               DIFF_ID  
3   01-JAN-2016 00:00:00  01-JAN-2017 00:00:00  3        
4   01-JAN-2018 00:00:00  01-JAN-2016 00:00:00  3      复制