DuckDB 正则这么玩

披头

发布于 2024-11-21 03:25:11

24500

代码可运行

文章被收录于专栏：datartisandatartisan

运行总次数：0

代码可运行

前文学习了 Excel 正则，今天咱们来学习下 DuckDB 正则。

DuckDB 中的正则表达式，基本上遵循了正则表达式的一些通用的规则，比如元字符、转义序列等，但是也有自己的特色（方言），在某些方面可以更好的实现需求。

需要说明的是 options 是正则函数的可选参数，表示一些辅助的可选项

可选项说明见下表：

options	说明
c	区分大小写
i	不区分大小写
l	匹配原始字符（字符的字面内容），而不是元字符
m、n、p	换行符敏感匹配
g	全局替换，仅 regexp_replace 可使用
s	换行符不敏感匹配

提取字符 regexp_extract

提取字符的场景，应该是最常用的。

基本语法为：

regexp_extract(string, pattern[, group = 0][, options])

其中：

string：是待提取字符
pattern：是正则表达式
group：是可选参数，表示捕获组的序号，默认是 0，也就是返回第一个捕获组
options：是可选参数，表示一些辅助的可选项，需要特别注意的是：当使用 options 参数时，group 参数不能省略。

请看示例：

D SELECT regexp_extract('ab^/$cd', '^/$', 0, 'l');
┌──────────────────────────────────────────┐
│ regexp_extract('ab^/$cd', '^/$', 0, 'l') │
│                 varchar                  │
├──────────────────────────────────────────┤
│ ^/$                                      │
└──────────────────────────────────────────┘
D SELECT regexp_extract('ab^/$cd', '^/$', 'l');
Binder Error: Could not choose a best candidate function for the function call "regexp_extract(STRING_LITERAL, STRING_LITERAL, STRING_LITERAL)". In order to select one, please add explicit type casts.
        Candidate functions:
        regexp_extract(VARCHAR, VARCHAR, VARCHAR[]) -> VARCHAR
        regexp_extract(VARCHAR, VARCHAR, INTEGER) -> VARCHAR

LINE 1: SELECT regexp_extract('ab^/$cd', '^/$', 'l');
               ^
D SELECT regexp_extract('ab^/$cd', '^/$', 1, 'l');
┌──────────────────────────────────────────┐
│ regexp_extract('ab^/$cd', '^/$', 1, 'l') │
│                 varchar                  │
├──────────────────────────────────────────┤
│                                          │
└──────────────────────────────────────────┘
D

当只有一个捕获组时
- group = 0 表示获取全部捕获字符
当捕获组大于一个时
- group = 0 表示获取全部捕获字符
- group = 1 表示仅获取第一个捕获组的捕获字符
- group = 2 表示仅获取第二个捕获组的捕获字符
- 以此类推......

请看示例：

D SELECT regexp_extract('abc', '(a)(b)');
┌─────────────────────────────────┐
│ regexp_extract('abc', '(a)(b)') │
│             varchar             │
├─────────────────────────────────┤
│ ab                              │
└─────────────────────────────────┘
D SELECT regexp_extract('abc', '(a)(b)', 0);
┌────────────────────────────────────┐
│ regexp_extract('abc', '(a)(b)', 0) │
│              varchar               │
├────────────────────────────────────┤
│ ab                                 │
└────────────────────────────────────┘
D SELECT regexp_extract('abc', '(a)(b)', 1);
┌────────────────────────────────────┐
│ regexp_extract('abc', '(a)(b)', 1) │
│              varchar               │
├────────────────────────────────────┤
│ a                                  │
└────────────────────────────────────┘
D SELECT regexp_extract('abc', '(a)(b)', 2);
┌────────────────────────────────────┐
│ regexp_extract('abc', '(a)(b)', 2) │
│              varchar               │
├────────────────────────────────────┤
│ b                                  │
└────────────────────────────────────┘
D

替换字符 regexp_replace

在数据处理过程中，可能会遇到字符格式不规范的情况，这时候就可以使用替换字符正则来进行处理了。

基本语法为：

regexp_replace(string, pattern, replacement[, options])

其中：

string：为待替换字符
pattern：为正则表达式
replacement：为替换后的内容
options：是可选参数，表示一些辅助的可选项

请看示例：

