[MySQL] Programmers SQL 고득점 Kit 풀기 - GROUP BY(2)

이전 글

https://fladi.tistory.com/451

 

 

서론

SQL 고득점 Kit 마지막 글이 될 것 같다. 더 풀고싶은데 조금 아쉽다.

GROUP BY도 계속 풀다보니 익숙해지는 것 같다. 감을 잃지 않도록 계속 복습해야겠다.

 

 

13. 년, 월, 성별 별 상품 구매 회원 수 구하기 - 레벨4

SELECT YEAR(O.SALES_DATE) AS YEAR, 
    MONTH(O.SALES_DATE) AS MONTH,
    U.GENDER AS GENDER,
    COUNT(DISTINCT U.USER_ID) AS USERS
FROM ONLINE_SALE O
JOIN USER_INFO U
ON O.USER_ID = U.USER_ID
WHERE U.GENDER IS NOT NULL
GROUP BY YEAR(O.SALES_DATE), MONTH(O.SALES_DATE), U.GENDER
ORDER BY YEAR, MONTH, GENDER

생각없이 풀다가 한 번 틀렸다.

 

1. JOIN을 해준다
2. GENDER가 NULL인 데이터는 포함하지 않는다
3. GROUP BY를 조건대로 수행해준다
4. COUNT에서는 회원 수를 집계해야하는 문제이기 때문에 DISTINCT를 사용해줘야한다. 같은 년,월에 같은 사람이 2번 이상 구매할 수 있기 때문이다. 같은 년, 월일 때 한 사람은 한 번만 집계되어야한다.

 

 

14. 입양 시각 구하기(1) - 레벨2

SELECT HOUR(`DATETIME`) AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
WHERE HOUR(`DATETIME`) BETWEEN 9 AND 20
GROUP BY HOUR(`DATETIME`)
ORDER BY HOUR

어렵지 않다

 

 

15. 입양 시각 구하기(2) - 레벨4 ✨✨

SET @hour := -1;

WITH HOURTABLE AS (
    SELECT (@hour := @hour + 1) AS HOUR
    FROM ANIMAL_OUTS
    WHERE @hour < 23
),

HOURCOUNT AS (
    SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT
    FROM ANIMAL_OUTS
    GROUP BY HOUR(DATETIME)
) 

SELECT HT.HOUR, IFNULL(HC.COUNT, 0) AS COUNT
FROM HOURTABLE HT
LEFT JOIN HOURCOUNT HC
ON HT.HOUR = HC.HOUR
ORDER BY HT.HOUR

INSERT문 없이 도대체 어떻게 풀어야할지 감도 안잡혔다. 구글에 검색해보니 ROW_NUMBER()를 이용해서 어거지로 0~23을 만들어내신 분도 있었고, SET을 사용하는 사람도 있었다.

(ROW_NUMBER()를 사용하신 분은 정말 존경스럽다. 자신이 아는 선에서 정답을 내놓긴 했으니말이다. 이 분의 블로그링크이다 https://hellobrocolli.tistory.com/25)

 

완전히 새로운 문법이 나왔으니 정리해보겠다.

 

1. SET으로 새로운 사용자 정의 변수 @hour를 정의한다. := 는 값을 할당하는 연산자이다
2. 변수 @hour를 -1부터 1씩 증가시키면서 0 ~ 23이 담긴 테이블 HOURTABLE을 만든다
3. HOUR(DATETIME)을 기준으로 GROUP BY를 수행하여 개수를 센 HOURCOUNT를 만들어준다. 
4. HOURTABLE과 HOURCOUNT를 JOIN해주고, HOURCOUNT에 없는 경우(NULL인 경우) 0으로 COUNT를 지정해서 출력한다.

 

<다른 방법 - SET사용하지 않고 재귀 CTE 사용>

WITH RECURSIVE HOURS AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1
    FROM HOURS
    WHERE HOUR < 23
)
SELECT H.HOUR, IFNULL(COUNT(A.DATETIME), 0) AS COUNT
FROM HOURS H
LEFT JOIN ANIMAL_OUTS A ON H.HOUR = HOUR(A.DATETIME)
GROUP BY H.HOUR
ORDER BY H.HOUR;

SET을 사용하지 않는 방법도 있다. RECURSIVE를 사용하는 방법이다. 재귀 CTE라고 불리는 방법이라고 한다. 

 

