SQL正则表达式 - 使用另一个字段中的子字符串进行替换

Question

SQL正则表达式 - 使用另一个字段中的子字符串进行替换

sqlregexoracleregexp-replaceregexp-substr

6

我有一个问卷反馈的数据库表格（Oracle 11g），其中包括多选和多项选择题。选项列中包含用户可以选择的每个值，而答案列中则包含他们所选内容的数字值。

ID_NO     OPTIONS                               ANSWERS
1001      Apple Pie|Banana-Split|Cream Tea      1|2
1002      Apple Pie|Banana-Split|Cream Tea      2|3
1003      Apple Pie|Banana-Split|Cream Tea      1|2|3

我需要一个查询来解码答案，将答案的文本版本作为单个字符串。

ID_NO     ANSWERS     ANSWER_DECODE
1001      1|2         Apple Pie|Banana-Split
1002      2|3         Banana-Split|Cream Tea
1003      1|2|3       Apple Pie|Banana-Split|Cream Tea

我曾经尝试使用正则表达式来替换值和获取子字符串，但是我无法想出一种正确合并两者的方法。

WITH feedback AS (
  SELECT 1001 id_no, 'Apple Pie|Banana-Split|Cream Tea' options, '1|2' answers FROM DUAL UNION
  SELECT 1002 id_no, 'Apple Pie|Banana-Split|Cream Tea' options, '2|3' answers FROM DUAL UNION
  SELECT 1003 id_no, 'Apple Pie|Banana-Split|Cream Tea' options, '1|2|3' answers FROM DUAL )
SELECT 
  id_no,
  options,
  REGEXP_SUBSTR(options||'|', '(.)+?\|', 1, 2) second_option,
  answers,
  REGEXP_REPLACE(answers, '(\d)+', ' \1 ') answer_numbers,
  REGEXP_REPLACE(answers, '(\d)+', REGEXP_SUBSTR(options||'|', '(.)+?\|', 1, To_Number('2'))) "???"
FROM feedback

我不希望在SQL中手动定义或解码答案；由于有很多调查问卷，提问方式不同（选项数量也不同），所以我希望有一种可以动态适用于所有问题的解决方案。

我尝试将选项和答案按LEVEL分成单独的行，并在代码匹配时重新连接它们，但是在实际数据集中运行速度非常慢（一个包含600行响应的5选项问题）。

WITH feedback AS (
  SELECT 1001 id_no, 'Apple Pie|Banana-Split|Cream Tea' options, '1|2' answers FROM DUAL UNION
  SELECT 1002 id_no, 'Apple Pie|Banana-Split|Cream Tea' options, '2|3' answers FROM DUAL UNION
  SELECT 1003 id_no, 'Apple Pie|Banana-Split|Cream Tea' options, '1|2|3' answers FROM DUAL )
SELECT
    answer_rows.id_no,
    ListAgg(option_rows.answer) WITHIN GROUP(ORDER BY option_rows.lvl)
FROM
  (SELECT DISTINCT
    LEVEL lvl,
    REGEXP_SUBSTR(options||'|', '(.)+?\|', 1, LEVEL) answer
  FROM
    (SELECT DISTINCT
      options,
      REGEXP_COUNT(options||'|', '(.)+?\|') num_choices
    FROM
      feedback)
  CONNECT BY LEVEL <= num_choices
  ) option_rows
  LEFT OUTER JOIN
  (SELECT DISTINCT
    id_no,
    to_number(REGEXP_SUBSTR(answers, '(\d)+', 1, LEVEL)) answer
  FROM
    (SELECT DISTINCT
      id_no,
      answers,
      To_Number(REGEXP_SUBSTR(answers, '(\d)+$')) max_answer
    FROM
      feedback)
  WHERE
    to_number(REGEXP_SUBSTR(answers, '(\d)+', 1, LEVEL)) IS NOT NULL
  CONNECT BY LEVEL <= max_answer
  ) answer_rows
    ON option_rows.lvl = answer_rows.answer
GROUP BY
    answer_rows.id_no
ORDER BY
  answer_rows.id_no

如果仅使用正则表达式无法解决问题，是否有比LEVEL更有效的方法来拆分值？或者是否有其他可行的方法？

- Pete Rennard-Cumming

请参见以下链接：https://dev59.com/mYTba4cB1Zd3GeqP4lV3 - QuestionC

为什么不用一个函数。应该更简单。 - Maheswaran Ravisankar

4个回答

1

看看这个简洁的解决方案：

   with sample_data as
