Hive函数大全

目录

第四章 Hive函数

4.1 聚合函数

4.2 关系函数

4.3 数学运算

4.4 逻辑运算

4.5 数值运算

4.6 条件函数

4.7 日期函数

4.8 字符串函数

4.9 字符串截取函数 

4.10 去空格函数

4.11 正则表达式与解析函数

4.12 explode函数

4.13 行转列与列转行

4.14 基础窗口函数与分析函数

4.14.1 窗口函数简介

4.14.2 窗口的含义

4.14.3 窗口函数分类

4.14.4 窗口函数的使用

4.14.5 窗口函数与group by区别

4.16 高级窗口函数 （todo）

4.17集合操作函数(todo)

4.18类型转换函数

---

第四章 Hive函数

4.1 聚合函数

        Hive支持count(),max(),min(),avg()等常用的聚合函数。

4.2 关系函数

        支持 =, !=, <>, < , >, <= , >, >=

        空值判断：is null， is not null.

        relike， regexp操作。

4.3 数学运算

        支持所有的数值类型： + , - , * , /， % ，& , | , ^, ~等。

4.4 逻辑运算

        逻辑与： and

        逻辑或： or

        逻辑非： not

4.5 数值运算

• 取整函数: round()

round(double a)    返回值bigint，返回double类型的整数值部门(遵循四舍五入)

• 指定精度取整函数

round(double a, int d)  返回值double。返回指定指定精度d的double类型。

• 向下取整函数

floor(double a )返回等于或者大于该double变量的最小的整数。

• 向上取整函数

ceil(double  a)   返回等于或者大于该double 变量的最小的整数；

• 取随机函数

rand(), rand(int  seed):返回一个0到1范围内的随机数。

• 自然指数函数

exp():  返回自然对数e的a次方。

• 幂运算函数

pow(double  a, double  p)：  返回a的p次幂。

• 开平方函数

sqrt(double a)： 返回a的平方根。

4.6 条件函数

• if函数

if(boolean  testCondition,T  valueTrue,  T  valueFalseOrNull)

当条件testCondition为True时候，返回valueTrue,否则返回valueFalseOrNull

• 非空查找函数coalesce

coalesce(T  v1,  T v2,.....) 

返回参数中第一个非空值，如果所有值都为null，那么返回Null。

• 条件判断函数case when

case when写法一：

case sex 

        when   ‘1’    then   ‘男’

        when   ‘2’    then   ‘女’

else   ‘其他’  end

case when写法二：

case when sex = ‘1’  then  ‘男’

         when sex = ‘2’  then  ‘女’

else   ‘其他’  end

4.7 日期函数

1.unix_timestamp函数的三种情况：

• 获取当前时间戳函数:  unix_timestamp

            语法：unix_timestamp();     

            返回值：bigint ,获得当前时区的unix时间戳

            样例：select  unix_timestamp()  from  table;

• 日期转unix时间戳函数:unit_timestamp

            语法：unix_timestamp(String  date)

            返回值：bigint

            说明：将格式为”yyyy-MM-dd HH:ss”的日期转换到unix时间戳，如果转换失败，则返回0.

• 将指定格式日期转unix时间戳函数:unix_timestamp() 

语法：unix_timestamp(string date, string pattern)

返回值：bigint

说明：转换pattern格式日期到unix时间戳。如果转化失败，则返回0.

样例：select  unix_timestamp(‘2021-03-08 14:21:11’,’yyyy-MM-dd HH:mm:ss’) from table

2.unit时间戳转日期函数: from_unixtime

• from_unixtime()

            语法：from_unixtime(bigint  unixtime)

            返回值：string

            说明：把具体的秒转化为时间日期。

3.获取当前的时间精确到毫秒

• current_timestamp()

            样例:select current_timestamp()  -- 2011-09-02 10:11:09.234212000

4.日期时间转日期函数:to_date

• 年月日时分秒只取其中的年月日部分：to_date（）

            语法：to_date(string  timestamp)

            返回值：string   返回日期时间部分的日期。

            样例：select  to_date(‘2021-09-02 12:09:09’) from table

5.日期转年/月/日/小时/分钟/秒/周函数

• 日期转年函数year()

语法：year(String date)

返回值:int   返回日期中的年

样例：select  year(‘2021-03-21 10:11:02’) from table

• 日期转月函数mounth

            返回日期中的月

• 日期转天函数day

返回日期中的天

• 日期转小时函数:hour

返回日期中小时函数

• 日期转分钟函数minute

            返回日期中的分钟

• 日期转秒函数second

            返回日期中的秒

• 日期转周函数 weekofyear

            weekofyear(string  date)

            返回值为int，返回日期在当前的周数

6.日期操作函数

• 日期比较函数：datediff

            语法:datediff(string   enddate, string  startdate)

            返回值:int  返回结束日期减去开始日期

• 日期增加函数 ：date_add

语法：date_add(string startdate, int days)

返回值string，返回开始日期startdate增加days天的日期。

• 日期减少函数 date_sub

            语法：date_sub(string startdate, int days)

            返回值：string. 返回开始日期startdate减少days天后的日期。

4.8 字符串函数

• 字符串长度函数: length()

            length(string a): 返回字符串a的长度

• 字符串反转函数：reverse

            reverse(string  a) ：返回字符串a的反转结果

