hivelag（hivelag函数）

本篇文章给大家谈谈hivelag，以及hivelag函数对应的知识点，希望对各位有所帮助，不要忘了收藏本站喔。

本文目录一览：

• 1、hive窗口函数总结
• 2、Hive 学习总结
• 3、Hive开窗函数
• 4、hive关于窗口函数的使用

hive窗口函数总结

根据官网的介绍，hive推出的窗口函数功能是对hive sql的功能增强，确实目前用于离线数据分析逻辑日趋复杂，很多场景都需要用到。以下就是对hive窗口函数的一个总结附上案例。

PRECEDING：往前

FOLLOWING：往后

CURRENT ROW：当前行

UNBOUNDED：起点（一般结合PRECEDING，FOLLOWING使用）

UNBOUNDED PRECEDING 表示该窗口最前面的行（起点）

UNBOUNDED FOLLOWING：表示该窗口最后面的行（终点）

比如说：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示从起点到当前行）

ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING（表示往前2行到往后1行）

ROWS BETWEEN 2 PRECEDING AND 1 CURRENT ROW（表示往前2行到当前行）

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING（表示当前行到终点）

官网有一段话列出了哪些窗口函数是不支持window子句的，如下图所示：

1.LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值第一个参数为列名，第二个参数为往下第n行（可选，默认为1，不可为负数），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

2.LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值第一个参数为列名，第二个参数为往上第n行（可选，默认为1，不可为负数），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

3.FIRST_VALUE取分组内排序局宏后，截止到当前行，第一个值，这最多需要两个参数。第一个参数是您想要第一个值的列，第二个（可选桐信册）参数必须是false默认为布尔值的布尔值。如果设置为true，则跳过空值。

4.LAST_VALUE取分组内排序后，截止到当前行,最后一个值，这最多需要两个参数。第一个参数是您想要第一个值的列，第二个（可选）参数必须是false默认为布尔值的布尔值。如果设置为true，则跳过空值。

让我们加上window子句来观察一下变化，虽然FIRST_VALUE和LAST_VALUE不常于与window子句结合使用。

1.COUNT

2.SUM

3.MIN

4.MAX

5.AVG

目前支持这五种带有聚合意义的窗口函数，以常用SUM举例。

从结果当中其实可以得到结论，默认情况就是从起点到当前行，不带order by语句其实就是表示窗口内全部行都参与聚合处理，这里其实还有其他用法，读者坦枯可以自行尝试一下。

1.ROW_NUMBER

从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列;通常用于获取分组内排序第一的记录;获取一个session中的第一条refer等。

2.RANK

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

3.DENSE_RANK

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

4.CUME_DIST

CUME_DIST 小于等于当前值的行数/分组内总行数

5.PERCENT_RANK

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

6.NTILE

NTILE(n) 用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。NTILE不支持ROWS BETWEEN

以上是带有分析功能的窗口函数，使用的频率没有上面两类高，但是也是需要掌握的。

我们先对1-3三种分析窗口函数进行演示

第4-5种：

第六种：NTILE

Hive 学习总结

    这一周主要学习了 Hive 的一些基础知识，学习了多个 Hive 窗口函数，虽然感觉这些窗口函数没有实际的应用意义，但还是都了解了一下。

：可以实现在窗口中实现逐行累加

其他 avg、count、min、max 的用法一样

#要先有一个统计出每个月总额的表，这里就是 t_access_amount 表，如下图一

# partition by uid：根据uid 分组，order by month ：根据月份排序，

rows between unbounded preceding and current_row：选择 无边界的前面的行和当前行之间的行，最后是求 sum 即和。得到下图二

# 是在窗口求和，而窗口的定义时按照 uid 分区 order by 排序得到的。得到一个字段

preceding：前面的，后来的，往序号变大的方向

following：往后

2 preceding ：表示前2行

3 following ：表示后3行

unbounded preceding：表示从第1行开始，启隐野从前面的起点

unbounded following：表示最后一行，从后面的终点

注意：使用 rows between 时，按order by 顺序编号（没有指定order by 会默认排序）需要左边是我号右边是大编号

注意：上面显示的排序结果不太对，所以看起来好像结果是相反的一样，实际上单独拿出来运行是对的，可以看以相同方式排序的 rn 编号。

注意：这上面这些都不支持使用 rows between 语句，row_number() 展示出来的排序会和查询字段的最后一个 over(order by)里面的顺序一样

