match(t:Table) where t.componentId=*** return t

MATCH (n:Table) with n, size((n)-[:process]->()) as OutDgree, size((n)<-[:process]-()) as InDgree set n.OutDgree=OutDgree,n.InDgree=InDgree;
match (n:Table) where n.OutDgree=0 return n
match (n:Table) where n.InDgree=0 return n

select 
    a.ip_role_id,
    b.account_no,
    coalesce(regexp_replace(b.cert_name,"\\,","\\1 "),b1.cust_name) as cert_name, 
    coalesce(b.chinese_name,b1.cust_chn_name) as chinese_name,
    case when b.currency='344' then 'HKD' else b.currency end as currency,
    b.vtrl_card_id,
    case when a1.cert_status='2' then '认证中' 
         when a1.cert_status is null then '已销户' end as cert_status_nm ,
    b1.user_bind_mbl,
    case when b3.ip_role_id is not null then 'Y' else 'N' end libra_f,
    b.pd_nm,
    b.previous_balance*0.01 as previous_balance,
    b.base_closing_balance*0.01 as base_closing_balance,
    case when b.account_status='T' then '正常'
         when b.account_status='B' then '冻结'
         when b.account_status='C' then '销户' end as account_status_nm,
    to_char(b.au_opn_dt, 'yyyyMMdd') as opn_dt,
    to_char(dateadd(b.au_opn_dt,60,'dd'),'yyyyMMdd') as opn_60d,
    case when b.account_status='C' then 'C' else cast(60-datediff(to_date('${dt}','yyyyMMdd'),b.au_opn_dt,'dd') as string) end as open_60d_f,
    to_char(coalesce(b.au_cls_dt,b2.au_cls_dt), 'yyyyMMdd') cls_dt,   
    case when c.onr_acct_nbr is not null then 'Y' else 'N' end as income_f,
    case when c.vb_svf_cnt > 0 then 'Y' else 'N' end as vb_svf_f,
    c.td_amt_sum,
    c.td_cnt,
    c.td_fail_cnt,
    c.inside_td_amt_sum,
    c.inside_td_cnt,
    c.inside_td_fail_cnt,
    to_char(a.gmt_create, 'yyyyMMdd') as role_opn_dt,
    to_char(dateadd(a.gmt_create,60,'dd'),'yyyyMMdd') as role_opn_60d,
    case when b.account_status='C' then 'C' else cast(60-datediff(to_date('${dt}','yyyyMMdd'),a.gmt_create,'dd') as string) end as role_open_60d_f,
    to_char(a2.gmt_his, 'yyyyMMdd') as role_cls_dt
from (
    select distinct ip_role_id,gmt_create
    from hkods.ods_cu_role_member 
    where dt<=${dt}
    and cert_status ='2'   --近7天曾是认证中的role
) a  
left join (
    select ip_role_id,cert_status
    from hkods.ods_cu_role_member 
    where dt=${dt}          --今日的状态
) a1
    on a.ip_role_id=a1.ip_role_id
left join (
    select 
        ip_id
        ,ip_role_id
        ,null as gmt_his
    from hkods.ods_cu_involved_party_role     --仍有效的role
    where dt=${dt}
    union all
    select 
        ip_id
        ,ip_role_id
        ,gmt_his
    from hkods.ods_cu_involved_party_role_his   --已被注销的role
    where dt=${dt}
) a2
    on a.ip_role_id=a2.ip_role_id
left join hkcdm.dwd_bank_hk_agt_dep_acc_info_dd b   --所有（活期、存款宝）账户信息（包括有效、已销户）
    on a.ip_role_id=b.ip_role_id
    and b.dt=${dt}
left join hkadm.adm_rgl_hkbk_supv_cust_base_info_dd b1   --所有用户的电话信息（已销户的无联系方式）
    on a2.ip_id=b1.ip_id
    and b1.dt=${dt}
left join hkods.ods_depc_dep_ar b2     --手动销户的账户实际销户时间。因bak表没有同步记录，会导致dwd_bank_hk_agt_dep_acc_info_dd里的账户销户时间为空( owner 御飞)
    on b.account_no=b2.fnd_au_id     
    and b2.dt=${dt}
left join (
    select distinct ip_role_id
    from hkcdm.dwd_bank_hk_agt_dep_acc_info_dd     --确认role关联的存款宝账户状态
    where dt=${dt}
    and account_status!='C'  --未销户
    and pd_code='01011000700000000002'  --存款宝账户
) b3
    on a.ip_role_id=b3.ip_role_id
left join (
    select 
        onr_acct_nbr,
        sum(case when opst_org_nm in ('MYBKC1HK',--螞蟻銀行(香港)
                                    'YINTC1HK',--銀傳集團有限公司
                                    'AUTOC1HK',--Autotoll Limited
                                    'EPAYC1HK',--EPAYLINKS TECHNOLOGY CO., LIMITED
                                    'FUSBC1HK',--富融銀行有限公司
                                    'AIRRC1HK',--天星銀行有限公司
                                    'APFSC1HK',--Alipay Financial Services (HK) Limited
                                    'HKTPC1HK',--HKT Payment Limited
                                    'LIVIC1HK',--LIVI VB LIMITED
                                    'MOXBC1HK',--Mox Bank Limited
                                    'TNGLC1HK',--TNG (Asia) Limited
                                    'UNICC1HK',--UniCard Solution Limited
                                    'WECHC1HK',--WeChat Pay Hong Kong Limited
                                    'WEDIC1HK',--Welab Bank Limited
                                    'OCTOC1HK',--八達通卡有限公司
                                    'PONCC1HK',--平安壹賬通銀行(香港)有限公司
                                    'KRILC1HK',--僑達國際有限公司
                                    'TTFSC1HK',--三三金融服務有限公司
                                    'AABLC1HK',--眾安銀行有限公司
                                    'PAYMC1HK')--payME
                then 1 else 0 end) as vb_svf_cnt,
        sum(case when td_in_st='success' and td_out_st='success' then td_amt else 0 end)*0.01 as td_amt_sum,
        count(1) as td_cnt,
        sum(case when td_in_st!='success' or td_out_st!='success' then 1 else 0 end) as td_fail_cnt,
        sum(case when biz_tp in ('31','01','21') and td_tp != '3102' and td_in_st='success' and td_out_st='success' then td_amt else 0 end)*0.01 as inside_td_amt_sum,
        sum(case when biz_tp in ('31','01','21') and td_tp != '3102' and td_in_st='success' and td_out_st='success' then 1 else 0 end) as inside_td_cnt,
        sum(case when biz_tp in ('31','01','21') and td_tp != '3102' and td_in_st!='success' or td_out_st!='success' then 1 else 0 end) as inside_td_fail_cnt
    from hkods.ods_dp_td_stmt 
    where dt=${dt}
    and biz_tp in ('31','01','19','21')   --转入
    and td_in_out_f='0'   --来帐
    group by onr_acct_nbr
) c
    on b.account_no=c.onr_acct_nbr
where a1.cert_status ='2'      --今天状态仍是认证中
   or a1.ip_role_id is null    --近7天状态曾是认证中，今天已被销户
;