[코딩테스트] 프로그래머스 SQL GROUP BY 11 ~ 20

제가 공부한 내용을 정리하는 블로그입니다.
아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁
Programmers SQL 고득점 Kit(GROUP BY)입니다.

고양이와 개는 몇마리 있을까

SELECT
    ANIMAL_TYPE,
    COUNT(*) AS COUNT
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE

• 고양이와 개를 그룹화합니다.
  GROUP BY ANIMAL_TYPE
• 개수를 구하기 위해 COUNT 함수를 사용하고 고양이가 먼저 나오기 위해 오름차순 정렬합니다.

동명 동물 수 찾기

SELECT
    NAME,
    COUNT(*) AS COUNT
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT(*) > 1
ORDER BY NAME

• 이름이 있으며, 이름을 기준으로 그룹화합니다.
  WHERE NAME IS NOT NULL
  GROUP BY NAME
• 동일한 이름의 동물을 찾기 위해 HAVING 절을 통해 COUNT 함수를 사용합니다.
  HAVING COUNT(*) > 1
• 동물 이름을 기준으로 오름차순 정렬합니다.

년, 월, 성별 별 상품 구매 회원 수 구하기

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

• 상품 구매한 회원을 구하기 위해 JOIN합니다.
  FROM USER_INFO U JOIN ONLINE_SALE O ON U.USER_ID = O.USER_ID
• 성별 정보가 없는 데이터는 제외하기 위해 조건을 걸어줍니다.
  WHERE U.GENDER IS NOT NULL
• 연도, 월, 성별을 기준으로 그룹화 및 정렬합니다.
  GROUP BY TO_CHAR(O.SALES_DATE, 'YYYY'), EXTRACT(MONTH FROM O.SALES_DATE), U.GENDER
  ORDER BY TO_CHAR(O.SALES_DATE, 'YYYY'), EXTRACT(MONTH FROM O.SALES_DATE), U.GENDER

입양 시각 구하기(1)

