Week08_day01 (Hive实现按照指定格式输出每七天的消费平均数)
Hive实现按照指定格式输出每七天的消费平均数
数据准备
2018/6/1,10 2018/6/2,11 2018/6/3,11 2018/6/4,12 2018/6/5,14 2018/6/6,15 2018/6/7,13 2018/6/8,37 2018/6/9,18 2018/6/10,19 2018/6/11,10 2018/6/12,11 2018/6/13,11 2018/6/14,12 2018/6/15,14 2018/6/16,15 2018/6/17,13 2018/6/18,17 2018/6/19,18 2018/6/20,19 2018/6/21,20 2018/6/22,21 2018/6/23,21 2018/6/24,22 2018/6/25,24 2018/6/26,25 2018/6/27,23 2018/6/28,27 2018/6/29,28 2018/6/30,29 2018/7/1,40 2018/7/2,41 2018/7/3,41 2018/7/4,42 2018/7/5,44 2018/7/6,45 2018/7/7,43 2018/7/8,47 2018/7/9,48 2018/7/10,49 2018/7/11,50 2018/7/12,51 2018/7/13,51 2018/7/14,52 2018/7/15,54 2018/7/16,55 2018/7/17,53 2018/7/18,57 2018/7/19,58 2018/7/20,59 2018/7/21,30 2018/7/22,31 2018/7/23,31 2018/7/24,32 2018/7/25,34 2018/7/26,35 2018/7/27,33 2018/7/28,37 2018/7/29,38 2018/7/30,39 2018/7/31,70 2018/8/1,71 2018/8/2,71 2018/8/3,72 2018/8/4,74 2018/8/5,75 2018/8/6,73 2018/8/7,77 2018/8/8,78 2018/8/9,79 2018/8/10,80 2018/8/11,81 2018/8/12,81
创建表
create table f ( date_time string, cost string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
使用本地加载数据
load data local inpath '文件所在的绝对路径' into table f;
查看内容
需求： 求每七天的消费平均数 ，要求输出格式如下：
第一步，先将日期分割
select split(date_time,'/') from f;
第二步，将日期使用‘-’ 进行连接，使用到 concat_ws() 函数
select concat_ws('-',split(date_time,'/')) from f;
第三步，我们要实现这个要求，主要思想就是怎么去实现统一分组，以及日期怎么去变化，先将每个日期进行减去第一天的日期。 datediff()
select datediff(concat_ws('-',split(date_time,'/')),'2018-6-1') from f；
第四步，在上面的基础之上进行除以7，因为hive有小数点，我们 floor再向下取整，这样我们发现，日期现在变的一致了。
select floor(datediff(concat_ws('-',split(date_time,'/')),'2018-6-1')/7)*7,cost from f;
第五步，现在需要思考的就是，怎么将其转化位日期，而且还要有分组之前的现象出现，我们使用第一天的日期加上当前的日期*7就是一样的了。
select date_add('2018-6-1',cast(floor(datediff(concat_ws('-',split(date_time,'/')),'2018-6-1')/7)*7 as int)),cost from f;
第六步，我们距离要求输出越来越近，现在需要考虑的就是怎么获取7天后的日期，以及怎么输出指定格式。很简单，后面7天的日期在当前日期前面加6就是的了，在使用函数 concat() 进行拼接，当然也可以使用concat_ws() 因为我这里就两个量进行拼接，可以用concat()
select concat(date_add('2018-6-1',cast(floor(datediff(concat_ws('-',split(date_time,'/')),'2018-6-1')/7)*7 as int)),'~',date_add('2018-6-1',cast(floor(datediff(concat_ws('-',split(date_time,'/')),'2018-6-1')/7)*7+6 as int))) as dtime,cost from f;
第七步，到这里应该就很熟悉了，我们现在就是要做的就是对这个结果进行group by分组,以及avg()求平均值了
select w.dtime,avg(w.cost) from (select concat(date_add('2018-6-1',cast(floor(datediff(concat_ws('-',split(date_time,'/')),'2018-6-1')/7)*7 as int)),'~', date_add('2018-6-1',cast(floor(datediff(concat_ws('-',split(date_time,'/')),'2018-6-1')/7)*7+6 as int))) as dtime,cost from f) w group by w.dtime;select w.dtime,avg(w.cost) from (select concat(date_add('2018-6-1',cast(floor(datediff(concat_ws('-',split(date_time,'/')),'2018-6-1')/7)*
select w.dtime,avg(w.cost) from (select concat(date_add('2018-6-1',cast(floor(datediff(concat_ws('-',split(date_time,'/')),'2018-6-1')/7)*7 as int)),'~', date_add('2018-6-1',cast(floor(datediff(concat_ws('-',split(date_time,'/')),'2018-6-1')/7)*7+6 as int))) as dtime,cost from f) w group by w.dtime;
第八步，我们将小数点保留至后两位，使用函数round()
select w.dtime,round(avg(w.cost),2) from (select concat(date_add('2018-6-1',cast(floor(datediff(concat_ws('-',split(date_time,'/')),'2018-6-1')/7)*7 as int)),'~', date_add('2018-6-1',cast(floor(datediff(concat_ws('-',split(date_time,'/')),'2018-6-1')/7)*7+6 as int))) as dtime,cost from f) w group by w.dtime;
最终结果：
