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 ;
