hive函数

内置函数（简单函数[map]、聚合函数[reduce]、特殊函数[炸裂函数]）
- 炸裂函数：

[1:1] —-> UDF
[N:1] —-> UDAF
[1:N] —-> UDTF

自定义函数

查看函数

查看hive中所有函数

show functions;（简单函数不写注释）

查看sum函数使用说明

desc function sum;

查看sum函数扩展信息

desc function extended sum;

常用函数

string

split(str, regex)

字符串, 正则表达式 —-> 返回数组

SELECT split('oneAtwoBthreeC', '[ABC]'); —->[“one”,”two”,”three”,””]

instr(str, substr)

原字符串, 查找的子字符串 —-> 返回子字符串在原字符串的位置

SELECT instr('Facebook', 'boo'); —->5
SELECT instr('Facebook', 'blo'); —->0 //索引从1开始

substr(str, [start, len])

原字符串, [截取的起始位置, 截取长度] —-> 截取的字符串

SELECT substr('Facebook', 5, 1); —-> b
SELECT substr('Facebook', -5); —-> ebook

upper(str), lower(str)

大小写转换

分组统计: distinct lower(key)

concat(str1, str2, …)

字符串拼接

concat(1, 'a', 'b') —-> 1ab //类型转换
concat_ws(",", array('1', '2'), array(3, 4), '5') —->’1,2,3,4,5’//指定分隔符

length(str)

coalesce()

多个值中返回第一个不为null的值

SELECT coalesce(null, null, 3, null); —-> 3

trim(), ltrim(), rtrim()

去空格[前后, 前, 后]

SELECT trim(" sds ");

lpad(str, int, str)

补前缀

select lpad("12", 4, "0"); —-> “0012”

nvl()

有值返回值，无值返回默认值

SELECT nvl("12", 10); —-> 12
SELECT nvl(null, 10); —-> 10

条件判断

case when

作等值判断

SELECT 
id, name, sex, age, dept, 
case dept
when "IS" then 1
when "CS" then 2
else 3
end dept_no
from stu_manager;

作条件判断

SELECT 
id, name, sex, age, dept, 
case
when age < 18 then 1
when age > 20 then 2
else 3
end AS age_no
from stu_manager;

三目表达式

Java: 条件判断? 返回值1 : 返回值2

Hive: IF(条件, true返回值, false返回值)

SELECT 
id, name, sex, age, 
IF (age >= 19, age*0.9, age) AS price
from stu_manager;

数值函数

ceil()
floor()
round()
rand()
max, min, avg, sum, …
日期函数
current_date()

获取当前系统时间

获取当前系统时间戳：current_timestamp():
实际使用: 日期转时间戳
from_unixtime(unix_time, format)

时间戳转日期

SELECT from_unixtime(1595347200, "yyyy-mm-dd"); —-> 2020-07-22

unix_timestamp

日期转时间戳

SELECT unix_timestamp("2020-07-22", "yyyy-mm-dd"); —-> 1595347200

对日期进行提取

year()
month()
day()
hour()
minute()
second()

SELECT year("2020-07-22 12:13:14");

数据类型转换

cast(原始数据 as 目标类型)

使用场景：数据分析 ——> 存储

集合操作函数

构建数组

select array(1, 2, 3); —-> [1, 2, 3]
select array(1, 2, "3"); —-> [“1”, “2”, “3”]

数组中是否包含某一元素

select array_contains(array(1, 2, 3), 1); —-> true

数组排序

select sort_array(array(2,1,3)); —-> [1, 2, 3]

构建map

select map(1, 2, 3, 4); —-> {1:2,3:4}

提取map的key 和 value

select map_keys(map(1, 2, 3, 4)); —-> [1,3]
select map_values(map(1, 2, 3, 4)); —-> [2,4]

收集函数

collect_set: 去重
collect_list: 不去重

表函数

炸裂函数 — explode()
使用场景：数组、集合
案例：

array
SELECT explode(word_locatioins) from person1; //查询数据只有炸裂结果
SELECT name, locations.location from person1

lateral view explode(word_locatioins) //查询结果除了炸裂结果外还有普通字段
locations //视图别名
AS location; //视图字段的别名

map

select name, sco.f1, sco.f2 from scores

lateral view explode(score) sco as f1, f2;

窗口函数

需求（分组TopN）：3个部门（id, name, sex, age, dept）
查询每个部门年龄最大的两个人
求解：row_number(), rank(), dense_rank() 组合 over()子句

SELECT id, name, sex, age, dept from (
SELECT id, name, sex, age, dept, 
row_number() over(partition by dept order by age desc) AS index  //按部门根据年龄排序
from stu_manager) a where index <= 2;                            //取top2

SELECT id, name, sex, age, dept from (
SELECT id, name, sex, age, dept, 
rank() over(partition by dept order by age desc) AS index        //支持并列  1, 2, 2, 4
from stu_manager) a where index <= 2;

SELECT id, name, sex, age, dept from (
SELECT id, name, sex, age, dept, 
dense_rank() over(partition by dept order by age desc) AS index  //支持并列  1, 2, 2, 3
from stu_manager) a where index <= 2;

开窗+聚合（max, min, sum）

SELECT id, name, sex, age, dept, 
max(age) over(partition by dept) AS index             
from stu_manager;

SELECT id, name, sex, age, dept, 
max(age) over(partition by dept order by age desc) AS index           //有一条计算一条
[ = max(age) over(distribute by dept sort by age desc) AS index]            
from stu_manager;

内置函数