RegExp不要選擇多個組是否匹配（多個XOR）-有解無憂

資料保存在 Oracle 12c 資料庫中，每個 ICD-10-CM 代碼一行，帶有患者 ID（外鍵），如下所示（注意可能還有許多其他代碼，以下只是與此問題相關的代碼):

ID   ICD10CODE
 1   S72.91XB
 1   S72.92XB
 2   S72.211A
 3   S72.414A
 3   S72.415A
 4   S32.509A
 5   S32.301A
 5   S32.821A
 6   S32.421A
 6   S32.422A
 7   S32.421A
 8   S32.421A
 8   S32.509A

手頭的任務是選擇僅匹配以下點之一的不同患者（使用標準正則運算式語法）：

任意數量：S32\.1\w\w\w, S32\.2\w\w\w, S32\.3\w\w\w, S32\.5\w\w\w, S32\.6\w\w\w, S32\.7\w\w\w,S32\.8\w\w\w
任意數量：S32\.4\w1\w, S32\.4\w3\w, S32\.4\w4\w, S32\.4\w6\w, S32\.4\w7\w, S32\.4\w8\w,S32\.4\w9\w
任意數量：S32\.4\w2\w, S32\.4\w3\w, S32\.4\w5\w, S32\.4\w6\w, S32\.4\w7\w, S32\.4\w8\w,S32\.4\w9\w
任意數量：S72\.[0-8]\w1\w, S72\.[0-8]\w3\w, S72\.[0-8]\w4\w, S72\.[0-8]\w6\w, S72\.[0-8]\w7\w, S72\.[0-8]\w8\w,S72\.[0-8]\w9\w
任意數量：S72\.[0-8]\w2\w, S72\.[0-8]\w3\w, S72\.[0-8]\w5\w, S72\.[0-8]\w6\w, S72\.[0-8]\w7\w, S72\.[0-8]\w8\w,S72\.[0-8]\w9\w
任意數量：S72\.91\w\w, S72\.93\w\w, S72\.94\w\w, S72\.96\w\w, S72\.97\w\w, S72\.98\w\w,S72\.99\w\w
任意數量：S72\.92\w\w, S72\.93\w\w, S72\.95\w\w, S72\.96\w\w, S72\.97\w\w, S72\.98\w\w,S72\.99\w\w

專案符號中列出的代碼的任何排列或組合（包括重復）對于每個患者都是允許的，但是跨行的排列或組合應該相互排他地發生在患者身上。LISTAGG我的方法是申請GROUP BY ID：

ID  LISTAGG(ICD10CODE, ',')
 1  S72.91XB,S72.92XB
 2  S72.211A
 3  S72.414A,S72.415A
 4  S32.509A
 5  S32.301A,S32.821A
 6  S32.421A,S32.422A
 7  S32.421A
 8  S32.421A,S32.509A

然后使用這個正則運算式過濾(S32\.(([1-3]|[5-8])|(4\w((1|4)|(2|5)|(3)|([5-9]))))\w )|(S72\.(([0-8]\w((1|4)|(2|5)|(3)|([5-9])))|(9((1|4)|(2|5)|(3)|([5-9]))))\w )，它幾乎是上面專案符號的文字表示。我的表達改編自這個答案中的想法，似乎((RB\s ) |(JJ\s ) )自動選擇一個"RB"或"JJ"，但不是兩者。

我無法讓它作業。答案應該只包含 ID 2、4、5 和 7。但是，我開發的運算式匹配所有 ID。

這個問題的解決方案是什么？

[編輯]更多資訊：

上述所有這些 S 代碼都與下肢骨骼的損傷有關：S32適用于骨盆骨折（髖骨），S72適用于股骨骨折（大腿骨）。請注意，我們有兩個股骨和兩個髖臼（股骨連接處的骨盆窩）。S32.4代碼表示髖臼（該系列的其余部分表示S32.[1235678]\w{3}骨盆的其他部分）。左右股骨和髖臼分別由第 6 個字符1|4或2|5在第 6 個字符中表示，除非代碼以S72.9這些數字出現在第 5 個字符中時開始。

納入研究人群的患者應該只有一根骨頭骨折。這意味著，兩個股骨之一，一個髖臼或骨盆，但不是它們的組合。單個骨頭的骨折組合無關緊要。例如，右單股骨可以在10個不同的地方和方式（膝蓋區域，中軸，頭部等，每個生成不同的S72.\w[1|4]\w{2}代碼），仍然應該選擇。

uj5u.com熱心網友回復：

選項1：

你可以用一個正則運算式來做到這一點：

SELECT t.id,
       t.icd10codes
FROM   ( SELECT id,
                LISTAGG(icd10code, ',') WITHIN GROUP (ORDER BY icd10code)
                  AS icd10codes
         FROM   table_name
         GROUP BY id
       ) t
