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

来源：互联网发布：js append html字符串编辑：程序博客网时间：2024/06/05 10:46

首先参考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 ;

0 0