🚩오늘의 문제는?

SQL - level 3

1.업그레이드 할 수 없는 아이템 구하기
2.물고기 종류 별 대어 찾기
3.대장균의 크기에 따라 분류하기 2

1.업그레이드 할 수 없는 아이템 구하기

문제 정의

문제 풀이

1.업그레이드 될 수 있는 ITEM을 구하기

SELECT DISTINCT PARENT_ITEM_ID 
FROM ITEM_TREE 
WHERE PARENT_ITEM_ID IS NOT NULL;

2.업그레이드 안되는 ITEM을 구하기

SELECT DISTINCT A.ITEM_ID
FROM ITEM_TREE A LEFT JOIN (SELECT DISTINCT PARENT_ITEM_ID 
FROM ITEM_TREE 
WHERE PARENT_ITEM_ID IS NOT NULL) AS B
ON A.ITEM_ID = B.PARENT_ITEM_ID 
WHERE B.PARENT_ITEM_ID IS NULL;

3.업그레이드 안되는 아이템의 ID, 명, 희귀도를 구하기(내림차순까지)

SELECT C.ITEM_ID,	C.ITEM_NAME,	C.RARITY
FROM ITEM_INFO AS C
WHERE C.ITEM_ID IN (SELECT DISTINCT A.ITEM_ID
FROM ITEM_TREE A LEFT JOIN (SELECT DISTINCT PARENT_ITEM_ID 
FROM ITEM_TREE 
WHERE PARENT_ITEM_ID IS NOT NULL) AS B
ON A.ITEM_ID = B.PARENT_ITEM_ID 
WHERE B.PARENT_ITEM_ID IS NULL)
ORDER BY C.ITEM_ID DESC;

2.물고기 종류 별 대어 찾기

문제 정의

문제 풀이

1.물고기의 종류별 최대 길이를 구하기

SELECT FISH_TYPE, MAX(LENGTH)
FROM FISH_INFO
GROUP BY FISH_TYPE;

2.최대 길이의 ID값을 구하기

SELECT A.ID ,A.FISH_TYPE, A.LENGTH
FROM FISH_INFO AS A JOIN (SELECT FISH_TYPE, MAX(LENGTH) AS LENGTH
FROM FISH_INFO GROUP BY FISH_TYPE) AS B
ON A.FISH_TYPE= B.FISH_TYPE AND A.LENGTH = B.LENGTH ;   

3. FISH_NAME 이 있는 테이블과 JOIN + 물고기 ID 오름차순

SELECT ID,FISH_NAME,LENGTH
FROM FISH_NAME_INFO C JOIN
(SELECT A.ID ,A.FISH_TYPE, A.LENGTH
FROM FISH_INFO AS A JOIN (SELECT FISH_TYPE, MAX(LENGTH) AS LENGTH
FROM FISH_INFO GROUP BY FISH_TYPE) AS B
ON A.FISH_TYPE= B.FISH_TYPE AND A.LENGTH = B.LENGTH) AS D
ON C.FISH_TYPE = D.FISH_TYPE
ORDER BY ID ASC;

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

문제 정의

문제 풀이

1.대장균의 크기를 기준으로 분류하기 (NTILE 함수 이용)

SELECT ID,PARENT_ID	,SIZE_OF_COLONY,DIFFERENTIATION_DATE,GENOTYPE,
NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC) AS COLONY_NAME
FROM ECOLI_DATA
ORDER BY ID ASC;

2.COLONY_NAME을 문구로 변경하고 ID 에 대해 오름차순으로 정렬하기

SELECT ID , 
CASE WHEN COLONY_NAME = 1 THEN 'CRITICAL'
WHEN COLONY_NAME = 2 THEN 'HIGH'
WHEN COLONY_NAME = 3 THEN 'MEDIUM'
ELSE 'LOW'
END AS COLONY_NAME 

FROM(
SELECT ID,PARENT_ID	,SIZE_OF_COLONY,DIFFERENTIATION_DATE,GENOTYPE,
NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC)  AS COLONY_NAME
FROM ECOLI_DATA
) AS A
ORDER BY ID ASC ;

주의할 점

✔ NTILE 함수 : 분위가 낮을 수록 값이 높다.
✔ NTILE(3) - 3개 단위로 쪼개짐 , NTILE(4) - 4개 단위로 쪼개짐

✔ NTILE(4) OVER ( ORDER BY COL2 )
: 여기서 COL2은 나열 기준 컬럼!
: 예시) 소득을 내림차순한 것을 기준으로 4개의 그룹으로 나누기

NTILE(4) OVER (ORDER BY 소득 DESC)

✔ NTILE(4) OVER ( PARTITION BY COL1 ORDER BY COL2 )
: 여기서 COL1을 기준으로 나누고, COL2로 나열하기
: 예시) 고객의 성별 내에서 소득을 내림차순한 것을 기준으로 4개의 그룹으로 나누기

NTILE(4) OVER (ORDER BY 성별 ORDER BY 소득 DESC)