我编写了两个 mysql 查询,一个获取一年中特定月份的总用户(注册),另一个获取一年中特定月份的活跃用户。我需要找到数量inactive当年的用户。为此,我正在考虑减去通过两个单独的查询获得的总用户数和活动用户列。
以下是查询
1. Fetch Total Registered users
set @numberOfUsers := 0;
SELECT T.createdMonth, T.monthlyusers, (@numberOfUsers := @numberOfUsers + T.monthlyusers) as totalUsers
FROM
(
SELECT month(from_unixtime(u.createdDate)) as createdMonth, count(u.id) as monthlyusers
FROM user u
where year(from_unixtime(u.createdDate)) = '2016'
group by month(from_unixtime(u.createdDate))
) T;
2. Fetch Active Users
select month(from_unixtime(lastActive)), (count(u.id)) as activeUsers from user u
where year(from_unixtime(lastActive)) = '2016'
group by month(from_unixtime(lastActive));
我需要从totalUsers 中减去activeUsers。我该如何实现这一目标?
您可以简单地减去两个查询或合并
select (select query) - (select query);
SET @numberOfUsers := 0;
SELECT l.totalUsers-a.monthlyusers FROM (SELECT T.createdMonth,
T.monthlyusers, (@numberOfUsers := @numberOfUsers + T.monthlyusers) AS totalUsers
FROM
(
SELECT MONTH(FROM_UNIXTIME(u.createdDate)) AS createdMonth, COUNT(u.id) AS monthlyusers
FROM USER u
WHERE YEAR(FROM_UNIXTIME(u.createdDate)) = '2016'
GROUP BY MONTH(FROM_UNIXTIME(u.createdDate))
) T ) l,(SELECT MONTH(FROM_UNIXTIME(lastActive)), (COUNT(u.id)) AS activeUsers FROM USER u
WHERE YEAR(FROM_UNIXTIME(lastActive)) = '2016'
GROUP BY MONTH(FROM_UNIXTIME(lastActive))) a ;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)