1. 초기행으로 0을 생성
2. UNION ALL을 통해 다음 값 생성
3. WHERE 조건으로 23까지만 생성되도록 제한

 

두 방법 모두 기억해뒀다가 SQL 코테 때 기억나는 친구를 사용해야겠다. 

 

 

16. 가격대 별 상품 개수 구하기

SELECT FLOOR(PRICE/10000)*10000 AS PRICE_GROUP, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY FLOOR(PRICE/10000)
ORDER BY PRICE_GROUP

만원대로 나누는 방식으로 그룹을 정해서 풀었다. 

 

 

17. 언어별 개발자 분류하기

WITH FE_SKILL AS (
    SELECT BIT_OR(CODE) AS CODE
    FROM SKILLCODES
    WHERE CATEGORY = 'Front End'
),

D_WITH_GRADE AS (
    SELECT ID, EMAIL, CASE
        WHEN SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME='Python') > 0 AND SKILL_CODE & (SELECT CODE FROM FE_SKILL) > 0 THEN 'A'
        WHEN SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME='C#')  > 0 THEN 'B'
        WHEN SKILL_CODE & (SELECT CODE FROM FE_SKILL) > 0 THEN 'C'
        ELSE NULL
    END AS GRADE
    FROM DEVELOPERS
)

SELECT GRADE, ID, EMAIL
FROM D_WITH_GRADE
WHERE GRADE IS NOT NULL
ORDER BY GRADE, ID

꼼수 부린다고 code값을 하드코딩했더니 틀렸다. 직접 Python과 FrontendSkill인 코드를 찾아야했다.

하지만 이 코드처럼 SELECT 문에 SELECT문을 넣으면 레코드 각각마다 조회가 일어난다. (물론 FE_SKILL 테이블에는 레코드가 하나만 들어있어서 크게 성능차이는 나지 않을 수 있따) SELECT 안에 SELECT문을 넣지 않기 위해서는 CROSS JOIN을 사용할 수 있다.

 

-- 먼저 필요한 모든 스킬 코드를 한번에 조회
WITH BASE_SKILLS AS (
    SELECT 
        MAX(CASE WHEN NAME = 'Python' THEN CODE END) AS PYTHON_CODE,
        MAX(CASE WHEN NAME = 'C#' THEN CODE END) AS CSHARP_CODE
    FROM SKILLCODES
),

FRONTEND_SKILLS AS (
    SELECT BIT_OR(CODE) AS FE_CODE
    FROM SKILLCODES
    WHERE CATEGORY = 'Front End'
),

GRADED_DEVELOPERS AS (
    SELECT 
        D.ID,
        D.EMAIL,
        CASE
            WHEN D.SKILL_CODE & BS.PYTHON_CODE > 0 
                 AND D.SKILL_CODE & FS.FE_CODE > 0 
                 THEN 'A'
            WHEN D.SKILL_CODE & BS.CSHARP_CODE > 0 
                 THEN 'B'
            WHEN D.SKILL_CODE & FS.FE_CODE > 0 
                 THEN 'C'
        END AS GRADE
    FROM DEVELOPERS D
    CROSS JOIN BASE_SKILLS BS
    CROSS JOIN FRONTEND_SKILLS FS
    # WHERE D.SKILL_CODE & (BS.PYTHON_CODE | BS.CSHARP_CODE | FS.FE_CODE) > 0
)

SELECT 
    GRADE,
    ID,
    EMAIL
FROM GRADED_DEVELOPERS
WHERE GRADE IS NOT NULL
ORDER BY 
    GRADE ASC,
    ID ASC;

 

1. Python과 C# 스킬을 attribute로 가지는 CTE를 만들어준다. 이 때 레코드는 MAX를 사용하여 한 건만 저장되도록 한다
2. Frontend 스킬을 attribute로 가지는 CTE도 만들어준다. 여기서도 BIT_OR을 사용하여 한 건만 저장되도록 한다(Cross join을 사용하기 위함)
3. 등급을 매길 GRADED_DEVELOPERS CTE를 만든다. CROSS JOIN을 사용하여 테이블에 Python, Frontend 등의 속성을 추가시킨 후 등급을 매긴다.
   1. (이렇게 하면 DEVELOPERS 테이블에 해당하는 각 레코드에 PYTHON_CODE, CSHARP_CODE, FE_CODE 3개의 attribute가 추가된다. BASE_SKILL과 FRONTEND_SKILLS 테이블에는 레코드가 하나만 있기 때문에 결과 레코드 수는 DEVELOPERS 테이블 레코드수와 동일하다.)
