查询以获取 DAU 并使用自加入进行安装

Question

我一直在努力获取DAU数量和每天的安装数量，这些数据按一些用户数据(如平台，OS_version，国家和其他一些列(细分。 这个想法是让查询返回如下内容：

date |DAU|Installs|Platform|OS_ver|country| 2017-05-01 | 2 | 0 |Android |5.0 | US | 2017-05-01 | 1 | 1 |Android |6.0 | US | 2017-05-01 | 1 | 1 |Android |6.0 | JP | 2017-05-01 | 3 | 0 |Android |6.0 | MX | 2017-05-01 | 10| 0 |Android |5.0 | MX |

如您所见，DAU 和安装将按这些(和其他一些(列分布。非常简单的分析查询。

所有信息都位于同一个表中，因此数据需要由表本身进行操作和连接。

一个不会显示信息的列是事件列，其中包含安装和"屏幕"事件(每次用户打开游戏中的任何屏幕时都会调用屏幕事件，即使在登录后也是如此，因此"屏幕"事件将用于计算 DAU(。

我最初的想法是创建两个 CTE，每个 CTE 只有按事件过滤的记录，一个按安装事件过滤，另一个按屏幕事件过滤，然后我会提取日期的日期部分(在 unix 时间戳中(并在每个 CTE 中创建另一列，install_day计算安装 CTE 上的安装量， 并activity_day DAU CTE 计算 #DAU。

创建这两个 CTE 后，我会使用平台加入它们，条件如下：dau_cte.platform = install_cte.platform。

我已经尝试以几种不同的方式创建这样的查询，但使用上述相同的逻辑，但每次都得到一堆重复的数据。因此，与其显示我的查询代码来帮助修复它，不如从社区中听到你们将使用什么方法来获得这些结果。

顺便说一句，此查询将在普雷斯托运行... 提前感谢！

Answer 1

查询必须相当简单。最终查询应按date、country、platform和os_ver进行分组。据我了解，两个感兴趣的指标是某些唯一用户(或事件(标识符的不同计数。根据 presto 文档，没有COUNT_DISTINCT(X if Y)类型的聚合，正如您提到的，它只剩下在子查询中执行不同的计数。下面的查询将做到这一点。请注意COALESCE的使用情况，以解决丢失的数据。

SELECT
a.date,
a.platform,
a.os_ver,
a.country,
COALESCE(a.DAU, 0) as DAU,
COALESCE(b.installs, 0) as installs,
FROM (
SELECT
date,
platform,
os_ver,
country,
COUNT(DISTINCT <user_id>) AS DAU
FROM <table_name>
WHERE event_name = 'screen'
GROUP BY 1,2,3,4 
) a
FULL JOIN (
SELECT
date,
platform,
os_ver,
country,
COUNT(DISTINCT <user_id>) AS installs
FROM <table_name>
WHERE event_name = 'install'
GROUP BY 1,2,3,4
) b
ON
a.date = b.date
AND a.platform = b.platform
AND a.os_ver = b.os_ver
AND a.country = b.country