# hive窗口函数/分析函数

## sum,avg,min,max 函数

``````建表语句:
create table bigdata_t1(
createtime string, --day
pv int
) row format delimited
fields terminated by ',';

load data local inpath '/root/hivedata/bigdata_t1.dat' into table bigdata_t1;

SET hive.exec.mode.local.auto=true;
``````

SUM函数和窗口函数的配合使用：结果和ORDER BY相关,默认为升序。

``````#pv1
sum(pv) over(partition by cookieid order by createtime) as pv1
from bigdata_t1;
#pv2
sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
from bigdata_t1;
#pv3
sum(pv) over(partition by cookieid) as pv3
from bigdata_t1;
#pv4
sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4
from bigdata_t1;
#pv5
sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5
from bigdata_t1;
#pv6
sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6
from bigdata_t1;
pv1: 分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号
pv2: 同pv1
pv4: 分组内当前行+往前3行，如，11号=10号+11号， 12号=10号+11号+12号，
13号=10号+11号+12号+13号， 14号=11号+12号+13号+14号
pv5: 分组内当前行+往前3行+往后1行，如，14号=11号+12号+13号+14号+15号=5+7+3+2+4=21
pv6: 分组内当前行+往后所有行，如，13号=13号+14号+15号+16号=3+2+4+4=13，
14号=14号+15号+16号=2+4+4=10
``````

preceding：往前

following：往后

current row：当前行

unbounded：起点

unbounded preceding 表示从前面的起点

unbounded following：表示到后面的终点

AVG，MIN，MAX，和SUM用法一样。

## row_number,rank,dense_rank,ntile 函数

