Hive 运算符与函数

批处理模式：处理完一次就结束

-e

        bin/hive -e 'show databases'

执行SQL脚本文件

-f

        bin/hive -f /data/a.sql

启动服务

--server

临时参数

set

参数优先级：set(临时) > hiveconf（Console） > hive-site.xml(全局)

---

关系运算符

is null ( is not nll)

like ( not like) --相似匹配

BETWEEN --之间

rlike --正则表达式

逻辑运算符

与或非AND OR NOT

在IN（NOT）——不相关子查询

存在EXISTS(NOT) ——相关子查询

算数运算符（只能对数字进行操作）

div取整

%取余mod

& 位

| 或

^ 异或（对比，两者值不同）

1100^1000==0100

---

函数

 创建空表测试函数 

函数展示

show funtions; --展示所有运算符

desc funtion [+];  --查看运算符功能

desc function extend [+]； --查看更详细信息

desc formatted 表名; --显示表schema

函数分类（内置函数、用户自定义函数）

内置函数

        字符串函数 1、concat("AB","CD")拼接

                            2、concat_ws(',',"AB","CD")==AB,efg带字符串拼接,第一个参数为字符串

                            3、substring("abcd",2)==cd 字符串截取，从1开始索引。

                            4、split('apache hive','\\s+')切割，\\s+空白符正则表达式

                            5、regexp_replace()正则替换

                            6、regexp_extract()正则提取

                            7、json

                            8、parse_url('www.baidu.com/asdad','HOST')==www.baidu.com URL解析函数

        时间函数 

                            1、select unix_timestamp();获取时间戳 （加时间就转时间戳）

                            2、select from_unixtime(1618238391);时间戳转日期

        数学函数

                            1、select round(3.1415)取整，设置精度(3.14159,4)

                            2、select rand();随机0-1之内，添加种子（x），其他地方写该参数得到同样数值

        集合函数

         条件函数

        case A  when B then C [when D then E] [else F] end

        对A判断，当B的时候转换为C

        cast 类型转换函数(如int转string)

        mask 数据脱敏函数

        杂项函数

        collect_set() 去重收集函数 

        collect_list() 收集函数

        select collect(sex) from t_student; 返回{男,女}

 

---

用户自定义函数

        UDF

        UDAF(COUNT、SUM)

        UDTF

UDF实现

UDAF案例

collect_set，多进一出

数据倾斜

 结构体函数

UDTF案例

一进多出，explode

 explode只能查询爆炸字段，若还想查询原表其他字段，一般配合lateral view侧视图使用。

Select T1.字段1 , T2.字段2

from T1

lateral view explode(字段) T2 as 字段1,[字段2]


--相当于两个from，lateral view相当于另一个from

爆炸函数只能炸数组、map。若要炸string

explode(split(字段,'分隔符'))

书的类型案例

---

开窗函数 over

可以将聚合、分组、排序统一使用的一种函数，并且可以设置聚合范围和过程（尾），还支持返回行数、分部分、数据飘逸等功能（头），是综合性较高的函数。

比如弥补了group by查询字段仅能用分组字段与聚合函数的不足。

聚合函数

ROW_NUMBER()：分组内有序，不重复

RANK()：数据重复，挤占后续标号。 如并列第二，那就是1 2 2 4

Dense_RANK()：数据重复，不挤占后续标号

nTILE（x）：分桶，其实就是分组。分成1/x

+

Over( Partition by [分组字段] order by [排序字段] )

···如果没有order by，聚合字段默认为分组内所有聚合

row_number() / ntile(数字) / 数据漂移

function 最后操作

partition by ==group by

order by ==每个分组内的排序（不写order by则全局求和）

rows between 行聚合的范围

--员工案例
select id,name,salary,dept,sum(salary) over (partition by dept [desc]) from employee

 开窗后

聚合函数

默认连续累加求和。可根据rows between设置聚合范围

rows between

行聚合范围

写在前面

 row_number()

返回行数函数，写在前面。TopN分析案例

TopN函数

ntile(数字)

如只看某天后1/3的销量，最后where part3 

分为若干个部分，3就是三分之一

数据偏移函数

LAG

LEAD

FIRST_VALUE

LAST_VALUE

LAG(字段，偏移量，偏移默认值)

  FIRST_VALUE

---

抽样函数