SQL自连接多条件逻辑解析：识别复购活跃用户的两大核心疑问

背景说明 #

需要编写SQL查询识别复购活跃用户：这类用户存在任意一笔订单，且在该订单的7天内完成了第二笔订单，最终输出符合条件的user_id列表。

数据表结构：

• id(int)：唯一订单ID
• user_id(int)：用户ID
• item(varchar)：商品名称
• created_at(datetime)：订单创建时间
• revenue(int)：订单收入

参考SQL语句：

SELECT DISTINCT(a1.user_id)
FROM amazon_transactions a1
JOIN amazon_transactions a2 ON a1.user_id=a2.user_id
    AND a1.id <> a2.id
    AND a2.created_at::date - a1.created_at::date BETWEEN 0 AND 7
ORDER BY a1.user_id

疑问解答 #

1. 为什么要把表和自身做连接？ #

我们的目标是找同一个用户的两笔不同订单，且这两笔订单的时间间隔不超过7天。自连接的核心作用是把同一张表拆成两个逻辑副本：a1可以看作用户的某一笔订单，a2则是该用户的另一笔订单。通过这种方式，我们能在同一条查询结果里同时拿到这两笔订单的数据，直接对比它们的创建时间是否符合要求。如果不用自连接，单表查询根本没法直接关联同一个用户的不同订单来做时间差对比。

2. AND a1.id <> a2.id这部分有什么用？ #

这条条件是用来排除订单和自身匹配的无效情况。如果只限定a1.user_id=a2.user_id，自连接时会出现a1和a2指向同一笔订单的情况（也就是同一个id）——这时候计算出来的时间差肯定是0，会错误地把只有一笔订单的用户判定为复购活跃用户。加上a1.id <> a2.id后，就能确保我们对比的是同一个用户的两笔完全不同的订单，过滤掉这种无意义的自匹配。

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