如何在Amazon QuickSight中用函数和筛选实现类似SQL LEFT JOIN的本月新用户统计？

需求背景 #

手里有一张带userID和LoginTime字段的表，要找出本月第一次登录的用户——也就是那些只在本月有登录记录，之前任何月份都没登录过的人。这逻辑就相当于用SQL做「本月登录用户」和「历史所有月份登录用户」的差集，或者左连接后筛出没匹配到历史记录的用户。

实现方法（用QuickSight的函数+筛选器） #

不用费劲写复杂的SQL，直接在QuickSight里自定义计算字段加筛选就能搞定，核心就是判断用户有没有历史登录记录：没有的话就是本月首次登录。

核心表达式（已翻译优化） #

not contains(collect(LoginTime, [userID]), unique(LoginTime between dateTrunc('YYYY', dateAdd('YYYY', -5, now())) and dateTrunc('MM', dateAdd('MM', -1, now()))))

表达式拆解 #

• dateTrunc('MM', dateAdd('MM', -1, now()))：拿到上个月月底的日期，也就是本月开始前的最后一天
• dateTrunc('YYYY', dateAdd('YYYY', -5, now()))：取5年前的年初，作为历史登录记录的起始时间（你可以根据自己的数据年限调整这个-5的数值）
• unique(LoginTime between ...)：筛选出这个用户在历史时间段里的所有唯一登录日期
• collect(LoginTime, [userID])：把当前用户的所有登录时间都收集起来
• not contains(...)：检查用户的所有登录记录里，有没有历史时间段的登录记录——要是没有，就说明这用户是本月第一次登录

操作步骤 #

• 打开你的QuickSight数据集，点击「添加计算字段」
• 把上面的表达式粘贴进去，给字段起个直观的名字比如「本月首次登录」
• 回到分析界面，把这个计算字段加为筛选器，选择筛选条件为「等于True」
• 现在数据集里剩下的就是本月首次登录的用户，直接做统计即可

内容的提问来源于stack exchange，提问作者Balualways