[SQL 알고리즘] 프로그래머스 -대장균의 크기에 따라 분류하기 2

https://school.programmers.co.kr/learn/courses/30/lessons/301649

프로그래머스

 

✅ 문제

대장균 개체의 크기를 내름차순으로 정렬했을 때 상위 0% ~ 25% 를 'CRITICAL', 26% ~ 50% 를 'HIGH', 51% ~ 75% 를 'MEDIUM', 76% ~ 100% 를 'LOW' 라고 분류합니다. 대장균 개체의 ID(ID) 와 분류된 이름(COLONY_NAME)을 출력하는 SQL 문을 작성해주세요. 이때 결과는 개체의 ID 에 대해 오름차순 정렬해주세요 . 단, 총 데이터의 수는 4의 배수이며 같은 사이즈의 대장균 개체가 서로 다른 이름으로 분류되는 경우는 없습니다.

 

 

 

문제만 보면 엄청 복잡하게 보일수 있다. 일단 첫번째로 알아야되는건 백분율을 구하는것이다 백분율을 구하는건 순위 / 전체숫자면 내가 상위 몇퍼센트인지 구할수 있다 쉽게 생각하면 우리 학교때 전교석차같은 개념이다. 이거를 먼저 알고 시작을 해야된다.

 

그렇다면 순위와 전체숫자를 구하는 쿼리를 먼저 작성해야된다.

✅ 순위와 전체숫자 쿼리

 SELECT RANK() OVER(ORDER BY SIZE_OF_COLONY DESC) AS RANKS, 
        COUNT(*) OVER() AS COUNT
    FROM ECOLI_DATA

• 일단 OVER()절과 RANK()에 대해서 알아야된다. 
• 나도 이번에 처음써보는것이고 어떤식으로 풀어야되나 고민하다가 찾아보니 OVER()을 사용하면 쉽게 해결된다.

📚 OVER절

예를 먼저 들어보면서 보는게 이해하기 쉽다.

물품 A의 재고관리를 위해 "창고"테이블이 있다고 가정하면 창고 A에 언제, 얼마나 입/출고 되었는지 나타내고

수량이 양수일 시 입고, 음수일 시 출고를 의미한다.

바로 이곳에서 OVER절을 사용한다. 

 

위 데이터에서 각 날짜별로 창고의 재고를 알고싶다고 할 때, 필요한 데이터는 전체 합이 아닌 날짜별 누적합이다. 데이터는 아래와 같을 것이다.

즉,

1번 행은 10월 1일까지의 합 SUM(1),
2번 행은 10월 2일까지의 합 SUM(1, 2),
3번 행은 10월 3일까지의 합 SUM(1, 2, 3) 이다.

 

이를 보면 각 행별로 SUM 함수에 사용된 레코드의 집합이 다 다른 것을 알 수 있다.

이렇게 각 행별로 특정 기준에 따라 필요한 집합을 구해 함수를 적용하고 싶을 때 쓰는 구문이 OVER 절이다.

 

OVER절을 사용한 쿼리

SELECT 번호
  , 날짜
  , 수량
  , SUM(수량) OVER(ORDER BY 날짜) AS 재고
FROM 창고

 

✏️공부한 내용

• 언뜻 들으면 GROUP BY랑 비슷하게 들리지만 둘은 엄연히 다르다
• OVER절은 행과 행 간의 관계를 정의하는 함수인 WINDOW FUNCTION 이며, 그룹을 지어주기보다는 행의 범위를 지정해주는 의미에 좀 더 가깝다.
• GROUP BY절은 결과 행 개수에 영향을 미치지만 OVER절은 영향을 미치지 않는다.

 

 

✅ RANK()함수

• RANK 함수는 중복 값들에 대해서 동일 순위로 표시하고, 중복 순위 다음 값에 대해서는 중복 개수만큼 떨어진 순위로 출력하도록 하는 함수 입니다.
  
  

✅ DENSE_RANK()함수

• DENSE_RANK 함수는 중복 값들에 대해서 동일 순위로 표시하고, 중복 순위 다음 값에 대해서는 중복 값 개수와 상관없이 순차적인 순위 값을 출력하도록 하는 함수 입니다.

✅ ROW_NUMBER()함수

• ROW_NUMBER 함수는 중복 값들에 대해서도 순차적인 순위를 표시하도록 출력하는 함수 입니다.

 

 

 

📚 정답코드

위에 함수들을 이용하면 쉽게 정답을 만들수 있다.

SELECT 
    A.ID,
    CASE WHEN A.RANKS / A.COUNT <= 0.25 THEN 'CRITICAL'
         WHEN A.RANKS / A.COUNT <= 0.5 THEN 'HIGH'
         WHEN A.RANKS / A.COUNT <= 0.75 THEN 'MEDIUM'
    ELSE 'LOW' END AS COLONY_NAME
FROM (
    SELECT *,
        RANK() OVER(ORDER BY SIZE_OF_COLONY DESC) AS RANKS, 
        COUNT(*) OVER() AS COUNT
    FROM ECOLI_DATA 
    ) A
    ORDER BY A.ID