devFancy BE Developer

[Programmers] SQL > JOIN 풀이 모음

2023-12-27
devFancy
SQL

상품 별 오프라인 매출 구하기(Lv.2)

상품 별 오프라인 매출 구하기

  • 문제: PRODUCT 테이블과 OFFLINE_SALE 테이블에서

    상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문을 작성해주세요.

    결과는 매출액을 기준으로 내림차순 정렬해주시고

    매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요.

Answer Code1(23.12.27)

SELECT A.PRODUCT_CODE,
       (A.PRICE * B.TOT_SALES_AMOUNT) AS SALES
FROM PRODUCT A
       LEFT JOIN (
  SELECT PRODUCT_ID
       , SUM(SALES_AMOUNT) TOT_SALES_AMOUNT
  FROM OFFLINE_SALE B
  GROUP BY
    PRODUCT_ID
) B
                 ON  A.PRODUCT_ID = B.PRODUCT_ID
ORDER BY SALES DESC, PRODUCT_CODE ASC

문제 풀이

문제를 풀기 위해서 해야할 작업들

  1. OFFLINE_SALE 테이블에서 PRODUCT_ID별 판매 수량 집계하기(GROUP BY)
  2. PRODUCT 테이블과 조인하기(JOIN)
  3. 이때, 매출액은 상품코드 별 매출액(판매가 * 판매량) 합계를 계산(SUM)
  4. 매출액을 기준으로 내림차순 정렬, 만약 매출액이 같다면 상품코드를 기준으로 오름차순 정렬(ORDER BY)

  • 첫 번째 단계는 OFFLINE_SALE 테이블에서 PRODUCT_ID 별 판매 수량을 집계한다.

  • 이때 GROUP BY를 집계해서 각 상품별 수량을 산출한다.

SELECT PRODUCT_ID
        , SUM(SALES_AMOUNT) TOT_SALES_AMOUNT
FROM OFFLINE_SALE B
GROUP BY 
    PRODUCT_ID

  • 두 번째 단계는 앞에서 진행한 결과를 서브쿼리로 넣고, PRODUCT 테이블을 기준으로 LEFT JOIN을 해준다.

  • 이 결과를 통해 각 상품별 총 판매개수와 상품별 금액을 알 수 있다.

SELECT *
FROM PRODUCT A
LEFT JOIN (
            SELECT PRODUCT_ID
                 , SUM(SALES_AMOUNT) TOT_SALES_AMOUNT
            FROM OFFLINE_SALE B
            GROUP BY
              PRODUCT_ID
) B
  ON  A.PRODUCT_ID = B.PRODUCT_ID
  • 세 번째 단계는 상품코드와 상품코드별 매출액(판매가 * 판매량) 합계를 산출한다.
SELECT A.PRODUCT_CODE,
       (A.PRICE * B.TOT_SALES_AMOUNT) AS SALES
FROM PRODUCT A
LEFT JOIN (
            SELECT PRODUCT_ID
                 , SUM(SALES_AMOUNT) TOT_SALES_AMOUNT
            FROM OFFLINE_SALE B
            GROUP BY
              PRODUCT_ID
) B
  ON  A.PRODUCT_ID = B.PRODUCT_ID
  • 마지막 단계에서는 매출액을 기준으로 내림차순 정렬, 만약 매출액이 같다면 상품코드를 기준으로 오름차순 정렬(ORDER BY)을 해준다.
SELECT A.PRODUCT_CODE,
       (A.PRICE * B.TOT_SALES_AMOUNT) AS SALES
FROM PRODUCT A
LEFT JOIN (
            SELECT PRODUCT_ID
                 , SUM(SALES_AMOUNT) TOT_SALES_AMOUNT
            FROM OFFLINE_SALE B
            GROUP BY
              PRODUCT_ID
) B
  ON  A.PRODUCT_ID = B.PRODUCT_ID
ORDER BY SALES DESC, PRODUCT_CODE ASC

조건에 맞는 도서와 저자 리스트 출력하기(Lv.2)

