1、具体问题

在数据分析师面试中，经常会遇到手写代码的问题，比如下面这道面试题，已知一个表格有三个字段，第一个是店铺shop，第二个是销售日期saledday，第三个是销售额gmv。求连续三天有销售记录的店铺。本文提供的是hive语法的解法。

2、解决方案

2.1 方法1 join两次

第一种方法比较容易想到，就是将表进行自连接，将表连续往后推移两天，如果都当前日期、当前日期的后一天、当前日期的后两天该店铺都有销售记录，那么这个店铺就是连续三天有销售记录的店铺。test_table为表名，将表自连接两次，连接字段为店铺名字和时间，第二个表的销售日期和第一个表的销售日期差1天，第三个表的销售日期的和第一个表的销售日期相差2天。

select t1.shop as shop1
,t1.saleday as saleday1
,t1.gmv as gmv1
,t2.shop as shop2
,t2.saleday as saleday2
,t2.gmv as gmv2
,t3.shop as shop3
,t3.saleday as saleday3
,t3.gmv as gmv3                                            
from test_table t1
left join test_table t2
on t1.shop =t2.shop
and replace(t1.saleday,'-','')=to_char(dateadd(to_date(t2.saleday,'yyyy-mm-dd'),-1,'dd'),'yyyymmdd')
left join test_table t3
on t1.shop=t3.shop
and replace(t1.saleday,'-','')=to_char(dateadd(to_date(t3.saleday,'yyyy-mm-dd'),-2,'dd'),'yyyymmdd')

上面的代码中t1为主表，左连接t2和t3，连接字段是店铺名和日期，这里用到dateadd函数用来做日期的加减，用replace函数改变日期的格式。结果如下表所示：

从上表可以看出如果某个店铺当前销售日期的后一天没有销售记录，后面就是NULL，所以我们下一步就是要把三天都有销售记录的店铺名字取出来，直接用where限制一下条件就好。

select distinct t1.shop                                               
from test_table t1
left join test_table t2
on t1.shop =t2.shop
and replace(t1.saleday,'-','')=to_char(dateadd(to_date(t2.saleday,'yyyy-mm-dd'),-1,'dd'),'yyyymmdd')
left join test_table t3
on t1.shop=t3.shop
and replace(t1.saleday,'-','')=to_char(dateadd(to_date(t3.saleday,'yyyy-mm-dd'),-2,'dd'),'yyyymmdd')
where t1.gmv>0 and t2.gmv>0 and t3.gmv>0

结果如下：

2.2 方法2 lead函数

第一种方法比较容易想到，也好理解，但是书写起来比较麻烦，要join两次，hive中提供了一些窗口函数可以使用，比如lead函数可以获取结果集中，按一定排序所排列的当前行的相邻后面若干行的某个行的某个列(不用结果集的自关联），这边首先要将销售日期字段升序排列，具体代码如下：

    select t1.shop as shop1,t1.saleday as saleday1,t1.gmv as gmv1
    ,lead(t1.shop,1,null) over (partition by t1.shop order by saleday) as shop2 
    ,lead(t1.saleday,1,null) over (partition by t1.shop order by saleday) as saleday2
    ,lead(t1.gmv,1,null) over (partition by t1.shop order by saleday) as gmv2
    ,lead(t1.shop,2,null) over (partition by t1.shop order by saleday) as shop3 
    ,lead(t1.saleday,2,null) over (partition by t1.shop order by saleday) as saleday3
    ,lead(t1.gmv,2,null) over (partition by t1.shop order by saleday) as gmv3                                          
    from test_table t1

lead(t1.shop,1,null) over (partition by t1.shop order by saleday) 代表数据按照店铺名字分组，按照销售日期升序排列，整个表格往后移一行的shop列取出来。结果如表下：

从上表可以看出某些店铺的销售日期不是连续的，比如店铺A销售日期2017-10-13后面是2017-10-15，所以接下来我们要限制一下时间间隔，还有gmv不为空。具体代码如下：

select distinct t2.shop1
from
(
    select t1.shop as shop1,t1.saleday as saleday1,t1.gmv as gmv1
    ,lead(t1.shop,1,null) over (partition by t1.shop order by saleday) as shop2 
    ,lead(t1.saleday,1,null) over (partition by t1.shop order by saleday) as saleday2
    ,lead(t1.gmv,1,null) over (partition by t1.shop order by saleday) as gmv2
    ,lead(t1.shop,2,null) over (partition by t1.shop order by saleday) as shop3 
    ,lead(t1.saleday,2,null) over (partition by t1.shop order by saleday) as saleday3
    ,lead(t1.gmv,2,null) over (partition by t1.shop order by saleday) as gmv3                                          
    from test_table t1
)t2
where t2.gmv2>0 and t2.gmv3>0
and replace(t2.saleday1,'-','')=to_char(dateadd(to_date(t2.saleday2,'yyyy-mm-dd'),-1,'dd'),'yyyymmdd')
and replace(t2.saleday1,'-','')=to_char(dateadd(to_date(t2.saleday3,'yyyy-mm-dd'),-2,'dd'),'yyyymmdd')

上述代码运行一下就可以得到连续三天有销售记录的店铺，结果如下：

2.3 方法3 新思路

最后一种方法比较难想到，如果我们把销售日期中的几号取出来，然后再分组按照销售日期降序排列，如果将两者相加，连续的天数的和应该是一样的。代码如下：

select *
,substr(saleday, 9, 2) as day
,row_number() over (partition by shop order by saleday desc) as rank
,cast(substr(saleday,9,2) as int) + row_number() over (partition by shop order by  saleday desc) as plus
from test_table

运行结果如下：

plus列中如果数字相等则表示改店铺有连续销售记录，如果连续三天，则有三个数字相等，如果连续四天则有四个数字相等，所以接下来我们只要根据店铺名字字段和plus字段分组，找到相同数据记录超过3行的店铺名字，具体代码如下：

select t1.shop
from
(
select *
,substr(saleday, 9, 2) as day
,row_number() over (partition by shop order by saleday desc) as rank
,cast(substr(saleday,9,2) as int) + row_number() over (partition by shop order by  saleday desc) as plus
from test_table
)t1
group by t1.shop,t1.plus
having count(*)>=3

运行结果如下：

方法3易于拓展，可以拓展到多天，不光连续3天，4天5天也可以，但是有个问题，如果跨月这个方法就会不准，如果原始数据存在跨月的情况，那建议采用方法1或者方法2。

image.png