D SELECT regexp_replace('aaa', 'a', 'X');
┌─────────────────────────────────┐
│ regexp_replace('aaa', 'a', 'X') │
│             varchar             │
├─────────────────────────────────┤
│ Xaa                             │
└─────────────────────────────────┘
D SELECT regexp_replace('aaa', 'a', 'X', 'g');
┌──────────────────────────────────────┐
│ regexp_replace('aaa', 'a', 'X', 'g') │
│               varchar                │
├──────────────────────────────────────┤
│ XXX                                  │
└──────────────────────────────────────┘
D

当捕获组包含多个时，regexp_replace 函数可以使用 \d 对指定的捕获组进行处理（d 表示捕获组的组号，d 取 0 时，表示对全部捕获组进行替换处理。

请看示例：

D SELECT regexp_replace('abc', '(b|c)', '\1\1\1\1');
┌────────────────────────────────────────────┐
│ regexp_replace('abc', '(b|c)', '\1\1\1\1') │
│                  varchar                   │
├────────────────────────────────────────────┤
│ abbbbc                                     │
└────────────────────────────────────────────┘
D SELECT regexp_replace('abc', '(b|c)', '\1\1\1\1', 'g');
┌─────────────────────────────────────────────────┐
│ regexp_replace('abc', '(b|c)', '\1\1\1\1', 'g') │
│                     varchar                     │
├─────────────────────────────────────────────────┤
│ abbbbcccc                                       │
└─────────────────────────────────────────────────┘
D SELECT regexp_replace('abc', '(b)(c)', '\1\1\1\1');
┌─────────────────────────────────────────────┐
│ regexp_replace('abc', '(b)(c)', '\1\1\1\1') │
│                   varchar                   │
├─────────────────────────────────────────────┤
│ abbbb                                       │
└─────────────────────────────────────────────┘
D SELECT regexp_replace('abc', '(b)(c)', '\2\2\2\2');
┌─────────────────────────────────────────────┐
│ regexp_replace('abc', '(b)(c)', '\2\2\2\2') │
│                   varchar                   │
├─────────────────────────────────────────────┤
│ acccc                                       │
└─────────────────────────────────────────────┘
D SELECT regexp_replace('abc', '(b)(c)', '\0\0\0\0');
┌─────────────────────────────────────────────┐
│ regexp_replace('abc', '(b)(c)', '\0\0\0\0') │
│                   varchar                   │
├─────────────────────────────────────────────┤
│ abcbcbcbc                                   │
└─────────────────────────────────────────────┘
D

理解了以上内容，现在再来看看下面这个复杂的示例：

假设原始数据是这样的

timeslot	location	event
2024-10-10 9am	room Mallard	Keynote
2024-10-10 10.30am	room Mallard	Customer stories
2024-10-10 10.30am	room Fusca	Deep dive 1
2024-10-10 12.30pm	main hall	Lunch
2024-10-10 2pm	room Fusca	Deep dive 2

现在想把 timeslot 转换为规范的时间格式，以便进行下一步的处理。目前的 timeslot 时分秒部分，有的包含分钟信息，有的不包含，需要统一一下。

这里会用到一个小技巧，如果将三参中的 \d 调整为 \d.00 可以实现小数位统一是两位的效果。

请看示例：

D select regexp_replace('9', '(\d+)', '\1.00');
┌───────────────────────────────────────┐
│ regexp_replace('9', '(\d+)', '\1.00') │
│                varchar                │
├───────────────────────────────────────┤
│ 9.00                                  │
└───────────────────────────────────────┘
D select regexp_replace('12', '(\d+)', '\1.00');
┌────────────────────────────────────────┐
│ regexp_replace('12', '(\d+)', '\1.00') │
│                varchar                 │
├────────────────────────────────────────┤
│ 12.00                                  │
└────────────────────────────────────────┘
D

具体到上面的问题，正则表达式可以这么写：

