SELECT agent.corp_name AS `品牌名称`, agent.shop_name AS `门店名称`, ca.shop_ca_name AS `对应CA`, agent.agent_code AS `经纪人系统号`, agent.agent_name AS `经纪人姓名`
, nvl(perfnum.perf_num, 0) AS `二手单量`
, nvl(perf.performance, 0) AS `二手业绩`
, nvl(perf_n.perf_n_num, 0) AS `新房单量`
, nvl(perf_n.performance, 0) AS `新房业绩`
, nvl(nvl(perfnum.perf_num, 0) + nvl(perf_n.perf_n_num, 0), 0) AS `合计单量`
, nvl(nvl(perf.performance, 0) + nvl(perf_n.performance, 0), 0) AS `合计业绩`
, house.hold_house AS `维护房源数量`, house.prospect AS `实勘量`, house.comment AS `房评量`, im.IM_nub AS `im进线量`, im.IM_min AS `IM一分钟量`
FROM (
SELECT agent_ucid, agent_code, agent_name, corp_code, corp_name
, shop_code, shop_name, on_job_days, is_zero_showing_cnt_lst7d, is_zero_showing_cnt_lst30d
FROM dwd.dwd_coo_***
WHERE pt = '20200505000000'
AND on_job_status_code = 170007002
AND brand_code IN (990004005, 990004002)
) agent
LEFT JOIN (
SELECT role_ucid, SUM(assign_amt) AS performance
FROM olap.olap_****
WHERE pt = '20200505000000'
AND catagory_name = '应收业绩'
AND settle_month = substr('20200505000000', 1, 6)
AND is_valid = 1
GROUP BY role_ucid
) perf
ON agent.agent_ucid = perf.role_ucid
LEFT JOIN (
SELECT role_ucid, SUM(assign_amt) AS performance, COUNT(DISTINCT agreement_id) AS perf_n_num
FROM olap.olap_nh_hr_performance_****
WHERE pt = '20200505000000'
AND role_type_name = '主成交经纪人'
AND substr(create_time, 1, 7) = substr('20200505000000', 1, 6)
AND assign_amt > 0
GROUP BY role_ucid
) perf_n
ON agent.agent_ucid = perf_n.role_ucid
LEFT JOIN (
SELECT sign_ucid, SUM(CASE
WHEN del_type = '买卖' THEN 1
ELSE 0
END) AS perf_num
FROM rpt.rpt_coo_****
WHERE pt = '20200505000000'
AND substr(sign_date, 1, 7) = substr('20200505000000', 1, 6)
AND status <> '合同-解约'
GROUP BY sign_ucid
) perfnum
ON perfnum.sign_ucid = agent.agent_ucid
LEFT JOIN (
SELECT sub_brand_name, shop_code, shop_name, area_name, shop_ca_name
, shop_agent_cnt
FROM olap.olap_****
WHERE pt = '20200505000000'
AND length(shop_ca_name) <> 0
AND is_valid = 1
AND length(shop_first_del_time) <> 0
) ca
ON ca.shop_code = agent.shop_code
LEFT JOIN (
SELECT hold_ucid, COUNT(city_code) AS hold_house, COUNT(CASE
WHEN is_prospecting_housedel = '是' THEN '1'
ELSE NULL
END) AS prospect
, COUNT(CASE
WHEN is_housedel_comment = '是' THEN '1'
ELSE NULL
END) AS comment
FROM rpt.rpt_****
WHERE pt = '20200505000000'
AND del_type = '买卖'
GROUP BY 1
) house
ON house.hold_ucid = agent.agent_ucid
LEFT JOIN (
SELECT employee_no, COUNT(city_code) AS IM_nub, COUNT(CASE
WHEN is_60s_response = '是' THEN 1
ELSE NULL
END) AS IM_min
FROM rpt.rpt_comp****
WHERE pt <= '20200505000000'
AND pt >= concat(substr('20200505000000', 1, 6), '01000000')
AND if_in_nine_to_twentytwo = '是'
AND is_closed = '否'
GROUP BY 1
) im
ON im.employee_no = agent.agent_code
LEFT JOIN (
SELECT employee_no, COUNT(city_code) AS tel_nub, COUNT(CASE
WHEN is_succeed = '是' THEN 1
ELSE NULL
END) AS tel_on
FROM rpt.rpt_rpt_****
WHERE pt <= '${-1d_pt}'
AND pt >= concat(substr('${-1d_pt}', 1, 6), '01000000')
AND is_connect = '是'
AND is_closed = '否'
AND if_in_nine_to_twentytwo = '是'
GROUP BY 1
) tel
ON tel.employee_no = agent.agent_code
ORDER BY `合计业绩` DESC
【SQL实例】写一段业绩的SQL,HIVE端
最新推荐文章于 2023-01-10 23:57:30 发布