文章目录
- 前言
- 1. 查看系统内置函数
- 2. 常用内置函数
- 2.1 NVL 空字段赋值
- 2.2 CASE WHEN THEN ELSE END 流程控制函数
- 2.3 行转列
- 2.4 列转行
- 2.5 窗口函数（开窗函数）
- 2.6 Rank
前言
测试数据：
1. 查看系统内置函数
查看系统自带的函数
show functions;显示自带的函数的用法
desc function 函数名;
详细显示自带的函数的用法
desc function extended 函数名;
2. 常用内置函数
2.1 NVL 空字段赋值
语法：
nvl(value,default_value)说明：
Returns default value if value is null else returns value如：
select emp.empno,nvl(emp.comm,0) from emp;
2.2 CASE WHEN THEN ELSE END 流程控制函数
语法：
CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END说明：
When a = b, returns c; when a = d, return e; else return f如：
select emp.empno, case emp.deptno when 10 then '研发' when 20 then '销售' when 30 then '后勤' else '秘书' end from emp;
2.3 行转列
说明：
CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数是剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
注意: CONCAT_WS 必须是 string 或 array<string> 类型 对于不同类型可cast( 字段名 as string )
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的所有值进行去重汇总，产生array类型字段;
COLLECT_LIST(col): 对字段所有值进行汇总，不去重
如：
表emp按照部门号分组，显示部门号以及部门的所有工作名称
select emp.deptno,collect_set(emp.job) from emp group by emp.deptno;
2.4 列转行
说明：EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行；
LATERAL VIEW 侧写表 LATERAL VIEW udtf(expression) tableAlias AS columnAlias用于和split, explode等UDTF一起使用，将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
如：
SELECT movie,category_name
FROM movie_info
lateral VIEW
explode(split(category,",")) movie_info_tmp AS category_name ;
2.5 窗口函数（开窗函数）
说明：OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的改变而变化；over(partition by 字段 order by 字段 rows between ... and ...) 会按照指定的字段进行分区，把分区字段值相同的数据划分到同一个分区，会在每个分区中按照指定的字段进行排序；分区中每条数据开启一个窗口，每条数据的窗口大小为指定的窗口大小；
between and 表示范围概念：
CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，
UNBOUNDED PRECEDING 表示从前面的起点，
UNBOUNDED FOLLOWING表示到后面的终点如：
select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,--按name分组，组内数据相加
sum(cost) over(partition by name order by orderdate) as sample3,--按name分组，组内数据累加
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
from business;
2.6 Rank
RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算如：
select * ,row_number() over() as rownum from business;
