SQL查询：每个品类曝光点击率最高的App (曝光量大于100)

SELECT a.category, a.app_name, ROUND(b.click_cnt/b.exposure_cnt, 4) AS ctr FROM ( SELECT category, app_name, SUM(exposure_cnt) AS exposure_cnt FROM logs GROUP BY category, app_name HAVING exposure_cnt > 100 ) a JOIN ( SELECT category, app_name, SUM(click_cnt) AS click_cnt, SUM(exposure_cnt) AS exposure_cnt FROM logs GROUP BY category, app_name HAVING exposure_cnt > 100 ) b ON a.category = b.category AND a.app_name = b.app_name LEFT JOIN ( SELECT category, MAX(click_cnt/exposure_cnt) AS max_ctr FROM ( SELECT category, app_name, SUM(click_cnt) AS click_cnt, SUM(exposure_cnt) AS exposure_cnt FROM logs GROUP BY category, app_name HAVING exposure_cnt > 100 ) GROUP BY category ) c ON a.category = c.category WHERE b.click_cnt/b.exposure_cnt = c.max_ctr ORDER BY a.category, ctr DESC, a.app_name;