级联求和 当前的状态数据跟之前的状态数据相关的指标计算

级联求和 当前的状态数据跟之前的状态数据相关的指标计算

比如需求:需求中，存在当前的状态数据跟之前的状态数据相关的指标计算，解决方向就是自己跟自己join

因为当自join的时候 才会把当前的状态数据和之前的状态数据出现在一行中。
比如我们当前的需求：求 每个人根据月份 累计的薪水【就是 2月的累计薪水为 1+2月，3月的薪水=1+2+3月的薪水】
举例：
数据
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 ‘,’;

load data local inpath ‘/root/hivedata/t_salary_detail.dat’ into table t_salary_detail;

1、第一步，先求个用户的月总金额
select username,month,sum(salary) as salary from t_salary_detail group by username,month;

怎么得到下图需求呢？

我们用级联的方法 使用内连接 自己 join 自己 【因为当自join的时候 才会把当前的状态数据和之前的状态数据出现在一行中。】
SELECT
a., b.
FROM
(
SELECT
username,
month,
SUM(salary) total
FROM
t_salary_detail
GROUP BY
username,
month
) a
INNER JOIN (
SELECT
username,
month,
SUM(salary) total
FROM
t_salary_detail
GROUP BY
username,
month
) b ON a.username = b.username;

如上图你想到了什么？【group by a.username ,a.month where a.month >=b.month】
SELECT
a., b.
FROM
(
SELECT
username,
month,
SUM(salary) total
FROM
t_salary_detail
GROUP BY
username,
month
) a
INNER JOIN (
SELECT
username,
month,
SUM(salary) total
FROM
t_salary_detail
GROUP BY
username,
month
) b ON a.username = b.username
WHERE
a. MONTH >= b. MONTH;
就能得到 下图数据：

根据上图 你又想到了什么？【根据 sum(b.total) group by a.username,a.month 即可 结束】
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.username=B.username
where B.month <= A.month
group by A.username,A.month
order by A.username,A.month;