D select regexp_replace('2024-10-10 9am', '( \d+)(am|pm)', '\1.00\2');
┌──────────────────────────────────────────────────────────────┐
│ regexp_replace('2024-10-10 9am', '( \d+)(am|pm)', '\1.00\2') │
│                           varchar                            │
├──────────────────────────────────────────────────────────────┤
│ 2024-10-10 9.00am                                            │
└──────────────────────────────────────────────────────────────┘
D select regexp_replace('2024-10-10 10.30am', '( \d+)(am|pm)', '\1.00\2');
┌──────────────────────────────────────────────────────────────────┐
│ regexp_replace('2024-10-10 10.30am', '( \d+)(am|pm)', '\1.00\2') │
│                             varchar                              │
├──────────────────────────────────────────────────────────────────┤
│ 2024-10-10 10.30am                                               │
└──────────────────────────────────────────────────────────────────┘
D

完整SQL如下：

D CREATE TABLE schedule_raw AS SELECT * FROM 'https://duckdb.org/data/schedule.csv';                
D
D SELECT * FROM schedule_raw;
┌────────────────────┬──────────────┬──────────────────┐
│      timeslot      │   location   │      event       │
│      varchar       │   varchar    │     varchar      │
├────────────────────┼──────────────┼──────────────────┤
│ 2024-10-10 9am     │ room Mallard │ Keynote          │
│ 2024-10-10 10.30am │ room Mallard │ Customer stories │
│ 2024-10-10 10.30am │ room Fusca   │ Deep dive 1      │
│ 2024-10-10 12.30pm │ main hall    │ Lunch            │
│ 2024-10-10 2pm     │ room Fusca   │ Deep dive 2      │
└────────────────────┴──────────────┴──────────────────┘
D
D SELECT
路     timeslot
路         .regexp_replace('( \d+)(am|pm)', ' \1.00\2')
路         .strptime('%Y-%m-%d %H.%M%p') AS timeslot
路     ,location
路     ,event
路 FROM schedule_raw;
┌─────────────────────┬──────────────┬──────────────────┐
│      timeslot       │   location   │      event       │
│      timestamp      │   varchar    │     varchar      │
├─────────────────────┼──────────────┼──────────────────┤
│ 2024-10-10 09:00:00 │ room Mallard │ Keynote          │
│ 2024-10-10 10:30:00 │ room Mallard │ Customer stories │
│ 2024-10-10 10:30:00 │ room Fusca   │ Deep dive 1      │
│ 2024-10-10 12:30:00 │ main hall    │ Lunch            │
│ 2024-10-10 14:00:00 │ room Fusca   │ Deep dive 2      │
└─────────────────────┴──────────────┴──────────────────┘
D

通过以上处理，timeslot 已经是 timestamp 格式了，非常方便进行进一步的处理。

匹配字符 regexp_matches

由于正则函数 regexp_matches 的返回结果固定为 true 或者 flase，其实际使用场景有限，而且函数 regexp_matches 使用时会尽可能优化为 LIKE 函数以获取更好的性能。所以，其使用场景仅限于 LIKE 无法实现的复杂匹配场景。

请看示例：

D SELECT regexp_matches('abcd', 'ABC', 'c'); 
┌────────────────────────────────────┐
│ regexp_matches('abcd', 'ABC', 'c') │
│              boolean               │
├────────────────────────────────────┤
│ false                              │
└────────────────────────────────────┘
D SELECT regexp_matches('abcd', 'ABC', 'i'); 
┌────────────────────────────────────┐
│ regexp_matches('abcd', 'ABC', 'i') │
│              boolean               │
├────────────────────────────────────┤
│ true                               │
└────────────────────────────────────┘
D SELECT regexp_matches('ab^/$cd', '^/$', 'l'); 
┌───────────────────────────────────────┐
│ regexp_matches('ab^/$cd', '^/$', 'l') │
│                boolean                │
├───────────────────────────────────────┤
│ true                                  │
└───────────────────────────────────────┘
D SELECT regexp_matches(E'hello\nworld', 'hello.world', 'p'); 
┌────────────────────────────────────────────────────┐
│ regexp_matches('hello\nworld', 'hello.world', 'p') │
│                      boolean                       │
├────────────────────────────────────────────────────┤
│ false                                              │
└────────────────────────────────────────────────────┘
D SELECT regexp_matches(E'hello\nworld', 'hello.world', 's');
┌────────────────────────────────────────────────────┐
│ regexp_matches('hello\nworld', 'hello.world', 's') │
│                      boolean                       │
├────────────────────────────────────────────────────┤
│ true                                               │
└────────────────────────────────────────────────────┘
D