SQL 查询分析：计算应用包的点击率并排除下载行为

SELECT a.package_name, COUNT(DISTINCT CASE WHEN a.action_type = 'itemClick' THEN a.userid END) AS click_count, -- 统计所有点击操作的用户数量 COUNT(DISTINCT CASE WHEN a.action_type = 'itemClick' AND NOT EXISTS (SELECT 1 FROM app WHERE userid = a.userid AND package_name = a.package_name AND action_type = 'download') THEN a.userid END) AS click_without_download_count, -- 统计没有下载行为的点击操作的用户数量 COUNT(DISTINCT CASE WHEN a.action_type = 'itemClick' AND NOT EXISTS (SELECT 1 FROM app WHERE userid = a.userid AND package_name = a.package_name AND action_type = 'download') THEN a.userid END) / COUNT(DISTINCT CASE WHEN a.action_type = 'itemClick' THEN a.userid END) AS ctr -- 计算没有下载行为的点击操作的比例 FROM app a GROUP BY a.package_name HAVING click_count > 0 ORDER BY ctr DESC LIMIT 100;