hive之账单

现有以下格局的一份数据：

店铺，月份，金额
a,01,150
a,01,200
b,01,1000
b,01,800
c,01,250
c,01,220
b,01,6000
a,02,2000
a,02,3000
b,02,1000
b,02,1500
c,02,350
c,02,280
a,03,350
a,03,250

需求：编写Hive的HQL语句求出每一个店铺确当月出售额和累计到当月的总出售额

成果SQL ：


1、第一步：预备数据
create database if not exists exercise;
use exercise;
drop table if exists sale;
create table if not exists sale(name string, month string, amount int) row format delimited fields terminated by ",";
truncate table sale;
load data local inpath "/home/hadoop/sale.txt" into table sale;
select * from exercise.sale;


2、第二部份：一口吻完成求出终究成果的SQL
SELECT tl.NAME  ,tl.month  ,max(tl.amount) AS month_amount  ,sum(tr.amount) AS accumulate 
FROM 
(SELECT NAME ,month ,sum(amount) AS amount FROM sale GROUP BY NAME,month ) tl 
JOIN 
(SELECT NAME ,month ,sum(amount) AS amount FROM sale GROUP BY NAME,month ) tr 
ON tl.NAME = tr.NAME 
WHERE tl.month >= tr.month 
GROUP BY tl.NAME ,tl.month 
ORDER BY tl.NAME ,tl.month;


终究成果：
a       01      350     350
a       02      5000    5350
a       03      600     5950
b       01      7800    7800
b       02      2500    10300
c       01      470     470
c       02      630     1100现有以下格局的一份数据：

店铺，月份，金额
a,01,150
a,01,200
b,01,100