• 字符串连接函数

            不带分隔符的字符串连接函数concat()

                    语法:concat(string A, string B，........)

                    返回输入字符串连接后的结果，支持任意个输入字符串

            带分隔符字符串连接函数 concat_ws()

                    concat_ws(String SEP, string a, string b)

                    返回输入字符串连接后的结果，sep表示各个字符串之间的分隔符。

4.9 字符串截取函数 

• substr(string a, int start, int len)

            返回字符串a从start位置开始，长度为len的字符串。

• substring(string a, int start, int len)

返回字符串a从start位置开始，长度为len的字符串。

• 字符串分割函数：split

 语法：split(string  str, string pat) 

4.10 去空格函数

• 去空格函数trim

             去掉字符串两边的空格

• 左边去空格ltrim

            ltrim(string  a) 去掉字符串左边的空格。

• 右边去空格函数

rtrim(string a)去掉字符串右边的空格。

4.11 正则表达式与解析函数

• 正则表达式替换函数regexp_replace(string a, string b, string c)

            将字符串a中符合java正则表达式b的部分替换为c.注意在有些情况下需要使用转义字符.    
            样例：select  regexp_replace(‘foobar’, ‘oo|ar’ , ‘’) from table_Name;

• 正则表达式解析函数：regexp_extract(string subject, string pattern, int index)

            样例：select  regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 1) from tableName; 

             将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。

• url解析函数: parse_url

样例：parse_url(string  urlString, string  partToExtract  [,  string  keyToExtract] )

说明：返回url中指定的部分。

• json解析：get_json_object()

            语法：get_json_object(string  json_string, string  path)

            样例：select  get_json_object(‘{......}’, ‘$.owner’)  from  tableName;

4.12 explode函数

    todo:结合百度需求进行总结

4.13 行转列与列转行

1.行转列

• concat(string a/col, string b/col,.....):返回输入字符串连接后的结果，支持任意个输入字符串。

• concat_ws(separator, str1, str2,....):它是一个特殊形式的concat

• collect_set(col):将某字段的值进行去重汇总，产生array类型字段。

2.列转行

• explode(col)

           将hive一列中复杂的array或者map结构拆成多行。可以结合lateral view进行使用。

4.14 基础窗口函数与分析函数

4.14.1 窗口函数简介

        在sql中有一类函数叫做聚合函数，例如sum(), avg(),max()等等，这类函数可以将多行数据按照规则聚集为一行，一般来讲，聚集后的行数是要少于聚集前的行数的。但是有时候我们既想要显示聚集前的数据，又要显示聚集后的函数，这个时候，我们便引入了窗口函数。窗口函数又叫OLAP函数/分析函数.

        窗口函数最重要的关键字是partition by和 order by.具体语法是：over(partition  by  用于分组的列名  order  by  用于排序的列名)。

4.14.2 窗口的含义

1）如果不指定rows between,默认为从起点到当前行；

2）如果不指定order by,则将分组内所有值累加；

3）理解rows between的含义，也叫window子句：

          preceding:往前；

          following：往后；

          current row: 当前行；

          unbounded:起点；

          unbounded  preceding : 表示从前面的起点；

          unbounded  following : 表示到后面的终点；

4.14.3 窗口函数分类

1）专用窗口函数：包括dense_rank(),rank(),row_number()等。

2）聚合函数：例如sum(), avg(),max(),min(),count().

样例1：

select   *,
       

sum(play_rate) over(order by user_id),
       

avg(play_rate) over(order by user_id),
       

max(play_rate) over(order by user_id),
       

min(play_rate) over(order by user_id),
       

count(play_rate) over(order by user_id)


from haokan_ads_test02;

样例1测试结果：

样例1分析：

    聚合函数在窗口函数中，是对自身记录及位于自身记录以上的数据进行求和的结果。eg:user_id为4的时候，在使用sum()窗口函数后的结果，是对1，2，3，4号deplat_rate求和。

注意1：可以注意到专用窗口函数括号里面是空的，什么也没有写，但是聚合函数后面括号不能为空，需要指定聚合的列名。

注意2：因为窗口函数是对where或者group by子句处理后的结果进行的操作，所以窗口函数原则上只能写在select子句中。

注意3：聚合函数对应的窗口函数都是对自身记录，以及自身记录之上的所有数据进行计算。如果想看所有人的聚合情况，我们直接看输出结果的最后一行即可。

4.14.4 窗口函数的使用

1）为什么叫窗口函数：这是因为partition by分组以后的结果叫做窗口，这里的窗口是范围的意思。窗口函数同时具有分组和排序的功能；不减少原有表的行数

2）带partition by使用区别：

      窗口函数中的partition by子句可以省去，但是此时就失去了分组的功能。

      窗口函数一般用于排名问题和top-N问题。

3）dense_rank(),rank(),row_number()区别

• DENSE_RANK() :排序相同时，会重复、总数会减少。1,1,2

• Rank()  :排序相同时，会重复，但是总数不会变。 1,1,3

• ROW_NUMBER(): 会根据顺序计算。1，2,3

4.14.5 窗口函数与group by区别

         group by分组汇总会改变行数（默认返回每组中的第一行）；而窗口函数不会减少原表中的行数。

4.16 高级窗口函数 （todo）

ntile;

lag:

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

lead:

        与lag相反。lead(col,n, default)用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行，第三个参数为默认值。

first_value

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

last_value:

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

4.17集合操作函数(todo)

grouping  sets

grouping_id

cube

rollup

4.18类型转换函数

    cast (字段名  as   转换的类型)