Hive练习（2）---累计值的计算、列转行、行转列、截取字段的经典案例

目录

第一类：累计值的计算

第二类：列转行 case ... when....  [collect_list和collect_set]

第三类：行转列（Lateral View和UDTF函数（explode、split）结合使用）

第四类：截取字段substr(...,...,...)

---

第一类：累计值的计算

第一题:根据下列数据，现要求出：每个用户截止到每月为止的最大单月访问次数、累计到该月的总访问次数、当月访问总次数。

数据：

1、准备数据：

[hdp@hdp02 demo]$ vim demo04.txt  //写入demo04.txt中 hive (demodb01)> create table demo01(name string, dates string ,Hz int) row format delimited fields terminated by ",";  //创建表 hive (demodb01)> load data local inpath "/home/hdp/demo/demo04.txt" into table demo01; //导入数据 hive (demodb01)> select * from demo01; //查询验证

2、需求分析：按照姓名、日期分组，求当月汇总访问次数，每个用户截止到每月为止的最大单月访问次数、累计到该月的总访问次数。（这种递进式的多条件累计就要用到表连接，条件计算或者窗口函数）

3、操作并得出结果：

方法1：传统sql语句思想：

（1）、按照姓名、日期分组，求当月汇总访问次数，并保存

create table demo01_1 as select name,dates, sum(hz) as s_hz 
from demo01 group by name,dates;

（2）、连接表 ,并保存为视图

create view demo01_2view as 
select t1.name aname,t1.dates adates,t1.s_hz ashz,t2.name bname,t2.dates bdates,t2.s_hz bshz 
from demo01_1 t1 join demo01_1 t2 on t1.name=t2.name;

（3）、条件统计,输出结果

select t.aname name,t.adates detes,t.ashz hz,max(bshz) sm_hz,sum(bshz) s_hz 
from demo01_2view t 
where t.adates>=t.bdates 
group by t.aname,t.adates,t.ashz;

name    detes   hz      sm_hz   s_hz
A       2015-01 33      33      33
A       2015-02 10      33      43
A       2015-03 38      38      81
B       2015-01 30      30      30
B       2015-02 15      30      45
B       2015-03 44      44      89

方法2：利用hive的窗口函数

select a.name,a.dates,max(a.hz),max(a.sm_hz),max(a.s_hz) from 
(select name,substr(dates,1,7) dates,
sum(hz) over(partition by name,substr(dates,1,7)) hz,
max(sum(hz) over(partition by name,substr(dates,1,7))) over(partition by name order by substr(dates,1,7)) sm_hz,
sum(hz) over(partition by name order by substr(dates,1,7)) s_hz
from demo01) a group by a.name,a.dates;

第二题:根据下列数据，编写Hive的HQL语句求出每个店铺的当月销售额和累计到当月的总销售额

数据：

1、准备数据

[hdp@hdp02 demo]$ vim demo05.txt  //写入demo05.txt中 hive> create table demo02 (Store string,Smonth string ,amount decimal(8,2)) row format delimited fields terminated by ','; //创建表 hive> load data local inpath '/home/hdp/demo/demo05.txt' into table demo02; //导入数据 hive> select * from demo02; //查询验证

2、分析需求：求出每个店铺的