连续60天畅销产品怎么找？SQL相邻问题之连续登录的四种解法

SQL中关于连续登录问题也是经常遇到的难点，Leetcode上有很多花样百出的考察连续问题的题目。简单点的，对初学者来说很容易解答，但是真在实际工作中遇到恐怕会遇到各种各样的问题。

有时候看到一些职场人士吐槽现在SQL考察越来越难了，几年前的“难题”现在成了入门级的。而我们现在初学者练习的基本SQL题远不能让你真正掌握SQL取数要领。“读万卷书，不及行万里路”。因此下面用实际案例数据还原真实取数场景，帮助你在实战中理解过程，总结思路。

问题

怎么取出连续60天畅销的产品？

背景：数据来源于微软示例数据库，一家销售自行车制造公司的销售数据，分为网络销售FactInternetSales和经销商销售FactResellerSales两张表，其中网络销售订单数据60398行，经销商销售数据有60855行。此处我们简化问题，重点在问题的解决上，只用到FactInternetSales表。表中有包含价格ProductKey和orderdate在内的共计26个字段。

分析：同SQL排名问题一样，都是要纵向比较。这里最重要的关键词是“产品”，限定条件一个是“连续60天畅销”，也就是说要根据时间条件来筛选出产品。它还隐含了一个背景，产品肯定不止一个，因此这个问题转化为分组问题–以每个产品为一个分组，组内日期是连续60天的就是符合条件的。

如果大家看过我原创的”“茴”字有几种写法？SQL排名问题之全局排名的四种解法“这篇文章，应该学会一种SQL解决问题的思路，那就是从简单查询、聚合函数/窗口函数、自定义变量和自连接四种角度去尝试。我收集了大约800种SQ题型，绝大多数都逃不过这四种解法。可以说掌握了每种解法的精髓，再难的SQL一看就会有思路。

思路一：首先想一下简单查询是否可以解决呢？也就是只使用12个SQL保留字直接取到预期结果。要分组取复合条件的数则必须使用聚合函数或者窗口函数，不过大家不要以为groupby分组无法取数，实际上在MySQL中groupby分组可以取出每组第一条数据（经过测试在Sql Server中会报错），这对某些特殊问题解决也提供了一个小小的trick。回到这个问题中，简单查询实际是无法解决的。下面是尝试成功的解法。

思路二：使用聚合函数和窗口函数

解法一 聚合函数count

使用哪一个聚合函数呢？既然是求连续当然是count函数最合适不过了。我们对日期进行计数，连续的日期数大于60不就是符合条件的？下面这段代码是在Sql Server中运行的，因此它的一些函数用法与MySQL中些不同，不过原理都是一样的。

select distinct l1.ProductKey
from factinternetsales l1 join factinternetsales l2
on l1.ProductKey=l2.ProductKey and datediff(day,l1.orderdate,l2.orderdate) between 1 and 60
group by l1.ProductKey,l1.orderdate 
having count(distinct l2.orderdate)=60;

先用笛卡尔积的方法将表和它自身做个连接，筛选条件是产品号相同，第二张表的日期在第一张表的1到60天之间范围，这样我们就找到了每个日期和它的60天之内的日期。那怎么判断是连续60天呢？就是用这个日期出现的次数来判断，如果这个日期出现了60次说明是连续60天的。如果不是说明中间出现中断需要被排除。这里需要注意的是去重，如果不去重，则计数可能重复。

解法二 窗口函数row_number()

上面用count函数聚合的思路是比较容易理解的。既然求连续60天，把60天以内的日期找出来，有60个就是符合条件的。但是下面这种解法就是用“曲线救国”的方式，稍微不好理解。

select distinct ProductKey 
from (select ProductKey,orderdate,r,dateadd(day,-r,orderdate)as dif
      from(select ProductKey,orderdate,row_number() over(partitionby ProductKey orderby orderdate asc)as r 
           from(select distinct ProductKey,orderdate from factinternetsales)b)a)c
group by ProductKey,dif
having count(dif)>=60
order by ProductKey;

上面这段代码有三层子查询的嵌套，它们分别什么作用呢？由里到外，第一层是用排名函数对日期做了一个排名；第二层将日期与对应的排名相减得到一列新的日期，最核心的就在于这里：理想情况下，日期是递增的，排名也是递增的，而且递增的梯度是一样的都是一，那么两者相减会得到一个恒定的数；但是如果中间不连续，那么相减得到的数就与之前不同，通过这种方式来判断连续，最后我们再统计相同的数等于60不就是符合条件了吗？效果如下图：