：分组 TOPN，即可以分组后排序，便于找到最好的几条数据

eg：有如下数据，要查出每种性别中年龄最大的2条数据

1,18，a，male

2,19，b，male

3,22，c，female

4，16，d，female

5，30，e，male

6，26，f，female

分析：如果使用按性别分组，是可以分出两条数据，但是分组的缺点是只能查出聚合函数（聚合函数只能产生一组中的一个值）和分组依悄喊据。而这里是要求多条数据（2个）

：用于将分组数据按照顺序切分成 n 片（不一定是平均），然后每一份都编号为1-n，这样就可以拿到想要那一份数据。如果切片不均匀，默认增加第一个切片的分布，例如，14 条记录切3片就切不好，就会切成 6、4、4，其中6那份编号为1。

注意：可以不指定 order by

应用：

    统计各个 cookieid，pv 数最多的前1/3天的数据

：生成数据项再分组中的排名，排名相等会在名次中留下空位

：生成数据项再分组中的排名，排名相等不会再名次中留下空位

注意：上面携誉两者都需要指定 order by，不然排名都是1

根据上图，可知区别：

    rank()：按顺序编号，相同分组排序有相同的排名，但是会占位，后面的排名隔一位，就是成绩一样名次相同，但是后面的名次得低两位。

    dense_rank()：按顺序编号，相同分组排序有相同的排名，后面排名顺序不边就是有并列第几名的情况。

    row_number() ：按顺序编号，不会有相同的编号，即使分组排序是相同的。

：小于等于当前值的行数/分组内总行数，注意这个要指定排序方式，不然全都是1

：分组内当前行的（rank 值-1）/（分组内总行数-1）

总结：排序、切片、编号、的都需要使用 order by，不然会全都是1，但是除了 row_number() 因为这个函数编号不重复且顺延，所以还是会有编号，但是不确定编号逻辑。

：用于获得窗口内往上第n行的值，n=0

第一个参数为列名

第二个参数为往上第n行（可选，默认为1）

第三个参数为默认值（当往上第n行为NULL时，取默认值，如不指定，则为NULL）

：与lag 相反，用于获取窗口内往下第n 行的值，n=0

第一个参数为列名

第二个参数为往上第n行（可选，默认为1）

第三个参数为默认值（当往上第n行为NULL时，取默认值，如不指定，则为NULL）

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

：取分组内排序后，第一个值

注意：使用窗口分析函数时，要特别注意 order by 的使用，如果使用的不恰当会导致统计的不是我们想要的。 row_number() over() 的展示排序好像时根据最后一个字段的over（order by）来展示的 。

这几个分析函数通常用于 olap 中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如分 时、分、天、月的 UV 数

：表示结果属于哪一个分组集合，注意中间是两个下划线

第一列时按照 month 进行分组的，

第二列时按照 day 进行分组的

第三列时按照 对应month、day 分组统计出来的结果

第四列 grouping__id 表示这一组结果属于哪个分组集合

注意：grouping sets 里面就是说明以什么分组，上面的group by 是指定可以进行组合的分组字段sets 里面的只能使用这里指定的字段，

如，这里 grouping sets(month , day) 表示分别根据 month、day 字段分组，               grouping sets(month,day,(month,day)) 则表示分别根据 month、day、month和day 分组。  

这里group by 和 grouping sets 可以搭配使用，不是这里的专属，并且这里也可以不用 groupind sets

：根据group by 的维度的所有组合进行聚合，就是省略 grouping sets ，直接将group by 后面的字段以各种可能的形式分组，然后union all 得到结果。

等价于下面的语句

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

上面可以实现一个叫上钻的效果：

    月天的uv==》月的uv==》总的uv

[img]

Hive开窗函数

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

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

lead(col, n, default)：用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为null时，取默认值，如不指定则为null）；

lag(col, n, default)：与lead相反，用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为null时，取默认值，如不指定，则为null）。

1）使用标准的聚合函数 count、sum、min、max、avg

2）使用 partition by 语句，使用一个或多个原始列

3）使用 partition by 与 order by 语句，使用一个或多个分区或者排序列

4）使用窗口规范，窗口规范支持以下格式：

当 ORDER BY 后面神耐缺少窗口从句条件，窗口规范默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW .

当 ORDER BY 和窗口从句都缺失, 窗口规范默认是 ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING .

