一.空字段赋值
1
)函数说明
NVL:给值为NULL的数据赋值,它的格式是NVL( value,default_value)。它的功能是如果value为NULL,则NVL函数返回default_value的值,否则返回value的值,如果两个参数都为NULL ,则返回NULL。
2
)样例
select comm,nvl(comm,mqrid) from emp; 如果员工的comm为Null,则用领导的id来代替
二. CASE WHEN THEN ELSE END
1
)函数说明
case (字段名) when (值) then value else value end
2
)样例
select dept_id,
sum(case sex when '
男
' then 1 else 0 end) male_count,
sum(case sex when '
女
' then 1 else 0 end) female_count
from
emp_sex; 第一个sum函数: 如果员工的性别为男,则赋值为1,其他为0
第二个sum函数: 如果员工的性别为女,则赋值为1,其他为0
case pay when '支付宝' then 1 when '微信' then 2 when '信用卡' then 3 else 0 end;
三. if
1
)函数说明
与case when then else end 作用类似,if用于两个值,case用于多个值
2
)样例
if(sex='男' , 1 , 0) ; 如果sex字段为'男',值为1,否则为0
四. 行转列
1
)相关函数说明
(
可以是一行转一列,多行转一列
)
CONCAT(string A/col, string B/col…):返回输入字符串连接后的结果,支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,...):它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL,返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
注意:
CONCAT_WS must be "string or array<string>"
COLLECT_SET(col):函数只接受基本数据类型,它的主要作用是将某字段的值进行
去重汇总,产生array类型字段。
COLLECT_LIST(col):函数只接受基本数据类型,它的主要作用是将某字段的值进行
不去重汇总,产生array类型字段。
2
)样例
select concat(emp, '-' , sal , '-' , deptno) from emp; 张三-5000-11 将员工名和工资和员工id拼接
李四-5500-12
...
concat_ws('-', ename , cast(empno as string)); 第一个为分隔符'-', 后面接的是string类型,不是string类型的需要用cast进行强制转换
collect_set( 对结果进行去重 ) collect_set(name) 将name字段的值进行去重汇总,形成array字段
collect_list(对结果不去重) 将name字段的值不进行去重汇总,形成array字段
五. 列转行 (一列转多行)
1
)函数说明
Split(str, separator):将字符串按照后面的分隔符切割,转换成字符array。
EXPLODE(col):将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW
用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释:lateral view用于和split, explode等UDTF一起使用,它能够将一行数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。
lateral view首先为原始表的每行调用UDTF,UDTF会把一行拆分成一或者多行,lateral view再把结果组合,产生一个支持别名表的虚拟表。
2
)样例
movie
|
category
|
《疑犯追踪》
|
悬疑,动作,科幻,剧情
|
《Lie to me》
|
悬疑,警匪,动作,心理,剧情
|
《战狼2》
|
战争,动作,灾难
|
select movie,category_name
from movie_info
lateral view explode(split(category,',')) tmp as category_name; 炸裂函数,把category字段炸开分成多列,然后与movie字段进行匹配
结果:
《疑犯追踪》
悬疑
《疑犯追踪》
动作
《疑犯追踪》
科幻
《疑犯追踪》
剧情
《
Lie to me
》
悬疑
《
Lie to me
》
警匪
《
Lie to me
》
动作
《
Lie to me
》
心理
《
Lie to me
》
剧情
《战狼
2
》
战争
《战狼
2
》
动作
《战狼
2
》
灾难
六. 窗口函数(开窗函数)
1
)相关函数说明
OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化。
CURRENT ROW:当前行
n PRECEDING:往前n行数据
n FOLLOWING:往后n行数据
UNBOUNDED:无边界
UNBOUNDED PRECEDING 前无边界,表示从前面的起点,
UNBOUNDED FOLLOWING后无边界,表示到后面的终点
LAG(col,n,default_val):往前第n行数据
LEAD(col,n, default_val):往后第n行数据
FIRST_VALUE (col,true/
false):当前窗口下的第一个值,第二个参数
为
true
,跳过空值
LAST_VALUE (col,true/
false):
当前窗口下的最后一个值
,第二个参数
为
true
,
跳过空值
NTILE(n):把有序窗口的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,NTILE返回此行所属的组的编号。
注意:
n
必须为
int
类型。
七. Rank
1
)函数说明
RANK() 排序相同时会重复,总数不会变
DENSE_RANK() 排序相同时会重复,总数会减少
ROW_NUMBER() 会根据顺序计算
Rank,NTile,DenseRank,CumeDist,PercentRank,Lead and Lag functions 不能补充窗口子句