SQL 查询语句：计算应用曝光量、点击量和点击率

-- 查询语句开始 SELECT m.category, -- 选择mapping_table表中的category字段 a.package_name, -- 选择app表中的package_name字段 COUNT(DISTINCT CASE WHEN a.action_type = 'itemShow' THEN a.userid END) AS exposure_count, -- 统计app表中action_type为itemShow的用户数量，作为曝光量 COUNT(DISTINCT CASE WHEN a.action_type = 'itemClick' THEN a.userid END) AS click_count, -- 统计app表中action_type为itemClick的用户数量，作为点击量 COUNT(DISTINCT CASE WHEN a.action_type = 'itemClick' THEN a.userid END) / COUNT(DISTINCT CASE WHEN a.action_type = 'itemShow' THEN a.userid END) AS ctr -- 计算点击率，即点击量除以曝光量 FROM app a JOIN mapping_table m ON a.package_name = m.package_name -- 使用JOIN连接app表和mapping_table表，连接字段是package_name GROUP BY m.category, a.package_name -- 按照category和package_name进行分组 HAVING exposure_count > 100 -- 选择曝光量大于100的数据 ORDER BY m.category, ctr DESC -- 按照category升序排列，ctr降序排列 -- 查询语句结束