题解 | #点击率排名与转化率排名差异#

考察内容：聚合函数、窗口函数、模糊查询、数据类型转换、排序

思路：

① 先计算22年8月各个用户对广告资源（资源ID以'ad'开头）的点击率排名与转化率排名：使用模糊查询筛选22年8月的广告 event_date like '2022-08%' and rid like 'ad%'，时间筛选也可以用 date_format(event_date, '%Y-%m') = '2022-08'，广告点击率=广告点击数/广告曝光数，广告转化率=广告转化数/广告点击数，使用窗口函数 rank 计算排名，round 保留3位小数

② 找出排名差异最大的前2个用户：由于广告点击率和转化率的差可能为负数，之前得到的 rk_ctr 和 rk_cvr 均为 unsigned 类型，需要将 rk_ctr 和 rk_cvr 转化为 signed 类型再相减计算绝对值

③ 按照排名差异 diff 降序、用户id降序排序，limit 取前两个

select
	uid,
	abs(cast(rk_ctr as signed)-cast(rk_cvr as signed)) as diff,
	ctr,
	cvr
from
(
	select 
		uid,
		ctr,
		cvr,
		rank() over(order by ctr desc, uid desc) rk_ctr,
		rank() over(order by cvr desc, uid desc) rk_cvr 
	from
	(
		select 
			uid,
			round(sum(is_click) / count(is_click), 3) as ctr,
			round(sum(is_convert) / sum(is_click), 3) as cvr
		from 
			user_res_event_log_tb
		where date_format(event_date, '%Y-%m') = '2022-08' and rid like 'ad%'
		group by uid
	) a 
) b
order by diff desc, uid desc
limit 2
;