php - Mysql查询上周非活跃用户

Question

我有一个表action_info，每个用户都单击保存在其中。我需要获取上周不活动用户的计数。以下是 ACTIVE 用户的查询:

SELECT COUNT( DISTINCT user_id ) as active_for_week FROM action_info 
     WHERE action_name = 'attack' 
     AND time >= (CURDATE( ) - INTERVAL 7 DAY)

Answer 1

这将从用户表中排除上周所有活跃的用户，因此只提供不活跃的用户。我希望这就是您正在寻找的内容。

SELECT COUNT(*) FROM users WHERE ID NOT IN(
    SELECT DISTINCT user_id FROM action_info 
        WHERE action_name = 'attack' 
        AND time >= (CURDATE( ) - INTERVAL 7 DAY)
) 

更新:

无需查询用户表，您可以执行以下操作:

SELECT COUNT(DISTINCT user_id) AS not_active_last_week FROM action_info 
WHERE action_name = 'attack' AND user_id NOT IN ( 
    SELECT DISTINCT user_id FROM action_info 
    WHERE action_name = 'attack' 
    AND time >= (CURDATE( ) - INTERVAL 7 DAY) 
)

它为您提供在某个时刻(但不是在上周)活跃的所有用户的数量。这是SQL fiddle