我正在尝试分析以下与分层和正则表达式相关的查询，任何人都可以帮助我理解吗?

Question

我遇到了下面的查询，但我无法完成与以下查询相关的分析。以下查询的主要目的是将数字转换为字母表。但是分层查询的使用让我感到困惑。

merge into s_export ex
using (
select
listagg(n, '') within group (order by lv) new_val,
row_id
from
(
SELECT
connect_by_root rowid row_id,
LEVEL lv,
CASE
WHEN Regexp_like(Regexp_substr( file_as, '[^0-9]+|((d+)(st|nd|rd|th)?)', 1, LEVEL ), 'd+')
THEN spell_number(
Regexp_substr( file_as, '[^0-9]+|((d+)(st|nd|rd|th)?)', 1, LEVEL, NULL, 2),
Regexp_substr( file_as, '[^0-9]+|((d+)(st|nd|rd|th)?)', 1, LEVEL, NULL, 3)
)
ELSE Regexp_substr( file_as, '[^0-9]+|((d+)(st|nd|rd|th)?)', 1, LEVEL )
END N
FROM s_export d
CONNECT BY NOCYCLE Regexp_substr( file_as, '([^0-9]+)|(d+)(st|nd|rd|th)?', 1, LEVEL ) IS NOT NULL
and rowid = prior rowid
and prior dbms_random.value is not null
)
group by row_id
) t
on (t.row_id = ex.rowid)
when matched then
update set ex.file_as = t.new_val;

示例数据集：

create table s_export  (file_as varchar2(2000));

insert into s_export  values ('Collection Four') ;
insert into s_export  values ('OM_Forty-One One');
insert into s_export  values ('OM_Twenty-Two | SOFifteen');
insert into s_export  values ('1st');
insert into s_export  values ('3M');
insert into s_export  values ('Collection Six');
insert into s_export  values ('2ND');
insert into s_export  values ('11TH100');

以下是我到目前为止的理解：

1. 我们正在对表s_export列执行更新file_as只要有任何数字说1，它就会转换这个 到'one'.

2. 就LEVEL用于Regexp_substr工作而言。

Answer 1

我已经分析了上面提到的查询，以下是我的分析以供参考。

考虑到我们的表格中有示例数据s_export，我们的目的是将字符串中出现的数值转换为字母表。

file_as     ROW_ID
-------     -------
123Test123  101

我们可以从最里面开始将查询分为 3 个部分

第 1 步：将字符串转换为字母表。

SELECT
connect_by_root rowid row_id,
LEVEL lv,
CASE
WHEN Regexp_like(Regexp_substr( file_as, '[^0-9]+|((d+)(st|nd|rd|th)?)', 1, LEVEL ), 'd+')
THEN spell_number(
Regexp_substr( file_as, '[^0-9]+|((d+)(st|nd|rd|th)?)', 1, LEVEL, NULL, 2),
Regexp_substr( file_as, '[^0-9]+|((d+)(st|nd|rd|th)?)', 1, LEVEL, NULL, 3)
)
ELSE Regexp_substr( file_as, '[^0-9]+|((d+)(st|nd|rd|th)?)', 1, LEVEL )
END N
FROM s_export d
CONNECT BY NOCYCLE Regexp_substr( file_as, '([^0-9]+)|(d+)(st|nd|rd|th)?', 1, LEVEL ) IS NOT NULL
and rowid = prior rowid
and prior dbms_random.value is not null

如果file_as列中有任何字符串，例如在我们的例子中"123Test123"，则此查询将获取特定行的row_id并从中获取子字符串，例如将此字符串row_id视为 101，现在将有 3 行具有相同的row_id唯一的区别是没有级别(拆分数量(，每个数字都将转换为字母表， 使用 (spell_number(

原值：123Test123

ROW_ID  LV      N
------- ------- -------
101     1       One Hundred Twenty-Three
101     2       Test
101     3       One Hundred Twenty-Three

2：对转换中获得的数据进行分组

select
listagg(n, '') within group (order by lv) new_val,
row_id
from (
---Query from Step 1
)  group by row_id

现在，从上面的输出中获得结果，我们将执行"listagg"(例如将列枢轴到行(并连接列"N"的值，从最内部的查询中获取单个字符串值，以在分组的基础上获得单个字符串值 关于row_id，

NEW_VAL                                                     ROW_ID
-------                                                     -------
One Hundred Twenty-Three Test One Hundred Twenty-Three      101

步骤 3：将数据合并到原始表的列中

merge into s_export ex
using (
--Query from Step 2 (Including Step 1)
)  t
on (t.row_id = ex.rowid)
when matched then
update set ex.file_as = t.new_val;

现在，我们将在row_id的基础上，将从上述查询中获得的"NEW_VAL"合并到s_export的"file_as"列中。

最终转换结果：

file_as                                                     ROW_ID
-------                                                     -------
One Hundred Twenty-Three Test One Hundred Twenty-Three      101