``````cookie1,2018-04-10,1
CREATE TABLE bigdata_t2 (
createtime string, --day
pv INT
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
stored as textfile;

load data local inpath '/root/hivedata/bigdata_t2.dat' into table bigdata_t2;
``````
• ROW_NUMBER()使用

ROW_NUMBER()从1开始，按照顺序，生成分组内记录的序列。

``````SELECT
createtime,
pv,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn
FROM bigdata_t2;
``````
• RANK 和 DENSE_RANK使用

RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位 。

DENSE_RANK()生成数据项在分组中的排名，排名相等会在名次中不会留下空位。

``````SELECT
createtime,
pv,
RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3
FROM bigdata_t2
``````
• NTILE

有时会有这样的需求:如果数据排序后分为三部分，业务人员只关心其中的一部分，如何将这中间的三分之一数据拿出来呢?NTILE函数即可以满足。

ntile可以看成是：把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。

然后可以根据桶号，选取前或后 n分之几的数据。数据会完整展示出来，只是给相应的数据打标签；具体要取几分之几的数据，需要再嵌套一层根据标签取出。

``````SELECT
createtime,
pv,
NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,
NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2,
NTILE(4) OVER(ORDER BY createtime) AS rn3
FROM bigdata_t2
``````

# 其他一些窗口函数

• LAG

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
`````` SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,
LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time
FROM bigdata_t4;
last_1_time: 指定了往上第1行的值，default为'1970-01-01 00:00:00'
last_2_time: 指定了往上第2行的值，为指定默认值
``````

与LAG相反

第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

`````` SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
FROM bigdata_t4;
``````
• FIRST_VALUE

取分组内排序后，截止到当前行，第一个值

`````` SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1
FROM bigdata_t4;
``````
• LAST_VALUE

取分组内排序后，截止到当前行，最后一个值

`````` SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1
FROM bigdata_t4;
``````

`````` SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1,
FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2
FROM bigdata_t4
``````

`````` SELECT cookieid,
createtime,
url,
FIRST_VALUE(url) OVER(PARTITION BY cookieid) AS first2
FROM bigdata_t4;
``````

## cume_dist,percent_rank 函数

• 数据准备
`````` d1,user1,1000
d1,user2,2000
d1,user3,3000
d2,user4,4000
d2,user5,5000
CREATE EXTERNAL TABLE bigdata_t3 (
dept STRING,
userid string,
sal INT
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
stored as textfile;

load data local inpath '/root/hivedata/bigdata_t3.dat' into table bigdata_t3;
``````
• CUME_DIST 和order by的排序顺序有关系

CUME_DIST 小于等于当前值的行数/分组内总行数 order 默认顺序 正序 升序

比如，统计小于等于当前薪水的人数，所占总人数的比例

`````` SELECT
dept,
userid,
sal,
CUME_DIST() OVER(ORDER BY sal) AS rn1,
CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2
FROM bigdata_t3;
rn1: 没有partition,所有数据均为1组，总行数为5，

rn2: 按照部门分组，dpet=d1的行数为3,

``````
• PERCENT_RANK

PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1

`````` SELECT
dept,
userid,
sal,
PERCENT_RANK() OVER(ORDER BY sal) AS rn1, --分组内
RANK() OVER(ORDER BY sal) AS rn11, --分组内RANK值
SUM(1) OVER(PARTITION BY NULL) AS rn12, --分组内总行数
PERCENT_RANK() OVER(PARTITION BY dept ORDER BY sal) AS rn2
FROM bigdata_t3;
rn1: rn1 = (rn11-1) / (rn12-1)

rn2: 按照dept分组，
dept=d1的总行数为3

``````

## grouping sets,grouping__id,cube,rollup 函数

• 数据准备
`````` 2018-03,2018-03-10,cookie1
CREATE TABLE bigdata_t5 (
month STRING,
day STRING,
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
stored as textfile;

load data local inpath '/root/hivedata/bigdata_t5.dat' into table bigdata_t5;
``````
• GROUPING SETS

grouping sets是一种将多个group by 逻辑写在一个sql语句中的便利写法。

等价于将不同维度的GROUP BY结果集进行UNION ALL。

GROUPING__ID，表示结果属于哪一个分组集合。

`````` SELECT
month,
day,
GROUPING__ID
FROM bigdata_t5
GROUP BY month,day
GROUPING SETS (month,day)
ORDER BY GROUPING__ID;
grouping_id表示这一组结果属于哪个分组集合，

SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM bigdata_t5 GROUP BY month UNION ALL
SELECT NULL as month,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM bigdata_t5 GROUP BY day;
``````

`````` SELECT
month,
day,
GROUPING__ID
FROM bigdata_t5
GROUP BY month,day
GROUPING SETS (month,day,(month,day))
ORDER BY GROUPING__ID;

SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM bigdata_t5 GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM bigdata_t5 GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM bigdata_t5 GROUP BY month,day;
``````
• CUBE

根据GROUP BY的维度的所有组合进行聚合。

`````` SELECT
month,
day,
GROUPING__ID
FROM bigdata_t5
GROUP BY month,day
WITH CUBE
ORDER BY GROUPING__ID;

SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM bigdata_t5
UNION ALL
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM bigdata_t5 GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM bigdata_t5 GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM bigdata_t5 GROUP BY month,day;
``````
• ROLLUP

是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。

`````` 比如，以month维度进行层级聚合：
SELECT
month,
day,
GROUPING__ID
FROM bigdata_t5
GROUP BY month,day
WITH ROLLUP
ORDER BY GROUPING__ID;
--把month和day调换顺序，则以day维度进行层级聚合：
SELECT
day,
month,
GROUPING__ID
FROM bigdata_t5
GROUP BY day,month
WITH ROLLUP
ORDER BY GROUPING__ID;
（这里，根据天和月进行聚合，和根据天聚合结果一样，因为有父子关系，如果是其他维度组合的话，就会不一样）
``````

## hive窗口函数/分析函数详细剖析的更多相关文章

1. [Hive_10] Hive 的分析函数

0. 说明 Hive 的分析函数 窗口函数  | 排名函数 | 最大值 | 分层次 | lead && lag 统计活跃用户 | cume_dist 1. 窗口函数(开窗函数) ove ...

2. linux动态库编译和使用详细剖析 - 后续

引言 - 也许是修行 很久以前写过关于动态库科普文章, 废话反正是说了好多. 核心就是在 linux 上面玩了一下 dlopen : ) linux动态库编译和使用详细剖析 - https://www ...

3. Hive 窗口函数sum() over()求当前行和前面n条数据的和

4. Hive 窗口函数、分析函数

1 分析函数:用于等级.百分点.n分片等 Ntile 是Hive很强大的一个分析函数. 可以看成是:它把有序的数据集合 平均分配 到 指定的数量(num)个桶中, 将桶号分配给每一行.如果不能平均分配 ...

5. Hive 窗口分析函数

1.窗口函数 1.LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值 第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值 ...

6. Hive 窗口函数LEAD LAG FIRST_VALUE LAST_VALUE

窗口函数(window functions)对多行进行操作,并为查询中的每一行返回一个值. OVER()子句能将窗口函数与其他分析函数(analytical functions)和报告函数(repor ...

7. hive中分析函数window子句

hive中有些分析函数功能确实很强大,在和sum,max等聚合函数结合起来能实现不少功能. 直接上代码演示吧 原始数据 channel1 2016-11-10 1 channel1 2016-11-1 ...

8. linux动态库编译和使用详细剖析

引言 重点讲述linux上使用gcc编译动态库的一些操作.并且对其深入的案例分析.最后介绍一下动态库插件技术, 让代码向后兼容.关于linux上使用gcc基础编译, 预编译,编译,生成机械码最后链接输 ...

10. hive参数配置详细

hive.exec.mode.local.auto 决定 Hive 是否应该自动地根据输入文件大小,在本地运行(在GateWay运行) true hive.exec.mode.local.auto.i ...

## 随机推荐

1. 【转】linux内核中writesb(), writesw(), writesl() 宏函数

writesb(), writesw(), writesl() 宏函数 功能 : writesb()    I/O 上写入 8 位数据流数据 (1字节) writesw()   I/O  上写入 16 ...

2. 常用API——日期型函数Date

上图 ·声明 var myDate = new Date(); //系统当前时间 var myDate = new Date(yyyy, mm, dd, hh, mm, ss); var myDate ...

3. json loads No JSON object could be decoded 问题解决

今天自己用json的 dumps生成的 json 文本: f2=open(filename,'w')f2.write(json.dumps(c) f=open(filename,'r')r= f.re ...

5. Keepalived高可用集群介绍

1.Keepalived服务介绍 Keepalived起初是专为LVS设计的,专门用来监控LVS集群系统中各个服务节点的状态,后来又加入了VRRP的功能,因此除了配合LVS服务外,也可以为其他服务(n ...

6. Nuget程序包 使用log4net

Nuget程序包不用细介绍,网上资源很多,有个项目使用了log4net,为项目打log,功能很多,足够一般使用. 使用时候需要在配置文件中对其进行相关配置,我自己的配置文件放在App.config文件 ...

7. hibernate_validator_03

约束继承 如果要验证的对象继承于某个父类或者实现了某个接口,那么定义在父类或者接口中的约束会在验证这个对象的时候被自动加载,如同这些约束定义在这个对象所在的类中一样. 让我们来看看下面的示例: pac ...

8. 10_9 java笔记

java中所有的关键字都是小写的注意main虽然被编译器识别,但是它并不是关键字包:(名字小写) 单级包:liyi 多级包:cn.itcast path 和classpath的区别:path环境变量里 ...

9. drupal中使用drush命令，快速批量的开启和关闭模块

方法一: drush pml --no-core --type=module --status=enabled --pipe > modules.txt  xargs -a modules.tx ...

10. 机器学习实战笔记(1)——k-近邻算法

机器学习实战笔记(1) 1. 写在前面 近来感觉机器学习,深度学习神马的是越来越火了,从AlphaGo到Master,所谓的人工智能越来越NB,而我又是一个热爱新潮事物的人,于是也来凑个热闹学习学习. ...