OVER 从句高枝支持以下函数， 但是并不支持和窗口一起使用它们。

Ranking 函数: Rank, NTile, DenseRank, CumeDist, PercentRank .

Lead 和 Lag 函数.

row_number() ：从1开始，按照顺序生成组内记录的序列，比如按照pv降序排列生成分组内的pv排名；获取分组内的top1记录；获取一个session内的第一条记录等等。

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

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

cume_dist ：小于等于当前值的行数/分组内总行数。比如，统计小于等于当前薪资的人数占总人数的比例。

percent_rank ： (分组内当前行的rank值-1)/(分组内总行数-1)。

ntile(n) ：用于将分组数据按照顺序切游念春分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。 ntile 不支持 rows between ，比如 ntile(2) over(partition by cookieied order by createtime rows between 3 preceding and current row) 。

4、测试数据集

hive关于窗口函数的使用

窗口函数可以理解为给数据划到一个窗口内并排上序号。

over()即那个窗口函数，需要搭配其他函数进行分析

over()括号内部可以加上partiton by进行分组，加上order by进行排序。不加上partition by是对所有数据开了一个窗，加上察洞partition by是对分组内部销神所有数据进行开窗。加上order by对分组内数据按排序进行开窗。

形如over(partition by class order by score)，这个会对class字段进行分组，然后在分组的内部基于score进行排序。

此外还可以给窗口计算加行范围，over(rows between 开始位置 and 结束位置),开始位置和结束位置可以填的包括

PRECEDING：往前

FOLLOWING：往后

CURRENT ROW：当前行

UNBOUNDED PRECEDING：窗口的起点

UNBOUNDED FOLLOWING：窗口的终点

形如：over(partition by class order by score rows between UNBOUNDED PRECEDING and 1 PRECEDING)，这个会对class字段进行分组，然后在排序后从窗口第一个值取到当前行前一个值

先假设一个总分表score_table有姓名name、班级class、总分score三个字段

row_number()、rank()、dense_rank()

对各个班级内部进行总分排名

对排名连续无相同名次，比如有三个分数98、98、97，排名为1、2、3，使用row_number()

形如sql：

select *, row_number() over(partition by class order by score desc) as num from score_table;

对排名不连续有相同名次，比如有三个分数98、98、97，排名为1、1、3，使用rank()

形如sql：

select *, rank() over(partition by class order by score desc) as num from score_table;

对排名连续有相同名次亏没亏，比如有三个分数98、98、97，排名为1、1、2使用dense_rank()

形如sql：

select *,dense_rank() over(partition by class order by score desc) as num from score_table;

avg(列名)、sum(列名)、max(列名)、min(列名)

计算排挤内前各个名次的平均分

形如sql：

select *, avg(score) over(partition by class order by score desc) as num from score_table;

ntile(n)函数

把窗口内按班级分组后的数据按先成绩排名后分成3份，分成1、2、3组

形如sql：

select *, ntile(3) over(partition by class order by score desc) as num from score_table;

lag(列名,往前的行,默认值)

把窗口内按班级分组后的数据，取该分组内每一行数据的前一名同学的成绩，如果第一名就显示100；

形如sql：

select *, lag(score,1,100) over(partition by class order by score desc) as num from score_table;

lead(列名,往后的行,默认值)

把窗口内按班级分组后的数据，取该分组内每一行数据的后一名同学的成绩，如果最后一名就显示0；

形如sql：

select *, lag(score,1,0) over(partition by class order by score desc) as num from score_table;

first_value(列名)和last_value(列名)

把窗口内按班级分组后的数据，取该分组内第一名同学的成绩

形如sql：select *, first_value(score) over(partition by class order by score desc) as num from score_table;

把窗口内按班级分组后的数据，取该分组内最后一名同学的成绩

形如sql：select *, last_value(score) over(partition by class order by score desc) as num from score_table;

总结：

窗口函数的使用大致遵循以下

函数名字(arg1, arg2, ... argN) OVER( [PARTITION BY 分组列] [ORDER BY 排序列] [计算的行范围] )

本文举的函数有row_number()、rank()、dense_rank()、avg(列名)、sum(列名)、max(列名)、min(列名)、ntile(n)、lag(列名,往前的行,默认值)、lead(列名,往后的行,默认值)、first_value(列名)和last_value(列名)

关于hivelag和hivelag函数的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站。