fact_broker_score_detail_monthly表结构
CREATE TABLE IF NOT EXISTS dws.fact_broker_score_detail_monthly
(
month DATE NOT NULL COMMENT '月份',
employee_id CHAR(36) NOT NULL COMMENT '内网人员标识',
broker_id CHAR(36) NULL COMMENT '平台经纪人标识',
entry_years DECIMAL(19, 2) NULL COMMENT '服务年限',
entry_years_score INT NULL COMMENT '服务年限积分',
sale_requirements_look INT NULL COMMENT '二手带看量',
agent_requirements_look INT NULL COMMENT '新房带看量',
rent_requirements_look INT NULL COMMENT '租房带看量',
look_score INT NULL COMMENT '带看积分',
look_score_rank INT NULL COMMENT '带看排行',
look_score_rank_total INT NULL COMMENT '带看排行总数（带看积分>0）',
look_score_rank_ratio DECIMAL(19, 4) NULL COMMENT '带看排行比例',
look_rank_score INT NULL COMMENT '带看排行积分',
look_score_total INT NULL COMMENT '带看总积分',
convert INT NULL COMMENT '单边委托量',
sale_contract_count INT NULL COMMENT '二手成交量',
rent_contract_count INT NULL COMMENT '租房成交量',
agent_contract_count INT NULL COMMENT '新房成交量',
trust_contract_count INT NULL COMMENT '托管成交量',
contract_score INT NULL COMMENT '成交积分',
contract_score_rank INT NULL COMMENT '成交积分排行',
contract_score_rank_total INT NULL COMMENT '成交积分排行总数（成交积分>0）',
contract_score_rank_ratio DECIMAL(19, 4) NULL COMMENT '成交积分排行比例',
contract_rank_score INT NULL COMMENT '成交积分排行积分',
contract_score_total INT NULL COMMENT '成交总积分',
share_count INT NULL COMMENT '推广量',
share_score DECIMAL(19, 2) NULL COMMENT '推广积分',
share_score_rank INT NULL COMMENT '推广积分排行',
share_score_rank_total INT NULL COMMENT '推广积分排行总数（推广积分>0）',
share_score_rank_ratio DECIMAL(19, 4) NULL COMMENT '推广积分排行比例',
share_rank_score INT NULL COMMENT '推广积分排行积分',
share_score_total DECIMAL(19, 2) NULL COMMENT '推广总分',
reply_customer_count_3m INT NULL COMMENT 'IM 3分钟回复次数',
im_num INT NULL COMMENT 'IM会话总量',
im_ratio DECIMAL(19, 4) NULL COMMENT 'IM 1分钟回复率',
im_score INT NULL COMMENT 'IM积分',
im_score_rank INT NULL COMMENT 'IM积分排行',
im_score_rank_total INT NULL COMMENT 'IM积分排行总数（IM积分>0）',
im_score_rank_ratio DECIMAL(19, 4) NULL COMMENT 'IM积分排行比例',
im_rank_score INT NULL COMMENT 'IM积分排行积分',
im_score_total INT NULL COMMENT 'IM总积分',
score_total DECIMAL(19, 2) NULL COMMENT '积分总分'
)UNIQUE KEY (month,employee_id)
COMMENT "经纪人每月积分明细"
DISTRIBUTED BY HASH(month) BUCKETS 1
PROPERTIES (
replication_num = ${replication_num}
);
-- 查询语句
DROP VIEW dws.view_broker_score_monthly2;
CREATE VIEW dws.view_broker_score_monthly2
AS
WITH fact_broker_score AS (
SELECT dcm.month,
fbsdm.employee_id,
fbsdm.broker_id,
fbsdm.month AS detail_month,
CASE
WHEN fbsdm.month = DATE_SUB(dcm.month, INTERVAL 1 MONTH)
THEN fbsdm.score_total * 0.5
WHEN fbsdm.month >= DATE_SUB(dcm.month, INTERVAL 6 MONTH)
AND fbsdm.month < DATE_SUB(dcm.month, INTERVAL 1 MONTH)
THEN fbsdm.score_total * 0.1
ELSE 0 END AS score_total
FROM dim.dim_calendar_month dcm
LEFT JOIN dws.fact_broker_score_detail_monthly2 fbsdm
ON fbsdm.month >= DATE_SUB(dcm.month, INTERVAL 6 MONTH)
AND fbsdm.month < dcm.month
WHERE dcm.month = DATE_FORMAT(CURDATE(), '%Y-%m-01')
)
SELECT f.month,
f.employee_id,
f.broker_id,
SUM(score_total) AS score
FROM fact_broker_score f
GROUP BY f.month, f.employee_id, f.broker_id;
SELECT x.* FROM dws.view_broker_score_monthly2 x
