hive数据表之开窗函数

 

//1.1 启动hive服务

bin/hiveserver2

//1.2 创建hive数据表

create database test8;
use test8;
create table test8(
    cname string,
    cdate string,
    spend int
)row format delimited fields terminated by ",";

//1.3 将数据导入表中

load data local inpath '/root/data/spend.txt' into table test8;

//1.4 查询在2017年4份购买过的顾客及总人数

select * from test8;
with
a as (select count(0) ct from test8 where cdate like "2017-04%"),
b as (select cname from test8 where cdate like "2017-04%")
select a.ct,b.cname from a,b;

//1.5 查询顾客的购买明细及所以顾客的购买总额所有人的花费求和

select cname,cdate,spend,sum(spend)over() from test8 ;

//1.6 查询顾客的购买明细以及每位顾客的总花费按人分组求和

select cname,cdate,spend,sum(spend)over (partition by cname) from test8 ;

//1.7 查询顾客的购买明细及到目前为止每个顾客购买总金额按人分组,按时间排序,花费 累加

select  cname,cdate,spend,sum(spend)over (distribute by cname sort by cdate)from test8 ;