//生成时间段内连续日期
select * from
( select date('2008-01-01') + (row_NUMBER() over () -1) days AS datennn
from sysibm.syscoldist )
where datennn < date ('2013-01-01')
//生成时间段内所有节点的笛卡尔积
select a.COMP_NAME, a.COMP_ID,b.datennn from MANAGE8_BASE_NODE_INFO a,
(
select * from
( select date('2017-01-01') + (row_NUMBER() over () -1) days AS datennn
from sysibm.syscoldist )
where datennn < date ('2017-01-30')
) b
where a.NODE_TYPE='0005
//查询出进场数据为空的节点数据
select c.NODE_NAME,c.NODE_ID,c.COLLECT_DATE,COALESCE(b.VEG_IN_COUNT,0),COALESCE(b.MEAT_IN_COUNT,0) from (
select a.COMP_NAME as NODE_NAME , a.COMP_ID as NODE_ID,b.datennn as COLLECT_DATE from MANAGE8_BASE_NODE_INFO a,
(
select * from
( select date('2017-01-01') + (row_NUMBER() over () -1) days AS datennn
from sysibm.syscoldist )
where datennn < date ('2017-01-30')
) b
where a.NODE_TYPE='0005'
) c left join
(select * from NODE_INFO_COLLECT where COLLECT_DATE >= '2017-01-01 00:00:00' and COLLECT_DATE <'2017-02-01 00:00:00' and NODE_TYPE='0005' ) b
on c.NODE_ID= b.NODE_ID
//下面是最终的sql，还没测试，有待修改
select DISTINCT tstart.NODE_ID,tstart.NODE_NAME,tstart.COLLECT_DATE from NODE_INFO_COLLECT as tstart ,NODE_INFO_COLLECT as tend
where tstart.NODE_ID = tend.NODE_ID
and (tstart.VEG_IN_COUNT+tstart.MEAT_IN_COUNT ) =0
and (tend.VEG_IN_COUNT+tend.MEAT_IN_COUNT ) >0
and tstart.NODE_TYPE='0005' and tend.NODE_TYPE='0005'
and tend.COLLECT_DATE >= DATE(tstart.COLLECT_DATE) + 3 days
and EXISTS (SELECT 1 FROM NODE_INFO_COLLECT AS TGood
WHERE TGood.NODE_ID = tstart.NODE_ID
AND (TGood.VEG_IN_COUNT+TGood.MEAT_IN_COUNT ) =0
AND TGood.COLLECT_DATE BETWEEN tstart.COLLECT_DATE AND tend.COLLECT_DATE
HAVING COUNT(TGood.NODE_ID) >=3)
AND NOT EXISTS (SELECT 1 FROM NODE_INFO_COLLECT AS TBad
WHERE TBad.NODE_ID = TStart.NODE_ID
AND (TBad.VEG_IN_COUNT+TBad.MEAT_IN_COUNT ) >0
AND TBad.COLLECT_DATE BETWEEN tstart.COLLECT_DATE AND tend.COLLECT_DATE)
//最后汇总sql,有待改进，执行效率慢
select DISTINCT tstart.NODE_ID,tstart.NODE_NAME,tstart.COLLECT_DATE
from
(
select tab1.NODE_NAME,tab1.NODE_ID,COALESCE(tab2.VEG_IN_COUNT,0) VEG_IN_COUNT,COALESCE(tab2.MEAT_IN_COUNT,0) MEAT_IN_COUNT,tab1.COLLECT_DATE from (
select c.NODE_NAME,c.NODE_ID,c.COLLECT_DATE from (
select a.COMP_NAME as NODE_NAME , a.COMP_ID as NODE_ID,b.datennn as COLLECT_DATE from MANAGE8_BASE_NODE_INFO a,
(
select * from
( select date('2017-01-01') + (row_NUMBER() over () -1) days AS datennn
from sysibm.syscoldist )
where datennn < date ('2017-02-01')
) b
where a.COMP_ID='410105095'
) c
) tab1 left join
(
select NODE_NAME,NODE_ID,VEG_IN_COUNT,MEAT_IN_COUNT,COLLECT_DATE from NODE_INFO_COLLECT
where COLLECT_DATE >= '2017-01-01 00:00:00' and COLLECT_DATE <'2017-02-01 00:00:00' and NODE_ID='410105095'
) tab2
on tab1.NODE_ID=tab2.NODE_ID and date(tab1.COLLECT_DATE) = date(tab2.COLLECT_DATE)
) tstart ,
(
select tab1.NODE_NAME,tab1.NODE_ID,COALESCE(tab2.VEG_IN_COUNT,0) VEG_IN_COUNT,COALESCE(tab2.MEAT_IN_COUNT,0) MEAT_IN_COUNT,tab1.COLLECT_DATE from (
select c.NODE_NAME,c.NODE_ID,c.COLLECT_DATE from (
select a.COMP_NAME as NODE_NAME , a.COMP_ID as NODE_ID,b.datennn as COLLECT_DATE from MANAGE8_BASE_NODE_INFO a,
(
select * from
( select date('2017-01-01') + (row_NUMBER() over () -1) days AS datennn
from sysibm.syscoldist )
where datennn < date ('2017-02-01')
) b
where a.COMP_ID='410105095'
) c
) tab1 left join
(
select NODE_NAME,NODE_ID,VEG_IN_COUNT,MEAT_IN_COUNT,COLLECT_DATE from NODE_INFO_COLLECT
where COLLECT_DATE >= '2017-01-01 00:00:00' and COLLECT_DATE <'2017-02-01 00:00:00' and NODE_ID='410105095'
) tab2
on tab1.NODE_ID=tab2.NODE_ID and date(tab1.COLLECT_DATE) = date(tab2.COLLECT_DATE)
) tend
where tstart.NODE_ID = tend.NODE_ID
and (tstart.VEG_IN_COUNT+tstart.MEAT_IN_COUNT ) =0
and (tend.VEG_IN_COUNT+tend.MEAT_IN_COUNT ) =0
and DATE(tend.COLLECT_DATE) = DATE(tstart.COLLECT_DATE) + 1 days
and EXISTS (SELECT 1 FROM
(
select tab1.NODE_NAME,tab1.NODE_ID,COALESCE(tab2.VEG_IN_COUNT,0) VEG_IN_COUNT,COALESCE(tab2.MEAT_IN_COUNT,0) MEAT_IN_COUNT,tab1.COLLECT_DATE from (
select c.NODE_NAME,c.NODE_ID,c.COLLECT_DATE from (
select a.COMP_NAME as NODE_NAME , a.COMP_ID as NODE_ID,b.datennn as COLLECT_DATE from MANAGE8_BASE_NODE_INFO a,
(
select * from
( select date('2017-01-01') + (row_NUMBER() over () -1) days AS datennn
from sysibm.syscoldist )
where datennn < date ('2017-02-01')
) b
where a.COMP_ID='410105095'
) c
) tab1 left join
(
select NODE_NAME,NODE_ID,VEG_IN_COUNT,MEAT_IN_COUNT,COLLECT_DATE from NODE_INFO_COLLECT
where COLLECT_DATE >= '2017-01-01 00:00:00' and COLLECT_DATE <'2017-02-01 00:00:00' and NODE_ID='410105095'
) tab2
on tab1.NODE_ID=tab2.NODE_ID and date(tab1.COLLECT_DATE) = date(tab2.COLLECT_DATE)
)
TGood
WHERE TGood.NODE_ID = tstart.NODE_ID
AND (TGood.VEG_IN_COUNT+TGood.MEAT_IN_COUNT ) =0
AND TGood.COLLECT_DATE BETWEEN tstart.COLLECT_DATE AND tend.COLLECT_DATE
HAVING COUNT(TGood.NODE_ID) >=2)
AND NOT EXISTS (SELECT 1 FROM
(
select tab1.NODE_NAME,tab1.NODE_ID,COALESCE(tab2.VEG_IN_COUNT,0) VEG_IN_COUNT,COALESCE(tab2.MEAT_IN_COUNT,0) MEAT_IN_COUNT,tab1.COLLECT_DATE from (
select c.NODE_NAME,c.NODE_ID,c.COLLECT_DATE from (
select a.COMP_NAME as NODE_NAME , a.COMP_ID as NODE_ID,b.datennn as COLLECT_DATE from MANAGE8_BASE_NODE_INFO a,
(
select * from
( select date('2017-01-01') + (row_NUMBER() over () -1) days AS datennn
from sysibm.syscoldist )
where datennn < date ('2017-02-01')
) b
where a.COMP_ID='410105095'
) c
) tab1 left join
(
select NODE_NAME,NODE_ID,VEG_IN_COUNT,MEAT_IN_COUNT,COLLECT_DATE from NODE_INFO_COLLECT
where COLLECT_DATE >= '2017-01-01 00:00:00' and COLLECT_DATE <'2017-02-01 00:00:00' and NODE_ID='410105095'
) tab2
on tab1.NODE_ID=tab2.NODE_ID and date(tab1.COLLECT_DATE) = date(tab2.COLLECT_DATE)
)
TBad
WHERE TBad.NODE_ID = TStart.NODE_ID
AND (TBad.VEG_IN_COUNT+TBad.MEAT_IN_COUNT ) >0
AND TBad.COLLECT_DATE BETWEEN tstart.COLLECT_DATE AND tend.COLLECT_DATE)
//加入临时物理表
create table TEST_NODE_COUNT as (
select tab1.NODE_NAME,tab1.NODE_ID,COALESCE(tab2.VEG_IN_COUNT,0) VEG_IN_COUNT,COALESCE(tab2.MEAT_IN_COUNT,0) MEAT_IN_COUNT,tab1.COLLECT_DATE from (
select c.NODE_NAME,c.NODE_ID,c.COLLECT_DATE from (
select a.COMP_NAME as NODE_NAME , a.COMP_ID as NODE_ID,b.datennn as COLLECT_DATE from MANAGE8_BASE_NODE_INFO a,
(
select * from
( select date('2017-01-01') + (row_NUMBER() over () -1) days AS datennn
from sysibm.syscoldist )
where datennn < date ('2017-02-01')
) b
where a.NODE_TYPE='0005'
) c
) tab1 left join
(
select NODE_NAME,NODE_ID,VEG_IN_COUNT,MEAT_IN_COUNT,COLLECT_DATE from NODE_INFO_COLLECT
where COLLECT_DATE >= '2017-01-01 00:00:00' and COLLECT_DATE <'2017-02-01 00:00:00' and NODE_TYPE='0005'
) tab2
on tab1.NODE_ID=tab2.NODE_ID and date(tab1.COLLECT_DATE) = date(tab2.COLLECT_DATE)
) definition only
select * from test_node_count
delete * from test_node_count
//核对信息
select * from (
select DISTINCT tstart.NODE_ID,tstart.NODE_NAME,tstart.COLLECT_DATE
from test_node_count tstart ,
(
select * from test_node_count
) tend
where tstart.NODE_ID = tend.NODE_ID
and (tstart.VEG_IN_COUNT+tstart.MEAT_IN_COUNT ) =0
and (tend.VEG_IN_COUNT+tend.MEAT_IN_COUNT ) =0
and DATE(tend.COLLECT_DATE) = DATE(tstart.COLLECT_DATE) + 2 days
and EXISTS (SELECT 1 FROM test_node_count TGood
WHERE TGood.NODE_ID = tstart.NODE_ID
AND (TGood.VEG_IN_COUNT+TGood.MEAT_IN_COUNT ) =0
AND TGood.COLLECT_DATE BETWEEN tstart.COLLECT_DATE AND tend.COLLECT_DATE
HAVING COUNT(TGood.NODE_ID) >=3)
AND NOT EXISTS (SELECT 1 FROM test_node_count TBad
WHERE TBad.NODE_ID = TStart.NODE_ID
AND (TBad.VEG_IN_COUNT+TBad.MEAT_IN_COUNT ) >0
AND TBad.COLLECT_DATE BETWEEN tstart.COLLECT_DATE AND tend.COLLECT_DATE)
) as tab where tab.node_id='410103023'