WHERE  REGEXP_LIKE(
         t.icd10codes,
             '^(S32\.[1235678]\w\w\w(,|$)) $'
         || '|^(S32\.4\w[1346789]\w(,|$)) $'
         || '|^(S32\.4\w[2356789]\w(,|$)) $'
         || '|^(S72\.[0-8]\w[1346789]\w(,|$)) $'
         || '|^(S72\.[0-8]\w[2356789]\w(,|$)) $'
         || '|^(S72\.9[1346789]\w\w(,|$)) $'
         || '|^(S72\.9[2356789]\w\w(,|$)) $'
       )

其中，對于您的示例資料：

CREATE TABLE table_name (ID, ICD10CODE) AS
SELECT 1, 'S72.91XB' FROM DUAL UNION ALL
SELECT 1, 'S72.92XB' FROM DUAL UNION ALL
SELECT 2, 'S72.211A' FROM DUAL UNION ALL
SELECT 3, 'S72.414A' FROM DUAL UNION ALL
SELECT 3, 'S72.415A' FROM DUAL UNION ALL
SELECT 4, 'S32.509A' FROM DUAL UNION ALL
SELECT 5, 'S32.301A' FROM DUAL UNION ALL
SELECT 5, 'S32.821A' FROM DUAL UNION ALL
SELECT 6, 'S32.421A' FROM DUAL UNION ALL
SELECT 6, 'S32.422A' FROM DUAL UNION ALL
SELECT 7, 'S32.421A' FROM DUAL UNION ALL
SELECT 8, 'S32.421A' FROM DUAL UNION ALL
SELECT 8, 'S32.509A' FROM DUAL;

輸出：

ID ICD10代碼

2 S72.211A

4 S32.509A

5 S32.301A,S32.821A

7 S32.421A

ID	ICD10代碼
2	S72.211A
4	S32.509A
5	S32.301A,S32.821A
7	S32.421A

選項 2：

您可以將正則運算式放入表中：

CREATE TABLE matches (id, match) AS
SELECT 1, 'S32\.[1235678]\w\w\w'    FROM DUAL UNION ALL
SELECT 2, 'S32\.4\w[1346789]\w'     FROM DUAL UNION ALL
SELECT 3, 'S32\.4\w[2356789]\w'     FROM DUAL UNION ALL
SELECT 4, 'S72\.[0-8]\w[1346789]\w' FROM DUAL UNION ALL
SELECT 5, 'S72\.[0-8]\w[2356789]\w' FROM DUAL UNION ALL
SELECT 6, 'S72\.9[1346789]\w\w'     FROM DUAL UNION ALL
SELECT 7, 'S72\.9[2356789]\w\w'     FROM DUAL;

然后你可以使用查詢：

SELECT t.id,
       m.id AS match_id,
       LISTAGG(t.icd10code, ',') WITHIN GROUP (ORDER BY t.icd10code)
         AS icd10codes
FROM   table_name t
       LEFT OUTER JOIN matches m
       PARTITION BY (m.id)
       ON (REGEXP_LIKE(t.icd10code, '^' || m.match || '$'))
GROUP BY
       t.id,
       m.id
HAVING
       COUNT(m.match) = COUNT(t.id);

選項 3：

與第一個選項類似，但您可以將匹配項放入表中，然后確定使用了哪個匹配項：

SELECT t.id,
       m.id AS match_id,
       t.icd10codes
FROM   ( SELECT id,
                LISTAGG(icd10code, ',') WITHIN GROUP (ORDER BY icd10code)
                  AS icd10codes
         FROM   table_name
         GROUP BY id
       ) t
       INNER JOIN matches m
       ON (REGEXP_LIKE(t.icd10codes, '^(' || m.match || '(,|$)) $' ))

選項 2 和 3 都輸出：

ID MATCH_ID ICD10代碼

4 1 S32.509A

5 1 S32.301A,S32.821A

7 2 S32.421A

2 4 S72.211A

ID	MATCH_ID	ICD10代碼
4	1	S32.509A
5	1	S32.301A,S32.821A
7	2	S32.421A
2	4	S72.211A

選項 4：

您還可以擺脫（慢）正則運算式并LIKE在將匹配項存盤為時使用：

