计算应用程序点击率 (CTR) 的 SQL 查询

以下是一个 SELECT 语句，用于计算每个应用程序的点击率 (CTR) 并按降序排列。在这个查询中，我们使用了三个聚合函数 COUNT 和一个 CASE 语句来计算点击次数、没有下载的点击次数以及 CTR。我们还使用了一个子查询来检查是否存在下载操作。最后，我们按 CTR 降序对结果进行排序，并限制结果集为前 100 个应用程序。

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;

解释:

SELECT a.package_name：选择应用程序的名称。
COUNT(DISTINCT CASE WHEN a.action_type = 'itemClick' THEN a.userid END) AS click_count：计算点击次数。仅当 action_type 为 'itemClick' 时，才会计算用户 ID。
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：计算没有下载的点击次数。仅当 action_type 为 'itemClick' 且没有相应的下载记录时，才会计算用户 ID。
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：计算 CTR。这是没有下载的点击次数除以点击次数。
FROM app a：从名为 'app' 的表中选择数据。
GROUP BY a.package_name：按应用程序名称对结果进行分组。
HAVING click_count > 0：仅当点击次数大于 0 时，才会包含应用程序。
ORDER BY ctr DESC：按 CTR 降序对结果进行排序。
LIMIT 100：仅返回前 100 个应用程序。

此查询提供了一种计算应用程序 CTR 的有效方法，可以帮助您确定哪些应用程序表现良好，哪些应用程序需要改进。