📕 SQL/MySQL

📕[SQL] 24.09.08 SQL

혜덕hyeduck 2024. 9. 8. 18:23

SQL ) LV3. 대장균의 크기에 따라 분류하기 2

관련 개념

  • 순위 함수
    • 종류
      • RANK() OVER()
        • 공동 순위만큼 건너뛴다.
          • EX) 1 , 2 , 2 ,4
      • DENSE_RANK() OVER()
        • 공동 순위만큼 건너뛰지 않는다.
          • EX) 1, 2, 2, 3
      • ROW_NUMBER() OVER()
        • 공동 순위 무시한다.
          • EX) 1, 2, 3, 4
      • PERCENT_RANK() OVER()
        • 0~1사이의 값을 반환한다.
        • 백분율 순위를 구할 때 사용한다.
      • NTILE() OVER()
        • PARTITION을 지정된 수 만큼 등급으로 나누어 각 등급 번호 출력
          • EX) SELECT NTILE(4) OVER(ORDER BY SIZE DESC) AS NO FROM TABLE_NAME
            • SIZE칼럼을 4개의 그룹으로 나눠서 1부터 4까지 번호를 매긴다.
    • PARTITION BY 열이름
      • 열이름별로 순위를 매기고 싶을 때 사용
        • 즉, 특정 열을 기준으로 그룹화한 뒤 해당 그룹 내에서 순위 매길 때 사용
    • ORDER BY
      • OVER안에 ORDER BY 열이름을 사용하면, 해당 열을 기준으로 순위를 매길 수 있다. 추가로 ASC, DESC 옵션을 줄 수 있다.

접근법

  • 접근법1
    • 서브쿼리로 인라인 뷰 2개 생성
      • E1은 ID별 순위 조회
      • E2는 전체 행의 개수 조회
    • 이후, SELECT 절에서 CASE문을 사용해 E1의 RANKING값이 전체 행 개수 COUNT * 비율(문제에 주어짐)을 했을 때 적절한 값을 갖도록 매칭했다
  • 접근법2 ~ 3
    • NTILE함수나 PERCENT_RANK함수를 사용하면 더 간단하게 구할 수 있다.

코드

  • 접근법1 : RANK 사용
SELECT 
    ID,
    CASE 
        WHEN RANKING <= COUNT * 0.25 THEN 'CRITICAL'
        WHEN RANKING <= COUNT * 0.5 THEN 'HIGH'
        WHEN RANKING <= COUNT * 0.75 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS COLONY_NAME
FROM (SELECT ID, RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS RANKING FROM ECOLI_DATA) E1, (SELECT COUNT(*) COUNT FROM ECOLI_DATA) E2
ORDER BY 1;
  • 접근법2 : NTILE 사용
SELECT ID,
        CASE
            WHEN NO = 1 THEN 'CRITICAL'
            WHEN NO = 2 THEN 'HIGH'
            WHEN NO = 3 THEN 'MEDIUM'
            ELSE 'LOW'
        END AS COLONY_NAME
FROM (SELECT ID, NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC) AS NO FROM ECOLI_DATA) E
ORDER BY 1;
  • 접근법3 : PERCEN_RANK사용
SELECT ID,
     CASE
            WHEN PERCENT <= 0.25 THEN 'CRITICAL'
            WHEN PERCENT <= 0.5 THEN 'HIGH'
            WHEN PERCENT <= 0.75 THEN 'MEDIUM'
            ELSE 'LOW'
    END AS COLONY_NAME
FROM (SELECT ID, PERCENT_RANK() OVER(ORDER BY SIZE_OF_COLONY DESC) AS PERCENT FROM ECOLI_DATA) E
ORDER BY 1;

 

SQL ) LV3. 물고기 종류 별 대어 찾기

접근법

  • WHERE절에서 서브쿼리로 FISH_TYPE별로 그룹화하여 각 그룹의 가장 큰 길이 MAX(LENGTH)와 FISH_TYPE을 반환했다.
  • 이때, 반환값과 일치하는 레코드만 조회했다.
  • 두 테이블의 칼럼 정보가 필요하므로 JOIN연산을 수행했다.

코드

SELECT ID, FISH_NAME, LENGTH
FROM FISH_INFO FI JOIN FISH_NAME_INFO NI ON FI.FISH_TYPE = NI.FISH_TYPE
WHERE (FI.FISH_TYPE, LENGTH) IN (SELECT FISH_TYPE, MAX(LENGTH) FROM FISH_INFO GROUP BY FISH_TYPE ORDER BY 1)
ORDER BY 1;

 

SQL ) LV3. 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기

접근법

  • WHERE절에서 서브쿼리로 FISH_TYPE별 그룹화했을 때, 평균 길이가 10이상인 경우의 FISH_TYPE들을 반환하도록 했다.
    • NULL인 데이터들의 길이는 모두 10으로 취급하라 했으므로, IFNULL로 처리했다.
  • 이후, FISH_TYPE으로 그룹화한 뒤, 그룹별 레코드 수, 최대 길이, FISH_TYPE을 출력했다.

코드

SELECT COUNT(*) FISH_COUNT, MAX(LENGTH) MAX_LENGTH, FISH_TYPE
FROM FISH_INFO
WHERE FISH_TYPE IN (SELECT FISH_TYPE FROM FISH_INFO GROUP BY FISH_TYPE HAVING AVG(IFNULL(LENGTH, 10)) >= 33)
GROUP BY FISH_TYPE
ORDER BY 3;

 

SQL ) LV3. 대장균의 크기에 따라 분류하기 1

접근법

  • CASE문을 통해 대장균 크기 별로 LOW, MEDIUM, HIGH구분해서 출력

코드

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