先生成排名r列，再用orderdate减去r得到相同日期，最后根据产品分组计数就好啦！思路是很巧妙的，看似复杂，其实很简单。

解法三 窗口函数lead()

数据库工具实际越来越人性化了，开发人员考虑到实际需求专门开发出解决某一类问题的工具。对于连续问题，其实最好用的也是经常被使用的就是lead()和lag()函数。前面两种解法别看我讲的简单，但实际你可能想不到，而且在解决别的连续问题可能有些能使用有些不能使用。但是这两个窗口函数是一定要掌握的。

回到我们的问题，既然我们要求连续第60天还在销售的产品，那么我直接从当前日期按数据顺序往后取60位，如果取到的日期恰好是这个日期加60天的日期就是符合条件的，不是就说明中间没有连续，这样就达到连续60天的效果。其实这是一种滑动（shift）数据的思想，它的典型应用场景就是在求同比和环比上。

with t as 
(selectdistinct ProductKey,orderdate from factinternetsales),
t2 as 
(select ProductKey,orderdate,lead(orderdate,60)over(partitionby ProductKey orderby orderdate asc)as ldte,
      datediff(day,lead(orderdate,60)over(partitionby ProductKey orderby orderdate asc),orderdate)as diff 
from t)
select distinct ProductKey from t2 where diff =-60;

这段代码使用了公共临时表（CTE），后续会详细介绍CTE的用法，它可以使代码看起来很简洁，而且逻辑清晰。主要取数的SQL语句是最后一行，前面两张临时表是它的条件t表是去重防止计数错误，在t表基础上用lead()函数滑动使得每个日期与它往后60行的日期相对应，有就显示没有则为null，同时再将两个日期相减得到日期差，这样产生t2表；最后用日期差等于60，就可以取到对应的日期啦！这段代码中第一个lead()产生的ldte实际不需要，只是为了让大家更容易明白，把它去掉你会发现这种解法非常简洁，也是这类问题的通用解法。

思路三：自定义变量。我在这里不给出自定义变量的解法代码，如果大家感兴趣可以关注公众号“二八Data”联系我。实际上，所有能使用窗口函数的地方都可以使用用户自定义变量。在Oracle、Sql Server、PostGreSQL等主流数据库里很早就有了窗口函数功能，而MySQL在最新8.0版本才支持窗口函数，在这之前要解决排名问题、连续问题、累加问题等都需要使用自定义变量才可以实现。所以大家可以参考将排名问题的解法来做这一道题。

思路四：自连接解法。

解法四 自连接

自连接的思路是很好理解的，不过难就难在你知道它啥套路，但是你不清楚它是怎么执行的。下面先放上一段代码：

with t as(selectdistinct ProductKey,orderdate from factinternetsales)
select distinct l1.ProductKeyfrom t l1 
where 60= (select count(*) from t l2 
           where l1.ProductKey=l2.ProductKey and datediff(day,l1.orderdate,l2.orderdate) between 1 and 60);

这段代码比上面的解法都简洁，实际执行中也比上面解法都快（00:00:00.079）。

初学者可能乍一看明白什么意思，但是自己不知道怎么写，也不知道它是怎么执行的。它的解决问题的思路和使用count函数的第一种解法是一致的。但它做了一个巧妙的优化，那就是表进行自连接根据一行一行扫描时同时就开始取出符合条件的数。为什么有这样效果？因为它把group by和having之后的条件之间放在where子句中，会基本SQL语句的都知道SQL查询语句有书写顺序和执行顺序，SQL在执行时where子句是比having子句先执行的，它将数据进行一道筛选后数据量缩小，就为后面SQL语句执行提供了效率，这叫谓词下推，它是SQL语句优化里很重要的优化思路之一，后续我会详细讲解。

对于SQL取数问题为什么要提供这么多种解法呢？其实这是数据分析师必须具备的思维。在实际工作中，我们确实以解决问题为主，无论简单方法还是高级方法只要能解决问题就够了，但是你不止要解决一个问题还会遇到相同问题的奇奇怪怪的变形，会需要你有发散思维，既抓住本质又有创新思路；另外，你只有知道多种解题思路你才知道如何优化取最好的方式，你就比别人降低更多的成本。

最后欢迎大家关注我，我是拾陆，搜索公众号“二八Data”，更多技术干货持续奉献。