每日10行代码154：sql实现最大连续增长（减少，翻倍，为0）的月数（年数，天数、季度数，期数）

一、业务背景

实际工作中，经常会遇到这种需求：查询连续三个月收入下滑的门店，查询连续6个月销售增长的商品，查询连续增长时间最长的商品，查询连续销售额增长时间最长的企业和月份，查询连续多少个月业绩为0的员工。
凡是此类问题，归纳起来都是连续XXX时间满足XXXX条件。细分一下的话，XXX条件有的简单一点，有的复杂一点，比如业绩为0的员工，这个条件就很简单，直接在where 销售额=0就可以了，连续增长就稍微麻烦点，需要跟上一期的销售额进行比较。不过总之解决这类问题的基本原理是一样的。

二、解决思路

一、查询出符合XXX条件的所有记录。
二、查询第一步符合条件的记录的报表期是否连续以及连续了多少期。（因为实际业务中关注的时间跨度不一样，有的以天为颗粒度，有的以年为颗粒度，所以这里我用报表期代表统泛化的时间跨度）
这两个问题主要难在第二个问题上，下面我用例子来演示下：
假如有这样一份数据：

我要求销售额连续为0的月份，那先查出销售额为0的记录。

下一步就是判断，3，4，6，7这四个月份，哪些是连续的，连续了多少个月。
我们对这个数据加一个序号：

再拿报表期减月份：

可以看到，连续的月份，拿报表期减序号是相等的，并且不同的连续断报表期减序号是不一样的，其实这从实际上意义也很好理解 ，就是连续的数减去递增为一的序列值 ，结果是相等的，一旦有一个不连续，跳号了，那么相应差值就增加了。
基于以上的原理，我们就可以设计一个算法，就是如果报表期减序号是相等的，那么报表期就是连续的。这样，我们就可以开始写sql了，可能有细心的朋友会问，那我月度跨年了怎么办，我的报表期是季度怎么办，其实这时需要做一个转换就是把报表期转换成一个步长为1的等差数列。

三、实际sql写法

下面用一份测试数据来实际的展示下怎么写sql：
测试数据是这样的：

id可以是员工的，也可以是企业的，或者是某种商品，bbq（报表期）是月份，je是业绩或者是销售额。
我需要知道企业连续增长的情况，连续增长了多少个月，连续增长的最后一个月是哪个月。

--odps sql
SELECT  id
        ,diff_rn
        ,max(bbq) max_bbq    --连续增长的最后一个月
        ,COUNT(1) cnt
FROM    (
            SELECT  id
                    ,bbq
                    ,yf
                    ,ROW_NUMBER() OVER(PARTITION BY id ORDER BY bbq ASC ) AS rn
                    ,yf-ROW_NUMBER() OVER(PARTITION BY id ORDER BY bbq ASC ) diff_rn
            FROM    (
                        SELECT  id
                                ,bbq
                                ,yf
                        FROM    (
                                    SELECT  id
                                            ,bbq
                                            ,je
                                            ,months_between(to_date(bbq,'YYYYMM'),'2000-01-01') yf    --连续化月份值，解决跨年问题，国为后面会用到月份与rn相减 
                                            ,LAG(je,1) OVER(PARTITION BY id ORDER BY bbq ASC) AS je2
                                    FROM    zjx_ybnsrsb
                                ) a1
                        WHERE   je>je2
                    ) b1
        ) c1
GROUP BY id
         ,diff_rn
having count(1)>2         
;

其中：

SELECT  id
                                ,bbq
                                ,yf
                        FROM    (
                                    SELECT  id
                                            ,bbq
                                            ,je
                                            ,months_between(to_date(bbq,'YYYYMM'),'2000-01-01') yf    --连续化月份值，解决跨年问题，国为后面会用到月份与rn相减 
                                            ,LAG(je,1) OVER(PARTITION BY id ORDER BY bbq ASC) AS je2
                                    FROM    zjx_ybnsrsb
                                ) a1
                        WHERE   je>je2

这一块用了个开窗，查询下金额增长的月份。

months_between(to_date(bbq,'YYYYMM'),'2000-01-01') yf 

这个就是把月份转换成一个具体的数字，这个数字就是以2000年1月1号算起，报表期有多少个月。

,ROW_NUMBER() OVER(PARTITION BY id ORDER BY bbq ASC ) AS rn
,yf-ROW_NUMBER() OVER(PARTITION BY id ORDER BY bbq ASC ) diff_rn

这两个语句是加一个序号以及报表期与序号间的差值。

最后的结果：

上面第一行就表示，1001这个对象在报表期为201809时已经连续增长三期了。第二行表示，1001这个对象在201908这个报表期时已经连续增长第8期了。