化整为零，逐个击破——浅谈SQL语句写法

roych

前段时间，有个网友跟我提了这么一个需求，想在SQL Server数据库里求出每个用户的最长连续登陆天数。这需求，听起来似乎很简单：把每一行和前一行对比，如果相差为1，则表示连续，否则就不连续。然后根据统计各个连续的天数，把最大值求出来。
然而，如何对比每一行和前一行呢？如果第一行返回空值，又应该如何计算呢？如何在技术上实现这些细节，则值得商榷了。

当你越深入去思考这些问题，越发觉得这个问题无解。——当然，也不是完全无解。大不了我们用存储过程来处理罢了，只是可能需要多个步骤。

为此，我们可以考虑把问题分解出来，逐步去解决。这也就是我们所说的“化整为零，逐个击破”。

那么，如何化整为零呢？个人推荐使用Excel作为工具进行模拟拆解。工具选好了，那么应该从哪里着手呢？在思路不够清晰的情况下，个人建议可以先从结果入手，逆向倒推回去。
我们的数据是这样的：

我们要的结果是这样的：

如何得到这一串数字呢？方法1：对比这个日期和上一个日期相差天数，如果大于1，表示不连续，则应该从头开始。
但前面说了，这一个方法需要自连接，可能还需要考虑空值的情况。所以我们暂时先不用这个方法。接下来我们考虑方法2：
我们可以用一个最小日期作为起始点，每条记录算一个步长值，那么，实际天数到这个起始点加步长值一致的话，则应为连续天数。
这样说，可能比较抽象。我们还是继续看图：

如上图，以201为例，起始日期为1号，我们列出一个天数序列（见第二行数字）。我们当然可以从第二列选择实际日期来查看是否连续。不幸的是，数据库把这些数字都挤在一起了（见第三行数字）。然而，我们通过细心观察，如果是连续天数，那么，实际日期减去对应的序列，得到的数值都是一样的（见第四行数字），例如，第一组连续天数对应的这个值是1，二组则是2，第三组是15等等。

看出来了吗？想明白了吗？事实上，只要这些天数是连续的，在这个数轴上，不管平移到哪里，它都是连续的。而现在只不过让它往前平移罢了。

起始日期难不难找？不难，用min，再按每个用户group by一下就有了。序列难不难？听起来难，实际上用over partition轻易解决。然后两者相减难不难？当然——说难的都应该被打屁屁了。

至此，我们可以画一个图：

分解完毕后，我们可以来写脚本了。
第一步，把最小值和序号列出来：

第二步：在这个基础上，用datediff将日期进行相减，获取相差天数，嵌套一下，得到以下结果：

第三步：对diff，userid进行分组统计。这当然还需要嵌套一次，结果已经呼之欲出了：

第四步：对userid分组统计，求出t2的最大值。至此，任务完成：

附上SQL脚本：

1. select userid,MAX(t2) t3 from(
   
2.         select userid,diff,COUNT(diff) t2 from(
   
3.                 select userid,loadtime,t1,mintime
   
4.                 --天数差异
   
5.                 ,DATEDIFF(DAY,minTime,loadtime)-t1 diff
   
6.                 from(
   
7.                         select userid ,loadtime
   
8.                                 --序号
   
9.                         ,ROW_NUMBER() over(partition by userid order by loadtime) t1,
   
10.                         --子查询，最小日期
    
11.                         (select MIN(loadtime) from login_data where login_data.userid=a.userid) minTime
    
12.                         from login_data a) a) a
    
13.         group by userid,diff) a
    
14. group by userid

复制代码

这也许不是一个最好的写法，但这是一个思路最为清晰，最容易被理解和接受的写法。后续有时间的话，我再试试用其它方式来写。

tmtony

赞一个！！

accben

roych威武!
半年前公司刚好有这个需求，当时是统计每位员工当月的连续加班的日数，我当时是用ADO解决的。
我SQL用得不好，纯用SQL计算绝做不出来。
roych所列举的这个应用场景是企业很常用的。
谢谢roych分享。