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;