SELECT 
    TO_NUMBER(TO_CHAR(DATETIME, 'HH24') AS HOUR,
    COUNT(*) AS COUNT
FROM ANIMAL_OUTS
WHERE TO_CHAR(DATETIME, 'HH24') BETWEEN 09 AND 19
GROUP BY TO_CHAR(DATETIME, 'HH24')
ORDER BY HOUR ASC

• 09시와 19시 사이의 데이터를 조회하기 위해 TO_CHAR와 BETWEEN 함수를 사용합니다.
  WHERE TO_CHAR(DATETIME, 'HH24') BETWEEN 09 AND 19
• 입양 시각을 기준으로 그룹화합니다.
  GROUP BY TO_CHAR(DATETIME, 'HH24')
• 정렬하기 위해 TO_NUMBER 함수를 사용하고 HOUR를 기준으로 오름차순 정렬합니다.

입양 시각 구하기(2)

SELECT 
    L.HOUR AS HOUR, 
    NVL(COUNT, 0) AS COUNT
FROM (
    SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR, COUNT(*) AS COUNT 
    FROM ANIMAL_OUTS 
    GROUP BY TO_CHAR(DATETIME, 'HH24') 
    ORDER BY HOUR
) O, (
    SELECT LEVEL -1 AS HOUR 
    FROM DUAL 
    CONNECT BY LEVEL <= 24
) L      
WHERE L.HOUR = O.HOUR(+)
ORDER BY L.HOUR;

• 입양 시각을 통해 그룹화를 진행합니다.
  (
      SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR, COUNT(*) AS COUNT 
      FROM ANIMAL_OUTS 
      GROUP BY TO_CHAR(DATETIME, 'HH24') 
      ORDER BY HOUR
  ) O
• 가상의 테이블와 계층 쿼리를 통해 LEVEL을 0부터 23까지 숫자를 반환합니다.
  ( SELECT LEVEL -1 AS HOUR FROM DUAL CONNECT BY LEVEL <= 24 ) L
• O 테이블과 L 테이블을 LEFT OUTER JOIN합니다. 레코드가 없는 행을 0으로 표현하기 위함입니다.
  WHERE L.HOUR = O.HOUR(+)
• NULL인 데이터를 0으로 표현하기 위해 NVL함수를 사용하고 HOUR를 기준으로 오름차순 정렬합니다.
  NVL(COUNT, 0) AS COUNT

가격대 별 상품 개수 구하기

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

• 만원 단위의 가격대로 표현하기 위해 버림과 10000 곱하기를 통해 그룹화합니다.
  GROUP BY FLOOR(PRICE / 10000) * 10000
• 가격대를 기준으로 오름차순 정렬합니다.

언어별 개발자 분류하기

WITH J AS (
    SELECT 
        ID,
        EMAIL,
        CASE -- 조건에 맞게 SCORE를 기록
           WHEN COUNT(CASE WHEN CATEGORY = 'Front End' THEN 1 END) > 0 AND 
                COUNT(CASE WHEN NAME = 'Python' THEN 1 END) > 0 
                THEN 3
           WHEN COUNT(CASE WHEN NAME = 'C#' THEN 1 END) > 0 THEN 2
           WHEN COUNT(CASE WHEN CATEGORY = 'Front End' THEN 1 END) > 0 THEN 1
        END AS SCORE
    FROM
        DEVELOPERS AS D
    JOIN -- 비트 논리 연산을 조건으로 JOIN
        SKILLCODES AS S ON D.SKILL_CODE & S.CODE
    GROUP BY 
        ID,EMAIL
    HAVING 
        SCORE > 0
)
SELECT 
    CASE 
        WHEN SCORE = 3 THEN 'A'
        WHEN SCORE = 2 THEN 'B'
        WHEN SCORE = 1 THEN 'C'
    END AS GRADE,
    ID,
    EMAIL
FROM 
    J
ORDER BY 
    1,2

• DEVELOPERS 테이블의 SKILL_CODE와 SKILLCODES 테이블의 CODE 값을 비트 논리 AND 연산(&)을 통해 조건에 맞는 조인을 수행
  FROM DEVELOPERS AS D JOIN SKILLCODES AS S ON D.SKILL_CODE & S.CODE
• ID와 EMAIL을 통해 그룹화 합니다.
  GROUP BY ID, EMAIL
• SCORE는 0보다 큰 것들만 조회합니다.
  HAVING SCORE > 0
• 개발자의 점수를 결정하기 위해 CASE 문을 사용합니다.
• WITH 절을 이용합니다.
• CASE 절을 통해 A, B, C를 결정합니다.
  CASE 
          WHEN SCORE = 3 THEN 'A'
          WHEN SCORE = 2 THEN 'B'
          WHEN SCORE = 1 THEN 'C'
      END AS GRADE,
• GRADE와 ID를 통해 오름차순 정렬합니다.

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

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

• 사원 정보와 연도를 기준으로 GROUP화하여 SCORE가 가장 높은 사원을 조회하는 서브 테이블을 G로 설정합니다.
  (
      SELECT
          EMP_NO,
          SUM(SCORE) AS SCORE
      FROM HR_GRADE
      GROUP BY EMP_NO, YEAR
      ORDER BY SCORE DESC
      LIMIT 1
  ) G
• HR_EMPLOYEES 와 회원 넘버를 통해 JOIN합니다.
  FROM (...) G JOIN HR_EMPLOYEES E ON G.EMP_NO = E.EMP_NO

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

SELECT
    G.EMP_NO,
    E.EMP_NAME,
    CASE
        WHEN G.SCORE / 2 >= 96 THEN 'S'
        WHEN G.SCORE / 2 >= 90 THEN 'A'
        WHEN G.SCORE / 2 >= 80 THEN 'B' 
        ELSE 'C'
    END AS GRADE,
    E.SAL * CASE
        WHEN G.SCORE / 2 >= 96 THEN 0.2
        WHEN G.SCORE / 2 >= 90 THEN 0.15
        WHEN G.SCORE / 2 >= 80 THEN 0.1
        ELSE 0
    END AS BONUS
FROM (
    SELECT
        EMP_NO,
        SUM(SCORE) AS SCORE
    FROM HR_GRADE
    GROUP BY EMP_NO, YEAR
    ORDER BY SCORE DESC
) G
JOIN HR_EMPLOYEES E ON G.EMP_NO = E.EMP_NO
ORDER BY G.EMP_NO;

• 사원 정보와 연도를 기준으로 GROUP화하여 합산 SCORE 사원을 조회하는 서브 테이블을 G로 설정합니다.
  (
      SELECT
          EMP_NO,
          SUM(SCORE) AS SCORE
      FROM HR_GRADE
      GROUP BY EMP_NO, YEAR
      ORDER BY SCORE DESC
  ) G
• HR_EMPLOYEES 와 회원 넘버를 통해 JOIN합니다.
  FROM (...) G JOIN HR_EMPLOYEES E ON G.EMP_NO = E.EMP_NO
• CASE 절을 통해 성과금과 평가등급을 설정합니다.
  CASE
          WHEN G.SCORE / 2 >= 96 THEN 'S'
          WHEN G.SCORE / 2 >= 90 THEN 'A'
          WHEN G.SCORE / 2 >= 80 THEN 'B' 
          ELSE 'C'
      END AS GRADE,
      E.SAL * CASE
          WHEN G.SCORE / 2 >= 96 THEN 0.2
          WHEN G.SCORE / 2 >= 90 THEN 0.15
          WHEN G.SCORE / 2 >= 80 THEN 0.1
          ELSE 0
      END AS BONUS

부서별 평균 연봉 조회하기

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

• 부서 아이디를 통해 그룹화하여 평균 연봉을 서브테이블 E로 구합니다.
  (
      SELECT
          DEPT_ID,
          ROUND(AVG(SAL)) AS AVG_SAL
      FROM HR_EMPLOYEES
      GROUP BY DEPT_ID
  ) E
• HR_DEPARTMENT 테이블과 E 테이블을 부서 아이디를 통해 JOIN합니다.
  FROM HR_DEPARTMENT D JOIN (...) E ON D.DEPT_ID = E.DEPT_ID
• 평균 연봉을 통해 내림차순 정렬합니다.