Mysql:比较和过滤时间条目

Question

我有一个包含用户ID和登录日期的表格。

id | customer | timestamp  
1  | 1        | 2017-02-10 11:30:28  
2  | 1        | 2017-02-11 11:30:28  
3  | 2        | 2017-02-12 11:30:28  
4  | 3        | 2017-02-13 11:30:28  
5  | 1        | 2017-02-14 11:30:28 

现在，我想获取每个客户最长的连续登录次数。

我到了重点，为一个客户正确计算了差额。

SELECT  a.id aId,
    b.id bId,
    a.customer,
    a.timestamp aTime,
    b.timestamp bTime,
    DATEDIFF(b.timestamp, a.timestamp) as diff
FROM logins a
INNER JOIN logins b
    ON a.customer = b.customer AND a.id < b.id
WHERE b.customer = 7
GROUP BY a.id

如何对整个表执行此操作并计算以下相差不到 1 天的登录次数？

此示例所需的结果应为：

customer | days of continuous login  
1        | 2  
2        | 1  
3        | 1

Answer 1

您可以使用

LEFT JOIN 执行此操作

查询

SELECT 
   logins.customer
 , COUNT(*) as "longest continuous streak of logins"
FROM ( 
SELECT 
  login1.*
FROM 
 login login1 
LEFT JOIN 
 login login2
ON
 login1.timestamp < login2.timestamp
AND
 # Only JOIN if date diff is less or equal then 1 day
 DATEDIFF(login2.timestamp, login1.timestamp) <= 1 
WHERE
   login2.id IS NOT NULL
 AND
   login2.customer IS NOT NULL
 AND
   login2.timestamp IS NOT NULL
ORDER BY 
 login1.customer
) 
 AS logins
GROUP BY
 logins.customer

结果

| customer | longest continuous streak of logins |
|----------|-------------------------------------|
|        1 |                                   2 |
|        2 |                                   1 |
|        3 |                                   1 |

查看演示 http://www.sqlfiddle.com/#!9/ad581/17