안녕하세요. 처음 질문을 남겨봅니다. 고수님들 잘 부탁드립니다.
의료용어를 저장하는 두개의 테이블이 있는데 각 테이블에 '영문명' 컬럼이 있습니다.
각 테이블의 '영문명' 컬럼의 데이터가 같은경우로 조인을 걸려고 하는데,
단순히 string 비교만으로 같은경우가 아니고,
어순과 관계없이 같은 단어들로 이루어진 행을 찾고자 합니다.
말로 풀어쓰려니 어려워서 예시를 들자면
[Table 1]
CODE_A, ENG
=================
A0001 a b c d
A0002 x y z
A0003 1a b c
A0004 Other specified diabetes mellitus with coma with lactic acidosis
[Table 2]
CODE_B, ENG
=================
B0001 a b c d
B0002 b c d a
B0003 b a c d
B0004 z y x
B0005 x y z x
B0006 y z x y
B0007 1a b c
B0008 c 1a b
B0009 Other specified diabetes mellitus with lactic acidosis with coma
여기서 원하는 결과물은
[Table 1] + [Table 2]
CODE_A, ENG_1, CODE_B, ENG_2
=================
A0001 a b c d B0002 b c d a
A0001 a b c d B0003 b a c d
A0002 x y z B0004 z y x
A0003 1a b c B0008 c 1a b
A0004 Other specified diabetes mellitus with coma with lactic acidosis B0009 Other specified diabetes mellitus with lactic acidosis with coma
입니다. ENG_1 과 ENG_2가 완전히 매치되는경우는 제외되어야하구요. (반드시는 아닙니다)
필수적으로 ENG_1과 ENG_2의 어순이 다르지만 속하는 단어들과 분절된 단어들의 수가 같아야 합니다.
* 분절: 공백(스페이스)을 구분자로 하여 용어를 split 함
단순하면 그냥 노가다로 할수 있겠는데..
분절후에 단어가 최대 50개까지 있고 데이터 양이 Table1은 약 30,000 rows, Table2는 약 4,000,000 rows 정도 됩니다.. ㅠ
현재는 무식하게 컬럼을 50개만든 테이블 2개를 만들어서 각각 테이블의 분절된 영문 단어들을 각 컬럼에 넣어서 각 컬럼별로 비교하는 쿼리를 짜보았는데... 한계에 부딪혀서 도움을 요청드립니다.
감사합니다!
하기 쿼리 참고하세요..
;with Table1 (CODE_A,ENG) As
(
Select 'A0001',Rtrim('a b c d ') Union All
Select 'A0002',Rtrim('x y z ') Union All
Select 'A0003',Rtrim('1a b c ') Union All
Select 'A0004',Rtrim('Other specified diabetes mellitus with coma with lactic acidosis')
)
,Table2 (CODE_B,ENG) As
(
Select 'B0001',Rtrim('a b c d ') Union All
Select 'B0002',Rtrim('b c d a ') Union All
Select 'B0003',Rtrim('b a c d ') Union All
Select 'B0004',Rtrim('z y x ') Union All
Select 'B0005',Rtrim('x y z x ') Union All
Select 'B0006',Rtrim('y z x y ') Union All
Select 'B0007',Rtrim('1a b c ') Union All
Select 'B0008',Rtrim('c 1a b ') Union All
Select 'B0009',Rtrim('Other specified diabetes mellitus with lactic acidosis with coma')
)
,Table1_1 (CODE_A,ENG,l_eng,cnt) As
(
Select a.CODE_A
,SubString(a.ENG,1,CharIndex(' ',a.ENG))
,SubString(a.ENG,CharIndex(' ',a.ENG) + 1, Len(a.ENG))
,1
From Table1 a
Union All
Select a.CODE_A
,SubString(a.l_eng,1,CharIndex(' ',a.l_eng))
,SubString(a.l_eng,CharIndex(' ',a.l_eng) + 1, Len(a.l_eng))
,a.cnt + 1
From Table1_1 a
Where CharIndex(' ',a.l_eng) <> 0
Union All
Select a.CODE_A
,a.l_eng
,''
,a.cnt + 1
From Table1_1 a
Where CharIndex(' ',a.l_eng) = 0
And a.l_eng <> a.ENG
And a.l_eng <> ''
)
,Table2_1 (CODE_B,ENG,l_eng,cnt) As
(
Select a.CODE_B
,SubString(a.ENG,1,CharIndex(' ',a.ENG))
,SubString(a.ENG,CharIndex(' ',a.ENG) + 1, Len(a.ENG))
,1
From Table2 a
Union All
Select a.CODE_B
,SubString(a.l_eng,1,CharIndex(' ',a.l_eng))
,SubString(a.l_eng,CharIndex(' ',a.l_eng) + 1, Len(a.l_eng))
,a.cnt + 1
From Table2_1 a
Where CharIndex(' ',a.l_eng) <> 0
Union All
Select a.CODE_B
,a.l_eng
,''
,a.cnt + 1
From Table2_1 a
Where CharIndex(' ',a.l_eng) = 0
And a.l_eng <> a.ENG
And a.l_eng <> ''
)
Select a.CODE_A
,a.ENG
,b.CODE_B
,b.ENG
From Table1 a
Inner Join
(
Select a.CODE_A
,Max(a.cnt) As a_cnt
,b.CODE_B
,Max(b.cnt) As b_cnt
From Table1_1 a
Inner Join
Table2_1 b
On a.ENG = b.ENG
And Len(a.ENG) = Len(b.ENG)
Group By a.CODE_A
,b.CODE_B
Having Max(a.cnt) = Max(b.cnt)
)
c
On a.CODE_A = c.CODE_A
Inner Join
Table2 b
On b.CODE_B = c.CODE_B
Where Len(a.ENG) = Len(b.ENG)
And a.ENG <> b.ENG