SELECT apackage_name COUNTDISTINCT auserid AS click_count COUNTDISTINCT CASE WHEN duserid IS NULL THEN auserid END AS click_without_download_count COUNTDISTINCT CASE WHEN duserid IS NULL THEN auserid

SELECT a.package_name, COUNT(DISTINCT a.userid) AS click_count, COUNT(DISTINCT CASE WHEN d.userid IS NULL THEN a.userid END) AS click_without_download_count, COUNT(DISTINCT CASE WHEN d.userid IS NULL THEN a.userid END) / COUNT(DISTINCT a.userid) AS ctr -- 从app表中选择应用包名称，以及计算点击次数、未下载的点击次数和点击率。

FROM app a -- 从app表中选择数据。

LEFT JOIN ( -- 左连接下载表。

SELECT userid, package_name FROM app WHERE action_type = 'download' -- 从app表中选择下载的用户ID和应用包名称。

) d ON a.userid = d.userid AND a.package_name = d.package_name -- 连接条件是用户ID和应用包名称必须相同。

WHERE a.action_type = 'itemClick' -- 选择动作类型为“itemClick”的数据。

GROUP BY a.package_name -- 按应用包名称分组。

HAVING click_count > 0 -- 过滤掉点击次数为0的数据。

ORDER BY ctr DESC -- 按点击率倒序排列。

LIMIT 100 -- 只选择前100条数据。