主页 / 问题 / 1273193
Accepted
MaxU - stop genocide of UA
Asked:2022-04-22 04:07:09 +0000 UTC2022-04-22 04:07:09 +0000 UTC

如何正确分解复杂的 SQL 函数？

  • 772

继续这个问题。

在工作中，我必须使用各种数据源，并且经常遇到质量较差的数据（手动输入系统）并且存储在不符合 3NF（3x 范式）规则的表中。那些。通常多个实体存储在一个单元格中。

例如：

email
-------------------------------------
mail1@mail.com, mail2@domain.com mail3@gmail.com

或者

phone
----------------------------------------------------
0172-1234/567, +49123456789 and 089 / 123-4567

我的团队面临的任务是在不更改数据模型的情况下对这些数据进行梳理和规范化，并以以下形式获取相同的数据：

email
-------------------------------------
mail1@mail.com; mail2@domain.com; mail3@gmail.com

和

phone
----------------------------------------------------
01721234567; 0049123456789; 0891234567

标准化个人电子邮件、电话等的功能。已经实现和测试要对此类数据进行规范化，首先需要以垂直形式（即 done UNPIVOT）解析和获取这些类似 CSV 的字符串，然后应用规范化函数，最后将已经规范化的值打包回 CSV 字符串相同的分隔符 ( '; ')。

在这个答案中，受人尊敬的0xdb展示了如何有效地解析此类数据并生成UNPIVOT. 根据答案中的函数，我编写了以下通用函数，它应该能够使用各种规范化函数对电话号码、WEB 地址和电子邮件进行规范化。

结果就是这个怪物：

create or replace function normalize_csv_values (
    str             varchar2,
    typ             varchar2    := 'phone',
    target_sep      char        := '; '
) return varchar2
as
    pattern     varchar2(64) := '([^[:space:]].*?)((\s*[,;]\s*)|($))';
    tokens      varchartab := varchartab ();
    s           varchar2(96);
    c           int := 0;
    l_res       varchar2(1024);
begin
    case
        when lower(trim(typ)) in ('phone', 'fax')
            then pattern := '([^[:space:]].*?)((' || '\s*(,|;|oder|o\.|or|und|and)\s*' || ')|($))';
        when lower(trim(typ)) in ('url', 'web', 'link')
            then pattern := '([^[:space:]].*?)((' || '[,;]' || ')|($))';
        when lower(trim(typ)) in ('email')
            then pattern := '([^[:space:]].*?)((' || '[[:space:],;/]' || ')|($))';
    end case;
    <<split>> loop c := c + 1;
        s := regexp_substr (str, pattern, 1, c, null, 1);
        exit split when s is null;
        if lower(typ) in ('phone', 'fax') then
            s := normalize_phone(s, 6, 17, '\s*\W?(,|;|oder|o.|und|or)\W?\s*');
        elsif lower(typ) in ('url', 'web', 'link') then
            s := normalize_url(s, '^(w{3,4}\.)');
        elsif lower(typ) in ('email') then
            s := normalize_email(s, '[:space:],;/');
        end if;
        tokens.extend;
        tokens(tokens.last) := s;
    end loop;
    -- pack parsed CSV values back to CSV form, using specified [target_sep]
    select listagg(column_value, target_sep) within group (order by rownum)
    into l_res
    from table(tokens);
    return l_res;
end;
/

问题：

