Practical (실용) SQL�14장: 의미 있는 데이터를 찾기 위한 텍스트 마이닝
대전대학교 • 데이터베이스보안 • 15주차
Aaron Snowberger
pp. 283-312
의미 있는 데이터를
찾기 위한
텍스트 마이닝
14
Mining Text to Find Meaningful Data
pp. 283-312
14-1 문자열 함수를 사용하여 텍스트 서식 지정하기
다음으로 SQL을 사용하여 텍스트를 변환하고 검색하며 분석하는 방법을 배웁니다. 고급 분석을 배우기 전에 문자열 포맷과 패턴 일치를 활용한 간단한 텍스트 조작으로 시작합니다. 이 장에서는 두가지 데이터셋을 사용합니다.
구조화되지 않은 데이터(연설이나 보고서, 보도 자료 등의 텍스트)를 테이블의 행과 열에 있는 구조화된 데이터로 변환하면 의미를 추출할 수 있습니다.
14-1-1 대소문자 형식
PostgreSQL에서는 대문자 사용, 문자열 결합, 원하지 않는 공백 제거와 같은 일상적이지만 필요한 작업을 처리하는 50개 이상의 문자열 함수가 기본 제공되고 있습니다.
upper() 및 lower() 함수는 표준 ANSI SQL 명령이지만 initcap()은 PostgreSQL 전용입니다.
14-1-2 문서 정보
여러 함수는 문자열을 변환하는 대신 문자열에 대한 데이터를 반환합니다. 이러한 기능은 그 자체로 유용하거나 다른 기능과 결합됩니다. char_length(string) 함수는 공백을 포함하여문자열의 문자 수를 반환합니다.
표준 ANSI SQL이 아닌 length(string) 함수를 사용하여 문자열을 계산할 수도 었습니다. 여기에는 2진수 문자열의 길이를 계산할 수 있는 변형이 있습니다.
position(substring in string) 함수는 문자열의 하위 문자열 문자의 위치를 반환합니다.
char_length() 및 position() 함수 모두 표준 ANSI SQL입니다.
length() 함수는 중국어, 일본어, 또는 한국어를 포함하는 문자 집합과 같은 멀티 바이트 인코딩과 함께 사용할 때 char_length() 함수와는 다른 값을 반환할 수 있습니다.
14-1-3 문자 삭제하기
trim(characters from string) 함수는 문자열에서 원하지 않는 문자를 제거합니다. 문자열 맨 앞 문자를 제거하는 leading, 맨 뒤 문자를 제거하는 trailing, 둘 다 제거하는 both 옵션은 이 함수를 매우 유연하게 만듭니다.
14-1-4 문자 추출하고 대체하기
표준 ANSI SQL인 left(string, number) 및 right(string, number) 함수는 문자열에서 선택한 수만큼 문자를 추출하고 반환합니다.
예를 들어, 전화번호 703-555-1212 에서 지역 코드 703 만 얻으려면
문자열의 문자를 대체하려면 replace(string , from, to) 함수를 사용하세요. 예를 들어 bat을 cat으로 변경하려면 replace('bat', 'b', 'c')를 사용하여 bat 의 b를 c로 대체하도록 지정합니다.
14-2 정규식을 사용하여 텍스트 패턴 매칭하기
정 규식 regular expressions : regex은 텍스트 패턴을 설명하는 표기 언어 유형입니다. 가령 네 자리 숫자와 하이픈, 두 자리 숫자가 이어진 문자열처럼 패턴이 눈에 띄는 문자열이 있는 경우에 이러한 패턴을 설명하는 정규식을 작성할 수 있습니다.
정규식은 초보 프로그래머에게 이해하기 어려운 것처럼 보일 수 있습니다.
직관적이지 않은 단일 문자 기호를 사용하기 때문에 익숙해지기 위해 연습을 해야 합니다. 패턴과 일치하는 표현식을 얻는 과정에서 시행 착오가 있을 수 있으며 프로그래밍 언어마다 정규식을 처리하는 방식에 미묘한 차이가 있습니다.
그래도 정규식을 배우면 많은 프로그래밍 언어, 텍스트 편집기 및 기타 응용 프로그램을 사용하여 텍스트를 검색하는 매우 강력한 능력을 얻게 되므로 시간을 들여 학습하는 것이 좋습니다.
14-2-1 정규식 표기법
문자와 숫자, 그리고 득정 기호는 동일한 문자를 나타내는 리터럴이기 때문에 정규식 표기법을 사용하여 일치시키는 것은 간단합니다.
14-2-1 정규식 표기법
이러한 기본 정규식을 사용하여 다양한 종류의 문자를 일치시킬 수 있으며 일치하는 횟수와 위치를 표시할 수도 있습니다. 예를 들어, 대괄호 안에 문자를 넣으면 단일 문자 또는 범위와 일치시킬 수 있습니다. ([A-Za-z0-9])
백슬래시(\)는 텍스트 파일의 줄 끝 문자인 탭 (\t), 숫자(\d) 또는 개행 문자(\n)와 같은 특수 문자의 지정자앞에 옵니다.
문자와 일치하는 횟수를 표시하는 방법에는 여러 가지가 있습니다. 중괄호 안에 숫자를 넣으면 그만큼 여러 번 일치시키려는 것입니다. (\d{4}, \d{1,4})
?, *, + 문자는 일치 수에 대한 유용한 축약 표기법을 제공합니다. 예를 들어, 문자 뒤의 + 기호는 한 번 이상 일치함을 나타냅니다.
14-2-1 정규식 표기법
추가로, 괄호는 캡처 그룹capture group을 나타내며, 쿼리 결과에 표시할 일치된 텍스트의 일부만 지정하는 데 사용할 수 있습니다. 이는 일치하는 표현식의 일부만 보고하는 데 유용합니다. 예를 들어 텍스트에서 HH:MM:SS 형식을 찾고 있고 시간만 보고하려는 경우 (\d{2}):\d{2}:\d{2}와 같은 표현식을 사용할 수 있습니다.
14-2-1 정규식 표기법
이 결과는 문자열에서 관심 있는 부분만 선택하는 정규식의 유용성을 보여 줍니다.
예를 들어 시간을 찾으려는 경우라면,
14-2-1 정규식 표기법
pgAdmin에서는 일치하는 텍스트를 반환하기 위해 substring(string from pattern) 함수 안에 텍스트와 정규식을 배치하는 식으로 정규식을 사용할 수 었습니다.
이 쿼리는 2024를 반환해야 합니다. 왜냐하면 패턴으로 네 자리 숫자를 찾도록 지정했고, 이 문자열에서 이러한 기준과 일치하는 유일한 숫자는 2024뿐이기 때문입니다.
14-2-2 WHERE와 함께 정규식 사용하기
WHERE 절에서 LIKE 및 ILIKE를 시용하여 쿼리를 필터링했습니다. 이번에는 더욱 더 복잡한 필터링을 수행할 수 있도록 WHERE 절에서 정규식을 사용하는 방법을 배웁니다.
정규식에서 물결표(~)를 사용하여 대소문자를- 구분하고 물결표-별표(~＊)를 사용하여 대소문자를 구분하지 않는 필터링을 수행합니다. 앞에 느낌표(!)를 추가하여 두 식을 모두 부정할 수 었습니다.
14-2-3 텍스트를 바꾸거나 분할하는 정규식 함수
텍스트 작업 시 유용할 수 있는 세 가지 정규식 함수를 더 살펴보겠습니다.
결과 주변의 중괄호와 함께 pgAdmin의 열 헤더에 있는 text[ ] 표기법은 이것이 실제로 다른 분
석 수단을 제공하는 배열 유형임을 확인합니다.
14-2-3 텍스트를 바꾸거나 분할하는 정규식 함수
regexp_split_to_array( )는 1차원 배열을 생성하며, 결과로 하나의 이름 목록이 반환됩니다. 배열에는 추가 차원을 가질 수 있는데, 가령 2차원 배열은 행과 열이 있는 행렬을 나타낼 수 있습니다.
따라서 array_length( )에 두 번째 인수 로 1을 입력하여 배열의 첫 번째(그리고 유일한) 차원의 길이를 원한다고 알립니다. 배열에 요소가 4 개 있으므로 쿼리는 4를 반환합니다.
텍스트에서 패턴을 식별할 수 있다면 정규 표현식 기호를 조합해 검색할 수도 있습니다. 실제 예제를 통해 정규 표현식 함수를 사용하는 방법을 연습해 보겠습니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
워싱턴 D.C. 근처의 한 보안관 부서는 부서가 조사한 사건의 날짜, 시간, 위치, 사건 경위를 자세히 설명하는 일일 보고서를 발행합니다. 이러한 보고서는 데이터베이스로 가져오기 좋은 파일 형식 대신 Microsoft 워드로 작성해 PDF 형식으로 저장한 파일로 게시된다는 점을 제외하면 분석하기에 좋습니다.
PDF에서 사건을 복사하여 텍스트 편집기에 붙여 넣으면 결과는 코드 14-4와 같은 텍스트 블록이 됩니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
각 텍스트 블록에는
약간의 불일치가 보이는군요. 예를 들어, 첫 번째 블록에는 4/16/17-4/17/17 이라는 두 개의 날짜와 2100-0900 hrs 라는 두 개의 시간이 있습니다. 이는 사건의 정확한 시간은 알려지지 않았으며 그 시간 내에 발생했을 가능성이 높다는 것을 의미합니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
범죄 보고서를 위한 테이블 생성하기
5건의 범죄 사건을 crime_reports.csv라는 파일로 수집했습니다.
pgAdmin의 결과 화면에서 셀을 더블 클릭하면 그림과 같이 해당 행의 모든 텍스트를 표시합니다.
나머지 열은 재우기 전까지 NULL 상태입니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
범죄 보고서 날짜 패턴 매칭하기
보고서 original_text 에서 추출하려는 첫 번째 데이터는 날짜 또는 범죄가 발생한 날짜입니다. 대부분의 보고서에는 하나의 날짜가 있지만 어떤 것에는 두 개가 있습니다. 또한 보고서에는 관련된 시간이 있으며 추출된 날짜와 시간을 타임스탬프로 결합합니다.
date_1은 각 보고서의 첫 번째 또는 유일한 날짜와 시간으로 채웁니다.
두 번째 날짜 또는 두 번째 시간이 있는 경우 타임스탬프를 만들어 date_2 에 추가합니다.
시작하려면 regexp_match()를 사용하여 crime_reports 의 다섯 사건 각각에서 날짜를 찾습니다. 일치시킬 일반적인 패턴은 MM/DD/YY 이지만 월과 일에 대해 한 자리 또는 두 자리 숫자가 있을 수 있습니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
범죄 보고서 날짜 패턴 매칭하기
슬래시(/）를 찾고 싶지만 슬래시는 정규식에서 특별한 의미를 가질 수있기 때문에 다음과 같이 백슬래시(\）를 앞에 배치하여 해당 문지를- 이스케이프해야 합니다. 이 컨텍스트에서 문지를- 이스케이프한다는 것은 단순히 문자가 특별한 의미를 갖도록 하는 것이 아니라 문자 그대로 취급하기를 원한다는 것을 뜻합니다. 따라서 백슬래시와 슬래시의 조합(\／）은 슬래시가 필요함을 나타냅니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
두 번째 날짜가 존재하는 경우 두 번째 날짜 매칭하기
텍스트의 모든 날짜를 찾아 표시하려면 관련 regexp_matches() 함수를 사용하고 코드 14-7처럼 플래그 g 형태로 옵션을 전달해야 합니다.
regexp_matches() 함수는 g 플래그 O가 제공될 때 표현식이 찾은 각 일치 항목을 결과에서 행으로 반환한다는 점에서 regexp_match() 함수와 다릅니다. regexp_match() 함수는 첫 번째 일치 항목만 반환했습니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
두 번째 날짜가 존재하는 경우 두 번째 날짜 매칭하기
범죄 보고서에 두 번째 날짜가 있을 때마다 해당 보고서와 관련 시간을 date_2 열에 로드하려고 합니다. 보고서에서 두 번째 날짜만 추출하기 위해 두 날짜가 있을 때 항상 표시되는 패턴을 사용할 수 있습니다.
결과로 하이픈과 함께 표시됩니다. 다행히도 다음과 같이 캡처 그룹을 생성하기 위해 괄호로 묶어 여러분이 변환하고자 하는 정규식의 정확
한 부분을 지정할 수 있습니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
주가 범죄 신고 요소 매칭하기
여러분이 방금 완료한 프로세스는 일반적입니다. 분석할 텍스트로 시작한 다음, 원하는 데이터를 찾을 때까지 정규식을 작성하고 구체화합니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
주가 범죄 신고 요소 매칭하기
여러분이 방금 완료한 프로세스는 일반적입니다. 분석할 텍스트로 시작한 다음, 원하는 데이터를 찾을 때까지 정규식을 작성하고 구체화합니다.
시간이 항상 새 줄에서 시작하기 때문에 개행 문자인 \n으로 줄 바꿈을 나타내고 \d{4}는 네 자리 시간 2100을 나타냅니다. 우리는 그 네 자리 시간만 반환하고 싶기 때문에 \d{4}를 괄호 안에 넣어 캡처 그룹으로 만듭니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
주가 범죄 신고 요소 매칭하기
여러분이 방금 완료한 프로세스는 일반적입니다. 분석할 텍스트로 시작한 다음, 원하는 데이터를 찾을 때까지 정규식을 작성하고 구체화합니다.
두 번째 시간이 존재할 경우 두 번째 시간은 하이픈 뒤에 옵니다. 그러니 첫 번째 시간을 찾기 위해 만든 표현식에 하이폰과 다른 \d{4}를 추가합니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
주가 범죄 신고 요소 매칭하기
여러분이 방금 완료한 프로세스는 일반적입니다. 분석할 텍스트로 시작한 다음, 원하는 데이터를 찾을 때까지 정규식을 작성하고 구체화합니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
주가 범죄 신고 요소 매칭하기
여러분이 방금 완료한 프로세스는 일반적입니다. 분석할 텍스트로 시작한 다음, 원하는 데이터를 찾을 때까지 정규식을 작성하고 구체화합니다.
도시는 항상 거리 접미사에 뒤따르기 때문에 방금 거리에 대해 만든 대체 기호로 구분된 용어를 재사용합니다. 개행 문지를- 입력한 다음 캡처 그룹 (\w+ \w+I\w+)를 사용하여 최종 개행 이전에 두 단어 또는 한 단어를 찾습니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
주가 범죄 신고 요소 매칭하기
여러분이 방금 완료한 프로세스는 일반적입니다. 분석할 텍스트로 시작한 다음, 원하는 데이터를 찾을 때까지 정규식을 작성하고 구체화합니다.
각 보고서에서 콜론은 범죄 유형을 서술할 때만
사용되었는데, 그러다 보니 콜론 앞에는 항상
범죄 유형이 나옵니다.
또 개행 문자를 추가하고 (.*):을 사용하여 콜론
앞에 0번 또는 그 이상 나타나는 문자를 찾습니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
주가 범죄 신고 요소 매칭하기
여러분이 방금 완료한 프로세스는 일반적입니다. 분석할 텍스트로 시작한 다음, 원하는 데이터를 찾을 때까지 정규식을 작성하고 구체화합니다.
범죄 설명은 항상 범죄 유형 뒤의 콜론과 사건 번호 사이에 있습니다. 따라서 표현식은 콜론, 공백 문자(\s) 로 시작한 다음 캡처 그룹에서 .+ 표기법을 사용하여 한 번 이상 나타나는 문자를 찾습니다.
비보고 캡처 그룹 (?:C0|SO) 은 프로그램에게 각 케이스 번호를 시작하는 두 문자 쌍인 C0 (숫자 0) 또는 SO (대문자 O)를 만나면 검색을 중지하라고 지시합니다. 설병 중에 하나 이상의 줄 바꿈이 있을 수 있기 때문예 이를 수행해야 합니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
주가 범죄 신고 요소 매칭하기
여러분이 방금 완료한 프로세스는 일반적입니다. 분석할 텍스트로 시작한 다음, 원하는 데이터를 찾을 때까지 정규식을 작성하고 구체화합니다.
사건 번호는 C0 또는 SO로 시작하고 그 뒤에 숫자들이 옵니다. 이 패턴을 일치시키기 위해 표현식은 [0-9] 범위 표기법을 사용하여 0에서 9까지의 숫자가 한 개 이상 뒤따르는 비보고 캡처 그룹에서 C0 또는 SO를 찾습니 다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
주가 범죄 신고 요소 매칭하기
이제 이러한 정규식을 regexp_match( )에 전달하여 작동하는지 살펴보겠습니다.
SELECT
regexp_match(original_text, '(?:C0|SO)[0-9]+') AS case_number,
regexp_match(original_text, '\d{1,2}\/\d{1,2}\/\d{2}') AS date_1,
regexp_match(original_text, '\n(?:\w+ \w+|\w+)\n(.*):') AS crime_type,
regexp_match(original_text, '(?:Sq.|Plz.|Dr.|Ter.|Rd.)\n(\w+ \w+|\w+)\n')
AS city
FROM crime_reports
ORDER BY crime_id;
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
regexp_match( ) 결과에서 텍스트 추출하기
파성된 각 요소를 데이블의 열에 로드하기 위해 UPDATE 쿼리를 만듭니다. 그러나 텍스트를 열에 삽입하기 전에 regexp_match( )가 반환하는 배열에서 텍스트를 추출하는 방법을 배워야 합니다.
regexp_match( )가 텍스트 값을 포함하는 배열을 반환합니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
regexp_match( ) 결과에서 텍스트 추출하기
우리가 업데이트하려는 crime_reports 테이블의 열은 배열 타입이 아니므로 regexp_match()에서 반환된 배열 값을 전달하는 대신 먼저 배열에서 값을 추출해야 합니다.
먼저 regexp_match() 함수를 괄호로 묶습니다. 그 뒤에 배열의 첫 번째 요소를 나타내는 값 1을 대괄호로 감싸 제공합니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
주즐된 데이터로 crime_reports 테이블 업데이트하기
첫 번째 날짜와 시간을 추출해 단일 timestamp
값으로 결합해 date_1 열에 저장합니다.
PostgreSQL 이중 파이프 연결 연산자를 사용하여 추출된 날짜와 시간을 timestamp with time zone 입력에 허용되는 형식으로 결합합니다.
지역의 시간대를 포
함합니다. 이러한 요소를 연결하면 timestamp 입력으로 허용되는 MM/DD/YY HH:MM TIMEZONE 패턴의
문자열이 생성됩니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
주즐된 데이터로 crime_reports 테이블 업데이트하기
UPDATE를 실행하면 RETURNING 절은 다음과 같이 original_text 열의 일부와 함께 현재 채워진 date_1 열을 포함하여 업데이트된 행에서 지정한 열을 표시합니다.
여러분이 동부 표준 시간대에 있지 않으면 타임스탬프는 그 대신 pgAdmin 클라이언트의 표준 시간대를 반영한다는 점을 유의하세요.
또한 pgAdmin에서 전체 텍스트를 보려면 original_text 열에서 셀을 더블 클릭해야 합니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
주즐된 데이터로 crime_reports 테이블 업데이트하기
나머지 각 데이터 요소에 대해 UPDATE 문을 작성할 수 있지만 이러한 구문을 하나로 결합하는 것이 더 효율적입니다.
date_2 열을 업데이트하려면 두 번째 날짜 및 시간의 일관성이 없는 부분도 고려해야 합니다. 제한된 데이터셋에는 세 가지 가능성이 있습니다.
date_2 열에 각 시나리오의 올바른 값을 삽입하기 위해 CASE 구문을 사용하여 각 가능성을 테스트합니다.
14-2-4 정규식 …
주즐된 데이터로 crime_reports 테이블 업데이트하기
나머지 각 데이터 요소에 대해 UPDATE 문을 작성할 수 있지만 이러한 구문을 하나로 결합하는 것이 더 효율적입니다.
UPDATE crime_reports
SET date_1 =
(
(regexp_match(original_text, '\d{1,2}\/\d{1,2}\/\d{2}'))[1]
|| ' ' ||
(regexp_match(original_text, '\/\d{2}\n(\d{4})'))[1]
||' US/Eastern'
)::timestamptz,
date_2 =
CASE
WHEN (SELECT regexp_match(original_text, '-(\d{1,2}\/\d{1,2}\/\d{2})') IS NULL)
AND (SELECT regexp_match(original_text, '\/\d{2}\n\d{4}-(\d{4})') IS NOT NULL)
THEN
((regexp_match(original_text, '\d{1,2}\/\d{1,2}\/\d{2}'))[1]
|| ' ' ||
(regexp_match(original_text, '\/\d{2}\n\d{4}-(\d{4})'))[1]
||' US/Eastern'
)::timestamptz
WHEN (SELECT regexp_match(original_text, '-(\d{1,2}\/\d{1,2}\/\d{2})') IS NOT NULL)
AND (SELECT regexp_match(original_text, '\/\d{2}\n\d{4}-(\d{4})') IS NOT NULL)
THEN
((regexp_match(original_text, '-(\d{1,2}\/\d{1,2}\/\d{1,2})'))[1]
|| ' ' ||
(regexp_match(original_text, '\/\d{2}\n\d{4}-(\d{4})'))[1]
||' US/Eastern'
)::timestamptz
END,
street = (regexp_match(original_text, 'hrs.\n(\d+ .+(?:Sq.|Plz.|Dr.|Ter.|Rd.))'))[1],
city = (regexp_match(original_text, '(?:Sq.|Plz.|Dr.|Ter.|Rd.)\n(\w+ \w+|\w+)\n'))[1],
crime_type = (regexp_match(original_text, '\n(?:\w+ \w+|\w+)\n(.*):'))[1],
description = (regexp_match(original_text, ':\s(.+)(?:C0|SO)'))[1],
case_number = (regexp_match(original_text, '(?:C0|SO)[0-9]+'))[1];
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
주즐된 데이터로 crime_reports 테이블 업데이트하기
코드 14-13 의 전체 쿼리를 실행하면 PostgreSQL이 UPDATE 5 라는 메시지를 반환해야 합니다. 성공적이군요! 모든 열을 적절한 데이터로 업데이트했으므로 이제 데이블의 모든 열을 검사하고 original_text 열에서 파성된 요소를 찾을 수 있습니다.
14-2-4 정규식 함수를 사용하여 텍스트를 데이터로 변환하기
프로세스의 가치
여러분은 원시 텍스트를 이 지역의 범죄 관련 질문에 답하고 요약할 수 있는 테이블로 성공적으로 변환했습니다.
정규식을 작성하고 쿼리를 코딩하여 테이블을 업데이트하는 데 시간이 걸릴 수 있지만 이러한 방식으로 데이터를 식별하고 수집하는 것은 가치 있는 일입니다. 실제로 다룰 수 있는 최고의 데이터셋 중 일부는 직접 구축한 데이터셋입니다.
다음 절에서는 추가 PostgreSQL 함수를 사용하는 정규식 탐색을 마저 살펴보겠습니다.
14-3 PostgreSQL에서 전체 텍스트 검색하기
PostgreSQL에는 대용량 텍스트 검색 기능을 더해 주는 강력한 전체 텍스트 검색 엔진이 함께 제공됩니다. 이 검색 엔진은 연구 데이터베이스에서 검색을 지원하는 온라인 서비스인 Factiva와 비슷합니다.
이 예에서는 2차 세계 대전 이후 복무한 전 미국 대통령들의 연설 79개를 모았습니다. 대부분 국정연설로 구성됩니다.
14-3-1 텍스트 검색 데이터 타입
PostgreSQL 의 텍스트 검색 구현에는 두 가지 데이터 타입이 포함됩니다.
14-3-1 텍스트 검색 데이터 타입
tsvector를 사용하며 텍스트를 어휘소로 저장하기
tsvector 데이터 타입은 텍스트를 언어의 최소 의미 단위인 어휘소 lexemes 의 정렬된 목록으로 줄입니다. 여기서 어휘소는 접미사로 만든 변형이 없는 단어라고 생각하면 도움이 될 것입니다.
예를 들어 tsvector 형식은 원본 텍스트에서 각 단어의 위치를 기록하면서 washes, washed, washing이라는 단어를 wash라는 어휘소로 저장합니다. 텍스트를 tsvector로 변환하면 the 또는 it과 같이 일반적으로 검색에서 역할을 하지 않는 작은 불용어 stop words 시 도 제거됩니다.
단어를 알파벳순으로 정렬하고, 각 콜론 다음의 숫자는 불용어를 포함한 원래 문자열에서의 위치를 나타냅니다.
14-3-1 텍스트 검색 데이터 타입
tsquery로 검색어 만들기
tsquery 데이터 타입은 다시 어휘소로 최적화된 전체 텍스트 검색 쿼리를 나타냅니다. 검색 제어를 위한 연산자도 제공합니다.
14-3-1 텍스트 검색 데이터 타입
검색에 @@ 일치 연산자 사용하기
텍스트 및 검색어를 전체 텍스트 검색 데이터 타입으로 변환하면 이중 앳 사인(@@) 일치 연산자 를 사용하여 쿼리가 텍스트와 일치하는지 확인할 수 있습니다.
TRUE
FALSE
14-3-2 전체 텍스트 검색을 위한 테이블 생성하기
코드 14-18은 president_speeches 테이블을 생성하고 채워서 원본 연설 텍스트에 대한 열과 tsvector 타입의 열을 포함합니다. CSV 파일을 설정하는 방법을 수용하기 위해 COPY의 WITH 절에는 일반적인 사용법과 다른 매개 변수 집합이 있습니다. 파이프(|)로 구분되며 인용에 @을 사용합니다.
여기서는 english를 사용하고 있지만 spanish, german, french 또는 여러분이 사용하려는 언어로 대체할 수 있습니다.(참고로 일부 언어는 추가 사전을 찾아 설치해야 합니다.)
14-3-2 전체 텍스트 검색을 위한 테이블 생성하기
마지막으로 검색 속도를 높이기 위해 search_speech_text 열을 인덱싱하려고 합니다.
전체 텍스트 검색의 경우 PostgreSQL 문서에서는 일반화된 간격 인덱스인 GIN generalized inverted index 사용을 권장합니다. 문서에 따르면 GIN 인덱스는 ‘각 단어(어휘소)에 대한 인덱스 항목과 일치하는 위치의 축약 목록’을 포함합니다.
14-3-3 연설 텍스트 검색하기
근 80년간의 대통령 연설은 역사를 탐구할 수 있는 비옥한 토대가 됩니다. 예를 들어 코드 쿼리는 대통령이 베트남을 언급한 연설을 나열합니다.
그러한 결과는 1961년 존 F. 케네디가 의회에 보낸 특별 메시지에서 처음으로 베트남을 언급했으며 미국의 베트남 전쟁 참여가 확대됨에 따라 1966년부터 반복해서 언급되는 주제가 되었음을 보여 줍니다.
14-3-3 연설 텍스트 검색하기
검색 결과 위치 표시하기
ts_headline() 함수를 사용해 텍스트에서 검색어가 나타나는 위치를 확인할 수 있습니다. 이 함수에는 디스플레이 형식을 지정하는 옵션과 검색 용어, 일치하는 검색어 주위에 표시할 단어 수, 각 텍스트 행에서 표시할 일치된 결과 수를 입력합니다.
14-3-3 연설 텍스트 검색하기
검색 결과 위치 표시하기
여기에서는
이러한 설정은 선택 사항이며 필요에 따라 조정할 수 있습니다.
14-3-3 연설 텍스트 검색하기
검색 결과 위치 표시하기
이제 검색한 용어의 컨텍스트를 빠르게 확인할 수 있습니다. 또한 비슷한 결과를 포함한 유연한 검색 결과를 제공할 수도 있습니다. 검색 엔진은 tax뿐 아니라 그것을 어근으로 삼는 tax, Tax, Taxes를 찾습니다.
14-3-3 연설 텍스트 검색하기
여러 검색어 사용하기
또 다른 예로, 대통령이 transportation이라는 단어를 언급했지만 roads에 대해서는 언급하지 않은 연설을 찾을 수 있습니다. 다시 말하지만 ts_headline() 을 사용하여 검색에서 찾은 용어를 강조 표시합니다.
14-3-3 연설 텍스트 검색하기
여러 검색어 사용하기
ts_headline 열에서 강조 표시된 단어에는 transportation과 transport가 포함됩니다.
그 이유는 to_tsquery( ) 함수가 transportation을 검색어인 어휘소 transport로 변환했기 때문입니다. 이 데이터베이스 동작은 관련 단어를 찾는 데 매우 유용합니다.
14-3-3 연설 텍스트 검색하기
인접 단어 검색하기
마지막으로, 죽음과 큼을 나타내는 기호 사이에 하이픈으로 구성된 거리 연산자(<->)를 사용하여 인접한 단어를 찾습니다. 또는 기호 사이에 숫지를· 넣어 여러 단어로 구분되는 용어를 찾을 수 있습니다. 예를 들어 , 코드 14-24는 military라는 단어 뒤에 defense라는 단어가 잇따르는 형 식 이 포함된 연설을 검색합니다.
검색어를 military <2> defense로 변경하면 용어가 정확히 두 단어로 떨어진 일치 항목을 반환합니다.
14-3-4 관련성에 따라 쿼리 매치 순위 매기기
PostgreSQL 의 두 가지 전체 텍스트 검색 기능을 사용하여 관련성에 따라 검색 결과의 순위를 지정할 수도 있습니다.
두 함수 모두 문서 길이 및 기타 요소를 고려하기 위해 선택적 인수를 사용할 수 있습니다. 생성되는 순위 값은 정렬에 유용하지만 고유한 의미가 없는 임의의 소수입니다.
14-3-4 관련성에 따라 쿼리 매치 순위 매기기
한 예로, 코드 14-25는 ts_rank()를 사용하여 war, security, threat, enemy라는 모든 단어가 포함된 연설의 순위를 매깁니다.
Longest
Iraq War
14-3-4 관련성에 따라 쿼리 매치 순위 매기기
더욱 정확한 순위를 얻기 위해 동일한 길이의 연설 간의 빈도를 비교하는 것이 이상적이지만 항상 가능한 것은 아닙니다. 그러나 ts_rank() 함수의 세 번째 매개 변수로 정규화 코드를 추가하여 각 연설의 길이를 고려할 수 있습니다.
옵션 코드 2를 추가하면 함수가 search_speech_text 열에 있는 데이터 길이로 score를 나누도록 지시합니다. 이 몫은 문서 길이에 의해 정규화된 점수를 나타내며, 연설 간의 합리적인 비교를 제공합니다.
과제1 연습 문제
국정연설 중 하나를 사용하여 5자 이상의 고유한 단어 수를 계산합니다.
(힌트: 서브쿼리에서 regexp_split_to_table()을 사용하여 계산할 단어 테이블을 만들 수 있습니다.)
보너스: 각 단어 끝에서 쉼표와 마침표를 제거합니다.
과제2 연습 문제
ts_rank() 대신 ts_rank_cd() 함수를 사용하여 코드 14-25의 쿼리를 다시 작성하세요. PostgreSQL문서에 따르면 ts_rank_cd()는 어휘소와 검색어가 서로 얼마나 가까운지를 고려하여 커버 밀도를 계산합니다. ts_rank_cd() 함수를 사용하면 결과가 크게 변경되나요?
Thanks!
Do you have any questions?�
Please keep this slide for attribution