记录一次优化SQL查询的经历。
需求：统计历史某个时间段内每个新增设备在未来30天中的活跃情况
问题：原SQL执行特别慢，原因在于通过device_id去关联dws_base.device_info_day的方式效率不高，它会为每个device_id去dws_base.device_info_day执行一次查询，每次的查询时间区间为自注册日first_day后的30天，所以导致查询时间特别长
1 | -- 原SQL |
思路：device_info_all（记为a）表中有设备id和设备首次登陆时间，dws_base.device_info_day（记为b）表中有设备id和只记录其当天的登陆的一条记录。 要统计a表中新增设备的登陆情况，则需要通过关联b表才能出来，但这里有一个问题，a表中设备的登陆记录可能不止一条，所以设备登陆日距离首次登陆日的日期差值date_diff会有多个，date_diff如何计算呢？如果按照之前的SQL，逻辑上是正确的，但是执行起来会消耗大量的资源，因为它会为每个设备id去b表中执行一次子查询，最终会导致内存溢出而执行不成功。所以我想了一个比较笨的办法，举个例子，如果我们要考虑"2018-12-17 00:00:00"至"2018-12-23 00:00:00"之间新注册登录的设备在在未来30天的活跃情况，1
2(SELECT distinct device_id, from_unixtime(unix_timestamp(first_day),'yyyyMMdd') dt FROM dws_base.device_info_all
where first_day>="2018-12-17 00:00:00" and first_day<="2018-12-23 00:00:00" and device_id IS NOT NULL AND device_id <> "") as a
那么我们仅仅需要在b表中把设备登陆日志的时间窗口限制在"2018-12-18 00:00:00"至"2018-01-22 00:00:00"之间即可，1
2
3(select * FROM dws_base.device_info_day
where dt >= from_unixtime(unix_timestamp("2018-12-17 00:00:00")+1*24*60*60,'yyyyMMdd')
and dt <= from_unixtime(unix_timestamp("2018-12-23 00:00:00")+30*24*60*60,'yyyyMMdd')) b
然后计算a表中设备id首次登陆日期与b表中该id在未来30多天登陆记录日期的日期差date_diff。这样，便保证了"2018-12-17 00:00:00"至"2018-12-23 00:00:00"之间的设备完整地被限制在30天的窗口内，虽然除了23号，其他天数的考察窗口都超过了30天，但我们可以通过date_diff参数来区分，留存和流失用户。
最后我们拿到的数据有4列，分别是设备id（device_id）、设备首次登陆日期（first_day）、设备日常登陆日期（login_day）、设备登陆时距离首次登陆的时间（date_diff）到底当设备的date_diff符合什么样的条件才能被判定为活跃呢？总结了一下，大体分为4种情形：
- 设备自首次登陆之后就再也没有登陆过，那它的
date_diff就都为0，所以该设备被判定为流失 - 设备自首次登陆之后的30天内没有登陆记录，但是30天后有登陆记录，此时的
date_diff{31,35,38...}都大于30，但该设备判定为流失 - 设备自首次登陆之后的30天内有登陆记录，并且30天后也有登陆记录，此时的
date_diff{0,5,16,31...}有大于30的有小于30的，该设备判定为留存 - 设备自首次登陆之后的30天内有登陆记录，并且30天后也有登陆记录，此时的
date_diff{5,16,31...}有大于30的有小于30的，该设备判定为留存
现在的任务就是如何写出一个条件准确地筛选出流失用户和留存用户，一开始我的设置的条件是min(date_diff)<=30 and min(date_diff)>0，也就是只要当date_diff的最小值小于等于30且大于0就判定为留存用户，但是SQL执行后的结果却很奇怪，只有时间窗口的第一天（2018-12-17）数据是正常的，其他的日期则留存设备数量明显偏少，很不符合实际情况。后来才发现，如果是2018-12-18，因为设备登陆日志的窗口在"2018-12-18 00:00:00"至"2018-01-22 00:00:00"，那么此时的date_diff会有等于0的情形，而我们的min(date_diff)>0把这部分数据都过滤掉了（对应情形3），所以导致数据不正常。然后我把过滤条件修改为min(date_diff)<=30 and avg(date_diff)>0，这样就完美地把4种情形区分开来了。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23-- 优化后的sql
SET mapreduce.job.queuename=root.hive;
select d.first_day dt,count(distinct d.device_id)new_device_nums,sum(label)liucun_device_nums
FROM
(select c.device_id,c.first_day,
case when (min(date_diff)<=30 and avg(date_diff)>0) then 1 -- 防止过滤掉date_diff为0的数据
-- else date_diff=0 and min(date_diff)>30 then 0
else 0 end as label
FROM
(select a.device_id,a.dt first_day,b.dt login_day,
datediff(from_unixtime(unix_timestamp(b.dt,'yyyyMMdd'),'yyyy-MM-dd'),from_unixtime(unix_timestamp(a.dt,'yyyyMMdd'),'yyyy-MM-dd')) as date_diff
FROM
(SELECT distinct device_id, from_unixtime(unix_timestamp(first_day),'yyyyMMdd') dt FROM dws_base.device_info_all
where first_day>="2018-12-17 00:00:00" and first_day<="2018-12-23 00:00:00" and device_id IS NOT NULL AND device_id <> "") as a
left join
(select * FROM dws_base.device_info_day
where dt >= from_unixtime(unix_timestamp("2018-12-17 00:00:00")+1*24*60*60,'yyyyMMdd')
and dt <= from_unixtime(unix_timestamp("2018-12-23 00:00:00")+30*24*60*60,'yyyyMMdd')) b
on a.device_id=b.device_id)
as c group by c.device_id,c.first_day)
as d
group by d.first_day
order by d.first_day;