조건에 맞는 도서와 저자 리스트 출력하기

  • 문제: '경제' 카테고리에 속하는 도서들의

    도서 ID(BOOK_ID), 저자명(AUTHOR_NAME), 출판일(PUBLISHED_DATE) 리스트를 출력하는 SQL문을 작성해주세요.

    결과는 출판일을 기준으로 오름차순 정렬해주세요.

Answer Code(23.12.27)

SELECT A.BOOK_ID, 
       B.AUTHOR_NAME, 
       DATE_FORMAT(A.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK A
LEFT JOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE A.CATEGORY = '경제'
ORDER BY A.PUBLISHED_DATE ASC

문제 풀이

문제를 풀기 위해서 해야할 작업들

  1. ‘경제’ 카테고리에 속하는 도서들을 -> WHERE
  2. AUTHOR 테이블과 조인한 뒤 , BOOK_ID, AUTHOR_NAME, PUBLISHED_DATE 조회 -> SELECT
  3. PUBLISHED_DATE 기준으로 오름차순 -> ORDER BY
  4. PUBLISHED_DATE를 예시와 같이 DATE_FORMAT을 사용하여 조회한다.
  • 첫 번째 단계에서는 BOOK 테이블에서 ‘경제’ 카테고리에 속하는 도서들을 조회한다.
SELECT *
FROM BOOK
WHERE CATEGORY = '경제'

  • 두 번째 단계에서는 AUTHOR_NAME을 조회하기 위해 AUTHOR_ID를 기준으로 AUTHOR 테이블과 조인한다.

  • 그런 다음, BOOK_ID, AUTHOR_NAME, PUBLISHED_DATE 조회한다.

SELECT A.BOOK_ID, B.AUTHOR_NAME, A.PUBLISHED_DATE
FROM BOOK A
LEFT JOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE A.CATEGORY = '경제'
  • 세 번째 단계에서는 PUBLISHED_DATE 기준으로 오름차순으로 정렬해준다.
SELECT A.BOOK_ID, B.AUTHOR_NAME, A.PUBLISHED_DATE
FROM BOOK A
LEFT JOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE A.CATEGORY = '경제'
ORDER BY A.PUBLISHED_DATE ASC

  • 마지막 단계에서는 PUBLISHED_DATE를 예시와 같이 DATE_FORMAT을 사용하여 조회한다.

  • DATE_FORMAT을() 함수를 이용해 '%Y-%m-%d' 형태로 변경해주면 된다.

SELECT A.BOOK_ID, 
       B.AUTHOR_NAME, 
       DATE_FORMAT(A.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK A
LEFT JOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE A.CATEGORY = '경제'
ORDER BY A.PUBLISHED_DATE ASC

Answer Code2(23.12.27)

SELECT  A.BOOK_ID
        , B.AUTHOR_NAME
        , DATE_FORMAT(A.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM  (
      SELECT  *
        FROM  BOOK AS A
       WHERE  A.CATEGORY = '경제'
      ) A
LEFT 
JOIN  AUTHOR AS B
  ON  A.AUTHOR_ID = B.AUTHOR_ID
ORDER
  BY  PUBLISHED_DATE ASC

문제 풀이

  • 두 번재 풀이의 경우, BOOK 테이블과 AUTHOR 테이블을 결합하기 전에 BOOK 테이블에서 CATEGORY 컬럼에서 ‘경제’ 값만 먼저 추출해주는 것이다.

  • 이후 AUTHOR 테이블과 조인을 통해 결합을 했다.

Review

  • 1번째 풀이 방식이 2번째보다 더 효율적이라고 생각한다.

  • 데이터의 양이 많을 경우 1번째 처럼 LEFT JOIN을 먼저 실행하고 WHERE 절을 적용하면, JOIN하는 과정에서 데이터가 더 많아져서 비효율적이라고 본다.

  • 반면에, 2번째 풀이 방식처럼 서브 쿼리가 들어가더라도 필요한 컬럼과 필요한 조건에 해당하는 데이터만 추출하여, 데이터의 크기(size)가 축소한 이후에 JOIN이 이뤄진다면

  • 1번째 보다 더 효율적이라고 생각한다.

오랜 기간 보호한 동물(1)

오랜 기간 보호한 동물(1)

  • 문제: 아직 입양을 못 간 동물 중,

    가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요.

    이때 결과는 보호 시작일 순으로 조회해야 합니다.

Answer Code(24.01.03)

SELECT A.NAME, A.DATETIME
FROM ANIMAL_INS A
LEFT JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.DATETIME IS NULL
ORDER BY A.DATETIME
LIMIT 3

문제 풀이

ANIMAL_INS - 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부

ANIMAL_OUTS - 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부

문제를 풀기 위해서 해야할 작업들

  • ANIMAL_ID를 기준으로 ANIMAL_INS 테이블과 ANIMAL_OUTS 테이블을 JOIN

  • 아직 입양을 못간 동물 조회(WHERE) -> WHERE B.DATETIME IS NULL

  • 3마리 이름과 보호 시작일 조회(SELECT) -> A.NAME, A.DATETIME (A: ANIMAL_INS) / LIMIT 3

  • 결과는 보호 시작일 순으로 조회, 가장 오래 보호소에 있었던 동물 -> ORDER BY A.DATETIME (기본이 내림차순)

없어진 기록 찾기(1)

없어진 기록 찾기

  • 문제: 천재지변으로 인해 일부 데이터가 유실되었습니다.

    입양을 간 기록은 있는데,

    보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.

Answer Code(24.01.03)

SELECT B.ANIMAL_ID, B.NAME
FROM ANIMAL_INS A
       RIGHT JOIN ANIMAL_OUTS AS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.ANIMAL_ID IS NULL
ORDER BY B.ANIMAL_ID

문제 풀이

ANIMAL_INS - 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부

ANIMAL_OUTS - 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부

문제를 풀기 위해서 해야할 작업들

  • ANIMAL_OUTS 테이블 기준으로 ANIMAL_INS 테이블과 ANIMAL_ID으로 RIGHT JOIN

  • 입양일은 존재하나, 보호 시작일이 존재하지 않은 동물 -> ANIMAL_INS 테이블에서 ANIMAL_ID가 없는 경우

  • ID와 이름을 조회(SELECT)

  • ID 순으로 조회(ORDER)

  • 위 그림처럼 INNER JOIN은 두 개의 테이블에 모두 속하는 교집합에 대한 결과를 찾아내는 것이고,

    OUTER JOIN은 교집합에 대해 포함된 결과와 LEFT, RIGHT 중 하나의 테이블은 무조건 결과에 포함시키는 쿼리이다.

  • 예를 들어, TABLE_A LEFT JOIN TABLE_B -> TABLE_A의 결과는 모두 노출하고 A와 교집합이 있는 B만 추가로 추출한다는 뜻이다.

  • 이 문제에서는 ANIMAL_OUTS 테이블을 기준으로 ID와 이름을 조회할 때 RIGHT JOIN을 사용하면, 오른쪽에는 ANIMAL_OUTS 테이블을 작성하고, 왼쪽인 FROM 절 뒤에는 ANIMAL_OUTS 테이블이 아닌 ANIMAL_INS 테이블을 작성한다.

있었는데요 없었습니다.

있었는데요 없었습니다

  • 문제: 관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다.

    보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요.

    이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.

Answer Code(24.01.04)

SELECT ANIMAL_ID, NAME
FROM ANIMAL_OUTS AS A
LEFT JOIN ANIMAL_INS AS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.DATETIME < A.DATETIME
ORDER BY B.DATETIME

문제 풀이

ANIMAL_INS - 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부

ANIMAL_OUTS - 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부

문제를 풀기 위해서 해야할 작업들

  • 입양일 필드를 가지고 있는 ANIMAL_OUTS 테이블을 기준으로 ANIMAL_INS 테이블과 JOIN

  • 보호 시작일보다 입양일이 더 빠른 동물 -> ANIMAL_INS.DATETIME(보호 시작일) > ANIMAL_OUTS.DATETIME(입양일)

    • 더 빠른 요일은 더 작다는 것을 의미하기 때문에, 보호 시작일이 입양일보다 커야 한다.

  • 동물의 아이디와 이름을 조회 -> SELECT

  • 보호 시작일을 기준으로 내림차순 정렬하여 결과를 출력한다.

주문량이 많은 아이스크림들 조회하기

주문량이 많은 아이스크림들 조회하기

  • 문제: 7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로

    상위 3개의 맛을 조회하는 SQL 문을 작성해주세요.

Answer Code(24.01.04)

SELECT A.FLAVOR
FROM FIRST_HALF AS A
       JOIN JULY AS B 
           ON A.FLAVOR = B.FLAVOR
GROUP BY A.FLAVOR
ORDER BY SUM(A.TOTAL_ORDER) + SUM(B.TOTAL_ORDER) DESC LIMIT 3

문제 풀이

FIRST_HALF : 아이스크림 가게의 상반기 주문 정보 - SHIPMENT_ID(JULY테이블의 SHIPMENT_ID의 외래 키), FLAVOR(기본 키), TOTAL_ORDER

JULY : 7월의 아이스크림 주문 정보 - SHIPMENT_ID(기본 키), FLAVOR(FIRST_HALF 테이블의 FLAVOR의 외래 키), TOTAL_ORDER

문제를 풀기 위해서 해야할 작업들

  • FLAVOR 이 조회되는 것이므로 해당 기본 키를 갖고 있는 테이블인 FIRST_HALF 테이블을 기준으로 JULY 테이블과 JOIN

  • 7월 아이스크림 총 주문량과 상반기 아이스크림 총 주문량을 더한 값을 구한다.

  • 상위 3개 맛을 조회하기 위해 LIMIT 절을 사용하고, FLAVOR을 기준으로 내림차순 정렬하여 결과를 출력한다.

[1] FLAVOR 이 조회되는 것이므로 해당 기본 키를 갖고 있는 테이블인 FIRST_HALF 테이블을 기준으로 JULY 테이블과 JOIN

  • FLAVOR를 기준으로 JULY 테이블과 JOIN한다.
SELECT A.FLAVOR
FROM FIRST_HALF AS A 
    JOIN JULY AS B 
        ON A.FLAVOR = B.FLAVOR

[2] 7월 아이스크림 총 주문량과 상반기 아이스크림 총 주문량을 더한 값을 구한다.

  • 상반기 아이스크림과 7월 아이스크림 총 주문량을 각각 계산해서 더해준다.
ORDER BY SUM(A.TOTAL_ORDER) + SUM(B.TOTAL_ORDER)

[3] 상위 3개 맛을 조회하기 위해 LIMIT 절을 사용하고, FLAVOR을 기준으로 내림차순 정렬하여 결과를 출력한다.

  • 2번에서 풀이한 것을 바탕으로 LIMIT 절과 FLAVOR을 기준으로 내림차순 해준다.

  • 이때, FIRST_HALF 테이블의 기본키인 FLAVOR을 기준으로 그룹핍을 해준다.

SELECT A.FLAVOR
FROM FIRST_HALF AS A
       JOIN JULY AS B 
           ON A.FLAVOR = B.FLAVOR
GROUP BY A.FLAVOR
ORDER BY SUM(A.TOTAL_ORDER) + SUM(B.TOTAL_ORDER) DESC LIMIT 3

Reference

  • https://kkw-da.tistory.com/
  • https://habiis.tistory.com/118 (JOIN 종류)

Back : [Goodfriends] - 사용자 기반 기능 전체 리팩터링

Next : Git 형상 관리 + 작업 단위와 PR 코드 리뷰 + 협업을 잘하는 개발자 (feat. 제미니의 개발실무)

Comments

Index