SQL在Netezza的日期范围内找到差距

Question

我想获得日期范围之间的差距，并且需要它以通过MM ID分隔间隙。

您能请某人帮助我在Netezza的下面解决

输入：

ID | MM Number | From | To 
12345|4444444 |2015-01-03 |2015-02-02 
12346|4444444 |2015-02-03 |2015-03-02 

＆lt;＆lt;缺少一个记录（EX相同的毫米数字没有记录，从2015-03-03到2015-04-02

12347|4444444 |2015-04-03 |2015-05-01 
12348|4444444 |2015-05-02 |2015-06-01 
12349|4444444 |2015-06-02 |2015-07-01 
12310|4444444 |2015-07-02 |2015-07-31 

＆lt;＆lt;缺少一个记录（EX：相同的毫米数字没有记录，从2015-08-01到2015-08-31

12310|4444444|2015-09-01|2015-09-30 

预期的put：

MM No | Missing Start Date | Missing To Date 
4444444 |2015-03-03 |2015-04-02 
4444444 |2015-08-01 |2015-08-31 

我有一个带有以下数据的表

create table icr_tmp 
( 
ID VARCHAR2(15), 
NMI VARCHAR2(50), 
INVOICE_START_DATE DATE, 
INVOICE_END_DATE DATE); 
insert into icr_tmp values('12345','4444444','03/01/2015','02/02/2015'); 
insert into icr_tmp values('12346','4444444','03/02/2015','02/03/2015'); 
insert into icr_tmp values('12347','4444444','03/04/2015','01/05/2015'); 
insert into icr_tmp values('12348','4444444','02/05/2015','01/06/2015'); 
insert into icr_tmp values('12349','4444444','02/06/2015','01/07/2015'); 
insert into icr_tmp values('12310','4444444','02/07/2015','31/07/2015'); 
insert into icr_tmp values('12310','4444444','01/09/2015','30/09/2015'); 

在SQL下方在Oracle中完美工作，但在Netezza中不工作。

select * 
from (
  select nmi,max(invoice_end_date) over(partition by nmi order by invoice_start_date) + 1 start_gap,
    lead(invoice_start_date) over(partition by nmi order by invoice_start_date) - 1 end_gap
  from icr_tmp3
)
where start_gap <= end_gap;
NMI      START_GAP   END_GAP
4444444  2015-03-03  2015-04-02
4444444  2015-08-01  2015-08-31

如何更改上述SQL以在Netezza中运行？

Answer 1

您的查询在Netezza上应该很好地工作，我看到的唯一一件事是对子查询的别名，因为如果子Queries不相称，Netezza会抱怨。

。

select * 
from (
  select nmi
  ,max(invoice_end_date) over(partition by nmi order by invoice_start_date) + 1 start_gap,
  lead(invoice_start_date) over(partition by nmi order by invoice_start_date) - 1 end_gap  
  from icr_tmp
) foo
where start_gap <= end_gap;