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查询

  • 14
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值