hive级联查询，
级联查询练习
建表：
create table t_access_times(name string,month string,salary int)
row format delimited
fields terminated by ',';
数据：
load data local initdb '/root/jilian_data.txt' into table t_access_times;
A,2017-01,34
A,2017-02,54
A,2017-03,74
B,2017-01,43
B,2017-02,45
B,2017-03,12
B,2017-04,190
C,2017-01,90
C,2017-02,78
C,2017-03,18
D,2017-03,12
D,2017-05,12
E,2017-02,120
E,2017-04,71
F,2017-01,10
F,2017-02,40
F,2017-03,12
G,2017-02,90
G,2017-04,120
目的 ：统计出每个人本月和前几个月的salary
1、
+----------------------+-----------------------+------------------------+--+
| t_access_times.name | t_access_times.month | t_access_times.salary |
+----------------------+-----------------------+------------------------+--+
| A | 2017-01 | 34 |
| A | 2017-02 | 54 |
| A | 2017-03 | 74 |
| B | 2017-01 | 43 |
| B | 2017-02 | 45 |
| B | 2017-03 | 12 |
| B | 2017-04 | 190 |
| C | 2017-01 | 90 |
| C | 2017-02 | 78 |
| C | 2017-03 | 18 |
| D | 2017-03 | 12 |
| D | 2017-05 | 12 |
| E | 2017-02 | 120 |
| E | 2017-04 | 71 |
| F | 2017-01 | 10 |
| F | 2017-02 | 40 |
| F | 2017-03 | 12 |
| G | 2017-02 | 90 |
| G | 2017-04 | 120 |
+----------------------+-----------------------+------------------------+--+
2、
create table jilian
as
select
a.name as aname,
a.month as amonth,
a.salary as salary,
b.name as bname,
b.month as bmonth,
b.salary as bsalary
from t_access_times a join t_access_times b
on a.name = b.name;
+---------------+----------------+----------------+---------------+----------------+-----------------+--+
| jilian.aname | jilian.amonth | jilian.salary | jilian.bname | jilian.bmonth | jilian.bsalary |
+---------------+----------------+----------------+---------------+----------------+-----------------+--+
| A | 2017-01 | 34 | A | 2017-01 | 34 |
| A | 2017-02 | 54 | A | 2017-01 | 34 |
| A | 2017-03 | 74 | A | 2017-01 | 34 |
| A | 2017-01 | 34 | A | 2017-02 | 54 |
| A | 2017-02 | 54 | A | 2017-02 | 54 |
| A | 2017-03 | 74 | A | 2017-02 | 54 |
| A | 2017-01 | 34 | A | 2017-03 | 74 |
| A | 2017-02 | 54 | A | 2017-03 | 74 |
| A | 2017-03 | 74 | A | 2017-03 | 74 |
| B | 2017-01 | 43 | B | 2017-01 | 43 |
| B | 2017-02 | 45 | B | 2017-01 | 43 |
| B | 2017-03 | 12 | B | 2017-01 | 43 |
| B | 2017-04 | 190 | B | 2017-01 | 43 |
| B | 2017-01 | 43 | B | 2017-02 | 45 |
| B | 2017-02 | 45 | B | 2017-02 | 45 |
| B | 2017-03 | 12 | B | 2017-02 | 45 |
| B | 2017-04 | 190 | B | 2017-02 | 45 |
| B | 2017-01 | 43 | B | 2017-03 | 12 |
| B | 2017-02 | 45 | B | 2017-03 | 12 |
| B | 2017-03 | 12 | B | 2017-03 | 12 |
| B | 2017-04 | 190 | B | 2017-03 | 12 |
| B | 2017-01 | 43 | B | 2017-04 | 190 |
| B | 2017-02 | 45 | B | 2017-04 | 190 |
| B | 2017-03 | 12 | B | 2017-04 | 190 |
| B | 2017-04 | 190 | B | 2017-04 | 190 |
| C | 2017-01 | 90 | C | 2017-01 | 90 |
| C | 2017-02 | 78 | C | 2017-01 | 90 |
| C | 2017-03 | 18 | C | 2017-01 | 90 |
| C | 2017-01 | 90 | C | 2017-02 | 78 |
| C | 2017-02 | 78 | C | 2017-02 | 78 |
| C | 2017-03 | 18 | C | 2017-02 | 78 |
| C | 2017-01 | 90 | C | 2017-03 | 18 |
| C | 2017-02 | 78 | C | 2017-03 | 18 |
| C | 2017-03 | 18 | C | 2017-03 | 18 |
| D | 2017-03 | 12 | D | 2017-03 | 12 |
| D | 2017-05 | 12 | D | 2017-03 | 12 |
| D | 2017-03 | 12 | D | 2017-05 | 12 |
| D | 2017-05 | 12 | D | 2017-05 | 12 |
| E | 2017-02 | 120 | E | 2017-02 | 120 |
| E | 2017-04 | 71 | E | 2017-02 | 120 |
| E | 2017-02 | 120 | E | 2017-04 | 71 |
| E | 2017-04 | 71 | E | 2017-04 | 71 |
| F | 2017-01 | 10 | F | 2017-01 | 10 |
| F | 2017-02 | 40 | F | 2017-01 | 10 |
| F | 2017-03 | 12 | F | 2017-01 | 10 |
| F | 2017-01 | 10 | F | 2017-02 | 40 |
| F | 2017-02 | 40 | F | 2017-02 | 40 |
| F | 2017-03 | 12 | F | 2017-02 | 40 |
| F | 2017-01 | 10 | F | 2017-03 | 12 |
| F | 2017-02 | 40 | F | 2017-03 | 12 |
| F | 2017-03 | 12 | F | 2017-03 | 12 |
| G | 2017-02 | 90 | G | 2017-02 | 90 |
| G | 2017-04 | 120 | G | 2017-02 | 90 |
| G | 2017-02 | 90 | G | 2017-04 | 120 |
| G | 2017-04 | 120 | G | 2017-04 | 120 |
+---------------+----------------+----------------+---------------+----------------+-----------------+--+
3、
create table final_t
as
select
aname,amonth,salary,sum(bsalary)as ji_salary
from jilian
where amonth >= bmonth
group by aname, amonth, salary;
+----------------+-----------------+-----------------+--------------------+--+
| final_t.aname | final_t.amonth | final_t.salary | final_t.ji_salary |
+----------------+-----------------+-----------------+--------------------+--+
| A | 2017-01 | 34 | 34 |
| A | 2017-02 | 54 | 88 |
| A | 2017-03 | 74 | 162 |
| B | 2017-01 | 43 | 43 |
| B | 2017-02 | 45 | 88 |
| B | 2017-03 | 12 | 100 |
| B | 2017-04 | 190 | 290 |
| C | 2017-01 | 90 | 90 |
| C | 2017-02 | 78 | 168 |
| C | 2017-03 | 18 | 186 |
| D | 2017-03 | 12 | 12 |
| D | 2017-05 | 12 | 24 |
| E | 2017-02 | 120 | 120 |
| E | 2017-04 | 71 | 191 |
| F | 2017-01 | 10 | 10 |
| F | 2017-02 | 40 | 50 |
| F | 2017-03 | 12 | 62 |
| G | 2017-02 | 90 | 90 |
| G | 2017-04 | 120 | 210 |
+----------------+-----------------+-----------------+--------------------+--+
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。