阿里面试题—蚂蚁森林HQL

有关大数据学习资源，请关注微信公众号 “ 码农书斋 ”。回复“大数据”，免费获取学习视频、源码及资料！

---------------------需求------------------
1.蚂蚁森林植物申领统计
问题：假设2017年1月1日开始记录低碳数据（user_low_carbon），假设2017年10月1日之前满足申领条件的用户都申领了一颗p004-胡杨，
剩余的能量全部用来领取“p002-沙柳” 。
统计在10月1日累计申领“p002-沙柳” 排名前10的用户信息；以及他比后一名多领了几颗沙柳。
得到的统计结果如下表样式：
user_id plant_count less_count(比后一名多领了几颗沙柳)
u_101 1000 100
u_088 900 400
u_103 500 …

----1.Hive的函数只能识别yyyy-MM-dd格式的日期----
--------先将user_low_carbon转化一下格式:使用hive中的常用函数——regexp_repalce(str,src,targer)--------
SELECT 
	user_id ,
	date_format(regexp_replace(data_dt,'/','-'),'yyyy-MM-dd') data_dt ,
	low_carbon 
FROM 
	user_low_carbon ;
---------基于上表查出2017年10月1日的top10------
SELECT low_carbon from plant_carbon where plant_name = '胡杨';
SELECT low_carbon from plant_carbon where plant_name = '沙柳';
select
	user_id ,
	plant_count,
	(plant_count - next_count) less_count
FROM 
(
	SELECT 
		user_id ,
		plant_count,
		lead(plant_count,1,0) over(order by plant_count DESC ) next_count
	FROM 
	(
		SELECT 
		user_id ,
		FLOOR ((sum_low_carbon - (SELECT low_carbon from plant_carbon where plant_name = '胡杨'))
			/ (SELECT low_carbon from plant_carbon where plant_name = '沙柳')) plant_count
		FROM 
		(
			SELECT 
				user_id ,
				sum(low_carbon) sum_low_carbon,
				rank() over(order by sum(low_carbon) desc) rk
			FROM 
			(
				select
					user_id ,
					data_dt,
					low_carbon 
				FROM 
				(
					SELECT 
						user_id ,
						date_format(regexp_replace(data_dt,'/','-'),'yyyy-MM-dd') data_dt,
						low_carbon 
					FROM 
						user_low_carbon
				)t1
				where YEAR(data_dt) = 2017 and MONTH (data_dt) < 10
			)t2
			group by user_id
		)t3
		where rk <= 11
	)t4
)t5
limit 10;

2、蚂蚁森林低碳用户排名分析
问题：查询user_low_carbon表中每日流水记录，条件为：
用户在2017年，连续三天（或以上）的天数里，
每天减少碳排放（low_carbon）都超过100g的用户低碳流水。
需要查询返回满足以上条件的user_low_carbon表中的记录流水。
例如用户u_002符合条件的记录如下，因为2017/1/2~2017/1/5连续四天的碳排放量之和都大于等于100g：

--------连续三天，等差为一的等差数列。再减去一个等差为一的等差数列(row_number)，结果是等值数列----
--------再以等值数列分区，使用开窗函数进行分区统计总数 ，过滤总数>= 3----->查出符合条件的user_id且data_dt
--------联接原表，查询出每日的流水记录---------------------------

SELECT 
 t7.user_id,
 t7.data_dt,
 t7.low_carbon
FROM 
(
	select
		t5.user_id,
		t5.data_dt
	from 
	(
		select
			t4.user_id,
			t4.data_dt,
			count(*) over(partition by user_id, sub_data_dt) cnt
		FROM 
		(
			select
				t3.user_id,
				t3.data_dt,
				t3.rn,
				date_sub(t3.data_dt,t3.rn) sub_data_dt
			FROM 
			(
				SELECT 
					t2.user_id,
					t2.data_dt,
					ROW_NUMBER() over(partition by user_id) rn
				FROM 
				(
					select
						t1.user_id,
						t1.data_dt,
						sum(t1.low_carbon) sum_low_carbon
					FROM 
					(
						SELECT 
							user_id ,
							date_format(regexp_replace(data_dt,'/','-'),'yyyy-MM-dd') data_dt ,
							low_carbon 
						FROM 
							user_low_carbon 
					) t1
					group by user_id,data_dt
					having sum_low_carbon > 100
				) t2
			) t3
		) t4
	) t5
	where t5.cnt >= 3
)t6 
join (
		SELECT 
			user_id ,
		 	date_format(regexp_replace(data_dt,'/','-'),'yyyy-MM-dd') data_dt ,
			low_carbon 
		FROM 
			user_low_carbon 
	 ) t7 
on t6.user_id = t7.user_id and t6.data_dt = t7.data_dt;