Impala

字符串函数

1、去除字符串之前和之后的任意个数的空格

​ btrim(string a)

[master:21000] > select btrim('hello ') as btrim;+-------+| btrim |+-------+| hello |+-------+

2、返回字符串的长度，两个函数功能相同

​ char_length(string a)

​ character_length(string a)

--char_length得到hello world的长度[master:21000] > select char_length('hello world') as char_length;+-------------+| char_length |+-------------+| 11|+-------------+--通过函数character_length得到hello world的长度[master:21000] > select character_length('hello world') as character_length;+------------------+| character_length |+------------------+| 11 |+------------------+

3、拼接多个字符串

​ concat(string a,string b…)

--连接hello和world两个字符串[master:21000] > select concat('hello','world') as concat;+------------+| concat |+------------+| helloworld |+------------+--连接hello、world、cauchy三个字符串[master:21000] > select concat('hello','world','cauchy') as concat;+------------------+| concat |+------------------+| helloworldcauchy |+------------------+

4、拼接多个字符串，由指定分隔符分割

​ concat_ws(string sep,string a,string b…)

--通过'-'连接两个字符串[master:21000] > select concat_ws('-','hello','world') as concat_ws;+-------------+| concat_ws |+-------------+| hello-world |+-------------+

5、 返回参数字符串的字符长度

​ length(string a)

--得到字符串'abcdefg'的长度[master:21000] > select length('abcdefg') as length;+--------+| length |+--------+| 7|+--------+

6、返回参数字符串，并从左侧删除任何前导空格

​ ltrim(string a)

--删除字符串'hello'左侧的所有空格[master:21000] > select ltrim('hello') as ltrim;+---------+| ltrim |+---------+| hello |+---------+

7、返回参数字符串，并从右侧删除任何后置空格

​ rtrim(string a)

--删除字符串'hello'右侧的所有空格[master:21000] > select rtrim('hello') as rtrim;+---------+| rtrim |+---------+| hello |+---------+

8、去掉字符串中所有前导和后置空格

​ trim(string a)

--去掉'hello world'的前导和后置空格[master:21000] > select trim('hello world') as trim;+-------------+| trim|+-------------+| hello world |+-------------+

9、 返回true或者false，表示字符串是否包含正则表达式的值

​ regexp_like(string source,string pattern,[string options])

options参数：

  • c： 区分大小写匹配（默认）
  • i：不区分大小写
  • m：多行匹配
  • n：换行符匹配
--判断字符'foo'是否包含'f'[master:21000] > select regexp_like('foo','f');+-------------------------+| regexp_like('foo', 'f') |+-------------------------+| true|+-------------------------+--判断字符'foo'是否包含'F'[master:21000] > select regexp_like('foo','F');+-------------------------+| regexp_like('foo', 'f') |+-------------------------+| false |+-------------------------+--判断字符'foo'是否包含'f',设置参数不区分大小写[master:21000] > select regexp_like('foo','F','i');+------------------------------+| regexp_like('foo', 'f', 'i') |+------------------------------+| true |+------------------------------+

10、以delimiter字符串作为拆分项，取第n个字符串返回

​ split_part(string source,string delimiter,bigint n)

--以','为分隔符拆分'x,y,z'并返回第1个字符串[master:21000] > select split_part('x,y,z',',',1);+-----------------------------+| split_part('x,y,z', ',', 1) |+-----------------------------+| x |+-----------------------------+--以','为分隔符拆分'x,y,z'并返回第2个字符串[master:21000] > select split_part('x,y,z',',',2);+-----------------------------+| split_part('x,y,z', ',', 2) |+-----------------------------+| y |+-----------------------------+--以','为分隔符拆分'x,y,z'并返回第3个字符串[master:21000] > select split_part('x,y,z',',',3);+-----------------------------+| split_part('x,y,z', ',', 3) |+-----------------------------+| z |+-----------------------------+

11、 截取字符串，返回左边的n个字符

​ strleft(string a,int num_chars)

--从左边截取字符串'hello world'，返回长度为4的字符串[master:21000] > select strleft('hello world',4) as strleft;+---------+| strleft |+---------+| hell|+---------+

12、截取字符串，返回右边的n个字符

​ strright(string a,int num_chars)

--从右边截取字符串'hello world'，返回长度为4的字符串[master:21000] > select strright('hello world',4) as strright;+----------+| strright |+----------+| orld |+----------+

13、返回从指定点开始的字符串部分,可选地指定最大长度

​ substr(string a,int start,[int len])

​ substring(string a,int start,[int len])

--截取字符串'hello world'，从第6位开始[master:21000] > select substr('hello world',6) as substr;+--------+| substr |+--------+|world |+--------+--截取字符串'hello world'，从第6位开始，长度为3[master:21000] > select substr('hello world',6,3) as substr;+--------+| substr |+--------+|wo|+--------+--截取字符串'hello world'，从第6位开始[master:21000] > select substring('hello world',6) as substring;+-----------+| substring |+-----------+|world|+-----------+--截取字符串'hello world'，从第6位开始，长度为3[master:21000] > select substring('hello world',6,3) as substring;+-----------+| substring |+-----------+|wo |+-----------+

时间函数

1、当前时间戳

​ now()

​ current_timestamp()

-- now()[master:21000] > select now();+-------------------------------+| now() |+-------------------------------+| 2023-01-16 10:07:07.628604000 |+-------------------------------+-- current_timestamp()[master:21000] > select current_timestamp();+-------------------------------+| current_timestamp() |+-------------------------------+| 2023-01-16 10:08:22.152984000 |+-------------------------------+

