福州专业建站公司望牛墩仿做网站
1. 函数简介：
Hive会将常用的逻辑封装成函数给用户进行使用，类似于Java中的函数。
好处：避免用户反复写逻辑，可以直接拿来使用。
重点：用户需要知道函数叫什么，能做什么。
Hive提供了大量的内置函数，按照其特点可大致分为如下几类：单行函数、聚合函数、炸裂函数、窗口函数。
以下命令可用于查询所有内置函数的相关信息。
1）查看系统内置函数
hive> show functions;2）查看内置函数用法
hive> desc function upper;3）查看内置函数详细信息
hive> desc function extended upper;2. 单行函数
单行函数的特点是一进一出，即输入一行，输出一行。
单行函数按照功能可分为如下几类: 日期函数、字符串函数、集合函数、数学函数、流程控制函数等。
2.1 算术运算函数
2.2 数值函数
1）round：四舍五入
hive> select round(3.3); 32）ceil：向上取整
hive> select ceil(3.1) ; 43）floor：向下取整
hive> select floor(4.8); 42.3 字符串函数
1）substring：截取字符串
语法一：substring(string A, int start)
返回值：string
说明：返回字符串A从start位置到结尾的字符串
语法二：substring(string A, int start, int len)
返回值：string
说明：返回字符串A从start位置开始，长度为len的字符串
2）replace ：替换
语法：replace(string A, string B, string C)
返回值：string
说明：将字符串A中的子字符串B替换为C。
hive> select replace('atguigu', 'a', 'A')3）regexp_replace：正则替换
语法：regexp_replace(string A, string B, string C)
返回值：string
说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符。
hive> select regexp_replace('100-200', '(\\d+)', 'num') 4）regexp：正则匹配
语法：字符串 regexp 正则表达式
返回值：boolean
说明：若字符串符合正则表达式，则返回true，否则返回false。
（1）正则匹配成功，输出true
hive> select 'dfsaaaa' regexp 'dfsa+'（2）正则匹配失败，输出false
5）repeat：重复字符串
语法：repeat(string A, int n)
返回值：string
说明：将字符串A重复n遍。
hive> select repeat('123', 3);hive> 1231231236）split ：字符串切割
语法：split(string str, string pat)
返回值：array
说明：按照正则表达式pat匹配到的内容分割str，分割后的字符串，以数组的形式返回。
hive> select split('a-b-c-d','-');hive> ["a","b","c","d"]7）nvl ：替换null值
语法：nvl(A,B)
说明：若A的值不为null，则返回A，否则返回B。
hive> select nvl(null,1); hive> 18）concat ：拼接字符串
语法：concat(string A, string B, string C, ……)
返回：string
说明：将A,B,C……等字符拼接为一个字符串
hive> select concat('beijing','-','shanghai','-','shenzhen');hive> beijing-shanghai-shenzhen9）concat_ws：以指定分隔符拼接字符串或者字符串数组
语法：concat_ws(string A, string…| array(string))
返回值：string
说明：使用分隔符A拼接多个字符串，或者一个数组的所有元素。
hive>select concat_ws('-','beijing','shanghai','shenzhen');hive> beijing-shanghai-shenzhen10）get_json_object：解析json字符串
语法：get_json_object(string json_string, string path)
返回值：string
说明：解析json的字符串json_string，返回path指定的内容。如果输入的json字符串无效，那么返回NULL。
2.4 日期函数
1）unix_timestamp：返回当前或指定时间的时间戳
语法：unix_timestamp()
返回值：bigint
案例实操：
hive> select unix_timestamp('2022/08/08 08-08-08','yyyy/MM/dd HH-mm-ss'); 16599460882）from_unixtime：转化UNIX时间戳（从 1970-01-01 00:00:00 UTC 到指定时间的秒数）到当前时区的时间格式
语法：from_unixtime(bigint unixtime[, string format])
返回值：string
案例实操：
hive> select from_unixtime(1659946088); 2022-08-08 08:08:083）current_date：当前日期
hive> select current_date; 2022-07-114）current_timestamp：当前的日期加时间，并且精确的毫秒
hive> select current_timestamp; 2022-07-11 15:32:22.4025）month：获取日期中的月
语法：month (string date)
hive> select day('2022-08-08 08:08:08') 8返回值：int
案例实操：
hive> select month('2022-08-08 08:08:08');86）day：获取日期中的日
语法：day (string date)
返回值：int
案例实操：
hive> select day('2022-08-08 08:08:08') 87）hour：获取日期中的小时
语法：hour (string date)
返回值：int
案例实操：
hive> select hour('2022-08-08 08:08:08'); 88）datediff：两个日期相差的天数（结束日期减去开始日期的天数）
语法：datediff(string enddate, string startdate)
返回值：int
案例实操：
hive> select datediff('2021-08-08','2022-10-09'); -4279）date_add：日期加天数
语法：date_add(string startdate, int days)
返回值：string
说明：返回开始日期 startdate 增加 days 天后的日期
案例实操：
hive> select date_add('2022-08-08',2); 2022-08-1010）date_sub：日期减天数
语法：date_sub (string startdate, int days)
返回值：string
说明：返回开始日期startdate减少days天后的日期。
案例实操：
hive> select date_sub('2022-08-08',2); 2022-08-0611）date_format:将标准日期解析成指定格式字符串
hive> select date_format('2022-08-08','yyyy年-MM月-dd日') 2022年-08月-08日2.5 流程控制函数
1）case when：条件判断函数
语法一：case when a then b [when c then d]* [else e] end
返回值：T
说明：如果a为true，则返回b；如果c为true，则返回d；否则返回 e
hive> select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from tabl eName;
mary语法二： case a when b then c [when d then e]* [else f] end
返回值: T
说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f
hive> select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from t ableName;
mary2）if: 条件判断，类似于Java中三元运算符
语法：if（boolean testCondition, T valueTrue, T valueFalseOrNull）
返回值：T
说明：当条件testCondition为true时，返回valueTrue；否则返回valueFalseOrNull
（1）条件满足，输出正确
hive> select if(10 > 5,'正确','错误'); （2）条件满足，输出错误
hive> select if(10 < 5,'正确','错误');3. 集合函数
1）size：集合中元素的个数
hive> select size(friends) from test; --2/2 每一行数据中的friends集合里的个数2）map：创建map集合
语法：map (key1, value1, key2, value2, …)
说明：根据输入的key和value对构建map类型
案例实操：
hive> select map('xiaohai',1,'dahai',2); hive> {"xiaohai":1,"dahai":2}3）map_keys： 返回map中的key
hive> select map_keys(map('xiaohai',1,'dahai',2));hive>["xiaohai","dahai"]4）map_values: 返回map中的value
hive> select map_values(map('xiaohai',1,'dahai',2));hive>[1,2]5）array 声明array集合
语法：array(val1, val2, …)
说明：根据输入的参数构建数组array类
案例实操：
hive> select array('1','2','3','4');hive>["1","2","3","4"]6）array_contains: 判断array中是否包含某个元素
hive> select array_contains(array('a','b','c','d'),'a');hive> true7）sort_array：将array中的元素排序
hive> select sort_array(array('a','d','c'));hive> ["a","c","d"]8）struct声明struct中的各属性
语法：struct(val1, val2, val3, …)
说明：根据输入的参数构建结构体struct类
案例实操：
hive> select struct('name','age','weight');hive> {"col1":"name","col2":"age","col3":"weight"}9）named_struct声明struct的属性和值
hive> select named_struct('name','xiaosong','age',18,'weight',80);hive> {"name":"xiaosong","age":18,"weight":80}4. 高级聚合函数
多进一出 （多行传入，一个行输出）。
1）普通聚合 count/sum
2）collect_list 收集并形成list集合，结果不去重
hive>
select sex,collect_list(job)
fromemployee
group by sex女 ["行政","研发","行政","前台"]
男 ["销售","研发","销售","前台"]3）collect_set 收集并形成set集合，结果去重
5. 炸裂函数
定义：UDTF（table-generating functions）
接收一行数据，输出一行或多行数据。
在Hive SQL中，处理数组或映射类型的“炸裂”通常涉及到将这些复杂类型的数据展开成多行或多列。Hive 提供了一些内置函数来帮助实现这一操作，特别是对于数组和映射类型。这里是一些常用的函数和示例：
5.1 使用lateral view和explode()
`explode()` 函数可以用来展开数组或映射类型中的元素。`LATERAL VIEW` 用于将这些展开的元素转换为单独的行。
对于数组
假设你有一个表 `orders`，其中包含一个名为 `items` 的数组字段，每个订单可能有多个项目。
CREATE TABLE orders (order_id INT,items ARRAY<STRING>
);-- 插入一些数据
INSERT INTO orders VALUES (1, array('item1', 'item2'));
INSERT INTO orders VALUES (2, array('item3'));-- 展开数组
SELECT order_id, item
FROM orders
LATERAL VIEW explode(items) exploded_table AS item;order_id | item
---------|------
1 | item1
1 | item2
2 | item3
```对于映射
如果 `items` 是一个映射类型，你可以使用 `explode()` 来展开键值对。
CREATE TABLE orders (order_id INT,items MAP<STRING, INT> -- 假设这是商品名到数量的映射
);-- 插入一些数据
INSERT INTO orders VALUES (1, map('item1', 2, 'item2', 3));
INSERT INTO orders VALUES (2, map('item3', 1));-- 展开映射
SELECT order_id, key, value
FROM orders
LATERAL VIEW explode(items) exploded_table AS key, value;
```这将产生如下结果：
```
order_id | key | value
---------|-------|------
1 | item1 | 2
1 | item2 | 3
2 | item3 | 1
```5.2 使用 `LATERAL VIEW` 和 `posexplode()`
如果你需要保留数组元素的位置信息，可以使用 `posexplode()` 函数。
-- 展开数组并获取位置
SELECT order_id, pos, item
FROM orders
LATERAL VIEW posexplode(items) exploded_table AS pos, item;
```这将产生如下结果（包含元素的位置）：
```
order_id | pos | item
---------|-----|------
1 | 0 | item1
1 | 1 | item2
2 | 0 | item35.3 使用 `LATERAL VIEW` 和 `inline()`
`inline()` 函数可以用于展开结构化的数组，例如包含多个字段的数组。
CREATE TABLE orders (order_id INT,items ARRAY<STRUCT<name: STRING, quantity: INT>>
);-- 插入一些数据
INSERT INTO orders VALUES (1, array(named_struct('name', 'item1', 'quantity', 2), named_struct('name', 'item2', 'quantity', 3)));
INSERT INTO orders VALUES (2, array(named_struct('name', 'item3', 'quantity', 1)));-- 展开结构化数组
SELECT order_id, i.name, i.quantity
FROM orders
LATERAL VIEW inline(items) exploded_table AS i;
```这将产生如下结果：
```
order_id | name | quantity
---------|-------|---------
1 | item1 | 2
1 | item2 | 3
2 | item3 | 16. 窗口函数
定义：窗口函数，能为每行数据划分一个窗口，然后对窗口范围内的数据进行计算，最后将计算结果返回给该行数据。
6.1 常见窗口函数
按照功能，常用窗口可划分为如下几类：聚合函数、跨行取值函数、排名函数。
1）聚合函数
max：最大值。
min：最小值。
sum：求和。
avg：平均值。
count：计数。
2）跨行取值函数
（1）lead和lag
注：lag和lead函数不支持自定义窗口。
（2）first_value和last_value
3）排名函数
rank 、dense_rank、row_number不支持自定义窗口。
