본문 바로가기
SQL/프로그래머스

[프로그래머스] SQL 고득점 Kit 문제 - GROUP BY 2

jiwon_d1 2022. 12. 15. 17:03
반응형

프로그래머스에 추가된 문제에 대한 풀이를 업로드합니다.

문제 순서는 프로그래머스에 있는 순서와 다르니 유의해서 봐주세요! (본 코드는 ORACLE 환경에서 작성되었습니다.)

 

 

 

[문제 1]  상반기 동안 각 아이스크림 성분 타입과 성분 타입에 대한 아이스크림의 총 주문량을 총주문량이 작은 순서대로 조회하는 SQL문 작성. 이때 총 주문량을 나타내는 컬럼명은 TOTAL_ORDER로 지정.

 

 

테이블 설명

  • FIRST_HALF: 아이스크림 가게의 상반기 주문 정보를 담은 테이블
    • SHIPMENT_ID: 아이스크림 공장에서 아이스크림 가게까지의 출하 번호 / FLAVOR (PK): 아이스크림 맛 /  TOTAL_ORDER: 상반기 아이스크림 총 주문량
  • ICECREAM_INFO: 아이스크림 성분에 대한 정보를 담은 테이블
    • FLAVOR (PK): 아이스크림 맛 / INGREDIENT_TYPE: 아이스크림 성분 타입

 

SELECT B.INGREDIENT_TYPE, SUM(A.TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF A 
INNER JOIN ICECREAM_INFO B ON A.FLAVOR = B.FLAVOR
GROUP BY B.INGREDIENT_TYPE
ORDER BY TOTAL_ORDER ASC;

 

 

 

 

 

 

[문제 2] PRODUCT 테이블에서 만원 단위의 가격대 별로 상품 개수를 출력하는 SQL문 작성. 이때 컬럼명은 PRICE_GROUP, PRODUCTS로 지정하고 가격대 정보는 각 구간의 최소금액으로 표시. 결과는 가격대를 기준으로 오름차순 정렬

 

 

테이블 설명

  • PRODUCT: 의류 쇼핑몰에서 판매중인 상품들의 정보를 담은 테이블
    • PRODUCT_ID: 상품 ID  / PRODUCT_CODE: 상품 코드 / PRICE: 판매가

 

 

1) CASE WHEN을 사용한 경우

SELECT P.PRICE_GROUP, COUNT(PRODUCT_ID) AS PRODUCTS
FROM (SELECT PRODUCT_ID, PRICE, 
CASE 
WHEN (0 <= PRICE AND PRICE < 10000) THEN 0
WHEN (10000 <= PRICE AND PRICE < 20000) THEN 10000
WHEN (20000 <= PRICE AND PRICE < 30000) THEN 20000
WHEN (30000 <= PRICE AND PRICE < 40000) THEN 30000
WHEN (40000 <= PRICE AND PRICE < 50000) THEN 40000
WHEN (50000 <= PRICE AND PRICE < 60000) THEN 50000
WHEN (60000 <= PRICE AND PRICE < 70000) THEN 60000
WHEN (70000 <= PRICE AND PRICE < 80000) THEN 70000
ELSE 80000 
END PRICE_GROUP
FROM PRODUCT) P
GROUP BY P.PRICE_GROUP
ORDER BY P.PRICE_GROUP;

 

 

가장 먼저 생각난 방법은 CASE WHEN을 이용한 방법입니다.

 

다만 해당 테이블은 데이터의 양이 작아서 이렇게 일일이 구간을 나눠서 할 수 있는 정도이지만 

회사에서 사용하는 DB는 데이터의 양이 방대하기 때문에 이 방법은 비효율적이라 생각했습니다.

그래서 일반화 하는 방법을 생각해 봤습니다.

 

 

 

2) 일반화

SELECT SUBSTR(PRICE, 1, 1) * 10000 AS PRICE_GROUP, 
		COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY SUBSTR(PRICE, 1, 1)
ORDER BY PRICE_GROUP;

 

확실히 코드가 간단해지네요!

 

 

 

 

 

 

[문제 3] APPOINTMENT 테이블에서 2022년 5월에 예약한 환자 수를 진료과 코드별로 조회하는 SQL문 작성. 이때 컬럼명은 '진료과코드', '5월예약건수'로 지정. 결과는 진료과별 예약한 환자수를 기준으로 오름차순 정렬하고, 예약한 환자 수가 같다면 진료과 코드를 기준으로 오름차순 정렬

 

 