4. GRADE가 NULL이 아닌 레코드를 조회하여 ORDER BY를 수행해준다.

 

이번에 CROSS JOIN을 처음 써봤는데 좀 참신했다. 기억해둬야겠다. . 

 

 

18. 조건에 맞는 사원 정보 조회하기

WITH TMP AS (
    SELECT SUM(HG.SCORE) AS SCORE, 
        HE.EMP_NO, 
        HE.EMP_NAME, 
        HE.POSITION, 
        HE.EMAIL
    FROM HR_GRADE HG
    JOIN HR_EMPLOYEES HE
    ON HG.EMP_NO = HE.EMP_NO
    WHERE HG.YEAR = '2022'
    GROUP BY HE.EMP_NO
),

TMP2 AS (
    SELECT SCORE, 
        EMP_NO, 
        EMP_NAME, 
        POSITION, 
        EMAIL,
        RANK() OVER (ORDER BY SCORE DESC) AS RRANK
    FROM TMP
)

SELECT SCORE, 
    EMP_NO, 
    EMP_NAME, 
    POSITION, 
    EMAIL
FROM TMP2 AS A
WHERE RRANK = 1;

생각보다 복잡해서 차근차근 하나씩 풀어냈다.

 

1. TMP CTE에서 EMP_NO를 기준으로 2022년 데이터를 GROUP BY로 묶어준다. 2022년 합계 SCORE를 구할 수 있다. 
2. TMP2 CTE에서 SCORE가 높은 순으로 RANK를 매겨준다. 
   1. 이 때 SCORE가 가장 높은 레코드(RRANK가 1인 레코드)는 여러 개가 나올 수 있다
3. 가장 높은 SCORE를 가지는(RANK가 1인) 레코드를 출력한다

 

만약 가장 높은 SCORE를 가지는 사원 1명만 출력하고 싶다면 LIMIT 1을 사용하거나, ROW_NUMBER를 사용하기나, FIRST_VALUE를 사용할 수 있다. 

 

 

<다른 쉬운 풀이>

SELECT SUM(SCORE) AS SCORE, G.EMP_NO, E.EMP_NAME, E.POSITION, E.EMAIL
FROM HR_EMPLOYEES E
    INNER JOIN HR_GRADE G ON E.EMP_NO = G.EMP_NO
WHERE G.YEAR = '2022'
GROUP BY EMP_NO
ORDER BY 1 DESC
LIMIT 1;

그냥 ORDER BY를 쓰는 아주 간단한 풀이도 있다. 나는 RANK부여하고 CTE를 2개나 만들면서 복잡하게 풀었는데, 단순하게 생각하면 굉장히 쉬운 문제인 것 같다.

 

 

19. 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기

WITH EMP_SUM AS (
    SELECT HE.EMP_NO, HE.EMP_NAME, HE.SAL, AVG(SCORE) AS SUM_SCORE
    FROM HR_EMPLOYEES HE
    JOIN HR_GRADE HG
    ON HE.EMP_NO = HG.EMP_NO
    GROUP BY EMP_NO
)

SELECT EMP_NO, EMP_NAME, 
    CASE 
        WHEN SUM_SCORE >= 96 THEN 'S'
        WHEN SUM_SCORE >= 90 THEN 'A'
        WHEN SUM_SCORE >= 80 THEN 'B'
        ELSE 'C'
    END AS GRADE, 
    CASE 
        WHEN SUM_SCORE >= 96 THEN SAL * 20 / 100
        WHEN SUM_SCORE >= 90 THEN SAL * 15 / 100
        WHEN SUM_SCORE >= 80 THEN SAL * 10 / 100
        ELSE 0
    END AS BONUS
FROM EMP_SUM
ORDER BY EMP_NO ASC

차근차근하면 어렵지 않은 문제다.

GROUP BY를 모두 수행한 후, SELECT를 진행하기 때문에 나처럼 CTE로 분리하지 않고 하나의 쿼리로도 충분히 작성가능할 것 같다. 하나의 쿼리로 만들면 다음과 같이 만들 수 있다.

 

