presto sql 求占比--开窗函数解法

需求:资金端capital_no有三个,求出每一个的放款笔数占比和放款金额占比

解决:

使用count( ) over(partition by ) 和sum( ) over(partition by) 进行分组统计 , 最后使用round函数求值。

-- success_time在统计日期当日 
SELECT
	dt -- 统计日期,
	capital_no -- 资金端,
	order_num -- 放款笔数,
	order_num_count -- 总放款笔数,
	round(
		order_num * 1.0000 / order_num_count * 100,
		2
	) AS bishu_zhanbi -- 已取 %,
	amount -- 放款金额,
	amount_sum -- 总放款金额,
	round(
		amount * 1.0000 / amount_sum * 100,
		2
	) AS jine_zhanbi -- 已取 %%,
	'success_time在统计日期当日' mark
FROM
	(
		-- success_time在统计日期当日 SELECT
		b.capital_no -- 资金端,
		'2021-07-13' dt,
		count(a.loan_id) over (
			PARTITION BY b.capital_no,
			etl_date
		) AS order_num -- 放款笔数,
		sum(b.amount) over (
			PARTITION BY b.capital_no,
			etl_date
		) AS amount -- 放款金额,
		count(a.loan_id) over (PARTITION BY etl_date) AS order_num_count -- 放款笔数,
		sum(b.amount) over (PARTITION BY etl_date) AS amount_sum -- 放款金额,
		row_number () over (
			PARTITION BY b.capital_no,
			etl_date
		) AS rk
	FROM
		(
			SELECT
				loan_id,
				loan_amount,
				success_time,
				etl_date
			FROM
				dp_ods.o_hw_bu_hw_makeloan_record_s
			WHERE
				etl_date = date('2021-07-12')
			AND loan_sts = 1
			AND date(success_time) = date('2021-07-12')
		) a
	LEFT JOIN (
		SELECT
			order_id,
			org_name,
			loan_amount / 100 AS amount -- 元,
			capital_no
		FROM
			dp_ods.o_hw_bu_hw_user_order_s
		WHERE
			etl_date = date('2021-07-12')
	) b ON a.loan_id = b.order_id
	) a
WHERE
	rk = 1

注:

开窗函数的排序,.必须得使用,最后where rk =1!