SQL如何将正则匹配到的多组TID、重量值拆分为独立新行

直接执行以下SQL即可实现需求：

WITH max_occurrences AS (
    SELECT 
        r_id,
        my_comment,
        -- 计算每条评论需要拆分的行数：取TID和重量的最大出现次数
        GREATEST(
            REGEXP_COUNT(my_comment, 'TID\s*\d+', 1, 'i'),
            REGEXP_COUNT(my_comment, '\d+\.?\d*\s*k\.?g', 1, 'i')
        ) AS max_cnt
    FROM reviews
)
SELECT
    t.r_id,
    REGEXP_SUBSTR(t.my_comment, 'TID\s*(\d+)', 1, lvl, 'i', 1) AS tid,
    REGEXP_SUBSTR(t.my_comment, '(\d+\.?\d*)\s*k\.?g', 1, lvl, 'i', 1) AS weight,
    t.my_comment
FROM max_occurrences t
CROSS JOIN (
    SELECT LEVEL AS lvl 
    FROM dual 
    CONNECT BY LEVEL <= (SELECT MAX(max_cnt) FROM max_occurrences)
) levels
WHERE lvl <= t.max_cnt
AND (
    REGEXP_SUBSTR(t.my_comment, 'TID\s*(\d+)', 1, lvl, 'i', 1) IS NOT NULL 
    OR REGEXP_SUBSTR(t.my_comment, '(\d+\.?\d*)\s*k\.?g', 1, lvl, 'i', 1) IS NOT NULL
)
ORDER BY t.r_id, lvl;

逻辑说明 #

1. 正则规则适配：
   • TID匹配正则TID\s*(\d+)：忽略大小写，支持TID和编号之间带任意空格，捕获组直接拿到编号值
   • 重量匹配正则(\d+\.?\d*)\s*k\.?g：忽略大小写，支持整数/小数重量、数值和单位之间带任意空格、单位为kg/k.g两种写法，捕获组直接拿到重量数值
2. 多行拆分实现：
   • 先统计每条评论中TID、重量的出现次数，取最大值作为该条评论需要拆分的行数
   • 用CONNECT BY LEVEL生成连续序号序列，通过笛卡尔积把单条评论拆分为对应数量的行
   • 按序号提取第N个TID和第N个重量，过滤掉两者都为空的无效行后排序即可

内容的提问来源于stack exchange，提问作者Zoey Malkov