SELECT HE.EMP_NO, HE.EMP_NAME, 
    CASE 
        WHEN AVG(SCORE) >= 96 THEN 'S'
        WHEN AVG(SCORE) >= 90 THEN 'A'
        WHEN AVG(SCORE) >= 80 THEN 'B'
        ELSE 'C'
    END AS GRADE, 
    CASE 
        WHEN AVG(SCORE) >= 96 THEN SAL * 20 / 100
        WHEN AVG(SCORE) >= 90 THEN SAL * 15 / 100
        WHEN AVG(SCORE) >= 80 THEN SAL * 10 / 100
        ELSE 0
    END AS BONUS
FROM HR_EMPLOYEES HE
JOIN HR_GRADE HG
ON HE.EMP_NO = HG.EMP_NO
GROUP BY EMP_NO
ORDER BY EMP_NO ASC

훨씬 깔끔하다!

 

 

20. 부서별 평균 연봉 조회하기

SELECT HD.DEPT_ID, HD.DEPT_NAME_EN, 
    ROUND(AVG(HE.SAL), 0) AS AVG_SAL
FROM HR_EMPLOYEES HE
JOIN HR_DEPARTMENT HD
ON HE.DEPT_ID = HD.DEPT_ID
GROUP BY HD.DEPT_ID
ORDER BY AVG(HE.SAL) DESC

그냥 GROUP BY만 쓰면 되는 쉬운 문제다. 평균연봉순으로 정렬 시, 혹시 몰라서 ROUND를 쓴 AVG_SAL을 쓰지 않고 AVG를 사용하였다.

 

 

21. 노선별 평균 역 사이 거리 조회하기 - 레벨2

SELECT ROUTE, 
    CONCAT(ROUND(SUM(D_BETWEEN_DIST), 1), 'km') AS TOTAL_DISTANCE,
    CONCAT(ROUND(AVG(D_BETWEEN_DIST), 2), 'km') AS AVERAGE_DISTANCE
FROM SUBWAY_DISTANCE 
GROUP BY ROUTE
ORDER BY ROUND(SUM(D_BETWEEN_DIST), 1) DESC

계속 틀렸다고 나와서 화가 났는데 이상한 실수를 한 거였다. 틀린 이유는 CONCAT을 하고 난 후인 TOTAL_DISTANCE를 기준으로 내림차순 정렬을 해서 원하는 결과가 나오지 않았다. 예를 들어 '12km'와 '111km'는 111가 더 커야하는데, 사전순으로 정렬되면서 꼬일 수 있다.

CONCAT도 조심해서 써야겠다. 

 

+) 소수점 둘째자리에서 반올림하는 경우, ROUND(X, 1) 이렇게 1을 써줘야한다. 나타내고자 하는 소수점 개수를 적어줘야한느 거 주의!

 

 

22. 물고기 종류 별 잡은 수 구하기 - 레벨2

SELECT COUNT(*) AS FISH_COUNT, FNI.FISH_NAME
FROM FISH_INFO FI
JOIN FISH_NAME_INFO FNI
ON FI.FISH_TYPE = FNI.FISH_TYPE
GROUP BY FNI.FISH_NAME
ORDER BY FISH_COUNT DESC

그냥 JOIN과 GROUP BY만 수행하면 전혀 어렵지 않은 문제다. 딱히 설명할 게 없다.

 

 

23. 월별 잡은 물고기 수 구하기 - 레벨2

SELECT COUNT(*) AS FISH_COUNT, 
    MONTH(TIME) AS MONTH
FROM FISH_INFO
GROUP BY MONTH(TIME)
ORDER BY MONTH ASC

GROUP BY만 잘 쓰면 어렵지 않은 문제다. 

MONTH() 함수를 사용하면 월을 출력할 때 0이 붙지 않고, GROUPBY를 사용했기 때문에 잡은 물고기가 없는 MONTH는 출력되지 않는다.

 

+) 더 알아가기: 만약 1을 01로 표시하고, 2를 02로 표시하고 싶은 경우 LPAD라는 함수를 사용하면 된다. LPAD(MONTH(TIME), 2, '0') 이런식으로 자릿수와 왼쪽에 채울 문자를 넣어주는 식으로 사용한다.

 

 

24. 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기 - 레벨3

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

 

HAVING에 AVG를 넣고 해당하는 것만 출력되게 만들었다. 어렵지 않은 문제다.