DEV01-GBase 8a MPP Cluster SQL 编码进阶篇之内置函数07

（十）正则函数

    使用正则表达式的内置函数叫做正则函数。在讲解正则函数之前需要熟悉正则表达式。

0、正则表达式

• 正则的功能
  (1) 校验数据有效性
  (2) 查找符合要求的文本内容
  (3) 对文本进行切割，替换等操作
• 元字符的概念
  (1) 正则表达式是由一系列元字符组成的匹配字串；
  (2) 元字符是正则表达式中具有特殊意义的专用字符；
  (3) 构成正则表达式的基本元件。
• 元字符的构成
  

(1) 特殊单字符

(2) 空白符

(3) 量词

(4) 范围

1、REGEXP_LIKE (source_char, pattern [, match_paramater])
功能：正则匹配，使用正则表达式匹配源字符串。

• REGEXP_LIKE 练习
  测试表 test_reg 的数据

(1) 查询value中以1开头60结束的记录并且长度是7位

select * from test_reg where value like '1____60'; # 使用 like，结果是正确的。每个“_”表示一个字符。
select * from test_reg where regexp_like(value,'1.{4}60'); # 贪婪模式造成多匹配出两个记录。贪婪模式是最大长度匹配，只要1到60之间找到4个字符，即为匹配。
select * from test_reg where regexp_like(value,'^1.{4}60$'); # 这是符合需求的最精确的正则。非贪婪模式，1到60之间存在且只有4个字符，才能匹配。“^”表示匹配开始，“$”表示匹配结束。
复制

(2) 查询value中以1开头60结束的记录并且长度是7位并且全部是数字的记录；

select * from test_reg where regexp_like(value,'^1[0-9]{4}60$');
select * from test_reg where regexp_like(value,'^1[\\d]{4}60$'); # \d 匹配一个数字字符
select * from test_reg where regexp_like(value,'^1[[:digit:]]{4}60$');-- 也可以使用字符集
复制

(3) 查询value中不是纯数字的记录

select * from test_reg where not regexp_like(value,'^[[:digit:]]+$');
# “[[:digit:]]” 是 PHP 正则表达式的通用字符簇，表示任何数字
复制

(4) 查询value中不包含任何数字的记录

select * from test_reg where regexp_like(value,'^[^[:digit:]]+$');
# “[[:digit:]]” 是 PHP 正则表达式的通用字符簇，表示任何数字。该表达式匹配到不存在数字的源字串
复制

(5) 查询以12或者1b开头的记录.不区分大小写

select * from test_reg where regexp_like(value,'^1[2b]','i'); # 不区分大小写
/*
id	value
1	1234560
3	1b3b560
7	123 45
10	1B
*/
复制

(6) 查询以12或者1b开头的记录.区分大小写

select * from test_reg where regexp_like(value,'^1[2B]');
/*
id	value
1	1234560
7	123 45
10	1B
*/
复制

(7) 查询数据中包含空白的记录

select * from test_reg where regexp_like(value,'[[:space:]]');
# “[[:space:]]” 是 PHP 正则表达式的通用字符簇，表示任意空白字符（空格、制表符、回车、换行）
复制

(8) 查询所有包含小写字母或者数字的记录

select * from test_reg where regexp_like(value,'^([a-z]+|[0-9]+)$');
# 注意 “[a-z]+” 和 “[0-9]+” 之间 有 “|” 表示或的关系
复制

(9) 查询任何包含标点符号的记录

select * from test_reg where regexp_like(value,'[[:punct:]]');
# “[[:punct:]]” 是 PHP 正则表达式的通用字符簇，表示任何标点符号
复制

2、REGEXP_REPLACE (source_char,pattern[,replace_string[,position [,occurrence[match_option]]]])
功能：正则替换，用指定的字符串替换源字符串中与指定正则表达式相匹配的字符串。

• REGEXP_REPLACE 练习

# 1、替换数字为字母 Q
select value, REGEXP_REPLACE (value,'[0-9]+','Q') AS Result from test_reg

# 2、替换第三个单词为 READ
#update test_reg set value='I can swim' where id=2
select value, REGEXP_REPLACE(value,'\\w+','READ', 1, 3) Result
from test_reg
where id=2
复制

