SQL 查询优化：计算应用点击率和无下载点击率

这两个查询的功能不完全一样。第一个查询中，只包括了action_type为'itemClick'的记录，并且对于同一用户在同一应用包中的多次点击，只计算一次点击。同时，还计算了在没有下载行为的情况下的点击次数和CTR值。第二个查询中，包括了所有记录，并且没有去除重复的点击记录，同时也没有计算没有下载行为的点击次数和CTR值。因此，这两个查询在功能上略有差异。

第一个查询的 SQL 语句为：

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
FROM app a
LEFT JOIN (
     SELECT userid, package_name FROM app WHERE action_type = 'download'
 ) d ON a.userid = d.userid AND a.package_name = d.package_name
WHERE a.action_type = 'itemClick'
GROUP BY a.package_name
HAVING click_count > 0
ORDER BY ctr DESC
LIMIT 100

第二个查询的 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