CREATE TABLE matches (id, match) AS
SELECT 1, 'S32.1___' FROM DUAL UNION ALL
SELECT 1, 'S32.2___' FROM DUAL UNION ALL
SELECT 1, 'S32.3___' FROM DUAL UNION ALL
SELECT 1, 'S32.5___' FROM DUAL UNION ALL
SELECT 1, 'S32.6___' FROM DUAL UNION ALL
SELECT 1, 'S32.7___' FROM DUAL UNION ALL
SELECT 1, 'S32.8___' FROM DUAL UNION ALL
SELECT 2, 'S32.4_1_' FROM DUAL UNION ALL
SELECT 2, 'S32.4_3_' FROM DUAL UNION ALL
SELECT 2, 'S32.4_4_' FROM DUAL UNION ALL
SELECT 2, 'S32.4_6_' FROM DUAL UNION ALL
SELECT 2, 'S32.4_7_' FROM DUAL UNION ALL
SELECT 2, 'S32.4_8_' FROM DUAL UNION ALL
SELECT 2, 'S32.4_9_' FROM DUAL UNION ALL
SELECT 3, 'S32.4_2_' FROM DUAL UNION ALL
SELECT 3, 'S32.4_3_' FROM DUAL UNION ALL
SELECT 3, 'S32.4_5_' FROM DUAL UNION ALL
SELECT 3, 'S32.4_6_' FROM DUAL UNION ALL
SELECT 3, 'S32.4_7_' FROM DUAL UNION ALL
SELECT 3, 'S32.4_8_' FROM DUAL UNION ALL
SELECT 3, 'S32.4_9_' FROM DUAL UNION ALL
SELECT 4, 'S72.' || (LEVEL - 1) || '_1_' FROM DUAL CONNECT BY LEVEL <= 9 UNION ALL
SELECT 4, 'S72.' || (LEVEL - 1) || '_3_' FROM DUAL CONNECT BY LEVEL <= 9 UNION ALL
SELECT 4, 'S72.' || (LEVEL - 1) || '_4_' FROM DUAL CONNECT BY LEVEL <= 9 UNION ALL
SELECT 4, 'S72.' || (LEVEL - 1) || '_6_' FROM DUAL CONNECT BY LEVEL <= 9 UNION ALL
SELECT 4, 'S72.' || (LEVEL - 1) || '_7_' FROM DUAL CONNECT BY LEVEL <= 9 UNION ALL
SELECT 4, 'S72.' || (LEVEL - 1) || '_8_' FROM DUAL CONNECT BY LEVEL <= 9 UNION ALL
SELECT 4, 'S72.' || (LEVEL - 1) || '_9_' FROM DUAL CONNECT BY LEVEL <= 9 UNION ALL
SELECT 5, 'S72.' || (LEVEL - 1) || '_2_' FROM DUAL CONNECT BY LEVEL <= 9 UNION ALL
SELECT 5, 'S72.' || (LEVEL - 1) || '_3_' FROM DUAL CONNECT BY LEVEL <= 9 UNION ALL
SELECT 5, 'S72.' || (LEVEL - 1) || '_5_' FROM DUAL CONNECT BY LEVEL <= 9 UNION ALL
SELECT 5, 'S72.' || (LEVEL - 1) || '_6_' FROM DUAL CONNECT BY LEVEL <= 9 UNION ALL
SELECT 5, 'S72.' || (LEVEL - 1) || '_7_' FROM DUAL CONNECT BY LEVEL <= 9 UNION ALL
SELECT 5, 'S72.' || (LEVEL - 1) || '_8_' FROM DUAL CONNECT BY LEVEL <= 9 UNION ALL
SELECT 5, 'S72.' || (LEVEL - 1) || '_9_' FROM DUAL CONNECT BY LEVEL <= 9 UNION ALL
SELECT 6, 'S72.91__' FROM DUAL UNION ALL
SELECT 6, 'S72.93__' FROM DUAL UNION ALL
SELECT 6, 'S72.94__' FROM DUAL UNION ALL
SELECT 6, 'S72.96__' FROM DUAL UNION ALL
SELECT 6, 'S72.97__' FROM DUAL UNION ALL
SELECT 6, 'S72.98__' FROM DUAL UNION ALL
SELECT 6, 'S72.99__' FROM DUAL UNION ALL
SELECT 7, 'S72.92__' FROM DUAL UNION ALL
SELECT 7, 'S72.93__' FROM DUAL UNION ALL
SELECT 7, 'S72.95__' FROM DUAL UNION ALL
SELECT 7, 'S72.96__' FROM DUAL UNION ALL
SELECT 7, 'S72.97__' FROM DUAL UNION ALL
SELECT 7, 'S72.98__' FROM DUAL UNION ALL
SELECT 7, 'S72.99__' FROM DUAL;

然后使用查詢：

SELECT t.id,
       m.id AS match_id,
       LISTAGG(t.icd10code, ',') WITHIN GROUP (ORDER BY t.icd10code)
         AS icd10codes
FROM   table_name t
       LEFT OUTER JOIN matches m
       PARTITION BY (m.id)
       ON (t.icd10code LIKE m.match)
GROUP BY
       t.id,
       m.id
HAVING
       COUNT(m.match) = COUNT(t.id);

db<>在這里擺弄

轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/432912.html

標籤：正则表达式 oracle12c 正则表达式组

上一篇：如何用其他字串的參考拆分字串串列

下一篇：如果規則，如何在gitlab-ci中使用正則運算式？