1 //1.1 启动hive服务 2 3 bin/hiveserver2 4 5 //1.2 创建hive数据表 6 7 create database test8; 8 use test8; 9 create table test8( 10 cname string, 11 cdate string, 12 spend int 13 )row format delimited fields terminated by ","; 14 15 //1.3 将数据导入表中 16 17 load data local inpath '/root/data/spend.txt' into table test8; 18 19 //1.4 查询在2017年4份购买过的顾客及总人数 20 21 select * from test8; 22 with 23 a as (select count(0) ct from test8 where cdate like "2017-04%"), 24 b as (select cname from test8 where cdate like "2017-04%") 25 select a.ct,b.cname from a,b; 26 27 //1.5 查询顾客的购买明细及所以顾客的购买总额所有人的花费求和 28 29 select cname,cdate,spend,sum(spend)over() from test8 ; 30 31 //1.6 查询顾客的购买明细以及每位顾客的总花费按人分组求和 32 33 select cname,cdate,spend,sum(spend)over (partition by cname) from test8 ; 34 35 //1.7 查询顾客的购买明细及到目前为止每个顾客购买总金额按人分组,按时间排序,花费 累加 36 37 select cname,cdate,spend,sum(spend)over (distribute by cname sort by cdate)from test8 ;