대규모 텍스트에서 문자열 찾기 - 정확한 매칭: IN, NOT IN

정확한 매칭 : IN, NOT IN

Red, Orange, Yellow, Green, Blue, Purple, White

• UFO를 목격한 설명이 담긴 description 컬럼에서 각 문장의 첫 단어가 색상으로 시작하는 값들만을 조회한다.

SELECT *
FROM (
	SELECT
		SPLIT_PART(description, ' ', 1) AS first_word,
		description
	FROM ufo
) AS A
WHERE first_word = 'Red'
	OR first_word = 'Orange'
	OR first_word = 'Yellow'
	OR first_word = 'Green'
	OR first_word = 'Blue'
	OR first_word = 'Purple'
	OR first_word = 'White';

IN 사용

SELECT *
FROM (
	SELECT
		SPLIT_PART(description, ' ', 1) AS first_word,
		description
	FROM ufo
) AS A
WHERE first_word IN ('Red', 'Orange', 'Yellow', 'Green', 'Blue', 'Purple', 'White');

Color, Shape, Motion, Other

• 문장의 첫 글자가 어느 종류에 속하는지를 구별한 후 개수를 센다.

SELECT
	(CASE
	 	WHEN LOWER(first_word) IN ('red', 'orange', 'yellow', 'green', 'blue', 'purple', 'white') THEN 'Color'
	 	WHEN LOWER(first_word) IN ('round','circular','oval','cigar') THEN 'Shape'
	 	WHEN first_word ILIKE 'triang%' THEN 'Shape'
	 	WHEN first_word ILIKE 'flash%' THEN 'Motion'
	 	WHEN first_word ILIKE 'hover%' THEN 'Motion'
	 	WHEN first_word ILIKE 'pulsat%' THEN 'Motion'
	 	ELSE 'Other'
	END) AS first_word_type,
	COUNT(*)
FROM (
	SELECT
		SPLIT_PART(description, ' ', 1) AS first_word,
		description
	FROM ufo
) AS A
GROUP BY 1
ORDER BY 2 DESC;