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 列表:
microseconds
milliseconds
second
minute
hour
day
week
month
year
decade
century
millennium
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 |
+---------------------------------------------------+