hive级联求和

create table t_salary_detail(username string,month string,salary int)
row format delimited fields terminated by ',';

load data local inpath '/export/servers/hivedatas/accumulate/t_salary_detail.dat' into table t_salary_detail;

用户	时间	收到小费金额
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


select username,month, sum(salary)
from  t_salary_detail
group  by  username,month




需求：统计每个用户每个月总共获得多少小费

select t.month,t.username,sum(salary) as salSum
from t_salary_detail t 
group by t.username,t.month;

+----------+-------------+---------+--+
| t.month  | t.username  | salsum  |
+----------+-------------+---------+--+
| 2015-01  | A           | 33      | 
| 2015-02  | A           | 10      | 
| 2015-03  | A           | 16      | 
| 2015-01  | B           | 30      | 
| 2015-02  | B           | 15      | 
| 2015-03  | B           | 17      | 
+----------+-------------+---------+--+


需求：统计每个用户累计小费

+----------+-------------+---------+--+
| t.month  | t.username  | salsum  | 累计小费
+----------+-------------+---------+--+
| 2015-01  | A           | 33      |  33
| 2015-02  | A           | 10      |  43
| 2015-03  | A           | 16      |  59
| 2015-01  | B           | 30      |  30
| 2015-02  | B           | 15      |  45
| 2015-03  | B           | 17      |  62
+----------+-------------+---------+--+



第一步：求每个用户的每个月的小费总和

select t.month,t.username,sum(salary) as salSum
from t_salary_detail t 
group by t.username,t.month;

+----------+-------------+---------+--+
| t.month  | t.username  | salsum  |
+----------+-------------+---------+--+
| 2015-01  | A           | 33      |
| 2015-02  | A           | 10      |
| 2015-03  | A           | 16      |
| 2015-01  | B           | 30      |
| 2015-02  | B           | 15      |
| 2015-03  | B           | 17      |
+----------+-------------+---------+--+




第二步：使用inner join 实现自己连接自己

select
A.* ,B.*
from
(select t.month,t.username,sum(salary) as salSum
from t_salary_detail t 
group by t.username,t.month) A
inner join 
(select t.month,t.username,sum(salary) as salSum
from t_salary_detail t 
group by t.username,t.month) B
on A.username = B.username;

+----------+-------------+-----------+----------+-------------+-----------+--+
| a.month  | a.username  | a.salsum  | b.month  | b.username  | b.salsum  |
+----------+-------------+-----------+----------+-------------+-----------+--+
取这一个作为一组
| 2015-01  | A           | 33        | 2015-01  | A           | 33        |

| 2015-01  | A           | 33        | 2015-02  | A           | 10        |
| 2015-01  | A           | 33        | 2015-03  | A           | 16        |
取这两个作为一组
| 2015-02  | A           | 10        | 2015-01  | A           | 33        |
| 2015-02  | A           | 10        | 2015-02  | A           | 10        |

| 2015-02  | A           | 10        | 2015-03  | A           | 16        |
取这三个作为一组
| 2015-03  | A           | 16        | 2015-01  | A           | 33        |
| 2015-03  | A           | 16        | 2015-02  | A           | 10        |
| 2015-03  | A           | 16        | 2015-03  | A           | 16        |


| 2015-01  | B           | 30        | 2015-01  | B           | 30        |
| 2015-01  | B           | 30        | 2015-02  | B           | 15        |
| 2015-01  | B           | 30        | 2015-03  | B           | 17        |
| 2015-02  | B           | 15        | 2015-01  | B           | 30        |
| 2015-02  | B           | 15        | 2015-02  | B           | 15        |
| 2015-02  | B           | 15        | 2015-03  | B           | 17        |
| 2015-03  | B           | 17        | 2015-01  | B           | 30        |
| 2015-03  | B           | 17        | 2015-02  | B           | 15        |
| 2015-03  | B           | 17        | 2015-03  | B           | 17        |
+----------+-------------+-----------+----------+-------------+-----------+--+


每一步相对于上一步的结果


加参数继续变形
select
A.* ,B.*
from
(select t.month,t.username,sum(salary) as salSum
from t_salary_detail t 
group by t.username,t.month) A
inner join 
(select t.month,t.username,sum(salary) as salSum
from t_salary_detail t 
group by t.username,t.month) B
on A.username = B.username
where B.month <= A.month;

+----------+-------------+-----------+----------+-------------+-----------+--+
| a.month  | a.username  | a.salsum  | b.month  | b.username  | b.salsum  |
+----------+-------------+-----------+----------+-------------+-----------+--+
| 2015-01  | A           | 33        | 2015-01  | A           | 33        |   33

| 2015-02  | A           | 10        | 2015-01  | A           | 33        |   43
| 2015-02  | A           | 10        | 2015-02  | A           | 10        |

| 2015-03  | A           | 16        | 2015-01  | A           | 33        |   59
| 2015-03  | A           | 16        | 2015-02  | A           | 10        |
| 2015-03  | A           | 16        | 2015-03  | A           | 16        |

| 2015-01  | B           | 30        | 2015-01  | B           | 30        |    30

| 2015-02  | B           | 15        | 2015-01  | B           | 30        |   45
| 2015-02  | B           | 15        | 2015-02  | B           | 15        |

| 2015-03  | B           | 17        | 2015-01  | B           | 30        |   62
| 2015-03  | B           | 17        | 2015-02  | B           | 15        |
| 2015-03  | B           | 17        | 2015-03  | B           | 17        |
+----------+-------------+-----------+----------+-------------+-----------+--+



第三步：从第二步的结果中继续通过a.month与a.username进行分组,并对分组后的b.salsum进行累加求和即可

select
A.username,A.month,max(A.salSum),sum(B.salSum) as accumulate
from
(select t.month,t.username,sum(salary) as salSum from t_salary_detail t group by t.username,t.month) A
inner join 
(select t.month,t.username,sum(salary) as salSum from t_salary_detail t group by t.username,t.month) B
on A.username = B.username
where B.month <= A.month
group by A.username,A.month
order by A.username,A.month;

累计的消费进行求和
+-------------+----------+------+-------------+--+
| a.username  | a.month  | _c2  | accumulate  |
+-------------+----------+------+-------------+--+
| A           | 2015-01  | 33   | 33          |
| A           | 2015-02  | 10   | 43          |
| A           | 2015-03  | 16   | 59          |
| B           | 2015-01  | 30   | 30          |
| B           | 2015-02  | 15   | 45          |
| B           | 2015-03  | 17   | 62          |
+-------------+----------+------+-------------+--+