hive函数（hive函数分为哪三类）

by intanet.cn ca 大数据 on 2024-03-18

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

本文目录一览：

hive内置函数有哪些

hive内置函数有：

1、在HIVE会话中add自定义函数的jar文件，然后创建function，继而使用函数。

2、在进入HIVE会话之前先自动执行创建function，不用用户手工创建。

3、把自定义的函数写坦猛到系统函数中，使之成为HIVE的一个默认函数。

关系函数

等值比较：=，语法：A=B。如果表达式A与表达式B相等，则为TRUE；否慧巧则为FALSE。

不等值比较：，语法：AB。如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A与表达式B不相等，则为TRUE；否则为FALSE。

小于比较：，语法：AB。如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A小于表达式B，则为TRUE；否则为FALSE。

小于等于比较：=，语法：A=B。如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A小于或者等于表达式B，则为TRUE；否则前信键为FALSE。

大于等于比较：=，语法：A=B。如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A大于或者等于表达式B，则为TRUE；否则为FALSE。

语法：A［NOT］LIKEB。如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合表达式B的正则语法，则为TRUE；否则为FALSE。

[img]

hive的高阶函数

1，如果让你计算5月21号这个日期前3天到后面3天的数据和？（不只是5月21号这一天，所有天的前3天加上后3天的和）

2，对于组内排序的顺序凯派问题？（态盯值相等的时候，排序也相等或是跳过）

需要使用hive的ROWS BETWEEN函数,也叫做帆孙和WINDOW函数

其中SUM、AVG、MIN、MAX。用于实现分组内所有和连续累积的统计。

如果不指定ROWS BETWEEN,默认为从起点到当前行;

如果不指定ORDER BY，则将分组内所有值累加;

关键是理解ROWS BETWEEN含义,也叫做WINDOW子句：

PRECEDING：往前

FOLLOWING：往后

CURRENT ROW：当前行

UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点

–其他AVG，MIN，MAX，和SUM用法一样。

以上内容转再来自

HiveSQL核心技能之常用函数

目标：

1、掌握hive基础语法、常用函数及其组合使用

2、掌握一些基本业务指标的分析思路与实现技巧

1）某次经营活动中，商家发起了“异性拼团购”，试着针姿肆对某个地区的用户进行推广，找出匹配用户。

注意：如果该表是一个分区表，则where条件中必须对分区字段进行限制

2）选出在2018年12月31日，购买的商品品类是food的用户名、购买数量、支付金额

3）试着对本公司2019年第一季度商品的热度与价值度进行分析。

"2019年一月到三月，每个品类有多少人购买，累计金额是多少"

GROUP BY ... HAVING（分类汇总过滤）

4）找出在2019年4月支付金额超过5万元的用户，给这些VIP用户赠送优惠券

5）2019年4月，支付金额最多的TOP5用户

查看Hive中的函数：show functions；

查看具体函数的用法：1）desc function 函数名；2）desc function extended函数名；

6）

7）用户的首次激活时间，与2019年5月1日的日期间隔。

datediff(string enddate,string startdate):结束日期减去开始日期的天数

拓展:日期增加函数（增加天数）、减少函数（减少天数） —— date_add、date_sub（类型要是string类型的）

date_add（string startdate,int days)、date_sub（string startdate,int days)

case when 函数

8）统计以下四个年龄段20岁以下、20-30岁、30-40岁、40岁以上的用户数

case when 的时候不用加group by，只有在聚合函数的时候才需要group by

if 函数

9）统计每个性别的用户等级高低分布情况（假设level大于5为高级，注意列名不能使用中文的）

10）分析每个月都拉新情况

substring(stringA,INT start,int len),substr(stringA,INT start,int len)，截取起始位置和截取长度

extra1需要解析json字段，然后用$.key取出想要获取的value;

extra2使用的是中括号加引号的方式进行字段提取和分组；

两种写法要看字段类型的不同采取不同的方式

11）求不同手机品牌的用户数

12）ELLA用户的2018年的平均每次支付金额，以及2018年最大的支付日期和最小的支付日期的间隔

13）2018年购买的商品品类在两个以上的用户数

步骤总结：

1、先求出每个人购买的商品品类书

2、筛选出购买商品品类书大于2的用户

3、统计符合条件的用户有多少个

14）用户激活时间在2018年，年龄段在20-30岁和30-40岁的婚姻情况分布

步骤总结：

1、先选出激活时间在2018年的用户，并把他们所在的年龄段计算好搭册笑，并提取出婚姻状况；

如何select后面没有进行聚合，则可以不用使用group by，直接使用where进行过滤就可以；

2、取出年龄段在20-30岁和30-40岁的知含用户，把他们的婚姻状况转义成可理解的说明；

3、聚合计算，针对年龄段，婚姻状况的聚合

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内置函数之时间函数

