Hive_04 使用sql进行增量结合历史数据分析

转载自：https://blog.csdn.net/qq_32641659/article/details/89435726

需求

已知用户的月度点击次数信息，如下图，第一列为用户名称，第二列为月份，第三列为该月用户点击次数。要求扩充维度，每行增加两列信息，包括目前最大点击次数和目前总点击次数。

Hive SQL的统计分析

• 创建月度点击统计表

CREATE  TABLE use_click_month(
use_name string,
date_month string,
count int
)row format delimited fields terminated by ',';

• 加载数据

LOAD DATA LOCAL INPATH '/home/hadoop/data/click/click.log' OVERWRITE INTO TABLE use_click_month ;

• 方法一：使用开窗函数进行数据分析

select use_name,date_month,count,
max(count) over(partition by use_name order by date_month) as maxcount,
sum(count) over(partition by use_name order by date_month) as sumcount
from use_click_month;
# 执行结果
A	201807	19	19	19
A	201808	13	19	32
A	201809	15	19	47
A	201810	34	34	81
A	201811	40	40	121
A	201812	39	40	160
B	201807	12	12	12
B	201808	45	45	57
B	201809	68	68	125
B	201810	19	68	144
B	201811	22	68	166
B	201812	35	68	201
C	201807	11	11	11
C	201808	12	12	23
C	201809	16	16	39
C	201810	19	19	58
C	201811	19	19	77
C	201812	9	19	86
D	201807	13	13	13
D	201808	9	13	22
D	201809	18	18	40
D	201810	26	26	66
D	201811	24	26	90
D	201812	20	26	110
E	201807	7	7	7
E	201808	8	8	15
E	201809	9	9	24
E	201810	3	9	27
E	201811	100	100	127
E	201812	50	100	177

• 使用join进行数据分析

select t.a_name,t.a_date_month,t.a_count,max(b_count),sum(b_count)
from (
select a.use_name as a_name,a.date_month a_date_month,a.count a_count,
b.use_name as b_name,b.date_month b_date_month,b.count b_count
from use_click_month a join use_click_month b
where a.use_name=b.use_name and b.date_month <= a.date_month ) t
group by t.a_name,t.a_date_month,t.a_count order by  t.a_name,t.a_date_month asc;
#执行结果
A	201807	19	19	19
A	201808	13	19	32
A	201809	15	19	47
A	201810	34	34	81
A	201811	40	40	121
A	201812	39	40	160
B	201807	12	12	12
B	201808	45	45	57
B	201809	68	68	125
B	201810	19	68	144
B	201811	22	68	166
B	201812	35	68	201
C	201807	11	11	11
C	201808	12	12	23
C	201809	16	16	39
C	201810	19	19	58
C	201811	19	19	77
C	201812	9	19	86
D	201807	13	13	13
D	201808	9	13	22
D	201809	18	18	40
D	201810	26	26	66
D	201811	24	26	90
D	201812	20	26	110
E	201807	7	7	7
E	201808	8	8	15
E	201809	9	9	24
E	201810	3	9	27
E	201811	100	100	127
E	201812	50	100	177

注意：生产上更倾向使用 join ，尽量避免使用开窗函数进行数据分析，因为开窗函数并不能很好的去控制数据shuffle过程中导致数据倾斜的问题。