1191 lines
70 KiB
SQL
1191 lines
70 KiB
SQL
-- *****************************
|
|
-- 创建人员: 杨坤安
|
|
-- 创建时间: 2024-07-31
|
|
-- 功能描述: 总裁驾驶舱对外看板数据初始化脚本
|
|
-- *****************************
|
|
-- 问题： 1. 如果加上片区维度，率值计算要在数据集中进行，需在表中增加基础指标，维护难度较大，所以不加片区+项目维度
|
|
|
|
|
|
|
|
-- ==================================================
|
|
-- 常规指标
|
|
-- ==================================================
|
|
|
|
DROP TABLE IF EXISTS dw.dws_president_base_index_d;
|
|
CREATE TABLE IF NOT EXISTS dw.dws_president_base_index_d (
|
|
id INT AUTO_INCREMENT PRIMARY KEY
|
|
,CURRENT_DT VARCHAR(50) COMMENT '当前日期'
|
|
,INDEX_INCOME_FACT DECIMAL(12,4) COMMENT '收入'
|
|
,INCOME_COMPLETION_RATE DECIMAL(6,4) COMMENT '收入完成率'
|
|
,INDEX_COST_FACT DECIMAL(12,4) COMMENT '成本'
|
|
,COST_COMPLETION_RATE DECIMAL(6,4) COMMENT '成本完成率'
|
|
,INDEX_PROFIT_FACT DECIMAL(12,4) COMMENT '利润'
|
|
,PROFIT_COMPLETION_RATE DECIMAL(6,4) COMMENT '利润完成率'
|
|
,INDEX_MARKET_FACT DECIMAL(12,4) COMMENT '市拓'
|
|
,MARKET_COMPLETION_RATE DECIMAL(6,4) COMMENT '市拓完成率'
|
|
,TODAY_TOLL_FEE DECIMAL(12,4) COMMENT '今日收费(单位:万)'
|
|
,TYEAR_TOLL_FEE DECIMAL(12,4) COMMENT '年度累计收费(单位:万)'
|
|
,MON_TOLL_FEE DECIMAL(12,4) COMMENT '月度累计收费(单位:万)'
|
|
,CU_COLLECTION_RATE DECIMAL(6,4) COMMENT '当期收缴率'
|
|
,PR_COLLECTION_RATE DECIMAL(6,4) COMMENT '往期收缴率'
|
|
,COLLECTION_RATE DECIMAL(6,4) COMMENT '综合收缴率'
|
|
,PR_OWED_AMT DECIMAL(12,4) COMMENT '往期欠费金额(单位:万)'
|
|
,CU_OWED_AMT DECIMAL(12,4) COMMENT '当期欠费金额(单位:万)'
|
|
,ENERGY_CONSUMPTION_AMT DECIMAL(12,4) COMMENT '能耗收费'
|
|
,DEVICE_GOOD_RATE DECIMAL(6,4) COMMENT '设备完好率'
|
|
,DEVICE_SPACE_NORMAL_RATE DECIMAL(6,4) COMMENT '设备空间巡查完成率'
|
|
,NEC INT COMMENT '在职人数'
|
|
,FMR DECIMAL(6,4) COMMENT '满编率'
|
|
,FMR_TARGET DECIMAL(6,4) COMMENT '满编率目标值'
|
|
,TR DECIMAL(6,4) COMMENT '离职率'
|
|
,TR_TARGET DECIMAL(6,4) COMMENT '离职率目标值'
|
|
,AC_FEE_RATE DECIMAL(6,4) COMMENT '人力成本费率'
|
|
,AC_FEE_RATE_TARGET DECIMAL(6,4) COMMENT '人力成本费率目标值'
|
|
,ASSESSMENT_OCCUPANY_RATE DECIMAL(6,4) COMMENT '空间运营出租率'
|
|
,RETAIL_FINISH_RATE DECIMAL(6,4) COMMENT '新零售完成率'
|
|
,RENT_RATE DECIMAL(6,4) COMMENT '商业资产出租率'
|
|
,RENT_COLLECTION_RATE DECIMAL(6,4) COMMENT '租金收缴率'
|
|
,GUEST_ROOM_FEE DECIMAL(12,4) COMMENT '客房单价(单位:元)'
|
|
,GUEST_RENT_RATE DECIMAL(6,4) COMMENT '客房出租率'
|
|
)COMMENT = '总裁驾驶舱对外展示基础指标'
|
|
;
|
|
|
|
TRUNCATE TABLE dw.dws_president_base_index_d
|
|
|
|
INSERT INTO dws_president_base_index_d
|
|
SELECT
|
|
current_date() AS CURRENT_DT -- 当前日期
|
|
-- ,'FIN' AS BUSINESS_TYPE -- 业务类型编码
|
|
-- ,'财务' AS BUSINESS_NAME -- 业务类型名称
|
|
,SUM(index_income_fact) AS INDEX_INCOME_FACT -- 收入
|
|
,CASE WHEN SUM(index_income_target) = 0 THEN 0
|
|
ELSE SUM(index_income_fact) / SUM(index_income_target)
|
|
END AS INCOME_COMPLETION_RATE -- 收入完成率
|
|
,SUM(index_cost_fact) AS INDEX_COST_FACT -- 成本
|
|
,CASE WHEN SUM(index_cost_target) = 0 THEN 0
|
|
ELSE SUM(index_cost_fact) / SUM(index_cost_target)
|
|
END AS COST_COMPLETION_RATE -- 成本完成率
|
|
,SUM(index_profit_fact) AS INDEX_PROFIT_FACT -- 利润
|
|
,CASE WHEN SUM(index_profit_target) = 0 THEN 0
|
|
ELSE SUM(index_profit_fact) / SUM(index_profit_target)
|
|
END AS PROFIT_COMPLETION_RATE -- 利润完成率
|
|
,SUM(index_market_fact) AS INDEX_MARKET_FACT -- 市拓
|
|
,CASE WHEN SUM(index_market_target) = 0 THEN 0
|
|
ELSE SUM(index_market_fact) / SUM(index_market_target)
|
|
END AS MARKET_COMPLETION_RATE -- 市拓完成率
|
|
,0 AS TODAY_TOLL_FEE -- 今日收费
|
|
,0 AS TYEAR_TOLL_FEE -- 年度累计收费
|
|
,0 AS MON_TOLL_FEE -- 月度累计收费
|
|
,0 AS CU_COLLECTION_RATE -- 当期收缴率
|
|
,0 AS PR_COLLECTION_RATE -- 往期收缴率
|
|
,0 AS COLLECTION_RATE -- 综合收缴率
|
|
,0 AS PR_OWED_AMT -- 往期欠费金额
|
|
,0 AS CU_OWED_AMT -- 当期欠费金额
|
|
,0 AS ENERGY_CONSUMPTION_AMT -- 能耗收费
|
|
,0 AS DEVICE_GOOD_RATE -- 设备完好率
|
|
,0 AS DEVICE_SPACE_NORMAL_RATE -- 设备空间巡查完成率
|
|
,0 AS NEC -- 在职人数
|
|
,0 AS FMR -- 满编率
|
|
,0 AS FMR_TARGET -- 满编率目标值
|
|
,0 AS TR -- 离职率
|
|
,0 AS TR_TARGET -- 离职率目标值
|
|
,0 AS AC_FEE_RATE -- 人力成本费率
|
|
,0 AS AC_FEE_RATE_TARGET -- 人力成本费率目标值
|
|
,0 AS ASSESSMENT_OCCUPANY_RATE -- 空间运营出租率
|
|
,0 AS RETAIL_FINISH_RATE -- 新零售完成率
|
|
,0 AS RENT_RATE -- 商业资产出租率
|
|
,0 AS RENT_COLLECTION_RATE -- 租金收缴率
|
|
,0 AS GUEST_ROOM_FEE -- 客房单价
|
|
,0 AS GUEST_RENT_RATE -- 客房出租率
|
|
FROM dw.ods_caiwu_feecollection
|
|
WHERE yr_month = '2024-06'
|
|
UNION ALL
|
|
SELECT
|
|
current_date() AS CURRENT_DT -- 当前日期
|
|
-- ,'FIN' AS BUSINESS_TYPE -- 业务类型编码
|
|
-- ,'财务' AS BUSINESS_NAME -- 业务类型名称
|
|
,0 AS INDEX_INCOME_FACT -- 收入
|
|
,0 AS INCOME_COMPLETION_RATE -- 收入完成率
|
|
,0 AS INDEX_COST_FACT -- 成本
|
|
,0 AS COST_COMPLETION_RATE -- 成本完成率
|
|
,0 AS INDEX_PROFIT_FACT -- 利润
|
|
,0 AS PROFIT_COMPLETION_RATE -- 利润完成率
|
|
,0 AS INDEX_MARKET_FACT -- 市拓
|
|
,0 AS MARKET_COMPLETION_RATE -- 市拓完成率
|
|
,sum(pr_paid_amt + cu_paid_amt + fu_paid_amt)/10000 AS TODAY_TOLL_FEE -- 今日收费
|
|
,0 AS TYEAR_TOLL_FEE -- 年度累计收费
|
|
,0 AS MON_TOLL_FEE -- 月度累计收费
|
|
,0 AS CU_COLLECTION_RATE -- 当期收缴率
|
|
,0 AS PR_COLLECTION_RATE -- 往期收缴率
|
|
,0 AS COLLECTION_RATE -- 综合收缴率
|
|
,0 AS PR_OWED_AMT -- 往期欠费金额
|
|
,0 AS CU_OWED_AMT -- 当期欠费金额
|
|
,0 AS ENERGY_CONSUMPTION_AMT -- 能耗收费
|
|
,0 AS DEVICE_GOOD_RATE -- 设备完好率
|
|
,0 AS DEVICE_SPACE_NORMAL_RATE -- 设备空间巡查完成率
|
|
,0 AS NEC -- 在职人数
|
|
,0 AS FMR -- 满编率
|
|
,0 AS FMR_TARGET -- 满编率目标值
|
|
,0 AS TR -- 离职率
|
|
,0 AS TR_TARGET -- 离职率目标值
|
|
,0 AS AC_FEE_RATE -- 人力成本费率
|
|
,0 AS AC_FEE_RATE_TARGET -- 人力成本费率目标值
|
|
,0 AS ASSESSMENT_OCCUPANY_RATE -- 空间运营出租率
|
|
,0 AS RETAIL_FINISH_RATE -- 新零售完成率
|
|
,0 AS RENT_RATE -- 商业资产出租率
|
|
,0 AS RENT_COLLECTION_RATE -- 租金收缴率
|
|
,0 AS GUEST_ROOM_FEE -- 客房单价
|
|
,0 AS GUEST_RENT_RATE -- 客房出租率
|
|
FROM dw.dws_finance_today_fees_d
|
|
UNION ALL
|
|
SELECT
|
|
current_date() AS CURRENT_DT -- 当前日期
|
|
-- ,'FIN' AS BUSINESS_TYPE -- 业务类型编码
|
|
-- ,'财务' AS BUSINESS_NAME -- 业务类型名称
|
|
,0 AS INDEX_INCOME_FACT -- 收入
|
|
,0 AS INCOME_COMPLETION_RATE -- 收入完成率
|
|
,0 AS INDEX_COST_FACT -- 成本
|
|
,0 AS COST_COMPLETION_RATE -- 成本完成率
|
|
,0 AS INDEX_PROFIT_FACT -- 利润
|
|
,0 AS PROFIT_COMPLETION_RATE -- 利润完成率
|
|
,0 AS INDEX_MARKET_FACT -- 市拓
|
|
,0 AS MARKET_COMPLETION_RATE -- 市拓完成率
|
|
,0 AS TODAY_TOLL_FEE -- 今日收费
|
|
,SUM(pr_paid_cu_amt + cu_paid_cu_amt + pr_offset_cu_amt + cu_offset_cu_amt - cu_offset_pr_amt - cu_paid_pr_amt)/10000
|
|
AS TYEAR_TOLL_FEE -- 年度累计收费
|
|
,SUM(case when ym = '2024-07' then (pr_paid_cu_amt + cu_paid_cu_amt + pr_offset_cu_amt + cu_offset_cu_amt - cu_offset_pr_amt - cu_paid_pr_amt) else 0 end) /10000
|
|
AS MON_TOLL_FEE -- 月度累计收费
|
|
,0 AS CU_COLLECTION_RATE -- 当期收缴率
|
|
,0 AS PR_COLLECTION_RATE -- 往期收缴率
|
|
,0 AS COLLECTION_RATE -- 综合收缴率
|
|
,0 AS PR_OWED_AMT -- 往期欠费金额
|
|
,0 AS CU_OWED_AMT -- 当期欠费金额
|
|
,0 AS ENERGY_CONSUMPTION_AMT -- 能耗收费
|
|
,0 AS DEVICE_GOOD_RATE -- 设备完好率
|
|
,0 AS DEVICE_SPACE_NORMAL_RATE -- 设备空间巡查完成率
|
|
,0 AS NEC -- 在职人数
|
|
,0 AS FMR -- 满编率
|
|
,0 AS FMR_TARGET -- 满编率目标值
|
|
,0 AS TR -- 离职率
|
|
,0 AS TR_TARGET -- 离职率目标值
|
|
,0 AS AC_FEE_RATE -- 人力成本费率
|
|
,0 AS AC_FEE_RATE_TARGET -- 人力成本费率目标值
|
|
,0 AS ASSESSMENT_OCCUPANY_RATE -- 空间运营出租率
|
|
,0 AS RETAIL_FINISH_RATE -- 新零售完成率
|
|
,0 AS RENT_RATE -- 商业资产出租率
|
|
,0 AS RENT_COLLECTION_RATE -- 租金收缴率
|
|
,0 AS GUEST_ROOM_FEE -- 客房单价
|
|
,0 AS GUEST_RENT_RATE -- 客房出租率
|
|
FROM dw.dws_finance_fees_serial_m
|
|
WHERE left(ym,4) = '2024'
|
|
UNION ALL
|
|
SELECT
|
|
current_date() AS CURRENT_DT -- 当前日期
|
|
-- ,'FIN' AS BUSINESS_TYPE -- 业务类型编码
|
|
-- ,'财务' AS BUSINESS_NAME -- 业务类型名称
|
|
,0 AS INDEX_INCOME_FACT -- 收入
|
|
,0 AS INCOME_COMPLETION_RATE -- 收入完成率
|
|
,0 AS INDEX_COST_FACT -- 成本
|
|
,0 AS COST_COMPLETION_RATE -- 成本完成率
|
|
,0 AS INDEX_PROFIT_FACT -- 利润
|
|
,0 AS PROFIT_COMPLETION_RATE -- 利润完成率
|
|
,0 AS INDEX_MARKET_FACT -- 市拓
|
|
,0 AS MARKET_COMPLETION_RATE -- 市拓完成率
|
|
,0 AS TODAY_TOLL_FEE -- 今日收费
|
|
,0 AS TYEAR_TOLL_FEE -- 年度累计收费
|
|
,0 AS MON_TOLL_FEE -- 月度累计收费
|
|
,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)))
|
|
AS 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)))
|
|
AS PR_COLLECTION_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 COLLECTION_RATE -- 综合收缴率
|
|
,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))-(- ifnull(cu_paid_pr_amt,0) - ifnull(cu_offset_pr_amt,0)))/10000
|
|
AS PR_OWED_AMT -- 往期欠费金额
|
|
,sum((ifnull(cu_receivable_amt,0) + ifnull(pr_exempt_cu_amt,0) + ifnull(cu_exempt_cu_amt,0))-(ifnull(pr_paid_cu_amt,0) + ifnull(cu_paid_cu_amt,0) - ifnull(pr_offset_cu_amt,0) - ifnull(cu_offset_cu_amt,0)))/10000
|
|
AS CU_OWED_AMT -- 当期欠费金额
|
|
,0 AS ENERGY_CONSUMPTION_AMT -- 能耗收费
|
|
,0 AS DEVICE_GOOD_RATE -- 设备完好率
|
|
,0 AS DEVICE_SPACE_NORMAL_RATE -- 设备空间巡查完成率
|
|
,0 AS NEC -- 在职人数
|
|
,0 AS FMR -- 满编率
|
|
,0 AS FMR_TARGET -- 满编率目标值
|
|
,0 AS TR -- 离职率
|
|
,0 AS TR_TARGET -- 离职率目标值
|
|
,0 AS AC_FEE_RATE -- 人力成本费率
|
|
,0 AS AC_FEE_RATE_TARGET -- 人力成本费率目标值
|
|
,0 AS ASSESSMENT_OCCUPANY_RATE -- 空间运营出租率
|
|
,0 AS RETAIL_FINISH_RATE -- 新零售完成率
|
|
,0 AS RENT_RATE -- 商业资产出租率
|
|
,0 AS RENT_COLLECTION_RATE -- 租金收缴率
|
|
,0 AS GUEST_ROOM_FEE -- 客房单价
|
|
,0 AS GUEST_RENT_RATE -- 客房出租率
|
|
FROM dw.dwd_finance_fees_serial_d
|
|
where ParentCostCode = '0001' and organ_name not in ('东湖商管','领悦总部','演示机构')
|
|
and ym = DATE_FORMAT(NOW(),'%Y-%m')
|
|
UNION ALL
|
|
SELECT
|
|
current_date() AS CURRENT_DT -- 当前日期
|
|
-- ,'ENGINE' AS BUSINESS_TYPE -- 业务类型编码
|
|
-- ,'工程管理' AS BUSINESS_NAME -- 业务类型名称
|
|
,0 AS INDEX_INCOME_FACT -- 收入
|
|
,0 AS INCOME_COMPLETION_RATE -- 收入完成率
|
|
,0 AS INDEX_COST_FACT -- 成本
|
|
,0 AS COST_COMPLETION_RATE -- 成本完成率
|
|
,0 AS INDEX_PROFIT_FACT -- 利润
|
|
,0 AS PROFIT_COMPLETION_RATE -- 利润完成率
|
|
,0 AS INDEX_MARKET_FACT -- 市拓
|
|
,0 AS MARKET_COMPLETION_RATE -- 市拓完成率
|
|
,0 AS TODAY_TOLL_FEE -- 今日收费
|
|
,0 AS TYEAR_TOLL_FEE -- 年度累计收费
|
|
,0 AS MON_TOLL_FEE -- 月度累计收费
|
|
,0 AS CU_COLLECTION_RATE -- 当期收缴率
|
|
,0 AS PR_COLLECTION_RATE -- 往期收缴率
|
|
,0 AS COLLECTION_RATE -- 综合收缴率
|
|
,0 AS PR_OWED_AMT -- 往期欠费金额
|
|
,0 AS CU_OWED_AMT -- 当期欠费金额
|
|
,sum(case when ym = '2024-07' and ParentCostCode = '0008' then (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_offset_pr_amt,0) - ifnull(cu_paid_pr_amt,0)) else 0 end) /10000
|
|
AS ENERGY_CONSUMPTION_AMT -- 能耗收费
|
|
,0 AS DEVICE_GOOD_RATE -- 设备完好率
|
|
,0 AS DEVICE_SPACE_NORMAL_RATE -- 设备空间巡查完成率
|
|
,0 AS NEC -- 在职人数
|
|
,0 AS FMR -- 满编率
|
|
,0 AS FMR_TARGET -- 满编率目标值
|
|
,0 AS TR -- 离职率
|
|
,0 AS TR_TARGET -- 离职率目标值
|
|
,0 AS AC_FEE_RATE -- 人力成本费率
|
|
,0 AS AC_FEE_RATE_TARGET -- 人力成本费率目标值
|
|
,0 AS ASSESSMENT_OCCUPANY_RATE -- 空间运营出租率
|
|
,0 AS RETAIL_FINISH_RATE -- 新零售完成率
|
|
,0 AS RENT_RATE -- 商业资产出租率
|
|
,0 AS RENT_COLLECTION_RATE -- 租金收缴率
|
|
,0 AS GUEST_ROOM_FEE -- 客房单价
|
|
,0 AS GUEST_RENT_RATE -- 客房出租率
|
|
FROM dw.dws_finance_fees_serial_m
|
|
WHERE left(ym,4) = '2024'
|
|
and organ_name not in ('东湖商管','领悦总部','演示机构')
|
|
UNION ALL
|
|
SELECT
|
|
current_date() AS CURRENT_DT -- 当前日期
|
|
-- ,'ENGINE' AS BUSINESS_TYPE -- 业务类型编码
|
|
-- ,'工程管理' AS BUSINESS_NAME -- 业务类型名称
|
|
,0 AS INDEX_INCOME_FACT -- 收入
|
|
,0 AS INCOME_COMPLETION_RATE -- 收入完成率
|
|
,0 AS INDEX_COST_FACT -- 成本
|
|
,0 AS COST_COMPLETION_RATE -- 成本完成率
|
|
,0 AS INDEX_PROFIT_FACT -- 利润
|
|
,0 AS PROFIT_COMPLETION_RATE -- 利润完成率
|
|
,0 AS INDEX_MARKET_FACT -- 市拓
|
|
,0 AS MARKET_COMPLETION_RATE -- 市拓完成率
|
|
,0 AS TODAY_TOLL_FEE -- 今日收费
|
|
,0 AS TYEAR_TOLL_FEE -- 年度累计收费
|
|
,0 AS MON_TOLL_FEE -- 月度累计收费
|
|
,0 AS CU_COLLECTION_RATE -- 当期收缴率
|
|
,0 AS PR_COLLECTION_RATE -- 往期收缴率
|
|
,0 AS COLLECTION_RATE -- 综合收缴率
|
|
,0 AS PR_OWED_AMT -- 往期欠费金额
|
|
,0 AS CU_OWED_AMT -- 当期欠费金额
|
|
,0 AS ENERGY_CONSUMPTION_AMT -- 能耗收费
|
|
,CASE WHEN sum(equipment_num) = 0 THEN 0
|
|
ELSE sum(good_equipment_num) / sum(equipment_num)
|
|
END AS DEVICE_GOOD_RATE -- 设备完好率
|
|
,0 AS DEVICE_SPACE_NORMAL_RATE -- 设备空间巡查完成率
|
|
,0 AS NEC -- 在职人数
|
|
,0 AS FMR -- 满编率
|
|
,0 AS FMR_TARGET -- 满编率目标值
|
|
,0 AS TR -- 离职率
|
|
,0 AS TR_TARGET -- 离职率目标值
|
|
,0 AS AC_FEE_RATE -- 人力成本费率
|
|
,0 AS AC_FEE_RATE_TARGET -- 人力成本费率目标值
|
|
,0 AS ASSESSMENT_OCCUPANY_RATE -- 空间运营出租率
|
|
,0 AS RETAIL_FINISH_RATE -- 新零售完成率
|
|
,0 AS RENT_RATE -- 商业资产出租率
|
|
,0 AS RENT_COLLECTION_RATE -- 租金收缴率
|
|
,0 AS GUEST_ROOM_FEE -- 客房单价
|
|
,0 AS GUEST_RENT_RATE -- 客房出租率
|
|
FROM dw.dws_engine_equipment_in_good_d
|
|
UNION ALL
|
|
SELECT
|
|
current_date() AS CURRENT_DT -- 当前日期
|
|
-- ,'ENGINE' AS BUSINESS_TYPE -- 业务类型编码
|
|
-- ,'工程管理' AS BUSINESS_NAME -- 业务类型名称
|
|
,0 AS INDEX_INCOME_FACT -- 收入
|
|
,0 AS INCOME_COMPLETION_RATE -- 收入完成率
|
|
,0 AS INDEX_COST_FACT -- 成本
|
|
,0 AS COST_COMPLETION_RATE -- 成本完成率
|
|
,0 AS INDEX_PROFIT_FACT -- 利润
|
|
,0 AS PROFIT_COMPLETION_RATE -- 利润完成率
|
|
,0 AS INDEX_MARKET_FACT -- 市拓
|
|
,0 AS MARKET_COMPLETION_RATE -- 市拓完成率
|
|
,0 AS TODAY_TOLL_FEE -- 今日收费
|
|
,0 AS TYEAR_TOLL_FEE -- 年度累计收费
|
|
,0 AS MON_TOLL_FEE -- 月度累计收费
|
|
,0 AS CU_COLLECTION_RATE -- 当期收缴率
|
|
,0 AS PR_COLLECTION_RATE -- 往期收缴率
|
|
,0 AS COLLECTION_RATE -- 综合收缴率
|
|
,0 AS PR_OWED_AMT -- 往期欠费金额
|
|
,0 AS CU_OWED_AMT -- 当期欠费金额
|
|
,0 AS ENERGY_CONSUMPTION_AMT -- 能耗收费
|
|
,0 AS DEVICE_GOOD_RATE -- 设备完好率
|
|
,CASE WHEN sum(task_num) = 0 THEN 0
|
|
ELSE SUM(task_finish_num) /sum(task_num)
|
|
END AS DEVICE_SPACE_NORMAL_RATE -- 设备空间巡查完成率
|
|
,0 AS NEC -- 在职人数
|
|
,0 AS FMR -- 满编率
|
|
,0 AS FMR_TARGET -- 满编率目标值
|
|
,0 AS TR -- 离职率
|
|
,0 AS TR_TARGET -- 离职率目标值
|
|
,0 AS AC_FEE_RATE -- 人力成本费率
|
|
,0 AS AC_FEE_RATE_TARGET -- 人力成本费率目标值
|
|
,0 AS ASSESSMENT_OCCUPANY_RATE -- 空间运营出租率
|
|
,0 AS RETAIL_FINISH_RATE -- 新零售完成率
|
|
,0 AS RENT_RATE -- 商业资产出租率
|
|
,0 AS RENT_COLLECTION_RATE -- 租金收缴率
|
|
,0 AS GUEST_ROOM_FEE -- 客房单价
|
|
,0 AS GUEST_RENT_RATE -- 客房出租率
|
|
FROM dw.dws_engine_equipment_inspect_task_m
|
|
WHERE task_ym = date_format(current_date(),'%Y%m')
|
|
UNION ALL
|
|
SELECT
|
|
current_date() AS CURRENT_DT -- 当前日期
|
|
-- ,'INNOVA' AS BUSINESS_TYPE -- 业务类型编码
|
|
-- ,'创新生态' AS BUSINESS_NAME -- 业务类型名称
|
|
,0 AS INDEX_INCOME_FACT -- 收入
|
|
,0 AS INCOME_COMPLETION_RATE -- 收入完成率
|
|
,0 AS INDEX_COST_FACT -- 成本
|
|
,0 AS COST_COMPLETION_RATE -- 成本完成率
|
|
,0 AS INDEX_PROFIT_FACT -- 利润
|
|
,0 AS PROFIT_COMPLETION_RATE -- 利润完成率
|
|
,0 AS INDEX_MARKET_FACT -- 市拓
|
|
,0 AS MARKET_COMPLETION_RATE -- 市拓完成率
|
|
,0 AS TODAY_TOLL_FEE -- 今日收费
|
|
,0 AS TYEAR_TOLL_FEE -- 年度累计收费
|
|
,0 AS MON_TOLL_FEE -- 月度累计收费
|
|
,0 AS CU_COLLECTION_RATE -- 当期收缴率
|
|
,0 AS PR_COLLECTION_RATE -- 往期收缴率
|
|
,0 AS COLLECTION_RATE -- 综合收缴率
|
|
,0 AS PR_OWED_AMT -- 往期欠费金额
|
|
,0 AS CU_OWED_AMT -- 当期欠费金额
|
|
,0 AS ENERGY_CONSUMPTION_AMT -- 能耗收费
|
|
,0 AS DEVICE_GOOD_RATE -- 设备完好率
|
|
,0 AS DEVICE_SPACE_NORMAL_RATE -- 设备空间巡查完成率
|
|
,0 AS NEC -- 在职人数
|
|
,0 AS FMR -- 满编率
|
|
,0 AS FMR_TARGET -- 满编率目标值
|
|
,0 AS TR -- 离职率
|
|
,0 AS TR_TARGET -- 离职率目标值
|
|
,0 AS AC_FEE_RATE -- 人力成本费率
|
|
,0 AS AC_FEE_RATE_TARGET -- 人力成本费率目标值
|
|
,assessment_occupany_rate / 100 AS ASSESSMENT_OCCUPANY_RATE -- 空间运营出租率
|
|
,0 AS RETAIL_FINISH_RATE -- 新零售完成率
|
|
,0 AS RENT_RATE -- 商业资产出租率
|
|
,0 AS RENT_COLLECTION_RATE -- 租金收缴率
|
|
,0 AS GUEST_ROOM_FEE -- 客房单价
|
|
,0 AS GUEST_RENT_RATE -- 客房出租率
|
|
FROM dw.dws_point_occupancy_rate_summary_all
|
|
UNION ALL
|
|
SELECT
|
|
current_date() AS CURRENT_DT -- 当前日期
|
|
-- ,'INNOVA' AS BUSINESS_TYPE -- 业务类型编码
|
|
-- ,'创新生态' AS BUSINESS_NAME -- 业务类型名称
|
|
,0 AS INDEX_INCOME_FACT -- 收入
|
|
,0 AS INCOME_COMPLETION_RATE -- 收入完成率
|
|
,0 AS INDEX_COST_FACT -- 成本
|
|
,0 AS COST_COMPLETION_RATE -- 成本完成率
|
|
,0 AS INDEX_PROFIT_FACT -- 利润
|
|
,0 AS PROFIT_COMPLETION_RATE -- 利润完成率
|
|
,0 AS INDEX_MARKET_FACT -- 市拓
|
|
,0 AS MARKET_COMPLETION_RATE -- 市拓完成率
|
|
,0 AS TODAY_TOLL_FEE -- 今日收费
|
|
,0 AS TYEAR_TOLL_FEE -- 年度累计收费
|
|
,0 AS MON_TOLL_FEE -- 月度累计收费
|
|
,0 AS CU_COLLECTION_RATE -- 当期收缴率
|
|
,0 AS PR_COLLECTION_RATE -- 往期收缴率
|
|
,0 AS COLLECTION_RATE -- 综合收缴率
|
|
,0 AS PR_OWED_AMT -- 往期欠费金额
|
|
,0 AS CU_OWED_AMT -- 当期欠费金额
|
|
,0 AS ENERGY_CONSUMPTION_AMT -- 能耗收费
|
|
,0 AS DEVICE_GOOD_RATE -- 设备完好率
|
|
,0 AS DEVICE_SPACE_NORMAL_RATE -- 设备空间巡查完成率
|
|
,0 AS NEC -- 在职人数
|
|
,0 AS FMR -- 满编率
|
|
,0 AS FMR_TARGET -- 满编率目标值
|
|
,0 AS TR -- 离职率
|
|
,0 AS TR_TARGET -- 离职率目标值
|
|
,0 AS AC_FEE_RATE -- 人力成本费率
|
|
,0 AS AC_FEE_RATE_TARGET -- 人力成本费率目标值
|
|
,0 AS ASSESSMENT_OCCUPANY_RATE -- 空间运营出租率
|
|
,CASE WHEN SUM(CASE WHEN revenue_type = '创新' AND business_type = '目标' THEN COALESCE(revenue_value,0) ELSE 0 END) = 0 THEN 0
|
|
ELSE SUM(CASE WHEN revenue_type = '创新' AND business_type = '收入' THEN COALESCE(revenue_value,0) ELSE 0 END)
|
|
/ SUM(CASE WHEN revenue_type = '创新' AND business_type = '目标' THEN COALESCE(revenue_value,0) ELSE 0 END)
|
|
END AS RETAIL_FINISH_RATE -- 新零售完成率
|
|
,0 AS RENT_RATE -- 商业资产出租率
|
|
,0 AS RENT_COLLECTION_RATE -- 租金收缴率
|
|
,0 AS GUEST_ROOM_FEE -- 客房单价
|
|
,0 AS GUEST_RENT_RATE -- 客房出租率
|
|
FROM dw.ods_innoveco_metrics_finish_d
|
|
WHERE business_type in('收入','目标') -- 业务类型
|
|
AND YM = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y-%m') -- 2024-06 当前月份的上一月
|
|
UNION ALL
|
|
SELECT
|
|
current_date() AS CURRENT_DT -- 当前日期
|
|
-- ,'OTHER' AS BUSINESS_TYPE -- 业务类型编码
|
|
-- ,'其他' AS BUSINESS_NAME -- 业务类型名称
|
|
,0 AS INDEX_INCOME_FACT -- 收入
|
|
,0 AS INCOME_COMPLETION_RATE -- 收入完成率
|
|
,0 AS INDEX_COST_FACT -- 成本
|
|
,0 AS COST_COMPLETION_RATE -- 成本完成率
|
|
,0 AS INDEX_PROFIT_FACT -- 利润
|
|
,0 AS PROFIT_COMPLETION_RATE -- 利润完成率
|
|
,0 AS INDEX_MARKET_FACT -- 市拓
|
|
,0 AS MARKET_COMPLETION_RATE -- 市拓完成率
|
|
,0 AS TODAY_TOLL_FEE -- 今日收费
|
|
,0 AS TYEAR_TOLL_FEE -- 年度累计收费
|
|
,0 AS MON_TOLL_FEE -- 月度累计收费
|
|
,0 AS CU_COLLECTION_RATE -- 当期收缴率
|
|
,0 AS PR_COLLECTION_RATE -- 往期收缴率
|
|
,0 AS COLLECTION_RATE -- 综合收缴率
|
|
,0 AS PR_OWED_AMT -- 往期欠费金额
|
|
,0 AS CU_OWED_AMT -- 当期欠费金额
|
|
,0 AS ENERGY_CONSUMPTION_AMT -- 能耗收费
|
|
,0 AS DEVICE_GOOD_RATE -- 设备完好率
|
|
,0 AS DEVICE_SPACE_NORMAL_RATE -- 设备空间巡查完成率
|
|
,0 AS NEC -- 在职人数
|
|
,0 AS FMR -- 满编率
|
|
,0 AS FMR_TARGET -- 满编率目标值
|
|
,0 AS TR -- 离职率
|
|
,0 AS TR_TARGET -- 离职率目标值
|
|
,0 AS AC_FEE_RATE -- 人力成本费率
|
|
,0 AS AC_FEE_RATE_TARGET -- 人力成本费率目标值
|
|
,0 AS ASSESSMENT_OCCUPANY_RATE -- 空间运营出租率
|
|
,0 AS RETAIL_FINISH_RATE -- 新零售完成率
|
|
,SUM(T.rentRoomAreaSum) / (SUM(T.rentRoomAreaSum) + SUM(T.notRentRoomAreaSum))
|
|
AS RENT_RATE -- 商业资产出租率
|
|
,0 AS RENT_COLLECTION_RATE -- 租金收缴率
|
|
,0 AS GUEST_ROOM_FEE -- 客房单价
|
|
,0 AS GUEST_RENT_RATE -- 客房出租率
|
|
FROM
|
|
(
|
|
SELECT
|
|
B.`type`
|
|
,SUM(CASE WHEN A.business_status = 1 THEN A.construction_area ELSE 0 END ) AS rentRoomAreaSum -- 出租面积
|
|
,SUM(CASE WHEN A.business_status != 4 AND A.business_status != 1 THEN A.construction_area ELSE 0 END ) AS notRentRoomAreaSum -- 未出租面积
|
|
FROM
|
|
(
|
|
SELECT
|
|
id
|
|
,erp_id
|
|
,building_id
|
|
,business_status
|
|
,construction_area -- 建筑面积
|
|
FROM dw.ods_bs_room_d -- 商管房间表
|
|
WHERE del_flag = 0
|
|
AND enable_flag = 0
|
|
AND review_status = 2
|
|
)A
|
|
LEFT JOIN
|
|
(
|
|
select
|
|
id
|
|
,project_id
|
|
,project_name
|
|
,`type` -- 业态 商业街，集中商业，写字楼，底商
|
|
from dim_business_building_d
|
|
)B
|
|
on A.building_id = B.id
|
|
GROUP BY
|
|
B.`type`
|
|
)T
|
|
UNION ALL
|
|
SELECT
|
|
current_date() AS CURRENT_DT -- 当前日期
|
|
-- ,'OTHER' AS BUSINESS_TYPE -- 业务类型编码
|
|
-- ,'其他' AS BUSINESS_NAME -- 业务类型名称
|
|
,0 AS INDEX_INCOME_FACT -- 收入
|
|
,0 AS INCOME_COMPLETION_RATE -- 收入完成率
|
|
,0 AS INDEX_COST_FACT -- 成本
|
|
,0 AS COST_COMPLETION_RATE -- 成本完成率
|
|
,0 AS INDEX_PROFIT_FACT -- 利润
|
|
,0 AS PROFIT_COMPLETION_RATE -- 利润完成率
|
|
,0 AS INDEX_MARKET_FACT -- 市拓
|
|
,0 AS MARKET_COMPLETION_RATE -- 市拓完成率
|
|
,0 AS TODAY_TOLL_FEE -- 今日收费
|
|
,0 AS TYEAR_TOLL_FEE -- 年度累计收费
|
|
,0 AS MON_TOLL_FEE -- 月度累计收费
|
|
,0 AS CU_COLLECTION_RATE -- 当期收缴率
|
|
,0 AS PR_COLLECTION_RATE -- 往期收缴率
|
|
,0 AS COLLECTION_RATE -- 综合收缴率
|
|
,0 AS PR_OWED_AMT -- 往期欠费金额
|
|
,0 AS CU_OWED_AMT -- 当期欠费金额
|
|
,0 AS ENERGY_CONSUMPTION_AMT -- 能耗收费
|
|
,0 AS DEVICE_GOOD_RATE -- 设备完好率
|
|
,0 AS DEVICE_SPACE_NORMAL_RATE -- 设备空间巡查完成率
|
|
,0 AS NEC -- 在职人数
|
|
,0 AS FMR -- 满编率
|
|
,0 AS FMR_TARGET -- 满编率目标值
|
|
,0 AS TR -- 离职率
|
|
,0 AS TR_TARGET -- 离职率目标值
|
|
,0 AS AC_FEE_RATE -- 人力成本费率
|
|
,0 AS AC_FEE_RATE_TARGET -- 人力成本费率目标值
|
|
,0 AS ASSESSMENT_OCCUPANY_RATE -- 空间运营出租率
|
|
,0 AS RETAIL_FINISH_RATE -- 新零售完成率
|
|
,0 AS RENT_RATE -- 商业资产出租率
|
|
,SUM(T.received_money) / SUM(T.current_sure_money)
|
|
AS RENT_COLLECTION_RATE -- 租金收缴率
|
|
,0 AS GUEST_ROOM_FEE -- 客房单价
|
|
,0 AS GUEST_RENT_RATE -- 客房出租率
|
|
FROM
|
|
(
|
|
SELECT
|
|
T1.received_money AS received_money
|
|
,T1.current_sure_money AS current_sure_money
|
|
|
|
FROM
|
|
(
|
|
SELECT
|
|
a.project_id -- 项目id 关联bs_project表
|
|
,SUM(a.received_money) AS received_money -- 实收金额
|
|
,SUM(a.current_sure_money) AS current_sure_money -- 实际应收金额=应收金额-合同减免金额
|
|
-- ,SUM(a.under_money) AS under_money -- 欠收金额
|
|
FROM
|
|
(
|
|
SELECT
|
|
contract_id
|
|
,accrual_month
|
|
,start_period
|
|
,bill_type
|
|
,received_money -- 实收金额
|
|
,current_sure_money -- 实际应收金额=应收金额-合同减免金额
|
|
,current_sure_money - received_money as under_money -- 欠收金额
|
|
,project_id
|
|
FROM dw.ods_bs_fi_bills_d
|
|
WHERE save_status = 1 -- 账单状态 0是保存 1是审核过的
|
|
-- ${if(len(project) == 0,"","AND project_id in (" + project + ")")}
|
|
)a
|
|
left join
|
|
(
|
|
SELECT
|
|
status
|
|
,stop_date
|
|
,id
|
|
FROM dw.ods_bs_contract_d
|
|
WHERE del_flag = 0
|
|
)bc
|
|
on bc.id = a.contract_id
|
|
WHERE ((bc.status != 2 and bc.stop_date is null) OR
|
|
(bc.stop_date is not null
|
|
and ((a.accrual_month <= DATE_FORMAT(bc.stop_date, '%Y-%m')
|
|
and a.start_period <= bc.stop_date)
|
|
|
|
or a.bill_type = 15)
|
|
))
|
|
group by
|
|
a.project_id
|
|
UNION ALL
|
|
SELECT
|
|
a.project_id
|
|
,SUM(b.received_amount) AS received_money
|
|
,SUM(b.received_money) AS current_sure_money
|
|
-- ,SUM(b.under_money) AS under_money
|
|
FROM
|
|
(
|
|
SELECT
|
|
receive_no -- 催缴单号
|
|
,project_id
|
|
FROM dw.ods_bs_fi_receive_other_d
|
|
where recheck_status = 2
|
|
)a
|
|
LEFT JOIN
|
|
(
|
|
SELECT
|
|
receive_no
|
|
,received_amount
|
|
,received_money
|
|
,received_money - received_amount AS under_money
|
|
FROM dw.ods_bs_fi_receive_other_item_d
|
|
)b
|
|
ON a.receive_no = b.receive_no
|
|
group by
|
|
project_id
|
|
)T1
|
|
left join
|
|
(
|
|
select
|
|
distinct id
|
|
,erp_id
|
|
from dw.ods_bs_project_d
|
|
) b
|
|
on T1.project_id = b.id
|
|
left join
|
|
(
|
|
select
|
|
distinct
|
|
organ_code
|
|
,organ_name
|
|
,comm_id
|
|
,comm_name
|
|
from dim_organ_mapping
|
|
where comm_id is not null
|
|
) c
|
|
on b.erp_id = c.comm_id
|
|
where 1 = 1
|
|
)T
|
|
UNION ALL
|
|
SELECT
|
|
current_date() AS CURRENT_DT -- 当前日期
|
|
-- ,'OTHER' AS BUSINESS_TYPE -- 业务类型编码
|
|
-- ,'其他' AS BUSINESS_NAME -- 业务类型名称
|
|
,0 AS INDEX_INCOME_FACT -- 收入
|
|
,0 AS INCOME_COMPLETION_RATE -- 收入完成率
|
|
,0 AS INDEX_COST_FACT -- 成本
|
|
,0 AS COST_COMPLETION_RATE -- 成本完成率
|
|
,0 AS INDEX_PROFIT_FACT -- 利润
|
|
,0 AS PROFIT_COMPLETION_RATE -- 利润完成率
|
|
,0 AS INDEX_MARKET_FACT -- 市拓
|
|
,0 AS MARKET_COMPLETION_RATE -- 市拓完成率
|
|
,0 AS TODAY_TOLL_FEE -- 今日收费
|
|
,0 AS TYEAR_TOLL_FEE -- 年度累计收费
|
|
,0 AS MON_TOLL_FEE -- 月度累计收费
|
|
,0 AS CU_COLLECTION_RATE -- 当期收缴率
|
|
,0 AS PR_COLLECTION_RATE -- 往期收缴率
|
|
,0 AS COLLECTION_RATE -- 综合收缴率
|
|
,0 AS PR_OWED_AMT -- 往期欠费金额
|
|
,0 AS CU_OWED_AMT -- 当期欠费金额
|
|
,0 AS ENERGY_CONSUMPTION_AMT -- 能耗收费
|
|
,0 AS DEVICE_GOOD_RATE -- 设备完好率
|
|
,0 AS DEVICE_SPACE_NORMAL_RATE -- 设备空间巡查完成率
|
|
,0 AS NEC -- 在职人数
|
|
,0 AS FMR -- 满编率
|
|
,0 AS FMR_TARGET -- 满编率目标值
|
|
,0 AS TR -- 离职率
|
|
,0 AS TR_TARGET -- 离职率目标值
|
|
,0 AS AC_FEE_RATE -- 人力成本费率
|
|
,0 AS AC_FEE_RATE_TARGET -- 人力成本费率目标值
|
|
,0 AS ASSESSMENT_OCCUPANY_RATE -- 空间运营出租率
|
|
,0 AS RETAIL_FINISH_RATE -- 新零售完成率
|
|
,0 AS RENT_RATE -- 商业资产出租率
|
|
,0 AS RENT_COLLECTION_RATE -- 租金收缴率
|
|
,AVG(price) AS GUEST_ROOM_FEE -- 客房单价
|
|
,AVG(occupancy_rate) / 100 AS GUEST_RENT_RATE -- 客房出租率
|
|
FROM dw.ods_hotel_price
|
|
WHERE quanter = CASE WHEN quarter(CURRENT_TIMESTAMP()) - 1 = 1 THEN '一季度'
|
|
WHEN quarter(CURRENT_TIMESTAMP()) - 1 = 2 THEN '二季度'
|
|
WHEN quarter(CURRENT_TIMESTAMP()) - 1 = 3 THEN '三季度'
|
|
WHEN quarter(CURRENT_TIMESTAMP()) - 1 = 4 THEN '四季度'
|
|
ELSE 0 END
|
|
;
|
|
|
|
|
|
-- ==================================================
|
|
-- 指标卡|地图
|
|
-- ==================================================
|
|
|
|
|
|
DROP TABLE IF EXISTS dw.dws_president_map_d;
|
|
CREATE TABLE IF NOT EXISTS dw.dws_president_map_d (
|
|
CURRENT_DT VARCHAR(50) COMMENT '当期日期'
|
|
,PROVINCE VARCHAR(100) COMMENT '省份'
|
|
,CITY VARCHAR(100) COMMENT '城市'
|
|
,COUNTY VARCHAR(100) COMMENT '县'
|
|
,TREATY_AREA DECIMAL(12,4) COMMENT '合约面积(万m²)'
|
|
,MANAGE_AREA DECIMAL(12,4) COMMENT '在管面积(万m²)'
|
|
,ROOM_NUM BIGINT COMMENT '总户数(户)'
|
|
,COMM_NUM BIGINT COMMENT '在管项目数(个)'
|
|
)COMMENT = '总裁驾驶舱对外展示地图指标'
|
|
;
|
|
|
|
|
|
TRUNCATE TABLE dw.dws_president_map_d;
|
|
DELETE FROM dw.dws_president_map_d WHERE CURRENT_DT = '2024-08-02';
|
|
|
|
INSERT INTO dw.dws_president_map_d
|
|
SELECT
|
|
current_date() AS CURRENT_DT -- 当前日期
|
|
,a.Province AS PROVINCE -- 省份
|
|
,a.City AS CITY -- 城市
|
|
,a.County AS COUNTY -- 县
|
|
,round(sum(a.ContractArea) / 10000,2) AS TREATY_AREA -- 合约面积(万m²)
|
|
,round(sum(a.TakeOverArea) / 10000,2) AS MANAGE_AREA -- 在管面积(万m²)
|
|
,sum(c.room_num) AS ROOM_NUM -- 总户数(户)
|
|
,count(*) AS COMM_NUM -- 在管项目数
|
|
FROM dw.dim_project_base_info_d a
|
|
inner join (select distinct organ_code, organ_name, comm_id , comm_name from dim_organ_mapping where comm_id is not null and comm_name not like '%撤场%' and comm_name not like '%案场%' and comm_name not like '%禁用%'
|
|
and length(comm_id) != 0) b
|
|
on a.CommId = b.comm_id
|
|
left join (
|
|
select
|
|
CommID, count(*) room_num
|
|
from dw.dim_room_d
|
|
group by CommID
|
|
) c on a.CommId = c.CommID
|
|
where 1=1
|
|
group by
|
|
a.Province
|
|
,a.City
|
|
,a.County
|
|
;
|
|
|
|
-- ==================================================
|
|
-- 图表
|
|
-- ==================================================
|
|
|
|
DROP TABLE IF EXISTS dw.dws_president_chart_d;
|
|
CREATE TABLE IF NOT EXISTS dw.dws_president_chart_d (
|
|
CURRENT_DT VARCHAR(50) COMMENT '年'
|
|
,YM VARCHAR(100) COMMENT '年月'
|
|
,MONTHS VARCHAR(100) COMMENT '月份'
|
|
,CURRENT_QUARTER VARCHAR(100) COMMENT '年月归属季度'
|
|
,PLAN_NUM INT COMMENT '计划总数'
|
|
,PLAN_INCOMPLETE_NUM INT COMMENT '未完成计划数量'
|
|
,PLAN_COMPLETE_RATE DECIMAL(6,4) COMMENT '计划完成率'
|
|
,KM_TARGET DECIMAL(6,4) COMMENT '客满目标得分'
|
|
,KM_DF DECIMAL(6,4) COMMENT '客满得分'
|
|
,RESPONSE_TIMELY_RATE DECIMAL(6,4) COMMENT '响应率'
|
|
,CLOSE_RATE DECIMAL(6,4) COMMENT '闭单率'
|
|
,DELAY_RATE DECIMAL(6,4) COMMENT '延期率'
|
|
,TOUSU_INCIDENT_NUM INT COMMENT '投诉总量'
|
|
,TOUSU_PER_ROOM_RATE INT COMMENT '每万户平均投诉量'
|
|
)COMMENT = '总裁驾驶舱对外展示图表指标'
|
|
|
|
|
|
DELETE FROM dw.dws_president_chart_d WHERE CURRENT_DT = '2024-08-01';
|
|
TRUNCATE TABLE dw.dws_president_chart_d;
|
|
|
|
INSERT INTO dw.dws_president_chart_d
|
|
SELECT
|
|
current_date() AS CURRENT_DT -- 当前日期
|
|
,ym AS YM -- 年月
|
|
,concat(right(ym,2) + 0 , '月') AS MONTHS
|
|
,'' AS CURRENT_QUARTER -- 年月归属季度
|
|
,sum(plan_num) AS PLAN_NUM -- 计划总数
|
|
,sum(plan_incomplete_num) AS PLAN_INCOMPLETE_NUM -- 未完成计划数量
|
|
,CASE WHEN sum(plan_num) = 0 THEN 0
|
|
ELSE sum(plan_complete_num) / sum(plan_num)
|
|
END AS PLAN_COMPLETE_RATE -- 计划完成率
|
|
,0 AS KM_TARGET -- 客满目标得分
|
|
,0 AS KM_DF -- 客满得分
|
|
,0 AS RESPONSE_TIMELY_RATE -- 响应率
|
|
,0 AS CLOSE_RATE -- 闭单率
|
|
,0 AS DELAY_RATE -- 延期率
|
|
,0 AS TOUSU_INCIDENT_NUM -- 投诉总量
|
|
,0 AS TOUSU_PER_ROOM_RATE -- 每万户平均投诉量
|
|
FROM dw.dws_operation_plan_summary_m a
|
|
left join
|
|
(
|
|
select
|
|
distinct
|
|
organ_code
|
|
, organ_name
|
|
, comm_id
|
|
, comm_name
|
|
, oa_organ_code
|
|
, oa_organ_name
|
|
, oa_comm_id
|
|
, oa_comm_name
|
|
from dim_organ_mapping where comm_id is not null
|
|
) b
|
|
on a.subcompanyid = b.oa_comm_id
|
|
where 1=1
|
|
and ym <= DATE_FORMAT(NOW(),'%Y-%m')
|
|
and STR_TO_DATE(concat(ym,'-01'),'%Y-%m-%d') >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 6 MONTH),'%Y-%m-01')
|
|
and left(ym,4) = DATE_FORMAT(NOW(),'%Y')
|
|
group by ym
|
|
-- order by ym asc
|
|
UNION ALL
|
|
SELECT
|
|
current_date() AS CURRENT_DT -- 当前日期
|
|
,NULL AS YM -- 年月
|
|
,NULL AS MONTHS
|
|
,t1.jd AS CURRENT_QUARTER -- 年月归属季度
|
|
,0 AS PLAN_NUM -- 计划总数
|
|
,0 AS PLAN_INCOMPLETE_NUM -- 未完成计划数量
|
|
,0 AS PLAN_COMPLETE_RATE -- 计划完成率
|
|
,t1.km_target AS KM_TARGET -- 客满目标得分
|
|
,t2.df AS KM_DF -- 客满得分
|
|
,0 AS RESPONSE_TIMELY_RATE -- 响应率
|
|
,0 AS CLOSE_RATE -- 闭单率
|
|
,0 AS DELAY_RATE -- 延期率
|
|
,0 AS TOUSU_INCIDENT_NUM -- 投诉总量
|
|
,0 AS TOUSU_PER_ROOM_RATE -- 每万户平均投诉量
|
|
FROM
|
|
(
|
|
select
|
|
'一季度' AS jd
|
|
,km_target
|
|
from ods_wuye_gszt
|
|
where km_target is not null
|
|
and yr = YEAR(now())
|
|
group by
|
|
km_target
|
|
union all
|
|
select
|
|
'二季度' AS jd
|
|
,km_target
|
|
from ods_wuye_gszt
|
|
where km_target is not null
|
|
and yr = YEAR(now())
|
|
group by
|
|
km_target
|
|
union all
|
|
select
|
|
'三季度' AS jd
|
|
,km_target
|
|
from ods_wuye_gszt
|
|
where km_target is not null
|
|
and yr = YEAR(now())
|
|
group by
|
|
km_target
|
|
union all
|
|
select
|
|
'四季度' AS jd
|
|
,km_target
|
|
from ods_wuye_gszt
|
|
where km_target is not null
|
|
and yr = YEAR(now())
|
|
group by
|
|
km_target
|
|
)t1
|
|
left join
|
|
(
|
|
select
|
|
jd
|
|
,df
|
|
from ods_wuye_gszt
|
|
where df is not null
|
|
AND zq = '物业整体'
|
|
)t2
|
|
on t1.jd = t2.jd
|
|
UNION ALL
|
|
SELECT
|
|
current_date() AS CURRENT_DT -- 当前日期
|
|
,a.incident_ym AS YM -- 年月
|
|
,concat(right(a.incident_ym,2) + 0 , '月') AS MONTHS
|
|
,0 AS CURRENT_QUARTER -- 年月归属季度
|
|
,0 AS PLAN_NUM -- 计划总数
|
|
,0 AS PLAN_INCOMPLETE_NUM -- 未完成计划数量
|
|
,0 AS PLAN_COMPLETE_RATE -- 计划完成率
|
|
,0 AS KM_TARGET -- 客满目标得分
|
|
,0 AS KM_DF -- 客满得分
|
|
, sum(response_timely_incident_num) / sum(incident_num) AS RESPONSE_TIMELY_RATE -- 响应率
|
|
, sum(completed_incident_num - completed_not_close_incident_num) / sum(incident_num)
|
|
AS CLOSE_RATE -- 闭单率
|
|
,sum(delay_incident_num) / sum(incident_num)
|
|
AS DELAY_RATE -- 延期率
|
|
,sum(tousu_incident_num)
|
|
AS TOUSU_INCIDENT_NUM -- 投诉总量
|
|
,(sum(tousu_incident_num) / sum(room_num)) * 10000 AS TOUSU_PER_ROOM_RATE -- 每万户平均投诉量
|
|
FROM dws_estate_incident_info_m a
|
|
left join
|
|
(
|
|
select
|
|
CommID, count(*) room_num
|
|
from dim_room_d
|
|
group by CommID
|
|
)b
|
|
on a.comm_id = b.CommId
|
|
where left(a.incident_ym,4) = '2024'
|
|
group by a.incident_ym
|
|
-- order by a.incident_ym
|
|
;
|
|
|
|
|
|
SELECT
|
|
SUM(CURRENT_DT ) AS CURRENT_DT -- 当期日期
|
|
,SUM(INDEX_INCOME_FACT ) AS INDEX_INCOME_FACT -- 收入
|
|
,SUM(INCOME_COMPLETION_RATE ) AS INCOME_COMPLETION_RATE -- 收入完成率
|
|
,SUM(INDEX_COST_FACT ) AS INDEX_COST_FACT -- 成本
|
|
,SUM(COST_COMPLETION_RATE ) AS COST_COMPLETION_RATE -- 成本完成率
|
|
,SUM(INDEX_PROFIT_FACT ) AS INDEX_PROFIT_FACT -- 利润
|
|
,SUM(PROFIT_COMPLETION_RATE ) AS PROFIT_COMPLETION_RATE -- 利润完成率
|
|
,SUM(INDEX_MARKET_FACT ) AS INDEX_MARKET_FACT -- 市拓
|
|
,SUM(MARKET_COMPLETION_RATE ) AS MARKET_COMPLETION_RATE -- 市拓完成率
|
|
,SUM(TODAY_TOLL_FEE ) AS TODAY_TOLL_FEE -- 今日收费(单位:万)
|
|
,SUM(TYEAR_TOLL_FEE ) AS TYEAR_TOLL_FEE -- 年度累计收费(单位:万)
|
|
,SUM(MON_TOLL_FEE ) AS MON_TOLL_FEE -- 月度累计收费(单位:万)
|
|
,SUM(CU_COLLECTION_RATE ) AS CU_COLLECTION_RATE -- 当期收缴率
|
|
,SUM(PR_COLLECTION_RATE ) AS PR_COLLECTION_RATE -- 往期收缴率
|
|
,SUM(COLLECTION_RATE ) AS COLLECTION_RATE -- 综合收缴率
|
|
,SUM(PR_OWED_AMT ) AS PR_OWED_AMT -- 往期欠费金额(单位:万)
|
|
,SUM(CU_OWED_AMT ) AS CU_OWED_AMT -- 当期欠费金额(单位:万)
|
|
,SUM(ENERGY_CONSUMPTION_AMT ) AS ENERGY_CONSUMPTION_AMT -- 能耗收费
|
|
,SUM(DEVICE_GOOD_RATE ) AS DEVICE_GOOD_RATE -- 设备完好率
|
|
,SUM(DEVICE_SPACE_NORMAL_RATE ) AS DEVICE_SPACE_NORMAL_RATE -- 设备空间巡查完成率
|
|
,SUM(NEC ) AS NEC -- 在职人数
|
|
,SUM(FMR ) AS FMR -- 满编率
|
|
,SUM(FMR_TARGET ) AS FMR_TARGET -- 满编率目标值
|
|
,SUM(TR ) AS TR -- 离职率
|
|
,SUM(TR_TARGET ) AS TR_TARGET -- 离职率目标值
|
|
,SUM(AC_FEE_RATE ) AS AC_FEE_RATE -- 人力成本费率
|
|
,SUM(AC_FEE_RATE_TARGET ) AS AC_FEE_RATE_TARGET -- 人力成本费率目标值
|
|
,SUM(ASSESSMENT_OCCUPANY_RATE ) AS ASSESSMENT_OCCUPANY_RATE -- 空间运营出租率
|
|
,SUM(RETAIL_FINISH_RATE ) AS RETAIL_FINISH_RATE -- 新零售完成率
|
|
,SUM(RENT_RATE ) AS RENT_RATE -- 商业资产出租率
|
|
,SUM(RENT_COLLECTION_RATE ) AS RENT_COLLECTION_RATE -- 租金收缴率
|
|
,SUM(GUEST_ROOM_FEE ) AS GUEST_ROOM_FEE -- 客房单价(单位:元)
|
|
,SUM(GUEST_RENT_RATE ) AS GUEST_RENT_RATE -- 客房出租率
|
|
FROM dw.dws_president_base_index_d
|
|
WHERE CURRENT_DT = '2024-07-31'
|
|
|
|
|
|
|
|
|
|
|
|
-- 2_president_map_对外
|
|
|
|
SELECT
|
|
CURRENT_DT -- 当期日期
|
|
,PROVINCE -- 省份
|
|
,CITY -- 城市
|
|
,COUNTY -- 县
|
|
,TREATY_AREA -- 合约面积(万m²)
|
|
,MANAGE_AREA -- 在管面积(万m²)
|
|
,ROOM_NUM -- 总户数(户)
|
|
,COMM_NUM -- 在管项目数(个)
|
|
FROM dw.dws_president_map_d
|
|
WHERE CURRENT_DT = '2024-07-31'
|
|
|
|
|
|
|
|
sum(b_president_map_对外.select(COMM_NUM))
|
|
|
|
sum(b_president_map_对外.select(TREATY_AREA))
|
|
|
|
|
|
sum(b_president_map_对外.select(MANAGE_AREA))
|
|
|
|
|
|
sum(b_president_map_对外.select(ROOM_NUM))
|
|
|
|
|
|
|
|
-- c_president_chart_plan_对外
|
|
|
|
|
|
SELECT
|
|
YM
|
|
,PLAN_NUM AS PLAN_NUM -- 计划总数
|
|
,PLAN_INCOMPLETE_NUM PLAN_INCOMPLETE_NUM -- 未完成计划数量
|
|
,PLAN_COMPLETE_RATE AS PLAN_COMPLETE_RATE -- 计划完成率
|
|
FROM dw.dws_president_chart_d
|
|
WHERE CURRENT_DT = '${p_ym}'
|
|
AND length(YM) = 4
|
|
|
|
|
|
|
|
-- d_president_chart_km_对外
|
|
|
|
SELECT
|
|
CURRENT_DT -- 年
|
|
,CURRENT_QUARTER -- 年月归属季度
|
|
,KM_TARGET -- 客满目标得分
|
|
,KM_DF -- 客满得分
|
|
FROM dw.dws_president_chart_d
|
|
WHERE CURRENT_DT = '2024-08-02'
|
|
AND CURRENT_QUARTER LIKE '%季度%'
|
|
|
|
|
|
|
|
-- e_president_chart_complaints_对外
|
|
|
|
|
|
|
|
SELECT
|
|
CURRENT_DT -- 年
|
|
,ym -- 年月
|
|
,RESPONSE_TIMELY_RATE -- 响应率
|
|
,CLOSE_RATE -- 闭单率
|
|
,DELAY_RATE -- 延期率
|
|
,TOUSU_INCIDENT_NUM -- 投诉总量
|
|
,TOUSU_PER_ROOM_RATE -- 每万户平均投诉量
|
|
FROM dw.dws_president_chart_d
|
|
WHERE CURRENT_DT = '2024-08-02'
|
|
AND length(YM) = 6
|
|
|
|
|
|
|
|
|
|
|
|
|
|
SELECT
|
|
CURRENT_DT -- 年
|
|
,YM -- 年月
|
|
,CURRENT_QUARTER -- 年月归属季度
|
|
,PLAN_NUM -- 计划总数
|
|
,PLAN_INCOMPLETE_NUM -- 未完成计划数量
|
|
,PLAN_COMPLETE_RATE -- 计划完成率
|
|
,KM_TARGET -- 客满目标得分
|
|
,KM_DF -- 客满得分
|
|
,RESPONSE_TIMELY_RATE -- 响应率
|
|
,CLOSE_RATE -- 闭单率
|
|
,DELAY_RATE -- 延期率
|
|
,TOUSU_INCIDENT_NUM -- 投诉总量
|
|
,TOUSU_PER_ROOM_RATE -- 每万户平均投诉量
|
|
FROM dw.dws_president_chart_d
|
|
WHERE CURRENT_DT = '${p_ym}'
|
|
|
|
|
|
|
|
|
|
|
|
|
|
SELECT
|
|
T.CURRENT_QUARTER AS CURRENT_QUARTER
|
|
,T.KM_TARGET AS KM_TARGET
|
|
,T.KM_DF AS KM_DF
|
|
FROM
|
|
(
|
|
SELECT
|
|
|
|
CURRENT_QUARTER -- 年月归属季度
|
|
,KM_TARGET -- 客满目标得分
|
|
,KM_DF -- 客满得分
|
|
,CASE WHEN CURRENT_QUARTER = '一季度' THEN 1
|
|
WHEN CURRENT_QUARTER = '二季度' THEN 2
|
|
WHEN CURRENT_QUARTER = '三季度' THEN 3
|
|
WHEN CURRENT_QUARTER = '四季度' THEN 4
|
|
END AS RK
|
|
FROM dw.dws_president_chart_d
|
|
WHERE CURRENT_DT = '${p_ym}'
|
|
AND CURRENT_QUARTER LIKE '%季度%'
|
|
)T
|
|
ORDER BY T.RK
|
|
;
|
|
|
|
|
|
|
|
SELECT
|
|
CURRENT_DT -- 年
|
|
,CURRENT_QUARTER -- 年月归属季度
|
|
,KM_TARGET -- 客满目标得分
|
|
,KM_DF -- 客满得分
|
|
FROM dw.dws_president_chart_d
|
|
WHERE CURRENT_DT = '${p_ym}'
|
|
AND CURRENT_QUARTER LIKE '%季度%'
|
|
;
|
|
|
|
|
|
|
|
|
|
|
|
-- OR(A3 == "1月",A3 == "2月",A3 == "3月" ,A3 == "4月", A3 == "5月",A3 == "6月",A3 == "7月",A3 == "8月" ,A3 == "9月", A3 == "10月",A3 == "11月",A3 == "12月")
|
|
|
|
|
|
|
|
-- OR(A3 == "一季度",A3 == "二季度",A3 == "三季度" ,A3 == "四季度")
|
|
|
|
|
|
|
|
|
|
|
|
|
|
SELECT
|
|
|
|
INDEX_INCOME_FACT AS INDEX_INCOME_FACT -- 收入
|
|
,INCOME_COMPLETION_RATE AS INCOME_COMPLETION_RATE -- 收入完成率
|
|
,INDEX_COST_FACT AS INDEX_COST_FACT -- 成本
|
|
,COST_COMPLETION_RATE AS COST_COMPLETION_RATE -- 成本完成率
|
|
,INDEX_PROFIT_FACT AS INDEX_PROFIT_FACT -- 利润
|
|
,PROFIT_COMPLETION_RATE AS PROFIT_COMPLETION_RATE -- 利润完成率
|
|
,INDEX_MARKET_FACT AS INDEX_MARKET_FACT -- 市拓
|
|
,MARKET_COMPLETION_RATE AS MARKET_COMPLETION_RATE -- 市拓完成率
|
|
,TODAY_TOLL_FEE AS TODAY_TOLL_FEE -- 今日收费(单位:万)
|
|
,TYEAR_TOLL_FEE AS TYEAR_TOLL_FEE -- 年度累计收费(单位:万)
|
|
,MON_TOLL_FEE AS MON_TOLL_FEE -- 月度累计收费(单位:万)
|
|
,CU_COLLECTION_RATE AS CU_COLLECTION_RATE -- 当期收缴率
|
|
,PR_COLLECTION_RATE AS PR_COLLECTION_RATE -- 往期收缴率
|
|
,COLLECTION_RATE AS COLLECTION_RATE -- 综合收缴率
|
|
,PR_OWED_AMT AS PR_OWED_AMT -- 往期欠费金额(单位:万)
|
|
,CU_OWED_AMT AS CU_OWED_AMT -- 当期欠费金额(单位:万)
|
|
,ENERGY_CONSUMPTION_AMT AS ENERGY_CONSUMPTION_AMT -- 能耗收费
|
|
,DEVICE_GOOD_RATE AS DEVICE_GOOD_RATE -- 设备完好率
|
|
,DEVICE_SPACE_NORMAL_RATE AS DEVICE_SPACE_NORMAL_RATE -- 设备空间巡查完成率
|
|
,NEC AS NEC -- 在职人数
|
|
,FMR AS FMR -- 满编率
|
|
,FMR_TARGET AS FMR_TARGET -- 满编率目标值
|
|
,TR AS TR -- 离职率
|
|
,TR_TARGET AS TR_TARGET -- 离职率目标值
|
|
,AC_FEE_RATE AS AC_FEE_RATE -- 人力成本费率
|
|
,AC_FEE_RATE_TARGET AS AC_FEE_RATE_TARGET -- 人力成本费率目标值
|
|
,ASSESSMENT_OCCUPANY_RATE AS ASSESSMENT_OCCUPANY_RATE -- 空间运营出租率
|
|
,RETAIL_FINISH_RATE AS RETAIL_FINISH_RATE -- 新零售完成率
|
|
,RENT_RATE AS RENT_RATE -- 商业资产出租率
|
|
,RENT_COLLECTION_RATE AS RENT_COLLECTION_RATE -- 租金收缴率
|
|
,GUEST_ROOM_FEE AS GUEST_ROOM_FEE -- 客房单价(单位:元)
|
|
,GUEST_RENT_RATE AS GUEST_RENT_RATE -- 客房出租率
|
|
FROM dw.dws_president_base_index_d
|
|
;
|
|
|
|
|
|
|
|
SELECT
|
|
T.INDEX_INCOME_FACT AS INDEX_INCOME_FACT -- 收入
|
|
,T.INCOME_COMPLETION_RATE AS INCOME_COMPLETION_RATE -- 收入完成率
|
|
,T.INDEX_COST_FACT AS INDEX_COST_FACT -- 成本
|
|
,T.COST_COMPLETION_RATE AS COST_COMPLETION_RATE -- 成本完成率
|
|
,T.INDEX_PROFIT_FACT AS INDEX_PROFIT_FACT -- 利润
|
|
,T.PROFIT_COMPLETION_RATE AS PROFIT_COMPLETION_RATE -- 利润完成率
|
|
,T.INDEX_MARKET_FACT AS INDEX_MARKET_FACT -- 市拓
|
|
,T.MARKET_COMPLETION_RATE AS MARKET_COMPLETION_RATE -- 市拓完成率
|
|
,T.TODAY_TOLL_FEE AS TODAY_TOLL_FEE -- 今日收费(单位:万)
|
|
,T.TYEAR_TOLL_FEE AS TYEAR_TOLL_FEE -- 年度累计收费(单位:万)
|
|
,T.MON_TOLL_FEE AS MON_TOLL_FEE -- 月度累计收费(单位:万)
|
|
,T.CU_COLLECTION_RATE AS CU_COLLECTION_RATE -- 当期收缴率
|
|
,T.PR_COLLECTION_RATE AS PR_COLLECTION_RATE -- 往期收缴率
|
|
,T.COLLECTION_RATE AS COLLECTION_RATE -- 综合收缴率
|
|
,T.PR_OWED_AMT AS PR_OWED_AMT -- 往期欠费金额(单位:万)
|
|
,T.CU_OWED_AMT AS CU_OWED_AMT -- 当期欠费金额(单位:万)
|
|
,T.ENERGY_CONSUMPTION_AMT AS ENERGY_CONSUMPTION_AMT -- 能耗收费
|
|
,T.DEVICE_GOOD_RATE AS DEVICE_GOOD_RATE -- 设备完好率
|
|
,T.DEVICE_SPACE_NORMAL_RATE AS DEVICE_SPACE_NORMAL_RATE -- 设备空间巡查完成率
|
|
,T.NEC AS NEC -- 在职人数
|
|
,T.FMR AS FMR -- 满编率
|
|
,T.FMR_TARGET AS FMR_TARGET -- 满编率目标值
|
|
,T.TR AS TR -- 离职率
|
|
,T.TR_TARGET AS TR_TARGET -- 离职率目标值
|
|
,T.AC_FEE_RATE AS AC_FEE_RATE -- 人力成本费率
|
|
,T.AC_FEE_RATE_TARGET AS AC_FEE_RATE_TARGET -- 人力成本费率目标值
|
|
,T.ASSESSMENT_OCCUPANY_RATE AS ASSESSMENT_OCCUPANY_RATE -- 空间运营出租率
|
|
,T.RETAIL_FINISH_RATE AS RETAIL_FINISH_RATE -- 新零售完成率
|
|
,T.RENT_RATE AS RENT_RATE -- 商业资产出租率
|
|
,T.RENT_COLLECTION_RATE AS RENT_COLLECTION_RATE -- 租金收缴率
|
|
,T.GUEST_ROOM_FEE AS GUEST_ROOM_FEE -- 客房单价(单位:元)
|
|
,T.GUEST_RENT_RATE AS GUEST_RENT_RATE -- 客房出租率
|
|
FROM
|
|
(
|
|
SELECT
|
|
INDEX_INCOME_FACT AS INDEX_INCOME_FACT -- 收入
|
|
,INCOME_COMPLETION_RATE AS INCOME_COMPLETION_RATE -- 收入完成率
|
|
,INDEX_COST_FACT AS INDEX_COST_FACT -- 成本
|
|
,COST_COMPLETION_RATE AS COST_COMPLETION_RATE -- 成本完成率
|
|
,INDEX_PROFIT_FACT AS INDEX_PROFIT_FACT -- 利润
|
|
,PROFIT_COMPLETION_RATE AS PROFIT_COMPLETION_RATE -- 利润完成率
|
|
,INDEX_MARKET_FACT AS INDEX_MARKET_FACT -- 市拓
|
|
,MARKET_COMPLETION_RATE AS MARKET_COMPLETION_RATE -- 市拓完成率
|
|
,TODAY_TOLL_FEE AS TODAY_TOLL_FEE -- 今日收费(单位:万)
|
|
,TYEAR_TOLL_FEE AS TYEAR_TOLL_FEE -- 年度累计收费(单位:万)
|
|
,MON_TOLL_FEE AS MON_TOLL_FEE -- 月度累计收费(单位:万)
|
|
,CU_COLLECTION_RATE AS CU_COLLECTION_RATE -- 当期收缴率
|
|
,PR_COLLECTION_RATE AS PR_COLLECTION_RATE -- 往期收缴率
|
|
,COLLECTION_RATE AS COLLECTION_RATE -- 综合收缴率
|
|
,PR_OWED_AMT AS PR_OWED_AMT -- 往期欠费金额(单位:万)
|
|
,CU_OWED_AMT AS CU_OWED_AMT -- 当期欠费金额(单位:万)
|
|
,ENERGY_CONSUMPTION_AMT AS ENERGY_CONSUMPTION_AMT -- 能耗收费
|
|
,DEVICE_GOOD_RATE AS DEVICE_GOOD_RATE -- 设备完好率
|
|
,DEVICE_SPACE_NORMAL_RATE AS DEVICE_SPACE_NORMAL_RATE -- 设备空间巡查完成率
|
|
,NEC AS NEC -- 在职人数
|
|
,FMR AS FMR -- 满编率
|
|
,FMR_TARGET AS FMR_TARGET -- 满编率目标值
|
|
,TR AS TR -- 离职率
|
|
,TR_TARGET AS TR_TARGET -- 离职率目标值
|
|
,AC_FEE_RATE AS AC_FEE_RATE -- 人力成本费率
|
|
,AC_FEE_RATE_TARGET AS AC_FEE_RATE_TARGET -- 人力成本费率目标值
|
|
,ASSESSMENT_OCCUPANY_RATE AS ASSESSMENT_OCCUPANY_RATE -- 空间运营出租率
|
|
,RETAIL_FINISH_RATE AS RETAIL_FINISH_RATE -- 新零售完成率
|
|
,RENT_RATE AS RENT_RATE -- 商业资产出租率
|
|
,RENT_COLLECTION_RATE AS RENT_COLLECTION_RATE -- 租金收缴率
|
|
,GUEST_ROOM_FEE AS GUEST_ROOM_FEE -- 客房单价(单位:元)
|
|
,GUEST_RENT_RATE AS GUEST_RENT_RATE -- 客房出租率
|
|
,ROW_NUMBER() OVER (ORDER BY id DESC) AS rn
|
|
FROM dw.dws_president_base_index_d
|
|
)T
|
|
WHERE rn = 1 |