SRP (Single Responsibility Principle)你能告诉我如何以原则和的方式分解这个函数DRY (Don't Repeat Yourself)吗？

也许您可以以某种方式编写通用 RegEx 并制作一个包装函数来调用必要的规范化函数？

регулярные-выражения

3 个回答

  1. Best Answer

    实施建议。

    将所有函数中的正则表达式模式删除到单独的表中：

    create table normalizerconf (tabname, colname, ty, splitpatt, rmpatt) as
    select 'data1', 'col1', 'phone', '(.*?)((,\s+)|($))', '\D+' from dual  
/

    然后一个工作查询将如下所示（在db<>fiddle上）：

    create table data1 (id, col1) as
    select 1, '123/456789, 032-156789' from dual
/

with 
function getphonelist (
    col varchar2, splitpatt varchar2, rmpatt varchar2) return varchar2 is
begin
    return NormalizePhone (col, splitpatt, rmpatt).join();
end;
select d.col1, getphonelist (col1, splitpatt, rmpatt) result
from data1 d
cross join normalizerconf c
where c.tabname = 'data1' and colname = 'col1'
/

COL1                   RESULT                
---------------------- ----------------------
123/456789, 032-156789 123456789; 032156789

    为每个规范创建一个基本自定义类型。数据类型其继承类型：

    create or replace type Normalizer as object (
    tokens tokenList,
    member procedure split (str varchar2, pattern char), -- return tokenList,
    not instantiable member procedure normalize (rmpatt char),
    member function join (delimiter char := '; ') return varchar2
) not instantiable not final
/
create or replace type NormalizePhone under Normalizer (
    constructor function NormalizePhone (
        str varchar2, splitpatt char, rmpatt char) return self as result,
    overriding member procedure normalize (rmpatt char) -- return tokenList,
) instantiable final
/

    类型实现示例：

    create or replace type body Normalizer as
    member procedure split (str varchar2, pattern char) is
        s varchar2(96);
        c int := 0;
    begin
        <<split>> loop c := c + 1;
            s := regexp_substr (str, pattern, 1, c, null, 1);
            exit split when s is null;
            self.tokens.extend;
            self.tokens(tokens.last) := s;
        end loop;
    end split;
    member function join (delimiter char := '; ') return varchar2 is
        ret varchar2 (32767);
    begin
        for i in 1..self.tokens.count loop 
            ret := ret||tokens(i)||delimiter; end loop;
        return rtrim (ret, delimiter);
    end join;
end;
/

    create or replace type body NormalizePhone as
    constructor function NormalizePhone (
        str varchar2, splitpatt char, rmpatt char) return self as result is
    begin
        self.tokens := tokenList(); 
        self.split (str, splitpatt);
        normalize (rmpatt);
        return;
    end;
    overriding member procedure normalize (rmpatt char) is
    begin 
        for i in 1..self.tokens.count loop
            self.tokens(i) := regexp_replace (self.tokens(i), rmpatt);
        end loop;
    end;
end;
/
    • 5

  2. 这就是包中包含函数的解决方案的外观。

    来自此答案的数据的最终请求将如下所示：

    with function getphonelist (
    col varchar2, splitpatt varchar2, rmpatt varchar2) return varchar2 is
begin
    return
        packNormalizer.join (
            packNormalizer.normalize (
                packNormalizer.split (col, splitpatt), rmpatt));
end;
select d.col1, getphonelist (col1, splitpatt, rmpatt) result
from data1 d
cross join normalizerconf c
where c.tabname = 'data1' and colname = 'col1'
/

COL1                   RESULT                          
---------------------- --------------------------------
123/456789, 032-156789 123456789; 032156789

    包实现（在db<>fiddle上）：

    create or replace package packNormalizer as 
    function split (str varchar2, pattern char) return tokenList;
    function normalize (tl tokenList, rmpatt char) return tokenList;
    function join (tokens tokenList, delimiter char := '; ') return varchar2;
end;
/

    create or replace package body packNormalizer as
    function split (str varchar2, pattern char) return tokenList is
        tokens tokenList := tokenList ();
        s varchar2(96);
        c int := 0;
    begin
        <<split>> loop c := c + 1;
            s := regexp_substr (str, pattern, 1, c, null, 1);
            exit split when s is null;
            tokens.extend;
            tokens(tokens.last) := s;
        end loop;
        return tokens;
    end split;
    function normalize (tl tokenList, rmpatt char) return tokenList is
        tokens tokenList := tl;
    begin 
        for i in 1..tokens.count loop
            tokens(i) := regexp_replace (tokens(i), rmpatt);
        end loop;
        return tokens;
    end; 
    function join (tokens tokenList, delimiter char := '; ') return varchar2 is
        ret varchar2 (32767);
    begin
        for i in 1..tokens.count loop ret := ret||tokens(i)||delimiter; end loop;
        return rtrim (ret, delimiter);
    end join;
end;
/
    • 5

  3. 到目前为止，我已经完成了分解，取出了获取 RegEx 并将各种实体规范化为单独函数的逻辑。

    create or replace function normalize_csv_values (
    str             varchar2,
    typ             varchar2    := 'phone',
    target_sep      char        := '; '
) return varchar2
as
    s           varchar2(96);
    n           int := 0;
    pattern     varchar2(64)    := '\s*[,;]\s*';
    tokens      varchartab      := varchartab ();
    l_re_pref   varchar2(64)    := '([^[:space:]].*?)((';
    l_re_suff   varchar2(64)    := ')|($))';
    l_type      varchar2(128)   := lower(trim(typ));
    l_res       varchar2(4096);
begin
    -- normalizes CSV-like strings in the following way:
    --  1. in a loop split [str] by the separator that is returned from "get_csv_split_re_pattern()"
    --      1.1 get a single splitted value
    --      1.2 normalize it, using "normalize_value(val, l_type)"
    --      1.3 append normalized value to collection [tokens]
    --  2. pack values from [tokens] collection back to CSV string using [target_sep] separator
    --  3. return resulting CSV string

    -- get split RegEx for the [typ] entity type
    pattern := l_re_pref || get_csv_split_re_pattern(l_type) || l_re_suff;

    -- loop through values in the CSV string
    <<split>> loop n := n + 1;
        -- get N-th value from the CSV string
        s := regexp_substr (str, pattern, 1, n, null, 1);
        exit split when s is null;
        s := normalize_value(s, l_type);
        tokens.extend;
        tokens(tokens.last) := s;
    end loop;

    -- pack parsed CSV values back to CSV form, using specified [target_sep]
    select listagg(column_value, target_sep) within group (order by rownum)
    into l_res
    from table(tokens);

    return l_res;
end;
/
    • 3

相关问题