网站流量日志分析--统计分析--hive 级联求和问题案例|学习笔记

开发者学堂课程【大数据分析之企业级网站流量运营分析系统开发实战（第四阶段）：网站流量日志分析--统计分析--hive 级联求和问题案例】学习笔记，与课程紧密联系，让用户快速学习知识。

课程地址：https://developer.aliyun.com/learning/course/696/detail/12243

网站流量日志分析--统计分析--hive 级联求和问题案例

1、为了更好理解 hive 中级联求和的问题，通过例子去理解级联求和以及为什么要通过自己跟自己 join 解决这类问题。

2、打开课程的参考资料，点击软件，点击 hive，点击 hive 级联求和。

3、结构化数据文件有三个字段，第一个字段表示的是用户名，a 用户，b 用户，第二个字段表示的是月份，几月份，第三个字段是获得的一次小费，费用，可以理解为a用户是餐厅的服务人员，比如a用户2015年1月份获得五块钱，还有一次获取15块钱，把结构化数据映射成一张表。

4、创建数据的文件，打开的 linux 本地，进行 vim 编辑，把结构化数据完整的复制进去，

A,2015-01 ,5

A,2015-01,15

B,2015-01 ,5

A,2015-01,8

B,2015-01 ,25

A,2015-01 ,5

A,2015-02,4

A,2015-02,6

B,2015-02,10

B,2015-02, 5

A,2015-03, 7

A,2015-03, 9

B,2015-03, 11

B,2015-03, 6

创建表，

create table t_ salary_ detail (username string ,month string,salary int)

row format delimited fields terminated by‘,’;

打开 hive 终端执行，使用 load 命令把数据文件加载到表中，为了验证方便进行验证，输入select * from t salary detail;查看表的数据是否映射成功，非常方便。

5、需求：统计每个用户每个月获得小费？

小费的计算是 salary 进行 sum 求和，a 用户和 b 用户要进行分组，每个用户根据月份分组。

分组维度：username month 的所有 b

select username , month, sum (salary) from t_ salary detail group by username , month ;

在每个组内进行求和，把所有小费都加起来，就是总的小费，哪个用户哪个月。

累计金额，用户到当前月累计获得多少小费，比如a用户1月份获得33累计获得33，a用户2月份获得十块钱，累计获得10加33，3月份a用户获得16累计获得43加16，59，字段是a用户的累计，同样B用户也要进行计算，b用户1月份获33累计33，2月份获得15累计获得48，3月份获得17累计获得48加17，65，如果先不考虑sql怎么写，要想得到截止到当月的累计金额，除当月的费用还要把当月往前的月份加起来，发现规律就是计算的某个指标除当前行的数据之外还跟之前行的数据相关，如果计算3月，除3月份还需要2月份1月份，这就是级联求和问题，针对这类问题，方向很明确，自己跟自己 join，如果不加过滤条件，总共会有36条记录，但是

a 和 b join 没有意义，所以在 join 时应该加条件，a.username= 另外的  username，左右两个表进行关联时，a 和 a 关联，b 和 b 关联才有意义。

6、把 sql 做嵌套查询起个名一个是 a 表，一个是 b 表，注意跟之前的 a 用户和 b 用户不一样，

将月总金额表，自己连接，自己连接

select A.*,B.* FROM

(select username , month, sum(salary) as salary from t_ salary detail group by username , month) A

inner join

(select username , month , sum (salary)

as salary from t_ salary detail group by use rname , month) B

on

A. Username=B. username

where B . month <= A . month

a和b根据username进行关联，结果有18条记录，按照第一条记录，当月的金额33，累计金额还是33，如果有两条记录，2月份的金额是10，累计金额就是10加33，如果有三条记录，3月份的累计金额就是33加10加16，规律是 b.month 小于等于 a.month 数据，加 where 条件进行过滤，执行。

通过第一行获得一月份，通过二三行获得二月份的，通过四五六行获得三月份的，如何让它们分在不同的组中，根据在每个分组内根据 b. salary 进行求和，就是累加金额，a. month 任选其一，就是当月的金额，三条 a 用户三月份，两条s用户二月份，再根据用户进行分组即可，相同用户的相同月进入同一分组中，在每个组内再进行 b. salary 进行求和得到累计金额，加 group by A.username ,A .

month 保证一月份的一组，二月份的一组，三月份的一组，在每个组内进行sum(B.salary) 可以得到累计金额，30加15加17就是 b 用户三月份的累计金额， max 取最大，都是17，任意一个都可以作为当月的，最后得到结果。

7、执行。

进行分组查询，分组的字段是 a.username a . month

求月累计值:将 b .month <= a . month 的所有 b. salary 求和即可

select A. username , A. month, max (A.salary) as salary, sum(B.salary) as accumulate

from

(select username , month, sum(salary) as salary from t_ salary_ detail group by username , month) A

inner join

(select username , month, sum(salary) as salary from t_ salary_ detail group by username , month) B

on

A. use rname=B . use rname

where B.month <= A. month

group by A.username ,A . month

order by A. username , A. month;

得到所描述的需求，当月的金额以及累计金额。

核心点不在于找规律，突破点在自己和自己 join。这就是 hive 中非常重要常见的级联求和问题，针对这类问题找准非常关键，当前行的指标数据计算需要除了自己这一行，还和之前的行有关，采用 join 自己和自己的方式解决。