ShangHaiLifeECIF/sql-achievement/2025年开发/客户缴费习惯-Proc.sql

CREATE
OR REPLACE PROCEDURE shanglifeecif.update_customer_payment_habits() -- 创建主存储过程
IS
BEGIN

UPDATE shanglifeecif.customerno_insurance_policies_info a
SET (
        pay_way_num_a,
        pay_way_date_a,
        pay_way_num_b,
        pay_way_date_b,
        pay_way_num_c,
        pay_way_date_c,
        policy_label_8
        ) = (select b.pay_way_num_a,
                    b.pay_way_date_a,
                    b.pay_way_num_b,
                    b.pay_way_date_b,
                    b.pay_way_num_c,
                    b.pay_way_date_c,
                    b.policy_label_8
             from (SELECT policy_no,
                          max(CASE WHEN habit = '银行批扣交费' THEN num END)         AS pay_way_num_a,
                          max(CASE WHEN habit = '银行批扣交费' THEN lastpaydate END) AS pay_way_date_a,
                          max(CASE WHEN habit = '线上交费' THEN num END)             AS pay_way_num_b,
                          max(CASE WHEN habit = '线上交费' THEN lastpaydate END)     AS pay_way_date_b,
                          max(CASE WHEN habit = '对公转账交费' THEN num END)         AS pay_way_num_c,
                          max(CASE WHEN habit = '对公转账交费' THEN lastpaydate END) AS pay_way_date_c,
                          max(CASE WHEN row_num = 1 THEN habit END)                  AS policy_label_8
                   FROM (SELECT habit,
                                policy_no,
                                num,
                                lastpaydate,
                                ROW_NUMBER() OVER (PARTITION BY policy_no ORDER BY num DESC,lastpaydate desc) AS row_num
                         FROM ((SELECT CASE
                                           WHEN t.PAYMODE = '4' THEN '银行批扣交费'
                                           WHEN upper(t.PAYMODE) = 'F' THEN '对公转账交费'
                                           WHEN upper(t.PAYMODE) in ('K', 'J') THEN '线上交费'
                                           else '' end as habit,
                                       t.otherno          policy_no,
                                       count(1)           num,
                                       max(t.makedate)    lastpaydate
                                FROM dsj.ljtempfeeclass t
                                WHERE t.othernotype = '2'
                                  AND t.paydate >= ADD_MONTHS(SYSDATE, -36)
                                GROUP BY t.otherno, t.PAYMODE)))
                   group by policy_no) b
             where b.policy_no = a.policy_no)
WHERE 1 = 1;

update shanglifeecif.Individual a
set (Label132) = (select main_payment_period
                  from (SELECT customer_no,
                               period_name AS main_payment_period
                        FROM (
                                 -- 对每个客户的三个时间段排序，取优先级最高的
                                 SELECT customer_no,
                                        period_name,
                                        total_num,
                                        latest_date,
                                        ROW_NUMBER() OVER (
            PARTITION BY customer_no
            ORDER BY total_num DESC, latest_date DESC
        ) AS rn  -- 按次数降序、日期降序排序
                                 FROM (
                                          -- 按客户号聚合，将三个时间段转为多行
                                          SELECT customer_no,
                                                 '银行批扣交费'      AS period_name,
                                                 SUM(pay_way_num_a)  AS total_num,
                                                 MAX(pay_way_date_a) AS latest_date
                                          FROM shanglifeecif.customerno_insurance_policies_info
                                          where pay_way_num_a is not null
                                          GROUP BY customer_no

                                          UNION ALL

                                          -- 12点-18点
                                          SELECT customer_no,
                                                 '线上交费'          AS period_name,
                                                 SUM(pay_way_num_b)  AS total_num,
                                                 MAX(pay_way_date_b) AS latest_date
                                          FROM shanglifeecif.customerno_insurance_policies_info
                                          where pay_way_num_b is not null
                                          GROUP BY customer_no

                                          UNION ALL

                                          -- 18点以后
                                          SELECT customer_no,
                                                 '对公转账交费'      AS period_name,
                                                 SUM(pay_way_num_c)  AS total_num,
                                                 MAX(pay_way_date_c) AS latest_date
                                          FROM shanglifeecif.customerno_insurance_policies_info
                                          where pay_way_num_c is not null
                                          GROUP BY customer_no) t1 -- t1：每个客户的三个时间段数据（一行一个时间段）
                             ) t2 -- t2：对每个客户的三个时间段排序
                        WHERE rn = 1) b
                  where a.scustid = b.customer_no)

where 1 = 1;



dbms_output
.
put_line
('update_customer_payment_habits函数跑批完成！');
EXCEPTION
			WHEN HIVE_EXCEPTION THEN
				 INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());
WHEN Others THEN
				INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());

END;