hive 无法count 表数据量太多 hive count over

开窗函数的引出：在写sql语句查询时经常会用到聚合函数，也就是sum(),count(),max(),avg()等函数，这些函数可以将多行数据聚集成一行，所以也就是最后查询的结果的行数少于聚集前的行数，但是这并不能满足我们所要的某些查询，于是开窗函数就被引出了，相对于聚合函数而言开窗函数是对于每个组返回多行(而聚合函数则是多行数据返回一行值)，组内的每一行数据都相当于一个窗口。

1：窗口函数的相关函数
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化；
CURRENT ROW：当前行；
n PRECEDING：往前n行数据；
n FOLLOWING：往后n行数据；
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点；
LAG(col,n)：往前第n行数据；
LEAD(col,n)：往后第n行数据；
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

2.订单表数据准备：name，orderdate，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
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

3.（1）查询在2017年4月份购买过的顾客及总人数
（2）查询顾客的购买明细及月购买总额
（3）上述的场景,要将cost按照日期进行累加
（4）查询顾客上次的购买时间
（5）查询前20%时间的订单信息

1)查询在2017年4月份购买过的顾客及总人数，此时的over函数内并没有限制条件，所以每个数据都是可见的。

在没有加over窗口函数之前我们试着来查询一下

select name,count(*) 
from business
where substring(orderdate,1,7) = '2017-04'
group by name;

但是这个sql只能统计具体那个人在四月份下单的次数，并不是我们期望的总人数。所以加上下面的over窗口函数在来试试。

select name,count(*) over ()
from business
where substring(orderdate,1,7) = '2017-04'
group by name;

这里查询的结果正是我们预期的

2）查询顾客的购买明细及月购买总额（这里用month函数先把月份提取出来，然后在对月份进行分区开窗）

select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from
 business;

3）上述的场景,要将cost按照日期进行累加
这里就用用到窗口函数相关的行了

select name,orderdate,cost,
sum(cost) over() as sample1,
sum(cost) over(partition by name) as sample2,
sum(cost) over(partition by name order by orderdate) as sample3,
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5,
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 
from business;

查询结果如下：不过最重要的基本上还是sample中的partition by ，order by

这里解释一下sample到sample7的意思：
sample1:所有行相加
sample2:按name分组，组内数据相加
sample3:按name分组并按订单日期排序，组内数据累加
sample4:和sample3一样,由起点到当前行的聚合
sample5:当前行和前面一行做聚合
sample6:当前行和前边一行及后面一行做聚合
sample7:当前行及后面所有行

4）查询顾客上一次的购买时间，这里就要用到LAG(col,n)函数了，他的意思是往前第n行数据。

select name,orderdate,cost,lag(orderdate,1) over(distribute  by name sort  by orderdate) from business;

扩展：查询顾客下一次的购买时间

select name,orderdate,cost,lead(orderdate,1) over(distribute by name sort by orderdate) from business;
注：LEAD(col,n)：往后第n行数据；

这里强调一下，LAG(col,n) 还可以这样写LAG(col,n,“str”)，因为我们在第一次购物之前是没有记录的，所以可以用个字符串来替换一下null值。LEAD(col,n)同上。

5）查询前20%时间的订单信息
这里我们先解释一下NTILE(n)：函数，类似于我们按将数据按照某个字段的分区和排序结束以后他会给最后一行给各个组编号，加入我们对个个字段进行排序后有10行不同的数据，如果n值设置为3，那么最后的结果将分为4个编号 ，1-3行数据编号都为1，4-6行数据编号都为1，7-9行数据编号都为3，最后一行数据编号为4.

1.我们先按订单日期将数据进行排序

select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
    from business;

可以看到一共有14行数据，我们把n设为5，所以每3个为一个编号，最后两个为一个编号。

2.现在就可以在表1的基础上将编号为1的数据截取出来，这也即是我们想要的前%20时间的订单信息。

select * from (
    select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
    from business
) t
where sorted = 1;

总结：
1.over函数的括号内不能写group by
2.开窗指的是对每一条数据都开了窗口，和group最本质的区别是group分组以后的数据为一组一个值，但是over是给每一条数据独立的开窗的(具体更详细的规则还要看over函数内的限定条件)
3.partition by要和order by连用 distribute by 要和sort by 连用，两者的效果一样，但是决不能混开用。