目录
3.获取 当前日期 前后 N天的 日期date_add date_sub
3. JSON 解析函数:get_json_object 处理 kv 结构数据(json)
2.获取 集合中的所有的key或value, 以 array形式返回
1.collect_set collect_list函数 列转行
2. explode爆炸函数 LATERAL VIEW AS侧视图函数 行转列
转换类
1.IF 函数:IF
语法:IF(boolean testCondition, T valueTrue, T valueFalseOrNull)
描述:当条件 testCondition 为 True 时,返回 valueTrue;否则返回 valueFalseOrNull。
select if(1 = 2, 100, 200);
200
2.非空查找函数:COALESCE
语法: COALESCE(value1, value2, …)
描述:返回参数中的第一个非空值;如果所有值都为 NULL,那么返回NULL。
select coalesce(null, '100', '50');
100
3.条件判断函数:CASE...WHEN...
语法: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
返回类型:T
描述:如果 a 等于 b,那么返回 c;如果 a 等于 d,那么返回 e;否则返回 f。
select case 100 when 50 then 'tom' when 100 then 'jack' else 'mary' end
jack
4.条件判断函数:CASE WHEN
语法: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
返回类型:T
描述:如果 a 为 TRUE,则返回 b;如果 c 为 TRUE,则返回 d;否则返回 e。
select case when 1 = 2 then 'tom' when 2 = 2 then 'jack' else 'mary' end
jack
日期类
1.UNIX 时间戳转日期函数:from_unixtime
语法:from_unixtime(bigint unixtime[, string format])
返回类型:string
说明:转化 UNIX 时间戳(从1970-01-01 00:00:00 UTC 到指定时间的秒数)到当前时区的时间格式。
select from_unixtime(1590000000, 'yyyy-MM-dd');
2020-05-21
2.获取当前日期 CURRENT_DATE
select CURRENT_DATE
select CURRENT_TIMESTAMP
3.获取 当前日期 前后 N天的 日期date_add date_sub
select date_add(CURRENT_DATE,7)
select date_sub(CURRENT_DATE,7)
4.日期比较函数:datediff
语法:datediff(string enddate, string startdate)
说明:返回结束日期减去开始日期的天数。
select datediff('2020-05-21', '2020-01-01');
141
5.截取 年月日 TO_DATE
select to_date('2020-10-28 12:12:12');
2020-10-28
日期截取的几种方式
select substr('2020-10-28 12:12:12',1,4)
select year('2020-10-28 12:12:12')
select month('2020-10-28 12:12:12')
select day('2020-10-28 12:12:12')
select hour('2020-10-28 12:12:12')
数值型
CAST类型转换函数
CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式,它包括用AS关键字分隔的源值和目标数据类型。
语法:CAST (expression AS data_type)
说明:
expression:任何有效的SQServer表达式。
AS:用于分隔两个参数
data_type:目标系统所提供的数据类型,包括bigint和sql_variant,不能使用用户定义的数据类型。
可以转换的类型是有限制的。这个类型可以是以下值其中的一个:
- 二进制,同带binary前缀的效果 : BINARY
- 字符型,可带参数 : CHAR()
- 日期 : DATE
- 时间: TIME
- 日期时间型 : DATETIME
- 浮点数 : DECIMAL
- 整数 : SIGNED
- 无符号整数 : UNSIGNED
字符串类型
1.字符串截取函数:substr | substring
语法:substr(string A, int start,int len)
操作类型:string
描述:返回字符串 A 从 start 位置到结尾的字符串,len可以省略,默认0
select substr('abcde', 3,2);
cd
select substring('abcde', -2,2);
de
2.字符串长度函数:length
语法:length(string A)
操作类型:int
描述:返回字符串 A 的长度
select length('abcedfg');
7
3. JSON 解析函数:get_json_object 处理 kv 结构数据(json)
语法:get_json_object(string json_string, string path)
操作类型:string
描述:解析 json 的字符串 json_string,返回 path 指定的内容。如果输入的 json 字符串无效,那么返回 NULL.
select get_json_object('{"name":"zs","age":"25"}', '$.name');
4.字符串连接函数:concat
语法:concat(string A, string B…)
操作类型:string
描述:返回输入字符串连接后的结果,支持任意多个输入字符串。
5.带分隔符字符串连接函数:concat_ws
语法:concat_ws(string SEP, string A, string B…)
操作类型:string
描述:返回输入字符串连接后的结果,SEP 表示各个字符串间的分隔符。
6. 分割字符串函数:split
语法:split(string str, string pat)
操作类型:array
描述:按照 pat 字符串分割 str,会返回分割后的字符串数组。
复杂数据类型(集合)函数
1. SIZE查询集合中元素个数
2.获取 集合中的所有的key或value, 以 array形式返回
行列转换
1.collect_set collect_list函数 列转行
列转行专用函数,有时为了字段拼接效果,多和concat_ws()函数连用;
描述:将同一组中不同行的数据聚合为一个集合(无重复数据),如果想要保留重复数据,可以使用collect_list。
用下标可以取某一个(不影响聚合的值)
collect_set(area)中值用’|'分割
2. explode爆炸函数 LATERAL VIEW AS侧视图函数 行转列
lateral view
类似于 join
- 分类:视图
- 功能:配合UDTF来使用,把某一行数据拆分成多行数据
很多的UDTF不能将结果与源表进行关联,使用lateral view ,可以将UDTF拆分的单个字段数据与原始表数据关联上
explode
可以将 explode 的结果想象为一张表
- 分类:UDTF
- 功能:函数可以将一个array或者map展开
- explode(array):
将array列表里的每个元素生成一行
- explode(map):
每一对元素作为一行,key为一列,value为一列
- explode(array):
- 使用方式:
- 直接使用
- 与lateral view连用
如何把这张表
转换成
explode和LATERAL VIEW连用
t2相当于 表别名 detail 相当于 列别名,我们为这个展开后的结果集explode(split(category_detail, ','))指定了一个表别名 t2
,这样在查询的后续部分就可以通过 t2
来引用这个展开后的结果集。
AS detail是将整个结果集当作一个新的列detail,这样就可以select查询