mysql - 选择在两天内有操作的用户，其中一天在上周内

Question

给定一个看起来像这样的 event_stream 表

*----*--------*---------*-------------*
| id | action | user_id | occurred_on |
*----*--------*---------*-------------*

和一个看起来像这样(简化)的users表

*----*-------*
| id | email | 
*----*-------*

我想选择在 events 表中至少有两个事件发生在不同日期的所有用户。此外，其中一项事件需要在过去 7 天内发生。

这是我目前所拥有的:

SELECT x.`email` 
  FROM (
      SELECT u.`email`, CAST(e.`occurred_on` AS DATE) d
        FROM `users` u
        JOIN `event_stream` e
             ON e.`user_id` = u.`id`
    GROUP BY CAST(e.`occurred_on` AS DATE), u.`email`
  ) x
GROUP BY x.`email`
  HAVING COUNT(*) > 1

这应该为我提供至少在两个不同的日子登录过事件的所有用户。现在我不太确定如何添加这些事件的其他约束，其中一个必须在过去 7 天内发生。

Answer 1

按 user_id 分组并有条件地计数:

select * 
from users
where id in
(
  select user_id
  from event_stream
  group by user_id
  having count(distinct date(occurred_on)) > 1 -- at least on two different days
     and sum(occurred_on >= current_date - interval 7 day) > 0 -- one in last seven days
);

在 MySQL 中 true = 1，false = 0，因此我们可以对 bool 表达式求和以计算它匹配的频率。

如果 occurred_on 已经是顾名思义的日期，而不是日期时间或时间戳，您可以将 count(distinct date(occurred_on)) 更改为 count (不同的 occurred_on)。