SQL_Programmers 코딩 테스트 1

학습사이트 > https://school.programmers.co.kr/

코딩테스트 연습을 할 수 있는 사이트로,
하루에 몇 문제 씩이라도 풀어보고 오답노트를 진행해보려고 한다.

개인 스터디로, 학습사이트에 테이블 로그 정의서 및 세부 기입된 설명 등은 기입하지 않는다.
→ 미래의 나야.. 필요하면 제목으로 입력하여 찾아 보자~

Lv 1부터 차근차근 나아가 보자.

---

특정 형질을 가지는 대장균 찾기 문제

SELECT COUNT(*) AS COUNT
FROM ECOLI_DATA
WHERE
(GENOTYPE & 2) = 0 AND (GENOTYPE & 5) > 0

해당 문제에서 포인트는 "&" 비트연산자 였다.

연산자	설명
&	AND 연산 (둘다 참일 경우 1로 표기)
l	OR 연산 (둘 중 하나만 만족해도 1로 표기)
^	XOR 연산 (둘 중 하나만 만족할 때 1로 표기)
~	NOT 연산 (1을 0으로, 0을 1로 바꿈)
<<	변수의 값을 왼쪽으로 지정된 비트 수 만큼 이동
>>	변수의 값을 오른쪽으로 지정된 비트 수 만큼 이동

<참고 : https://blowupmomo.tistory.com/328>

부모의 형질을 모두 가지는 대장균

SELECT 
    CHILD.ID, 
    CHILD.GENOTYPE, 
    PARENT.GENOTYPE AS PARENT_GENOTYPE
FROM 
    ECOLI_DATA AS CHILD		
LEFT JOIN 
    ECOLI_DATA AS PARENT 
ON 
    CHILD.PARENT_ID = PARENT.ID
WHERE 
    PARENT.GENOTYPE IS NOT NULL
    AND (CHILD.GENOTYPE & PARENT.GENOTYPE) = PARENT.GENOTYPE
ORDER BY CHILD.ID;

해당 문제에서 포인트는 LEFT JOIN 이였다.
개념을 다시 한 번 숙지하도록 하자.

LEFT JOIN의 경우,
여러 테이블이 있는 경우 기존 테이블을 그대로 출력하면서 정보를 가져오기 위함이다.

대장균의 크기에 따라 분류하기 2

WITH CLASSIFIED AS (
	SELECT
    	ID,
        CASE NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC)
            WHEN 1 THEN 'CRITICAL'
            WHEN 2 THEN 'HIGH'
            WHEN 3 THEN 'MEDIUM'
            WHEN 4 THEN 'LOW'
        END AS COLONY_NAME
    FROM ECOLI_DATA
)
SELECT
	ID, COLONY_NAME
FROM
	CLASSIFIED
ORDER BY ID;

해당 문제에서 포인트는 WITH 구문 과 NTILE( ) 함수 였다.

<WITH 구문 필요성>
QUERY를 반복해서 사용하는 경우가 발생하는데,
수가 많아질 수록 가독성이 떨어지고 수정도 쉽지 않음

① CTE (Common Table Expressions)
: 영구적으로 저장되지 않고, 쿼리가 실행되는 동안에만 임시로 존재하는 결과 값
: SELECT (INSERT, UPDATE, DELETE 포함) 쿼리를 실행할 때, 해당 쿼리 내 CTE 구현 가능

위 코드 'CLASSIFIED' 가 CTE 라고 보면 된다.
근데 서브 쿼리를 사용하면 되는거 아닌가?!

서브 쿼리와의 차이점 → "재사용"
서브 쿼리의 경우 쿼리 내에서만 사용이 가능하지만 재사용이 불가

-- 최초에는 서브쿼리로 작성함 (정답 처리)

SELECT
    A.ID,
    A.COLONY_NAME
FROM
(
    SELECT
        ID,
        CASE NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC)
            WHEN 1 THEN 'CRITICAL'
            WHEN 2 THEN 'HIGH'
            WHEN 3 THEN 'MEDIUM'
            WHEN 4 THEN 'LOW'
        END AS COLONY_NAME
    FROM ECOLI_DATA
) AS A
ORDER BY A.ID;

NTILE( ) 함수는 "전체 데이터를 N개의 그룹(분위)으로 균등하게 나누는" 윈도우 함수이다.
기본 문법 : NTILE(N) OVER (ORDER BY COL_NAME [ASIC | DESC]

위 문제의 경우,
상위 0 ~ 25% / 26 ~ 50% / 51 ~ 75% / 76 ~ 100% 으로 분류 조건이 있어,
사용하기 적합한 윈도우 함수였다.