Hive窗口函数 over()

有以下数据：

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：
+----------------+---------------------&