零、生产常用粗搏组合方式

（0.1）离线数仓获取昨天的日期作为分区，格式yyyyMMdd

regexp_replace(date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1) ,'-','')

或者

date_format(date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1),'yyyyMMdd')

一、源码部分

Hive的函数类为：org.apache.hadoop.hive.ql.exec.FunctionRegistry

二、常用时间函数

对于函数，除了知道怎么用，还需要知道返回值是什么类型，这里给出官方文档，文档中给出了函数的返回值类型

官方文档见：

（2.1）from_unixtime(bigint unixtime[, string format])

示例：

select from_unixtime(1591627588); -- 2020-06-08 22:46:28

select from_unixtime(1591627588,'yyyyMMddHHmmss'); -- 20200608224628

（2.2）unix_timestamp()、unix_timestamp(string date)、unix_timestamp(string date, string pattern)

示例：

select unix_timestamp('2020-06-08 22:50:00'); -- 1591627800

select unix_timestamp('20200608225000','yyyyMMddHHmmss'); -- 1591627800

（2.3）to_date(string timestamp)

示例：

SELECT to_date('2009-07-30 04:17:52'); -- 2009-07-30

（2.4）year(string date)、month(string date)、day(string date)、hour(string date)、minute(string date)、second(string date)

这些函数是差不多的，弯脊都是从一个时间字符串中抽取出某个特定的时间字段。具有相同功能的还有extract(field FROM source)函数

示例：

SELECT day('2009-07-29 20:30:40'); -- 29

SELECT minute('2009-07-29 20:30:40'); -- 30

（2.5）date_add(date/timestamp/string startdate, tinyint/smallint/int days)、date_sub(date/timestamp/string startdate, tinyint/smallint/埋凳渗int days)

这两个功能是类似的

示例：

SELECT date_add('2009-07-30 20:50:59', 1); -- 2009-07-31

（2.6）datediff(string enddate, string startdate)

截图中结果是错误的，应该为-1。

示例：

SELECT datediff('2009-06-30', '2009-07-02'); -- -2

SELECT datediff('2009-07-30', '2009-07-28'); -- 2

（2.7）current_date、current_timestamp

这两个函数使用desc function extended 查看会报错

示例：

（2.8）date_format(date/timestamp/string ts, string fmt)

示例：

SELECT date_format('2015-04-08', 'yyyyMMdd'); -- 20150408

Hive常用查询函数

函数说明：

NVL ：给值为 NULL 的数据赋值，

它的格式是

NVL( string1, replace_with)。

它的功能是如果string1 为 NULL，则 NVL 函数返回 replace_with 的值，否则返回 string1 的值，如果两个参数都为 NULL ，则返回NULL。

date_format:格式化时间

date_add:时间跟天数相加

date_sub:时间跟天数相减，类似与add，使用一个就ok

select date_sub('2019-06-29',5);

datediff:两个时间相减

select datediff('2019-06-29','2019-06-24');

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。

分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL， 返回值也将为 NULL。

这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

select concat_ws('-',str1,str2) from XX; //str为string类型的列名

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，

产生 array 类芹毁型字段。

练习：把星座和血型一样的人归类到一起

第一步转换：

白羊座,A 孙悟空

射手座,A 大海

白羊座,B 宋宋

白羊座,A 猪八戒

射手座,A 凤姐

合并销前

EXPLODE(col)：将hive 一嫌斗备列中复杂的 array 或者 map 结构拆分成多行。

LATERAL VIEW //侧写

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和 split, explode 等UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

练习：将电影分类中的数组数据展开

select explode(category) from movie_info;

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化；

/ 在over(里面使用) /

CURRENT ROW：当前行；

n PRECEDING：往前 n 行数据；

n FOLLOWING：往后 n 行数据；

UNBOUNDED：起点，

UNBOUNDED PRECEDING 表示从前面的起点，UNBOUNDEDFOLLOWING 表示到后面的终点；

/* 在over外面使用*/

LAG(col,n)：往前第n 行数据；

LEAD(col,n)：往后第 n 行数据；

NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从 1 开始，

对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

数据：

一、查询在 2017 年 4 月份购买过的顾客及总人数

group分组一组一个值，over给每一条数据独立开窗

查询顾客的购买明细及购买总额

要将 cost 按照日期进行累加

先排序

再累加

查询顾客上次的购买时间

下次的购买时间

查询前 20%时间的订单信息

RANK() 排序相同时会重复，总数不会变，可以并列

DENSE_RANK() 排序相同时会重复，总数会减少

ROW_NUMBER() 会根据顺序计算

练习：计算每门学科成绩排名

计算每门学科成绩排名

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

cpu核心数（cpu核心数8核是什么意思） 关于oraclecontinue的信息
相关阅读
最近发表
文章归档
标签列表