(
  select 'ala|ma|kota' options, '1|2' answers from dual
  union all
  select 'apples|oranges|bacon', '1|2|3' from dual
  union all
  select 'a|b|c|d|e|f|h|i','1|3|4|5|8' from dual
)
select answers, options,
regexp_replace(regexp_replace(options,'([^|]+)\|([^|]+)\|([^|]+)','\' || replace(answers,'|','|\')),'[|]+','|') answer_decode
from sample_data;

输出：

  ANSWERS   OPTIONS              ANSWER_DECODE
--------- -------------------- ---------------------------
1|2       ala|ma|kota          ala|ma
1|2|3     apples|oranges|bacon apples|oranges|bacon
1|3|4|5|8 a|b|c|d|e|f|h|i      a|c|d|f|h|i

- Piotr Siekierski

如果调查中有超过3个可能的答案，会发生什么？ - DougieHauser

仍然可以使用，只需进行少量额外的清理 - 删除不必要的分隔符。 - Piotr Siekierski

0

我已经在MySQL中编写了一个紧密的解决方案（现在没有安装Oracle）-但我已经写出了需要更改的内容，以便查询在Oracle中正常工作。

此外，我的代码中最丑陋的部分在Oracle中将会更加美观，因为它具有更好的INSTR函数。

这个想法是使用数字列表（1到10，以支持每个调查多达10个选项）进行交叉连接，并将OPTIONS字段拆分成不同的行...（通过使用数字列表和Oracle的INSTR函数来实现，详见注释）。

从那里开始，您可以过滤掉未选择的行并将所有内容分组在一起。

-- I've used GROUP_CONCAT in MySQL, but in Oracle you'll have to use WM_CONCAT
select ID_NO, ANSWERS, group_concat(broken_down_options,'|') `OPTIONS`
from (
    select your_table.ID_NO, your_table.ANSWERS, 
            -- Luckily, you're using ORACLE so you can use an INSTR function that has the "occurrence" parameter
            -- INSTR(string, substring, [position, [occurrence]])
            -- use the nums.num field as input for the occurrence parameter
            -- and just put '1' under "position"
            case when nums.num = 1 
                then substr(your_table.`OPTIONS`, 1, instr(your_table.`OPTIONS`, '|') - 1)
                when nums.num = 2
                then substr(substr(your_table.`OPTIONS`, instr(your_table.`OPTIONS`, '|') + 1), 1, instr(substr(your_table.`OPTIONS`, instr(your_table.`OPTIONS`, '|') + 1), '|') - 1)
                else substr(your_table.`OPTIONS`,  length(your_table.`OPTIONS`) - instr(reverse(your_table.`OPTIONS`), '|') + 2) end broken_down_options
    from (select 1 num union all
        select 2 num union all
        select 3 num union all
        select 4 num union all
        select 5 num union all
        select 6 num union all
        select 7 num union all
        select 8 num union all
        select 9 num union all
        select 10 num
        ) nums 
        CROSS JOIN
        (select 1001 ID_NO, 'Apple Pie|Banana-Split|Cream Tea' `OPTIONS`, '1|2' ANSWERS union
        select 1002 ID_NO, 'Apple Pie|Banana-Split|Cream Tea' `OPTIONS`, '2|3' ANSWERS union
        select 1003 ID_NO, 'Apple Pie|Banana-Split|Cream Tea' `OPTIONS`, '1|2|3' ANSWERS
        ) your_table
    -- for example: 2|3 matches 2 and 3 but not 1
    where your_table.ANSWERS like concat(concat('%',nums.num),'%')
) some_query
group by ID_NO, ANSWERS

- DougieHauser

0

创建一个存储过程，并执行以下步骤：

声明一个与您大小相同的数组。
从第一行获取 option 数据。使用正则表达式或 level 提取管道之间的值，然后将它们存储在数组中。注意：这只是一次迭代。因此，您不需要为每一行重复执行此操作。
现在，在循环中，对于每一行，选择 answers 并使用数组值分配 answers 的值。

- Utsav

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Alex Poole · Accepted Answer

由于您每次都要展开每一行，所以速度很慢。您使用的连接条件是跨越所有行进行查找，因此最终会得到大量需要排序的数据——这可能就是为什么您最终使用了 DISTINCT 的原因。

您可以在连接条件中添加两个 PRIOR 子句，第一个子句用于保留 ID_NO，第二个子句用于避免循环——任何不确定性函数都可以，我选择了 dbms_random.value，但如果您喜欢，也可以使用 sys_guid 或其他内容。您也不需要太多子查询，只需使用两个即可；或者使用 CTEs，我认为这样更清晰：

WITH feedback AS (
  SELECT 1001 id_no, 'Apple Pie|Banana-Split|Cream Tea' options, '1|2' answers FROM DUAL UNION
  SELECT 1002 id_no, 'Apple Pie|Banana-Split|Cream Tea' options, '2|3' answers FROM DUAL UNION
  SELECT 1003 id_no, 'Apple Pie|Banana-Split|Cream Tea' options, '1|2|3' answers FROM DUAL
),
option_rows AS (
  SELECT
    id_no,
    LEVEL answer,
    REGEXP_SUBSTR(options, '[^|]+', 1, LEVEL) answer_text
  FROM feedback
  CONNECT BY LEVEL <= REGEXP_COUNT(options, '[^|]+')
  AND id_no = PRIOR id_no
  AND PRIOR dbms_random.value IS NOT NULL
),
answer_rows AS (
  SELECT
    id_no,
    REGEXP_SUBSTR(answers, '[^|]+', 1, LEVEL) answer
  FROM feedback
  CONNECT BY LEVEL <= REGEXP_COUNT(answers, '[^|]+')
  AND PRIOR id_no = id_no
  AND PRIOR dbms_random.value IS NOT NULL
)
SELECT
  option_rows.id_no,
  LISTAGG(option_rows.answer, '|') WITHIN GROUP (ORDER BY option_rows.answer) AS answers,
  LISTAGG(option_rows.answer_text, '|') WITHIN GROUP (ORDER BY option_rows.answer) AS answer_decode
FROM option_rows
JOIN answer_rows
ON option_rows.id_no = answer_rows.id_no
AND option_rows.answer = answer_rows.answer
GROUP BY option_rows.id_no
ORDER BY option_rows.id_no;

Which gets:

     ID_NO ANSWERS    ANSWER_DECODE                          
---------- ---------- ----------------------------------------
      1001 1|2        Apple Pie|Banana-Split                  
      1002 2|3        Banana-Split|Cream Tea                  
      1003 1|2|3      Apple Pie|Banana-Split|Cream Tea

我还修改了你的正则表达式模式，因此您不必添加或删除|。