ShangHaiLifeECIF/sql-achievement/2025年开发/客户接听习惯-Proc.sql

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

UPDATE shanglifeecif.customerno_insurance_policies_info a
SET (
        phone_answering_time_num_a,
        phone_answering_time_date_a,
        phone_answering_time_num_b,
        phone_answering_time_date_b,
        phone_answering_time_num_c,
        phone_answering_time_date_c,
        policy_label_7
        ) = (select b.phone_answering_time_num_a,
                    b.phone_answering_time_date_a,
                    b.phone_answering_time_num_b,
                    b.phone_answering_time_date_b,
                    b.phone_answering_time_num_c,
                    b.phone_answering_time_date_c,
                    b.policy_label_7
             from (SELECT productno,
                          max(CASE WHEN timeslot = '习惯12点前接听电话' THEN talknum END)             AS phone_answering_time_num_a,
                          max(CASE WHEN timeslot = '习惯12点前接听电话' THEN maxTalkStartTime END)    AS phone_answering_time_date_a,
                          max(CASE WHEN timeslot = '习惯12点-18点接听电话' THEN talknum END)          AS phone_answering_time_num_b,
                          max(CASE WHEN timeslot = '习惯12点-18点接听电话' THEN maxTalkStartTime END) AS phone_answering_time_date_b,
                          max(CASE WHEN timeslot = '习惯18点以后接听电话' THEN talknum END)           AS phone_answering_time_num_c,
                          max(CASE WHEN timeslot = '习惯18点以后接听电话' THEN maxTalkStartTime END)  AS phone_answering_time_date_c,
                          max(CASE WHEN row_num = 1 THEN timeslot END)                                AS policy_label_7
                   FROM (SELECT productno,
                                timeSlot,
                                talkNum,
                                maxTalkStartTime,
                                ROW_NUMBER() OVER (PARTITION BY productno ORDER BY talkNum DESC, maxTalkStartTime DESC) AS row_num
                         FROM ((SELECT actionData.productno,
                                       CASE WHEN HOUR (actionLog.talkStart) < 12 THEN '习惯12点前接听电话'
                             WHEN 12 <= HOUR (actionLog.talkStart) < 18 THEN '习惯12点-18点接听电话'
                             WHEN HOUR (actionLog.talkStart) >= 18 THEN '习惯18点以后接听电话'
                             ELSE 'other' END AS timeSlot,
                             count (1) AS talkNum ,
                             max (actionLog.talkStart) AS maxTalkStartTime
                             FROM dsj.cc_action_data actionData
                             LEFT JOIN dsj.cc_action_actionlog actionLog
                             ON actionData.dataguid = actionLog.dataguid
                             WHERE DATE_FORMAT(actionData.inserttime
                             , 'yyyy-MM-dd HH:mm:ss.S') >= ADD_MONTHS(SYSDATE
                             , -12)
                             AND actionLog.usercode NOT LIKE '%admin%'
                             AND actionlog.resultid = '0203000000'
                             AND actionData.actiondefguid IN ('402837815c2ff6b5015c3005e7fb0001'
                             , '402837815c2ff6b5015c3005e7fb0002'
                             , '402837815c2ff6b5015c3005e7fb0003'
                             , '402837815c2ff6b5015c3005e7fb0005')
                             GROUP BY productno, timeSlot)))
             GROUP BY productno) b
where b.productno = a.policy_no)
WHERE 1 = 1;


---更新用户表标签数据

update shanglifeecif.Individual a
set (Label131) = (select main_answering_period
                  from (SELECT customer_no,
                               period_name AS main_answering_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,
                                                 -- 12点前
                                                 '习惯12点前接听电话'                   AS period_name,
                                                 SUM(phone_answering_time_num_a)  AS total_num,
                                                 MAX(phone_answering_time_date_a) AS latest_date
                                          FROM shanglifeecif.customerno_insurance_policies_info
                                          where phone_answering_time_num_a is not null
                                          GROUP BY customer_no

                                          UNION ALL

                                          -- 12点-18点
                                          SELECT customer_no,
                                                 '习惯12点-18点接听电话'                AS period_name,
                                                 SUM(phone_answering_time_num_b)  AS total_num,
                                                 MAX(phone_answering_time_date_b) AS latest_date
                                          FROM shanglifeecif.customerno_insurance_policies_info
                                          where phone_answering_time_num_b is not null
                                          GROUP BY customer_no

                                          UNION ALL

                                          -- 18点以后
                                          SELECT customer_no,
                                                 '习惯18点以后接听电话'                 AS period_name,
                                                 SUM(phone_answering_time_num_c)  AS total_num,
                                                 MAX(phone_answering_time_date_c) AS latest_date
                                          FROM shanglifeecif.customerno_insurance_policies_info
                                          where phone_answering_time_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_response_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;