2、时间戳取整

Impala 2.11 之前的取整当前时间的写法:select trunc(now(), 'YEAR') --取整到年份, 得到当年 1 月 1 日 0 点 0 分select trunc(now(), 'MONTH') --取整到月份, 得到当月 1 日 0 点 0 分select trunc(now(), 'DD') --取整到日期, 得到当天 0 点 0 分select trunc(now(), 'DAY') --取整到星期, 得到本星期第一天的 0 点 0 分select trunc(now(), 'HH24') --取整到小时, 得到当前小时的 0 分select trunc(now(), 'MI') --取整到分钟, 得到当前分钟 0 秒Impala 2.11 之后增加了 date_trunc() 函数, 下面是几个取整的写法:date_trunc('year',now())date_trunc('month',now())date_trunc('week',now())date_trunc('day',now())date_trunc('hour',now())date_trunc('minute',now())date_trunc() 的语法和 date_part() 类似, 下面是完整的时间 part 列表:microsecondsmillisecondssecondminutehourdayweekmonthyeardecadecenturymillennium

3、时间戳提取

​ date_part(‘year’, now())

​ extract(now(), ‘year’)

​ extract(year from now())

​ year(now())

-- date_part('year', now())[master:21000] > select date_part('year', now());+--------------------------+| date_part('year', now()) |+--------------------------+| 2023 |+--------------------------+-- date_part('day', now())[master:21000] > select date_part('day', now());+-------------------------+| date_part('day', now()) |+-------------------------+| 16|+-------------------------+-- extract(now(), 'year')[master:21000] > select extract(now(), 'year');+------------------------+| extract(now(), 'year') |+------------------------+| 2023 |+------------------------+-- extract(year from now())[master:21000] > select extract(year from now());+--------------------------+| extract(year from now()) |+--------------------------+| 2023 |+--------------------------+-- year(now())[master:21000] > select year(now());+--------------+| year(now())|+--------------+| 2023 |+--------------+-- month(now())[master:21000] > select month(now());+--------------+| month(now()) |+--------------+| 1|+--------------+-- -- day(now())[master:21000] > select day(now());+--------------+| day(now()) |+--------------+| 16 |+--------------+

4、时间加减

时间戳可以直接加减 interval n days/months/years/hours/minutes .也可以使用下面的函数:years_add(timestamp t, int n)years_sub(timestamp t, int n)months_add(timestamp t, int n)months_sub(timestamp t, int n)days_add(timestamp t, int n)days_sub(timestamp t, int n)hours_add(timestamp t, int n)hours_sub(timestamp t, int n)minutes_add(timestamp t, int n)minutes_sub(timestamp t, int n)-- years_add(now(),2)[master:21000] > select years_add(now(),2);+-------------------------------+| years_add(now(), 2) |+-------------------------------+| 2025-01-16 10:35:30.402209000 |+-------------------------------+也可以用下面两个通用的函数:date_add(time, interval N months)date_sub(time, interval N hours)-- date_add(time, interval N year)[master:21000] > select date_add(now(),interval 1 years);+-----------------------------------+| date_add(now(), interval 1 years) |+-----------------------------------+| 2024-01-16 10:45:26.244200000 |+-----------------------------------+-- date_sub(time, interval N year)[master:21000] > select date_sub(now(),interval 1 years);+-----------------------------------+| date_sub(now(), interval 1 years) |+-----------------------------------+| 2022-01-16 10:45:49.260996000 |+-----------------------------------+

5、获取月份最后一天

​ last_day(timestamp t)

[master:21000] > select last_day(now());+---------------------+| last_day(now()) |+---------------------+| 2023-01-31 00:00:00 |+---------------------+

6、获取月份间隔

[master:21000] > select months_between('2023-06-18', '2023-01-18');+--------------------------------------------+| months_between('2023-06-18', '2023-01-18') |+--------------------------------------------+| 5|+--------------------------------------------+

7、获取时间间隔天数

​ datediff(end time,start time)

-- datediff(end time,start time)[master:21000] > select datediff('2023-01-18 10:47:24', '2023-01-16 15:47:16');+--------------------------------------------------------+| datediff('2023-01-18 10:47:24', '2023-01-16 15:47:16') |+--------------------------------------------------------+| 2|+--------------------------------------------------------+

8、获取前昨天日期

yyyyMMdd格式

substr(regexp_replace(cast(date_sub(now(),1) as string),‘-’,‘’),1,8)

-- substr(regexp_replace(cast(date_sub(now(),1) as string),'-',''),1,8)[dn02:21000] > select substr( regexp_replace(cast(date_sub(now(),2) as string),'-',''),1,8);+---------------------------------------------------------------------------+| substr(regexp_replace(cast(date_sub(now(), 2) as string), '-', ''), 1, 8) |+---------------------------------------------------------------------------+| 20230129|+---------------------------------------------------------------------------+

yyyy-MM-dd格式

substr(cast(date_sub(now(),1) as string),1,10)

-- substr(cast(date_sub(now(),1) as string),1,10)[dn02:21000] > select substr(cast(date_sub(now(),1) as string),1,10);+---------------------------------------------------+| substr(cast(date_sub(now(), 1) as string), 1, 10) |+---------------------------------------------------+| 2023-01-30|+---------------------------------------------------+