hive sum函数的顶级应用(配合开窗函数OVER)

首先参考http://jingyan.baidu.com/article/8cdccae969e758315413cd1e.html

(可能需要使用hdfs用户,运行hive命令)


create table tmp.hive_sum(id string COMMENT '会员ID',bank_name string COMMENT '银行名称',create_time string COMMENT '交易时间',amount double COMMENT '交易金额') COMMENT 'hive_sum顶级应用' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';


load data  inpath '/test/input/hive_sum.txt' overwrite into table tmp.hive_sum;


select id,bank_name,create_time,amount,sum(amount) over(partition by id) amount_all  from hive_sum order by id,bank_name,create_time;


select id,bank_name,create_time,amount,sum(amount) over(partition by id) amount_all  from hive_sum order by amount_all;




select id,bank_name,create_time,amount,sum(amount) over(partition by id,bank_name order by create_time asc ) amount_all from tmp.hive_sum order by id,bank_name,create_time asc;


select id,create_time,  amount,sum(amount) over(partition by id order by create_time asc rows between 2 preceding and 2 following ) amount_all from tmp.hive_sum order by id, create_time asc;


select id,bank_name,sum(amount)  amount_all from tmp.hive_sum group by id,bank_name with rollup order by id, bank_name desc ;