mysql - 使用 group by 来获取计数，同时具有其他唯一性条件

Question

假设我们有一个由以下列构成的表referrals:

(string) referral_id, (string) referrer, (string) user_id, (int) timestamp, (int) condition

我想要的是在任何给定时刻，条件 > 0的不同用户的数量，按推荐人分组。此外，如果用户有多个推荐人的条件 > 0，则仅应计算最近的推荐。因此，如果“推荐人 A”和“推荐人 B”都推荐了给定用户，但推荐人 B 的时间较新，则只有推荐人 B 才会将该用户添加到其计数中。

我知道为了按推荐人获得计数，我会这样做

SELECT referrer, COUNT(DISTINCT(user_id)) 
FROM referrals 
WHERE condition > 0 
GROUP BY referrer

我如何添加独特性和最近的推荐条件？

Answer 1

不要查询 referrals 表本身，而是使用 SQL select only rows with max value on a column 中的一种技术，使用子查询来获取每个用户的最新推荐。

select referrer, COUNT(DISTINCT user_id))
FROM (SELECT referrer, user_id
      FROM referrals AS r
      JOIN (SELECT user_id, MAX(timestamp) AS timestamp
            FROM referrals
            WHERE condition > 0
            GROUP BY user_id) AS maxr
      ON r.user_id = maxr.user_id AND r.timestamp = maxr.timestamp) AS x
GROUP BY referrer