Hive SQL复杂场景实现(1) —— 连续发单天数

本文同时发表于我的个人博客http://xinyuwg.com，访问该链接以获得详细信息与更好的阅读体验。
本文为原创内容，未经允许请勿转载。

至今在数据分析岗摸爬滚打已有一年，尚且不敢说自己挖掘洞见的本事提升多少。但实打实的与SQL打了一年的交道，接触过各种各样的业务场景，完成过各种千奇百怪的需求，自我感觉在sql编程上也颇有体会。

相信接触过SQL的人都明白知道其非常容易上手，作为一个结构化查询语言，其在数据提取上给人们提供了非常大的便捷。然而在考虑到开发成本和计算的复杂度的情况下，并非所有的提数需求都适合用sql来实现，也并非sql能够在各种业务场景下提供数据提取的最优解。有些时候hive streaming、spark、甚至简单的python脚本都能把sql难以实现的逻辑变得得心应手。即便如此，探究sql在一些复杂场景下的实现还是对锻炼逻辑思维很有帮助。

最近我会专门开辟一个专栏来分享这一年来我所遇到的一些比较复杂的业务场景，并力求通过纯sql的方法实现，给出我能想到的所有解法与大家分享。这也算是见证和记录自己一年来sql编程的心路历程了吧。

毕竟我的目标是data scientist呀。

---

背景

数据运营人员常常会需要查找活跃用户名单，而活跃用户很多情况下被定义为连续在线或发单n天及以上的用户。一方面我们可以根据n的值直接进行筛选；更具一般性地，就要求我们去求取每个用户某段时间内的最大连续在线或者发单天数了。

SQL求连续在线天数是一个非常经典的问题，该问题在不考虑计算成本下有非常多的解法。该问题也是我在面试实习生时最喜欢深入问的一个问题，在引导一个候选人去完成这个问题的过程中可以看出其对sql的理解深度以及其思维是否灵敏。

该问题的最大难点在于如何判断日期与日期间是否连续，那这就要涉及到处理行与行之间的关系了。说到这对SQL比较熟悉的同学应该就会反应出使用join或者窗口函数来处理了。

---

数据：

假设我们有19年一月份每日用户发单数据存储于订单表order_base:

user_id	order_id	create_time
234520012	1231512416323	2019-01-02 12:21:11
123149908	2412298719221	2019-01-04 01:11:34
…	…	…

---

解法1（通过与特定日期的日期差判定连续）：

本方法比较tricky。连续的时间以为着这些时间点与某一个特定时间点的时间差也是连续的，从下表可以直观理解这一点：

日期	特定日期	日期差d
2019-01-01	2019-01-01	0
2019-01-02	2019-01-01	1
2019-01-04	2019-01-01	3
2019-01-05	2019-01-01	4
2019-01-06	2019-01-01	5

那么我们对该日期差d进行个排序，如果连续的话，d与序号的差值应该是相同的，如下表：

<

日期	特定日期	日期差d	序号r	日期差d与序号r的差值
2019-01-01	2019-01-01	0	0	0
2019-01-02	2019-01-01	1	1	0
2019-01-04	2019-01-01	3	2	1
2019-01-05	2019-01-01	4	3