3、REGEXP_INSTR (source_char,pattern[,position[,occurrence[,return_opt[,match_parameter[,subexpr]]]]])
功能：正则搜索，获得匹配字符串的位置。

subexpr 参数不常用，本文不在赘述。

• REGEXP_INSTR 练习

# 1、搜索数字第一次出现的位置
select value, REGEXP_INSTR (value,'[0-9]+') AS Result from test_reg

# 2、找数字（从第一个字母开始匹配，找第1个匹配项目的紧跟着的第一个字符的位置）
select value, REGEXP_INSTR (value,'[0-9]+', 1, 1, 1) AS Result from test_reg
# position=1 表示从 value 字符串的第一个位置开始搜索；occurrence=1 表示找到第一次出现的位置；return_opt=1 表示匹配字符的下一个位置

# 3、找到第三个单词的位置
select value, REGEXP_INSTR(value,'\\w+', 1, 3) Result
from test_reg
where id=2
复制

4、REGEXP_SUBSTR (source_char,pattern[,position[,occurrence]])
功能：获得匹配到的字符串。

• REGEXP_SUBSTR 练习

# 1、搜索第一次出现的数字
select value, REGEXP_SUBSTR (value,'[0-9]+') AS Result from test_reg

# 2、找到第三个单词的内容
select value, REGEXP_SUBSTR(value,'\\w+', 1, 3) Result
from test_reg
where id=2
复制

（十一）信息函数

1、Database()：返回当前数据库名字，比如

courseware
复制

2、User()：返回当前用户名，比如

root@127.0.0.1
复制

3、Version()：返回当前系统版本，比如

9.5.2.39.126761
复制

4、CHARSET(<字符串>)：返回字符串参数使用的字符集

（十二）函数与性能

1、函数不要用在字段上：
【优化前】select Sid, Sname, Sage from Student
where to_char(Sage,‘YYYY’) >= 2013;
【优化后】select Sid, Sname, Sage from Student
where Sage >= ‘2013-01-01’;

小贴士：函数使用在字段上，每一行字段数据都会执行函数，智能索引失效；函数使用在值上，则只执行一次，还能利用智能索引。

2、避免使用无必要的转换函数：
【优化前】select Sid, Sname, Sage from Student
where Sage >= to_date(‘2010-08-06’, ‘YYYY-MM-DD’);
【优化后】select Sid, Sname, Sage from Student
where Sage >= ‘2010-08-06’;

小贴士：WHERE 子句中，表达式的比对数值能用隐式转换就不使用数据类型转换函数。

3、超大数据量不能使用 NOT EXISTS：
【优化前】select * from Student where NOT EXISTS(select 1 from
score where SId=Student.SId)
【优化后】select * from Student where SId not in(select SId from score where SId is not null)

小贴士：需要量表关联，但是关联表字段不同值很少，表数据量较大，NOT EXISTS 会导致产生大量临时文件。改成 not in 后效率会有较大提高，但是需要加上关联字段不为空的条件。

4、巧用 COUNT( DISTINCT)：
【优化前】select count(DISTINCT CId),count(DISTINCT TId) from course;
【优化后】select * from(select count(DISTINCT CId) from course)T1
join (select count(DISTINCT TId) from course)T2;

小贴士：对多列去重计数，当前执行计划先在各节点对参与 count(distinct) 的列进行分组去重，结果再汇总到一个节点进行 count(distinct) 运算。当 count(distinct) 列很多时，参与 group by 运算的列也很多，去重效果不理想，导致大量数据要拉到一个节点进行 count(distinct) 运算，拉表和汇总计算耗时非常长。优化的方法是各列分别计算 count(distinct)，提高去重效果，然后对结果集进行拼接。

5、简单的模糊匹配不要使用正则匹配函数：
【优化前】select * from test_reg where REGEXP_LIKE(value, ‘^1.{4}60$’);
【优化后】select * from test_reg where value LIKE ‘1____60’

小贴士：正则匹配函数应用场景是复杂的模糊匹配，功能强大的同时，资源耗费肯定高于原生的 LIKE。