테이블 설명

  • APPOINTMENT: 종합병원의 진료 예약정보를 담은 테이블
    • APNT_YMD: 진료예약일시 / APNT_NO: 진료예약번호 / PT_NO: 환자번호 / MCDP_CD: 진료과 코드 / MDDR_ID: 의사ID / APNT_CNCL_YN: 예약취소여부 / APNT_CNCL_YMD: 예약취소날짜

 

 

SELECT MCDP_CD AS "진료과코드", COUNT(*) AS "5월예약건수"
FROM APPOINTMENT
WHERE TO_CHAR(APNT_YMD, 'YYYY-MM') = '2022-05'
GROUP BY MCDP_CD
ORDER BY "5월예약건수", "진료과코드";

 

 

 

 

 

SELECT MCDP_CD AS "진료과코드", COUNT(*) AS "5월예약건수"
FROM APPOINTMENT
WHERE TO_CHAR(APNT_YMD, 'YYYY-MM') = '2022-05' AND
	 (APNT_CNCL_YN = 'N' OR APNT_CNCL_YN IS NULL)  
GROUP BY MCDP_CD
ORDER BY "5월예약건수", "진료과코드";

 

여담이지만 문제에 APNT_CNCL_YN 컬럼이 예약 취소 여부를 담은 컬럼이고 NULL값도 있어서 원래 위와 같은 쿼리를 작성했었습니다. 

 

그런데 계속 틀려서 다른 분들이 푸신 내용을 봤는데

예약 취소는 무시하고 그냥 예약을 했다는 거에 초점을 두고 푸는 문제였다고합니다... ^^

 

예약 취소 컬럼이 있어서 당연히 반영해야 하는지 알았는데 저와 같은 분들이 조금 있네요.. 하하

 

 

 

 

 

 

[문제 4] FOOD_PRODUCT 테이블에서 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문 작성. 이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력하고 결과는 식품 가격을 기준으로 내림차순 정렬.

 

 

테이블 설명

  • FOOD_PRODUCT: 식품 정보를 담은 테이블
    • PRODUCT_ID: 식품 ID / PRODUCT_NAME: 식품 이름 / PRODUCT_CD: 식품 코드 / CATEGORY : 식품 분류 / PRICE: 식품 가격

 

1) WHERE IN을 이용한 풀이

SELECT CATEGORY, PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN (SELECT CATEGORY, MAX(PRICE)
                                            FROM FOOD_PRODUCT
                                            GROUP BY CATEGORY
                                            HAVING CATEGORY 
                                            IN ('과자', '국', '김치','식용유'))
ORDER BY PRICE DESC;

 

 

 

 

2) JOIN을 이용한 풀이

SELECT A.CATEGORY, A.PRICE, A.PRODUCT_NAME
FROM FOOD_PRODUCT A
INNER JOIN (SELECT CATEGORY, MAX(PRICE) AS MAX
            FROM FOOD_PRODUCT
            GROUP BY CATEGORY) B
ON A.CATEGORY = B.CATEGORY
WHERE A.PRICE = B.MAX AND A.CATEGORY IN ('과자', '국', '김치', '식용유')
ORDER BY A.PRICE DESC;

 

사실 JOIN을 이용한 방법은 별로 좋은 방법이 아니라고 생각합니다. 

 

그렇게 판단한 이유를 설명하기 위해 FOOD_PRODUCT 테이블을 INNER JOIN 한 테이블을 살펴보겠습니다. 

 

위의 테이블에서 초록색 박스에 해당하는 부분이 A 테이블, 보라색 박스에 해당하는 부분이 B 테이블에 해당합니다.

 

B 테이블은 카테고리별 MAX PRICE를 찾게 설계되어 있습니다.

결과를 조회하면 (면, 4950), (식용유, 8950), ··· 이런식으로요. 

 

이제 두 테이블을 카테고리 기준으로 조인하면 (각 테이블은 기본키가 따로 있지 않음으로) 

B 테이블에서 하나의 행으로 조회되던 내용이 각 카테고리에 해당하는 제품 수만큼 중복된 내용으로 채워진 것을 확인할 수 있습니다. 

CATEGORY = '면'인 경우를 보면 MAX PRICE인 4950이 4개가 채워진 것처럼요.

 

물론 프로그래머스에서 사용하는 테이블의 경우 데이터의 양이 작기 때문에 JOIN을 이용한 방법도 빠르게 돌아갑니다.

