『SQL로 시작하는 데이터 분석』 Chapter 5. 텍스트 분석

캐시 타니무라, 『SQL로 시작하는 데이터 분석』, 박상근, 한빛미디어-OREILLY(2022), p235-298.

 

텍스트 분석: 텍스트 데이터에서 의미와 인사이트를 도출하는 과정

• 정성적 분석: 원문 분석(textual analysis), 별도의 지식을 기반으로 텍스트에 내재된 의미를 탐색
• 정량적 분석: 분석 결과가 정량적이라는 차이가 있음
• 분석 방법:
  • 텍스트 추출: 여러 텍스트 사이에서 의미 있는 텍스트를 가져옴
  • 카테고리화: 텍스트 데이터에서 정보를 추출하고 파싱해 태그를 붙이거나 특정 카테고리를 지정
  • 감성 분석: 글쓴이의 의도와 분위기를 파악해 텍스트의 긍/부정을 수치화
• SQL이 적합하지 않은 경우
  • 사람이 직접 할 때가 나을 때 (e. g. 데이터 셋이 작은 경우)
  • 빠르게 특정 문자열을 검색하거나, 특정 문자열을 포함하는 레코드를 찾는 경우 (Elasticsearch, Splunk 사용 권장)
  • 자연어 처리와 관련된 분석 (머신러닝, 파이썬 권장)

 

initcap: 각 단어의 첫 글자만 대문자로 변환하고 나머지는 소문자로 변환 (몇몇 데이터베이스만 제공)

 

CAST AS 구문 = :: 

reported::timestamp

 

실행 순서: AND 연산자 >> OR 연산자 (괄호를 사용해서 연산 순서를 구분하는 게 더 좋을 듯)

 

정규표현식 (POSIX 연산자)

• ~ : 두 문자열을 비교해서 첫 번째 문자열이 두 번째 문자열을 포함하면 True (대소문자 구분)
• ~* : 두 문자열을 비교해서 첫 번째 문자열이 두 번째 문자열을 포함하면 True (대소문자 구분 X)
• !~ : 두 문자열을 비교해서 첫 번째 문자열이 두 번째 문자열을 포함하면 False (대소문자 구분)
• !~* : 두 문자열을 비교해서 첫 번째 문자열이 두 번째 문자열을 포함하면 False (대소문자 구분 X)
• []: 매칭하려는 문자의 집합을 표시
  • 대괄호 안에 여러 개 문자가 있다면, 그 중 매칭되는 문자 하나를 찾아냄
  • [0-9]: 모든 숫자 매칭
  • [a-z]: 모든 소문자 매칭
  • [A-Z]: 모든 대문자 매칭
  • [A-Za-z0-9]: 모든 대소문자, 숫자 매칭
  • [A-z]: 모든 아스키 문자 매칭 (특수 문자를 포함한 모든 문자를 매칭)
• +: 1회 이상 반복하는 패턴 매칭
• *: 0회 이상 반복하는 패턴 매칭
• ?: 0회 또는 1회만 발생하는 패턴 매칭
• {}: 중괄호 안에 표기된 숫자만큼 반복되는 패턴 매칭
• {,}: 중괄호 안에 표기된 범위만큼 반복되는 패턴 매칭 (e. g. {3,5} - 3~5회 매칭)
• ^: 매칭되지 않는 패턴 매칭
• \: 특수 문자를 포함하는 패턴을 찾고 싶을 때, '패턴 매칭 기호'로 오해하지 않기 위한 별도 처리 (e. g. \?)
  • \t: 탭
  • \r: 캐리지 리턴 (개행 문자)
  • \n: 라인 피드
  • \s: 어떤 개행 문자든 매칭 (공백 문자까지 포함)
  • \y or \b: 패턴이 단어 시작 지점부터 매칭되는지 or 끝 지점에서 매칭되는지 지정
  • \A: 문자열에서 맨 앞에 나타나는 단어를 매칭
  • \Z: 문자열에서 맨 뒤에 나타나는 단어를 매칭
• 다른 데이터 베이스
  • rlike or regexp_like 사용
  • regexp_like(string, pattern, optional_parameters)
    • optional_parameters: 대소문자 구분 등 매칭을 위한 상세 내용 설정
  • regexp_replace(field or string, patter, replacement value)

SELECT duration
,(regexp_matches(duration,'\m[Hh][Oo][Uu][Rr][A-Za-z]*\y'))[1] as matched_hour
,(regexp_matches(duration,'\m[Mm][Ii][Nn][A-Za-z]*\y'))[1] as matched_minutes
,regexp_replace(regexp_replace(duration,'\m[Mm][Ii][Nn][A-Za-z]*\y','min'),'\m[Hh][Oo][Uu][Rr][A-Za-z]*\y','hr') as replaced_text
FROM
(
    SELECT split_part(sighting_report,'Duration:',2) as duration
    ,count(*) as reports
    FROM ufo
    GROUP BY 1
) a
;

 

 

문자열 연결

• concat: 문자열 연결
• concat_ws: 첫 번째 인자를 구분자로 사용하여 연결
  • concat_ws(separator, value1, value2)
• || or + 사용 (e. g. value1 || value2)
• string_agg: 문자열 그룹핑
  • group_concat(MySQL), listagg (snowflake)
• regexp_split_to_table: 하나의 행에 저장된 값을 여러 행으로 분할
  • split_to_table

SELECT location
,string_agg(shape,', ' order by shape asc) as shapes
FROM
(
    SELECT 
    case when split_part(split_part(sighting_report,'Duration',1),'Shape: ',2) = '' then 'Unknown'
         when split_part(split_part(sighting_report,'Duration',1),'Shape: ',2) = 'TRIANGULAR' then 'Triangle'
         else split_part(split_part(sighting_report,'Duration',1),'Shape: ',2)  
         end as shape
    ,split_part(split_part(sighting_report,'Shape',1),'Location: ',2) as location
    ,count(*) as reports
    FROM ufo
    GROUP BY 1,2
) a
GROUP BY 1
;

 

SELECT word, count(*) as frequency
FROM
(
    SELECT regexp_split_to_table(lower(description),'\s+') as word
    FROM ufo
) a
LEFT JOIN stop_words b on a.word = b.stop_word
WHERE b.stop_word is null
GROUP BY 1
ORDER BY 2 desc
;