240708

SELF JOIN

https://school.programmers.co.kr/learn/courses/30/lessons/299305#qna

위 사진과 같은 테이블 ECOLI_DATA은 대장균 개체에 관련된 데이터이다.

• 문제:
  대장균 개체의 ID(ID)와 자식의 수(CHILD_COUNT)를 출력하는 SQL 문을 작성해주세요. 자식이 없다면 자식의 수는 0으로 출력해주세요. 이때 결과는 개체의 ID 에 대해 오름차순 정렬해주세요.

이 때 ID와 PARENT_ID가 동일한 경우의 수를 구해야 한다.
SELF JOIN을 통해 풀어볼 수 있다.

1. FROM ECOLI_DATA A LEFT JOIN ECOLI_DATA B
   ON A.ID = B.PARENT_ID

   ID와 PARENT_ID가 일치하는 경우를 JOIN하는 FROM절을 작성한다.
   이 때 ID를 기준으로 GROUP BY를 진행해야 하므로 LEFT JOIN을 수행한다.

2. SELECT A.ID ID,
          COUNT(B.ID) CHILD_COUNT
   FROM ECOLI_DATA A LEFT JOIN ECOLI_DATA B
   ON A.ID = B.PARENT_ID
   GROUP BY 1
   ORDER BY 1

   ID에 해당되는 그룹의 수를 구해야 하기 때문에 COUNT(B.ID)를 적어준다.

이처럼 하나의 테이블에서 각 칼럼이 일치하는 경우를 반환하는 쿼리를 수행해야 하는 경우 SELF JOIN을 사용하면 쉽다.

CASE WHEN 부등호의 효율적 사용

• 나의 코드

SELECT ID,
       CASE WHEN SIZE_OF_COLONY <= 100 THEN 'LOW'
            WHEN SIZE_OF_COLONY > 100 AND SIZE_OF_COLONY <=1000 THEN 'MEDIUM'
            WHEN SIZE_OF_COLONY > 1000 THEN 'HIGH'
            END SIZE 
FROM ECOLI_DATA
ORDER BY 1

• 더 간단한 쿼리

SELECT ID,
       CASE 
           WHEN SIZE_OF_COLONY <= 100 THEN 'LOW'
           WHEN SIZE_OF_COLONY <= 1000 THEN 'MEDIUM'
           ELSE 'HIGH'
       END AS SIZE 
FROM ECOLI_DATA
ORDER BY ID;

"더 간단한 쿼리"에서는 어차피 순차적으로 진행되는 case when 절의 속성을 사용하여 "<=" 조건만 사용하였다.

QUARTER 함수

https://school.programmers.co.kr/learn/courses/30/lessons/299308#qna

SELECT QUARTER("2017-06-15");
QUARTER 함수는 해당 날짜의 분기(1~4 사이의 숫자)를 반환합니다.

위 테이블에서 분기별 대장균 개체의 총 수를 구하기 위해서는 다음과 같은 쿼리를 작성할 수 있습니다.

SELECT CASE WHEN QUARTER(DIFFERENTIATION_DATE) = 1 THEN '1Q'
            WHEN QUARTER(DIFFERENTIATION_DATE) = 2 THEN '2Q'
            WHEN QUARTER(DIFFERENTIATION_DATE) = 3 THEN '3Q'
            WHEN QUARTER(DIFFERENTIATION_DATE) = 4 THEN '4Q'
            END QUARTER,
       COUNT(*) ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY 1
ORDER BY 1

그리고 위 쿼리를 CONCAT함수를 통해 더 효율적으로 만들 수 있다.

SELECT CONCAT(QUARTER(DIFFERENTIATION_DATE), 'Q') AS QUARTER,
       COUNT(*) AS ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY QUARTER
ORDER BY QUARTER;

• 쿼리 결과