SQL 求最大连续月数

一、业务背景及口径说明

背景：
在零售行业，会有一种负毛利的现象，就是商品的售价要低于进价，虽说出现负毛利不代表一定亏损（还要看销售返利），但是如果某商品连续几个月都出现了负毛利的情况则需要引起重视，可能是因为采购价格过高造成的因素。
所以有必要计算出现连续负毛利的商品，并且查看下出现负毛利的最大连续月数是多少。
口径说明：
假定出现负毛利为1，不是负毛利则为0

最大连续月数：一段时间内，连续出现负毛利的月份数量之中最大的那个数量。比如在1~6月期间，某商品在1月、2月、3月、5月、6月是负毛利的，4月份售价高于进价，则在这段期间的连续月数有3（1~3月）和2（5~6月）这两个，最大连续月数就是在连续月数中取最大的那个就是3。

二、SQL解决方案

2.1 数据介绍

为了防止大家对于口径说明还存在疑惑，这里造了个玩具数据提供给大家理解。
假设现在是1月份，想查看下各商品在前6个月时期内出现的最大负毛利连续月数，加工后的数据呈现为下图，item_id 是商品编号，每一个 item_id 唯一确认一个商品，每个月没有出现负毛利的商品记录已经过滤。

在上图中，我们肉眼可以看到：

• 商品 id 为 1 的商品，在7~12月份每个月都出现了负毛利的情况，所以其最大连续月数是6；
• 商品 id 为 2 的商品在7月、9月、11月、12月出现了负毛利的情况，最大连续月数是2（11月和12月）；
• 商品 id 为 3 的商品最大连续月数是3（10月到12月）
• 商品 id 为 4 的商品最大连续月数是2（7月到8月或10月到12月）；
• 商品 id 为 5 的商品最大连续月数是4（7月到10月）；
• 商品 id 为 6 的商品最大连续月数是5（7月到10月）。

-- HQL 插入数据
create table xn_test.consecutive_months
(
    item_id int comment '商品id'
  , months int comment '月份'
 )
 stored as orc
;
 insert into xn_test.consecutive_months(
    item_id
  , months
)
values
      (1, 7), (1, 8), (1, 9), (1, 10), (1, 11), (1, 12)
    , (2, 7), (2, 9), (2, 11), (2, 12)
    , (3, 7), (3, 8), (3, 10), (3, 11), (3, 12)
    , (4, 7), (4, 8), (4, 11), (4, 12)
    , (5, 7), (5, 7), (5, 8), (5, 9), (5, 10), (5, 12)
    , (6, 7), (6, 8), (6, 9), (6, 10), (6, 11)
;

2.2 计算逻辑

使用 窗口函数row_number()可以解决该类问题，原理是这样的：
如果月份是连续的，那某个月的下一条记录一定是+1，而 row_number() 也是这个逻辑。

下面是计算逻辑：

1. 使用 row_number() 新生成一个字段 rn，具体是对每个商品分区，然后按月份升序标号;

row_number() over(partition by item_id order by months asc)

2. 使用月份 months 减去 rn，如果差值是相同的，那么这几个月是连续；

months - row_number() over(partition by item_id order by months asc) diff_rn

3. 对每个商品、每个差值聚合，求出每个商品下每个差值的数量，这就是每个月份区间上的连续月数；

with mid as 
(
    select d.item_id
     , d.months
     , row_number() over(partition by d.item_id order by d.months asc) rn
     , d.months - row_number() over(partition by d.item_id order by d.months asc) diff_rn
    from xn_test.consecutive_months d
)
select m.item_id
     , m.diff_rn
     , count(*) cnt
from mid m
group by m.item_id, m.diff_rn

5. 最后只要用 max() 函数得到每个商品最大连续月数即可。

上图中 diff_rn 就是 months - rn 的值，可以看到 item_id=2，diff_rn为8的记录，他对应的 months 是连续的（11月和12月）；item_id=3时有两个连续月份区间，一个是7月和8月，还有个是10到12月。
具体SQL实现：

-- HQL
with mid as 
(
    select d.item_id
     , d.months
     , row_number() over(partition by d.item_id order by d.months asc) rn
     , d.months - row_number() over(partition by d.item_id order by d.months asc) diff_rn
    from xn_test.consecutive_months d
)
select 
        t.item_id
      , max(t.cnt) max_consecutive_months
from 
    (
        select m.item_id
             , m.diff_rn
             , count(*) cnt
        from mid m
        group by m.item_id, m.diff_rn
    ) t
group by t.item_id
order by t.item_id
;

返回结果：

可以看到，与我们之前肉眼所见的结果是一致的。
商品1在前6个月中每个月都出现了负毛利的情况，需要重点关注，看看是不是因为采购价格过高的因素导致的。