Mysql得到丢失用户的数量

Question

我有一个用户表(id, name, created_at)和一个事务表(id, user_id, created_at, amount)。对于每个月，我想知道在该月之前的3个月内没有任何交易的用户数量。例如，对于2022年4月，查询将返回在2022年1月、2022年2月和2022年3月没有交易的用户数量。依此类推。

我可以做到这一点与一个单一的MySQL查询，没有PHP循环?

如果我只想在2022年4月使用它，那么我想这就可以了:

SELECT count(distinct(users.id)) FROM users 
INNER JOIN transactions 
on users.id = transactions.user_id 
WHERE transactions.user_id NOT IN 
(SELECT user_id FROM transactions WHERE created_at > "2022-01-01" AND created_at < "2022-04-01" );

如何获得所有月份的?

Answer 1

在正常情况下，您将有一个日历表，例如，它存储一段时间内所有月份的开始，如calendar(start_of_month)。

从这里开始，您可以使用users表对日历进行cross join，以生成月份和客户的所有可能组合(相对于用户的创建时间)。最后一步是检查每个user/month元组中最近3个月内的事务。

select c.start_of_month, count(*) as cnt_inactive_users
from calendar c
cross join users u 
where not exists (
select 1 
from transactions t 
where t.user_id = u.id 
and t.created_at >= c.start_of_month - interval 3 month
and t.created_at <  c.start_of_month
)
where c.start_of_month >= '2021-01-01' and c.start_of_month < '2022-01-01'
group by c.start_of_month
order by c.start_of_month

这将为您提供每月至少有一个"非活动"的行。客户，用相应的计数。

使用where子句控制查询应用的月份范围(例如，上面给出了整个2021年)。

Answer 2

SELECT count(*) 
FROM users 
WHERE NOT EXISTS (
SELECT NULL
FROM transactions 
WHERE users.id = transactions.user_id 
AND created_at > '2022-01-01' AND created_at < '2022-04-01'
);