count与实际记录数不符 hive hive count over

hive 开窗函数

什么是开窗函数

我们想要对一组数据进行操作的时候，一般需要的是对数据进行聚合操作,例如通过group by，然后进行求解sum,count,avg等，这时候返回的是一个值，可以理解为多对一的关系，如果我买了想要获取需要聚合数据的多对多关系呢，那就需要开窗函数，为窗口中的每一行数据返回一个值。

具体来说就是：

窗口函数对一组行进行操作，并为基础查询中的每一行返回一个值。术语窗口描述函数操作的一组行。窗口函数使用窗口中行的值来计算返回值。

怎么定义窗口

在查询中使用窗口函数时，使用**over()**子句定义窗口。over()子句将窗口函数与其他分析和报告函数区分开来。查询可以包含具有相同或不同窗口定义的多个窗口函数。

• 开窗函数可以分为三类：

• 聚合类型
• 排序类型
• 值类型

聚合类型

聚合类的开窗函数有count(), avg(), max(), min(),sum()

下面仅以count举例，其他的类型

• count()
  返回一个bigint类型，基本用法和聚合函数里面的用法类似。
  假设有这么个需求，需要获取每个员工所在部门的人数:

select 
    name, dep_name, count(name) over(partition by dep_name) as count
from test;

如上面的查询语句，over定义了一个根据dep_nam分区的窗口，那么count的基础就是dep_name的集合，返回的就是每个部门的人数，当然不用开窗函数的话使用join也可以实现相同的功能。

over内支持partiion by order by， rows between * and *指定范围等

排序类型

排序类型的开窗函数有rank(), row_number(), dense_rank(), percent_rank(), ntile(), cume_dist()

• rank()
  返回的是一个bigint类型，是一个值基于over子句中order by 在一组中的排名，如果partition by存在的话，则是为每个分区中的每个值排名。排名可能不是连续的，如果有几个相等的排名，比如两个1，,则下一个排名就是3

select *, 
       rank() over() as rank1,
       --对所有员工年龄进行排序
       rank() over(order by age) as rank2,
       --对每个部门的员工年龄进行排序
       rank() over(partition by dep_name order by age) as rank3
from
    test;

可以看到如果over内不指定的话默认都是1，后面两列分别是根据所有员工和部门员工的年龄进行分类。

• dense_rank()
  和rank()的不同之处在于排名是连续的。

select name, dep_name, dense_rank() over(order by age) from test;

可以看到即使是有多个重复排名，下一位的排名也是连续的，多个1之后还是2

• percent_rank()
  顾名思义，计算给定行的百分比，计算公式是：(当前行的rank值-1)/(分组内的总行数-1)

select name,
       salary,
       percent_rank() over(order by salary),
       percent_rank() over(partition by dep_name order by salary)
from
    test

• row_number()
  从1开始对分区内的数据排序，和dense_rank不同的是同一分区相同的值具有不同的排序

select name, dep_name, age,
       row_number() over(partition by dep_name order by age) 
from test;

可以

• ntile()
  将分区中已排序的行划分为大小尽可能相等的指定数量的排名的组，并返回给定行所在的组的排名。

select name, age, ntile(3) over(order by age) from test;

将所有人按照年龄分为3组，每人对应的分组

• cume_dist()
  计算某个窗口或分区中某个值的累积分布

select name,
       cume_dist() over(order by age) as cume1,
       cume_dist() over(order by age asc) as cum2,
       cume_dist() over(order by age desc) as cum3
from test

可以看到默认是按照升序处理的

值类型开窗函数

有first_value(), last_value(), lag(),lead()

• first_value(), last_value()
  返回的是分区中第一个和最后一个值

select
    first_value(age) over(partition by dep_name),
    last_value(age) over(partition by dep_name order by age)
from
    test

• lag(), lead()
  用于统计窗口内往上/下第n个值
  参数: col: 基准列；n:向上/下第n行，default:如果值为null时的默认值

select name, lag(age, 3,10) over(partition by dep_name),
       lead(age, 10, 2) over()
from
    test