HQL常用函数

目录

转换类 

1.IF 函数：IF

2.非空查找函数：COALESCE

3.条件判断函数：CASE...WHEN...

4.条件判断函数：CASE WHEN

日期类

1.UNIX 时间戳转日期函数：fom_unixtime 

2.获取当前日期 CURRENT_DATE

3.获取 当前日期 前后 N天的 日期date_add date_sub

4.日期比较函数：datediff

5.截取 年月日 TO_DATE

日期截取的几种方式

数值型

CAST类型转换函数

字符串类型

1.字符串截取函数：substr | substring

2.字符串长度函数：length

3. JSON 解析函数：get_json_object 处理 kv 结构数据(json)

4.字符串连接函数：concat

5.带分隔符字符串连接函数：concat_ws

6. 分割字符串函数：split

复杂数据类型(集合)函数

1. SIZE查询集合中元素个数

2.获取 集合中的所有的key或value, 以 array形式返回

行列转换

1.collect_set collect_list函数 列转行

2. explode爆炸函数 LATERAL VIEW AS侧视图函数 行转列

lateral view

explode

explode和LATERAL VIEW连用

---

转换类 

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为一列
• 使用方式：
  • 直接使用
  • 与lateral view连用

如何把这张表

转换成

explode和LATERAL VIEW连用

 t2相当于 表别名  detail 相当于 列别名,我们为这个展开后的结果集explode(split(category_detail, ','))指定了一个表别名 t2，这样在查询的后续部分就可以通过 t2 来引用这个展开后的结果集。

AS detail是将整个结果集当作一个新的列detail,这样就可以select查询