设为首页收藏本站Access中国

Office中国论坛/Access中国论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

返回列表 发新帖
查看: 2239|回复: 2

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

[复制链接]
发表于 2019-11-8 00:27:59 | 显示全部楼层 |阅读模式
前段时间,有个网友跟我提了这么一个需求,想在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
复制代码
这也许不是一个最好的写法,但这是一个思路最为清晰,最容易被理解和接受的写法。后续有时间的话,我再试试用其它方式来写。

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?注册

x

点击这里给我发消息

发表于 2019-11-8 14:40:57 | 显示全部楼层
赞一个!!
回复

使用道具 举报

发表于 2019-11-17 10:34:20 | 显示全部楼层
本帖最后由 accben 于 2019-11-17 10:37 编辑

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

本版积分规则

QQ|站长邮箱|小黑屋|手机版|Office中国/Access中国 ( 粤ICP备10043721号-1 )  

GMT+8, 2024-3-29 19:19 , Processed in 0.085843 second(s), 28 queries .

Powered by Discuz! X3.3

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表