Oracle 查找连续5天或以上没有交易的代理商

askTom 2017-05-03

324

问题描述

表1: 交易
字段: AGENT_ID、TRNX_AMT、INSERTED_ON

create table TRANSACTION (AGENT_ID varchar2(10), TRN_AMT number(4,2),INSERTED_ON date);复制

表2: 代理 _ 详细信息
字段: AGNET_ID、名称、状态

create table AGENT_DETAILS(AGENT_ID varchar2(10), NAME varchar2(20),STATUS varchar2(10));

Data on TRANSACTION table
insert into TRANSACTION values( '1111',50.00,to_date('02-FEB-17'));
insert into TRANSACTION values( '1111',50.00,to_date('03-FEB-17'));
insert into TRANSACTION values( '2222',10.00,to_date('03-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('06-FEB-17'));
insert into TRANSACTION values( '1111',15.00,to_date('06-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('07-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('08-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('09-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('10-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('11-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('12-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('13-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('13-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('14-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('15-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('16-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('17-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('18-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('19-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('20-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('23-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('24-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('26-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('27-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('27-FEB-17'));复制

代理表上的数据

insert into AGENT_DETAILS values ('1111','Test1','ACTIVE');
insert into AGENT_DETAILS values ('2222','Test2','ACTIVE');复制

在我解决问题之前的一些历史记录，代理将发布事务，每个事务都在事务表上更新。

我的问题是我需要确定在任何给定月份连续5天没有执行任何交易的代理列表。

寻求帮助以构建查询，这将有助于识别连续5天未执行交易的代理，并给我们此结果

代理id，天数
1111，1
2222，2

对于AGNET_ID-1111其1天2月12日
对于AGNET_ID-2222它的2天-19日和2月26日

专家解答

您可以使用lag() 查找代理的上一个交易的日期。因此，要查找两个事务之间的天数，请从inserted_on中减去lag():

inserted_on - lag(inserted_on) over (partition by agent_id order by inserted_on)复制

然后过滤这个差异> = 5的结果。并按agent_id分组并计数:

with diffs as (
  select t.*, inserted_on - lag(inserted_on) over (partition by agent_id order by inserted_on) d
  from   transaction t
  order  by agent_id, inserted_on
)
  select agent_id, count(*) 
  from   diffs
  where  d >= 5
  group  by agent_id;

AGENT_ID  COUNT(*)  
1111      1         
2222      2  复制

PS: 记住在日期中使用4位数年份!