MYSQL last login and number of logins in last 3 mo

问题:

I'm trying to write a query to join a user table to an activity logging table and return the following for each user:

A) The time they last logged in.

B) The number of logins in the last 3 months.

This is what I have come up with so far:

SELECT A.UserID, COUNT( Activity ) AS Logins, MAX( TIME ) AS LastLogin  
FROM UserMaster A  
LEFT JOIN UserWebActivity B ON A.UserID = B.UserID  
AND Activity =  'Login'  
AND TIME BETWEEN DATE_SUB( NOW( ) , INTERVAL 3 MONTH ) AND NOW( )  
GROUP BY A.UserID  

This almost works, but it doesn't return the latest login for any user that hasn't logged in within the last 3 months. How can I get count() and max() to work together properly?

回答1:

First solve each problem separately:

SELECT A.UserID, MAX(TIME) AS LastLogin  
FROM UserMaster A  
LEFT JOIN UserWebActivity B
ON A.UserID = B.UserID  
AND Activity = 'Login'  
GROUP BY A.UserID

SELECT A.UserID, COUNT(Activity) AS Logins
FROM UserMaster A  
LEFT JOIN UserWebActivity B ON A.UserID = B.UserID  
AND Activity = 'Login'  
AND TIME BETWEEN (NOW() - INTERVAL 3 MONTH) AND NOW( )  
GROUP BY A.UserID

Test them separately to ensure that each of these queries works as you want, and adjust them if necessary.

Then when you are happy that they both work, join the results together:

SELECT T1.UserID, T1.LastLogin, T2.Logins
FROM
(
    SELECT A.UserID, MAX(TIME) AS LastLogin  
    FROM UserMaster A  
    LEFT JOIN UserWebActivity B
    ON A.UserID = B.UserID  
    AND Activity = 'Login'  
    GROUP BY A.UserID
) AS T1
JOIN
(
    SELECT A.UserID, COUNT(Activity) AS Logins
    FROM UserMaster A  
    LEFT JOIN UserWebActivity B
    ON A.UserID = B.UserID  
    AND Activity = 'Login'
    AND TIME BETWEEN (NOW() - INTERVAL 3 MONTH) AND NOW()
    GROUP BY A.UserID
) AS T2
ON T1.UserID = T2.UserID

This will allow MySQL to make best use of the indexes for the different queries.

回答2:

You can use a CASE statement in your COUNT:

SELECT A.UserID,
  COUNT(
    CASE WHEN TIME BETWEEN DATE_SUB( NOW( ) , INTERVAL 3 MONTH ) AND NOW( )
    THEN Activity
  END ) AS Logins,
  MAX( TIME ) AS LastLogin  
FROM UserMaster A  
LEFT JOIN UserWebActivity B ON A.UserID = B.UserID  
AND Activity =  'Login'  
GROUP BY A.UserID

This solution however will be less efficient than @MarkByers if you have a large amount of data.

回答3:

Just as one more crazy idea - instead of MAX( TIME )

coalesce(max(time), (select max(time) from UserWebActivity where ...) )