有以下数据:
Jack,2017-01-01,10
Tony,2017-01-02,15
Jack,2017-02-03,23
Tony,2017-01-04,29
Jack,2017-01-05,46
Jack,2017-04-06,42
Tony,2017-01-07,50
Jack,2017-01-08,55
Mark,2017-04-08,62
Mart,2017-04-09,68
Meil,2017-05-10,12
Mart,2017-04-11,75
Meil,2017-06-12,80
Mart,2017-04-13,94
需求:
1、查询2017-04购买的顾客总人数
2、顾客购买明细及月份总额
3、上述场景,将cost按日期累加
4、查询顾客上次购买时间
5、查询前20%购买的订单信息
一、建表并导入数据:
-- 建表
create table business(
name string,
orderdate string,
cost int)
row format delimited
fields terminated by ",";
--导入数据
load data local inpath "/usr/local/src/test4/hive/business.txt" into table business;
查询表:
二、需求分析
1、查询2017-04购买的顾客总人数
a、首先想到使用聚合函数count()
-- 先求出2017-04这月一共有多少条记录
select count(*) from business where substr(orderdate,1,7) = "2017-04";
res:
+------+--+
| _c0 |
+------+--+
| 5 |
+------+--+
b、现在按照顾客进行分组
select name,count(*) from business where substr(orderdate,1,7) = "2017-04" group by name;
res:
+-------+------+--+
| name | _c1 |
+-------+------+--+
| Jack | 1 |
| Mark | 1 |
| Mart | 3 |
+-------+------+--+
数据被分成了三组:
使用over()函数:over只对聚合函数起作用,count分别对上面三个组内进行计数,over统计一共有多少个组(有一个count进行累加一次)
select name,count(*) over() total_num from business where substr(orderdate,1,7) = "2017-04" group by name;
res:
+-------+------------+--+
| name | total_num |
+-------+------------+--+
| Mart | 3 |
| Mark | 3 |
| Jack | 3 |
+-------+------------+--+
2.查询顾客购买明细及月份总额
a、首先选出所有明细信息:
select * from business;
res:
+----------------+---------------------+----------------+--+
| business.name | business.orderdate | business.cost |
+----------------+---------------------+----------------+--+
| Jack | 2017-01-01 | 10 |
| Tony | 2017-01-02 | 15 |
| Jack | 2017-02-03 | 23 |
| Tony | 2017-01-04 | 29 |
| Jack | 2017-01-05 | 46 |
| Jack | 2017-04-06 | 42 |
| Tony | 2017-01-07 | 50 |
| Jack | 2017-01-08 | 55 |
| Mark | 2017-04-08 | 62 |
| Mart | 2017-04-09 | 68 |
| Meil | 2017-05-10 | 12 |
| Mart | 2017-04-11 | 75 |
| Meil | 2017-06-12 | 80 |
| Mart | 2017-04-13 | 94 |
+----------------+---------------------+----------------+--+
b、求总额:(这是所有数据的总和,因为没有分组(group by),所以over()的针对的是每一条数据)
select *, sum(cost) over() from business;
res:
+----------------+---------------------&