【SQL实例】写一段业绩的SQL，HIVE端

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