DROP TABLE dim_early_warning_comm_d; -- 项目预警指标数据 CREATE TABLE dim_early_warning_comm_d( organ_code VARCHAR(50) COMMENT '片区id' ,organ_name VARCHAR(50) COMMENT '片区名称' ,comm_id VARCHAR(50) COMMENT '项目id' ,comm_name VARCHAR(50) COMMENT '项目名称' ,km_comple_rate DECIMAL(6,4) COMMENT '客满得分完成率' ,ts_close_rate DECIMAL(6,4) COMMENT '投诉关闭率' ,report_close_rate DECIMAL(6,4) COMMENT '报事关闭率' ,cu_coll_comple_rate DECIMAL(6,4) COMMENT '当期收缴完成率' ,pr_coll_comple_rate DECIMAL(6,4) COMMENT '往期收缴完成率' ,total_coll_comple_rate DECIMAL(6,4) COMMENT '综合收缴完成率' ,device_space_rate DECIMAL(6,4) COMMENT '设备空间巡查完成率' ,water_electric_rate DECIMAL(6,4) COMMENT '水电费收缴率' )COMMENT = '项目预警指标数据'; SELECT * FROM dim_early_warning_comm_d; INSERT INTO dim_early_warning_comm_d SELECT T.organ_code AS organ_code ,T.organ_name AS organ_name ,T.comm_id AS comm_id ,T.comm_name AS comm_name ,SUM(km_comple_rate) AS km_comple_rate -- 客满得分完成率 ,SUM(ts_close_rate) AS ts_close_rate -- 投诉关闭率 ,SUM(report_close_rate) AS report_close_rate -- 报事关闭率 ,SUM(cu_coll_comple_rate) AS cu_coll_comple_rate -- 当期收缴完成率 ,SUM(pr_coll_comple_rate) AS pr_coll_comple_rate -- 往期收缴完成率 ,sum(total_coll_comple_rate) AS total_coll_comple_rate ,SUM(device_space_rate) AS device_space_rate -- 设备空间巡查完成率 ,SUM(water_electric_rate) AS water_electric_rate -- 水电费收缴率 FROM ( SELECT T1.organ_code AS organ_code ,T1.organ_name AS organ_name ,T1.comm_id AS comm_id ,T1.comm_name AS comm_name ,IF(IFNULL(T1.comm_target,0)=0,0,(IFNULL(T2.index_dc_myddf,0) / IFNULL(T1.comm_target,0))) AS km_comple_rate -- 客满得分完成率 ,0 AS ts_close_rate -- 投诉关闭率 ,0 AS report_close_rate -- 报事关闭率 ,0 AS cu_coll_comple_rate -- 当期收缴完成率 ,0 AS pr_coll_comple_rate -- 往期收缴完成率 ,0 AS total_coll_comple_rate -- 综合收缴完成率 ,0 AS device_space_rate -- 设备空间巡查完成率 ,0 AS water_electric_rate -- 水电费收缴率 FROM ( SELECT organ_code ,organ_name -- 片区名称 ,comm_id ,comm_name -- 项目名称 ,comm_target -- 项目目标得分 FROM ods_wuye_satisfaction_target WHERE yr = LEFT(CURRENT_DATE,4) AND comm_name NOT LIKE '%案场%' AND comm_name NOT LIKE '%撤场%' AND comm_name NOT LIKE '%禁用%' -- AND comm_type = '' )T1 LEFT JOIN ( SELECT organ_code ,organ_name ,comm_id ,comm_name ,index_dc_myddf FROM ods_wuye_complaint WHERE yr = LEFT(CURRENT_DATE,4) AND jd = '二季度' -- AND jd = CASE WHEN QUARTER(CURDATE()) = 1 THEN '一季度' -- WHEN QUARTER(CURDATE()) = 2 THEN '二季度' -- WHEN QUARTER(CURDATE()) = 3 THEN '三季度' -- WHEN QUARTER(CURDATE()) = 4 THEN '四季度' EN )T2 ON T1.organ_code = T2.organ_code AND T1.comm_id = T2.comm_id UNION ALL -- 投诉关闭率/报事关闭率 SELECT organ_code AS organ_code ,organ_name AS organ_name ,comm_id AS comm_id ,comm_name AS comm_name ,0 AS km_comple_rate -- 客满得分完成率 ,IF(IFNULL(sum(tousu_incident_num),0)=0,0,sum(tousu_close_incident_num) / sum(tousu_incident_num)) AS ts_close_rate -- 投诉关闭率 ,IF(IFNULL(sum(incident_num),0)=0,0,sum(completed_incident_num - completed_not_close_incident_num) / sum(incident_num)) AS report_close_rate -- 报事关闭率 ,0 AS cu_coll_comple_rate -- 当期收缴完成率 ,0 AS pr_coll_comple_rate -- 往期收缴完成率 ,0 AS total_coll_comple_rate -- 综合收缴完成率 ,0 AS device_space_rate -- 设备空间巡查完成率 ,0 AS water_electric_rate -- 水电费收缴率 FROM dws_estate_incident_info_m WHERE incident_ym = date_format(current_date,'%Y%m') GROUP BY organ_name,comm_name,comm_id,organ_code UNION ALL -- 收缴率 SELECT T1.organ_code AS organ_code ,T1.organ_name AS organ_name ,T1.commid AS comm_id ,T1.comm_name AS comm_name ,0 AS km_comple_rate -- 客满得分完成率 ,0 AS ts_close_rate -- 投诉关闭率 ,0 AS report_close_rate -- 报事关闭率 ,SUM(T1.cu_collection_rate) / SUM(T2.index_dq_target) AS cu_coll_comple_rate -- 当期收缴完成率 ,SUM(T1.pr_collection_rate) / SUM(T2.index_wq_target) AS pr_coll_comple_rate -- 往期收缴完成率 ,0 AS total_coll_comple_rate -- 综合收缴完成率 ,0 AS device_space_rate -- 设备空间巡查完成率 ,0 AS water_electric_rate -- 水电费收缴率 FROM ( SELECT commid ,comm_name ,organ_code ,organ_name ,sum(ifnull(pr_paid_cu_amt,0) + ifnull(cu_paid_cu_amt,0) - ifnull(pr_offset_cu_amt,0) - ifnull(cu_offset_cu_amt,0)) / sum(ifnull(cu_receivable_amt,0) + ifnull(pr_exempt_cu_amt,0) + ifnull(cu_exempt_cu_amt,0)) cu_collection_rate ,sum((- ifnull(cu_paid_pr_amt,0) - ifnull(cu_offset_pr_amt,0))) / sum(ifnull(pr_total_receivable_amt,0) + ifnull(pr_total_received_amt,0) + ifnull(cu_early_exempt_pr_amt,0) + ifnull(cu_exempt_pr_amt,0)) pr_collection_rate from dwd_finance_fees_serial_d where ParentCostCode = '0001' and ym = left(current_date,7) and organ_name not in ('东湖商管','领悦总部','演示机构') and comm_name not like '%天富%' and comm_name not in('【喀什】融合国际新城','【贵阳】鹏淇·宏桥公园（撤场）') GROUP BY organ_code ,organ_name ,commid ,comm_name )T1 LEFT JOIN ( select comm_id ,comm_name ,organ_code ,organ_name ,index_dq_target ,index_wq_target from ods_finance_collection_target_project WHERE ym = left(current_date,7) )T2 ON T1.organ_code = T2.organ_code AND T1.commid = T2.comm_id GROUP BY T1.organ_name,T1.comm_name,T1.commid,T1.organ_code UNION ALL -- 综合收缴率 SELECT T1.organ_code AS organ_code ,T1.organ_name AS organ_name ,T1.comm_id AS comm_id ,T1.comm_name AS comm_name ,0 AS km_comple_rate -- 客满得分完成率 ,0 AS ts_close_rate -- 投诉关闭率 ,0 AS report_close_rate -- 报事关闭率 ,0 AS cu_coll_comple_rate -- 当期收缴完成率 ,0 AS pr_coll_comple_rate -- 往期收缴完成率 ,SUM(T1.index_receipts) / SUM(T2.index_dj_target) AS total_coll_comple_rate -- 综合收缴完成率 ,0 AS device_space_rate -- 设备空间巡查完成率 ,0 AS water_electric_rate -- 水电费收缴率 FROM ( SELECT organ_code ,organ_name ,comm_id ,comm_name ,SUM(index_receivable) / SUM(index_receipts) AS index_receipts FROM ods_caiwu_profit -- where `year_month` = date_format(current_date,'%Y%m') where `year_month` = '202408' and comm_name not in('【喀什】融合国际新城','【贵阳】鹏淇·宏桥公园（撤场）') GROUP BY organ_code ,organ_name ,comm_id ,comm_name )T1 LEFT JOIN ( select organ_code ,comm_id ,index_dj_target from ods_finance_collection_target_project WHERE ym = left(current_date,7) )T2 ON T1.organ_code = T2.organ_code AND T1.comm_id = T2.comm_id GROUP BY T1.organ_code,T1.organ_name,T1.comm_id ,T1.comm_name UNION ALL -- 设备空间巡查完成率 SELECT organ_code AS organ_code ,organ_name AS organ_name ,comm_id AS comm_id ,comm_name AS comm_name ,0 AS km_comple_rate -- 客满得分完成率 ,0 AS ts_close_rate -- 投诉关闭率 ,0 AS report_close_rate -- 报事关闭率 ,0 AS cu_coll_comple_rate -- 当期收缴完成率 ,0 AS pr_coll_comple_rate -- 往期收缴完成率 ,0 AS total_coll_comple_rate -- 综合收缴完成率 ,SUM(task_finish_num) / SUM(task_num) AS device_space_rate -- 设备空间巡查完成率 ,0 AS water_electric_rate -- 水电费收缴率 FROM dws_engine_equipment_inspect_task_m where task_ym = date_format(current_date,'%Y%m') GROUP BY organ_name ,organ_code ,comm_id ,comm_name UNION ALL -- 水电费收缴率 SELECT organ_code AS organ_code ,organ_name AS organ_name ,commid AS comm_id ,comm_name AS comm_name ,0 AS km_comple_rate -- 客满得分完成率 ,0 AS ts_close_rate -- 投诉关闭率 ,0 AS report_close_rate -- 报事关闭率 ,0 AS cu_coll_comple_rate -- 当期收缴完成率 ,0 AS pr_coll_comple_rate -- 往期收缴完成率 ,0 AS total_coll_comple_rate -- 综合收缴完成率 ,0 AS device_space_rate -- 设备空间巡查完成率 ,sum((ifnull(pr_paid_cu_amt,0) + ifnull(cu_paid_cu_amt,0) - ifnull(pr_offset_cu_amt,0) - ifnull(cu_offset_cu_amt,0))+(- ifnull(cu_paid_pr_amt,0) - ifnull(cu_offset_pr_amt,0))) / sum((ifnull(cu_receivable_amt,0) + ifnull(pr_exempt_cu_amt,0) + ifnull(cu_exempt_cu_amt,0))+(ifnull(pr_total_receivable_amt,0) + ifnull(pr_total_received_amt,0) + ifnull(cu_early_exempt_pr_amt,0) + ifnull(cu_exempt_pr_amt,0))) AS water_electric_rate -- 水电费收缴率 FROM dwd_finance_fees_serial_d WHERE ParentCostCode = '0008' AND MiddleCostCode in ('00080001','00080002','00080003','00080004','00080005','00080006','00080007','00080008','00080017','00080018','00080019','00080022','00080023','00080024','00080027','00080028','00080032','00080035') AND ym = LEFT(current_date,7) GROUP BY organ_name,organ_code,commid,comm_name )T JOIN (SELECT organ_code,comm_id FROM dim_organ_mapping GROUP BY organ_code,comm_id)t1 ON T.comm_id = t1.comm_id and T.organ_code = t1.organ_code GROUP BY T.organ_code ,T.organ_name ,T.comm_id ,T.comm_name ;