potgresql中case when包含子查询的改写办法

改写参考办法

-- case when 包含not in 的子查询 使用left join 和 is null 的组合进行转换
-- case when 包含 in 的子查询，使用 left join 和 not null 的组合进行转换
-- 1=1 的按照原条件输出即可
-- 需要注意 join 后的on 条件和其他条件与原有保持一致

1.case when 在select投影列中

-- 转换前
select cust_pty_id,
sum(case when SUBSTR(coalesce(txn_cha_id,case when recall_acct_flag =''Y'' then ''ZX'' else open_cha_id end), 1, 2) NOT IN (''ZX'', ''PW'',''WX'') 
and (dep_sub_prd_id not in (select prod_code from sdi.zlc_fms_prod_info where sub_type = ''2'' and prod_type = ''ZLC03'')) then acct_bal else 0 end) 
as sf_bal,
sum(case when SUBSTR(coalesce(txn_cha_id,case when recall_acct_flag =''Y'' then ''ZX'' else open_cha_id end), 1, 2) IN (''ZX'', ''PW'',''WX'') 
or (dep_sub_prd_id in (select prod_code from sdi.zlc_fms_prod_info where sub_type = ''2'' and prod_type = ''ZLC03'')) then acct_bal else 0 end) as 
zy_bal,
sum(acct_bal) as acct_bal
from nsor.t03_dep_acct_agt_l_prt_p_''||V_DATA_DT_2||''
where acct_bal > 0
and cust_pty_id like ''9%''
group by 1
-- 转换后
select cust_pty_id,
sum(case when SUBSTR(coalesce(txn_cha_id,case when recall_acct_flag =''Y'' then ''ZX'' else open_cha_id end), 1, 2) NOT IN (''ZX'', ''PW'',''WX'') 
and (tba.prod_code is null) then acct_bal else 0 end) 
as sf_bal,
sum(case when SUBSTR(coalesce(txn_cha_id,case when recall_acct_flag =''Y'' then ''ZX'' else open_cha_id end), 1, 2) IN (''ZX'', ''PW'',''WX'') 
or (tba.prod_code is not null) then acct_bal else 0 end) as 
zy_bal,
sum(acct_bal) as acct_bal
from nsor.t03_dep_acct_agt_l_prt_p_''||V_DATA_DT_2||'' t
left join (select prod_code from sdi.zlc_fms_prod_info where sub_type = ''2'' and prod_type = ''ZLC03'') tba
on t.dep_sub_prd_id = tba.prod_code
where acct_bal > 0
and cust_pty_id like ''9%''
group by 1

2.case when在条件中

-- 转换前
SELECT date(V_DATA_DATE) as data_dt  -- 数据日期
   ,count(distinct t1.cust_no) as all_cust_cnt  -- 客群总数
   ,count(distinct t2.cust_no) as buy_cust_cnt  -- 购买产品客户数
   ,count(distinct t2.cust_no)*1.0/count(distinct t1.cust_no) as cust_sd_rate
FROM
(select cust_no from tps.prod_7d_qt_order_list where buy_date between '2022-02-01' and '2022-07-31' group by cust_no) t1  -- 第二批订单客户
LEFT JOIN
(select cust_no from tps.qt_cust_ta_prod_txn_dtl where txn_dt <= V_DATA_DATE and txn_dt >= '2024-07-10' and accting_txn_type_cd in ('5010','240') group by cust_no) t2
ON t1.cust_no = t2.cust_no
WHERE case when date(V_DATA_DATE) < date('2024-08-01')
   then t1.cust_no not in (select cust_no from tps.prod_7d_qt_order_list where date(buy_date) between '2021-06-01' and '2022-01-31' group by cust_no)
   when date(V_DATA_DATE) >= date('2024-08-01') and date(V_DATA_DATE) < date('2024-08-15')
   then 1 = 1
   when date(V_DATA_DATE) >= date('2024-08-15') and date(V_DATA_DATE) < date('2024-08-30')
   then t1.cust_no not in (select cust_no from tps.prod_7d_qt_order_list where date(buy_date) between '2022-08-01' and '2022-12-31' group by cust_no)
   when date(V_DATA_DATE) >= date('2024-08-30')
   then t1.cust_no not in (select cust_no from tps.prod_7d_qt_order_list where date(buy_date) between '2022-08-01' and '2023-05-31' group by cust_no)
end
; 

-- 转换后

SELECT date(V_DATA_DATE) as data_dt
   ,count(distinct t1.cust_no) as all_cust_cnt
   ,count(distinct t2.cust_no) as buy_cust_cnt
   ,count(distinct t2.cust_no)*1.0/count(distinct t1.cust_no) as cust_sd_rate
FROM
(select cust_no from tps.prod_7d_qt_order_list where buy_date between '2022-02-01' and '2022-07-31' group by cust_no) t1
LEFT JOIN
(select cust_no from tps.qt_cust_ta_prod_txn_dtl where txn_dt <= V_DATA_DATE and txn_dt >= '2024-07-10' and accting_txn_type_cd in ('5010','240') group by cust_no) t2
ON t1.cust_no = t2.cust_no
-- 针对date(V_DATA_DATE) < date('2024-08-01')的条件
LEFT JOIN (select cust_no from tps.prod_7d_qt_order_list where date(buy_date) between '2021-06-01' and '2022-01-31' group by cust_no) t3
ON t1.cust_no = t3.cust_no and date(V_DATA_DATE) < date('2024-08-01')
-- 针对date(V_DATA_DATE) >= date('2024-08-30')的条件
LEFT JOIN (select cust_no from tps.prod_7d_qt_order_list where date(buy_date) between '2022-08-01' and '2023-05-31' group by cust_no) t4
ON t1.cust_no = t4.cust_no and date(V_DATA_DATE) >= date('2024-08-30')
-- 针对date(V_DATA_DATE) >= date('2024-08-15') and date(V_DATA_DATE) < date('2024-08-30')的条件
LEFT JOIN (select cust_no from tps.prod_7d_qt_order_list where date(buy_date) between '2022-08-01' and '2022-12-31' group by cust_no) t5
ON t1.cust_no = t5.cust_no and date(V_DATA_DATE) >= date('2024-08-15') and date(V_DATA_DATE) < date('2024-08-30')
WHERE (date(V_DATA_DATE) < date('2024-08-01') and t3.cust_no is null)
   OR (date(V_DATA_DATE) >= date('2024-08-01') and date(V_DATA_DATE) < date('2024-08-15'))
   OR (date(V_DATA_DATE) >= date('2024-08-15') and date(V_DATA_DATE) < date('2024-08-30') and t5.cust_no is null)
   OR (date(V_DATA_DATE) >= date('2024-08-30') and t4.cust_no is null);