하지만 기업에서 사용하는 DB의 경우 데이터의 양이 어마어마하기 때문에 굳이 굳이 중복되게 내용을 채운 후 WHERE문을 이용하여

다시 거르는 과정이 비효율적이라 생각했습니다. 

 

그럼에도 해당 쿼리를 적은 이유는  본 블로그는 제가 공부한 과정을 담는 곳이고

하나의 문제를 이 방법으로 풀었다가 저 방법으로 풀었다가 하는 과정이 의미가 있다고 생각했습니다.

모두 열심히 공부해서 성공합시다!!!

 

 

 

3) RANK 함수를 이용한 풀이

SELECT CATEGORY, PRICE, PRODUCT_NAME
FROM (SELECT CATEGORY, PRICE, PRODUCT_NAME, 
        RANK() OVER(PARTITION BY CATEGORY ORDER BY PRICE DESC) AS R_PRICE
      FROM FOOD_PRODUCT
      WHERE CATEGORY IN ('과자', '국', '김치', '식용유')) A
WHERE R_PRICE = 1
ORDER BY PRICE DESC;

 

 

 

 

 

[문제 5] REST_INFO 테이블에서 음식 종류별로 즐겨찾기수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기수를 조회하는 SQL문 작성. 이때 결과는 음식 종류를 기준으로 내림차순.

 

 

테이블 설명

  • REST_INFO: 식당 정보를 담은 테이블
    • REST_ID: 식당ID / REST_NAME: 식당이름 / FOOD_TYPE: 음식종류 / VIEWS: 조회수 / FAVORITES: 즐겨찾기수 / PARKING_LOT: 주차창 유무 / ADDRESS: 주소 / TEL: 전화번호

 

 

1) WHERE IN을 이용한 풀이

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN (SELECT FOOD_TYPE, MAX(FAVORITES)
                                 FROM REST_INFO
                                GROUP BY FOOD_TYPE)
ORDER BY FOOD_TYPE DESC;

 

 

 

 

2) INNER JOIN을 이용한 풀이

SELECT A.FOOD_TYPE, A.REST_ID, A.REST_NAME, A.FAVORITES
FROM REST_INFO A INNER JOIN 
(SELECT FOOD_TYPE, MAX(FAVORITES) AS MAX
FROM REST_INFO
GROUP BY FOOD_TYPE) B
ON A.FOOD_TYPE = B.FOOD_TYPE
WHERE A.FAVORITES = B.MAX
ORDER BY A.FOOD_TYPE DESC;

 

 

 

3) RANK 함수를 이용한 풀이

SELECT A.FOOD_TYPE, A.REST_ID, A.REST_NAME, A.FAVORITES
FROM (SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES,
      RANK() OVER(PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) AS RANK
     FROM REST_INFO) A
WHERE A.RANK = 1
ORDER BY FOOD_TYPE DESC;

 

 

 

 

 

[문제 6] USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문 작성. 결과는 년, 월, 성별을 기준으로 오름차순 정렬하고 성별 정보가 없는 경우 결과에서 제외.

 

 

테이블 설명

  • USER_INFO: 의류 쇼핑몰에 가입한 회원 정보를 담은 테이블
    • USER_ID: 회원 ID / GENDER: 성별 / AGE: 나이 / JOINED: 가입일
    • GENDER 컬럼은 비어있거나 0 OR 1의 값을 가짐 (0 = 남자, 1 = 여자) 
  • ONLINE_SALE: 온라인 상품 판매 정보를 담은 테이블
    • ONLINE_SALE_ID: 온라인 상품 판매 ID / USER_ID: 회원 ID / PRODUCT_ID: 상품 ID / SALES_AMOUNT: 판매량 / SALES_DATE: 판매일

 

SELECT TO_CHAR(B.SALES_DATE, 'YYYY') AS YEAR, 
        TO_NUMBER(TO_CHAR(B.SALES_DATE, 'MM')) AS MONTH,
        A.GENDER, COUNT(DISTINCT A.USER_ID) AS USERS
FROM USER_INFO A 
INNER JOIN ONLINE_SALE B ON A.USER_ID = B.USER_ID
WHERE A.GENDER IS NOT NULL
GROUP BY TO_CHAR(B.SALES_DATE, 'YYYY'), TO_NUMBER(TO_CHAR(B.SALES_DATE, 'MM')),
            A.GENDER
ORDER BY YEAR, MONTH, A.GENDER;

 

 

여기서 주의해야 할 부분은 MONTH 변수를 구할 때입니다.

처음에는 YEAR 변수와 동일하게 TO_CHAR(B.SALES_DATE, 'MM') 이런 식으로 작성했었는데 자꾸 답이 틀리다고 나와서 엄청 당황했던 기억이 있습니다.

 

다른 분들이 하신 것도 찾아봤는데 결과가 저게 맞는데 왜지??? 멘탈이 살짝 터진...

 

알고 보니 문제에는 설명되어 있지 않지만 MONTH 변수를 출력할 때 3월이면 03이 아니라 3으로 출력해야 하는 거였더라고요.

다들 주의하세요... ^^ 

 

 

 

 

 

[문제 7] 2022년 1월의 카테고리 별 도서 판매량을 합산하고, 카테고리, 총 판매량 리스트를 출력하는 SQL문 작성. 결과는 카테고리를 기준으로 오름차순 정렬

 

 

테이블 설명

  • BOOK: 서점에서 판매 중인 도서들의 정보를 담은 테이블
    • BOOK_ID: 도서 ID / CATEGORY: 카테고리 / AUTHOR_ID: 저자 ID / PRICE: 판매가(원) / PUBLISHED_DATE: 출판일
  • BOOK_SALES: 판매 정보를 담은 테이블
    • BOOK_ID: 도서 ID / SALES_DATE: 판매일 / SALES: 판매량

 

SELECT A.CATEGORY, SUM(B.SALES) AS TOTAL_SALES 
FROM BOOK A 
INNER JOIN BOOK_SALES B ON A.BOOK_ID = B.BOOK_ID
WHERE TO_CHAR(B.SALES_DATE, 'YYYY-MM') = '2022-01'
GROUP BY A.CATEGORY 
ORDER BY A.CATEGORY;

 

 

문제 7,8번 같은 경우는 정말 오늘 막 올라온 따끈따끈한 문제입니다.

 

사족이지만 사실 GROUP BY 파트에서 6번이 마지막이었는데 다 풀고 나니까 7,8번이 실시간으로 업데이트돼있던거 있죠 ㅋㅋㅋ

빨리 포스팅해야지 했는데 조금 당황

그래도 후딱 풀어서 같이 올립니다!

 

 

 

 

 

 

[문제 8] 2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가)을 구하여, 저자 ID, 저자명, 카테고리, 매출액 리스트를 출력하는 SQL문 작성. 결과는 저자 ID를 기준으로 오름차순, 저자 ID가 같다면 카테고리를 기준으로 내림차순 정렬

 

 

테이블 설명

  • BOOK: 서점에서 판매중인 도서들의 정보를 담은 테이블
    • BOOK_ID: 도서 ID / CATEGORY: 카테고리 / AUTHOR_ID: 저자 ID / PRICE: 판매가(원) / PUBLISHED_DATE: 출판일
  • BOOK_SALES: 판매 정보를 담은 테이블
    • BOOK_ID: 도서 ID / SALES_DATE: 판매일 / SALES: 판매량
  • AUTHOR: 도서의 저자 정보를 담은 테이블
    • AUTHOR_ID: 저자 ID / AUTHOR_NAME: 저자명

 

 

SELECT B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, SUM(B.PRICE * BS.SALES) AS TOTAL_SALES
FROM BOOK B 
INNER JOIN BOOK_SALES BS ON B.BOOK_ID = BS.BOOK_ID
INNER JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE TO_CHAR(BS.SALES_DATE, 'YYYY-MM') = '2022-01'
GROUP BY B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY
ORDER BY B.AUTHOR_ID, B.CATEGORY DESC;

 

 

 

이렇게 GROUP BY 파트를 완료했습니다!

 

 

 

 

 

 

 

 

반응형

'SQL > 프로그래머스' 카테고리의 다른 글

[프로그래머스] SQL 고득점 Kit 문제 - IS NULL 2  (0) 2022.12.23
[프로그래머스] SQL 고득점 Kit 문제 - SUM, MAX, MIN 2  (0) 2022.12.14
[프로그래머스] SQL 고득점 Kit 문제 - SELECT 2  (0) 2022.11.16
[프로그래머스] SQL 고득점 Kit 문제 - IS NULL  (0) 2022.09.15
[프로그래머스] SQL 고득점 Kit 문제 - GROUP BY  (0) 2022.09.15

'SQL/프로그래머스' Related Articles

티스토리툴바