241209 TIL 데이터 분석 (SQL 프로그래머스 오답노트 - 조건에 맞는 사용자와 총 거래금액 조회하기, 가격대 별 상품 개수 구하기, 식품분류별 가장 비싼 식품의 정보 조회하기)

1. 조건에 맞는 사용자와 총 거래금액 조회하기

 

 

 

풀이 1 

 

1. 출력할 값: USER_ID, NICKNAME, SUM(PRICE) AS SALES_TOTAL 

 

2. 서로 다른 두 테이블 USED_GOODS_BOARD와 USED_GOODS_USER을 JOIN 함수로 연결해준다.

WRITER_ID = USER_ID 컬럼을 기준으로 연결할 수 있다. 

 

3. WHERE 절에서 완료된 중고 거래만을 찾고자 하므로 A.STATUS가 'DONE' 값을 가지고 있는 데이터만을 필터링

WHERE B.STATUS = 'DONE' 

 

4. GROUP BY절로 USER_ID를 묶어줌. 

 

5. HAVING 절로 그룹화된 결과에 대한 조건을 지정. 총 매출이 70만원 이상인 사용자만을 필터링

HAVING은 그룹화된 결과에 조건을 걸어주고자 할 때 사용함.  -> 헷갈리지 말자!! 

 

6. ORDER BY절로 총 매출액을 오름차순으로 정렬. 

SELECT U.USER_ID,
       U.NICKNAME,
       SUM(B.PRICE) AS SALES_TOTAL
FROM USED_GOODS_BOARD AS B
     JOIN USED_GOODS_USER AS U ON B.WRITER_ID = U.USER_ID 
WHERE B.STATUS = 'DONE'
GROUP BY U.USER_ID
HAVING SALES_TOTAL >= 700000
ORDER BY SALES_TOTAL

 

풀이 2 

구글링해보니 CASE WHEN을 활용한 풀이법도 나오는데, 쿼리문이 지저분해지고 길기 때문에 지양하는 게 좋을 것 같다. 

SELECT  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
             WHEN (80000 <= PRICE) and (PRICE < 90000) then 80000
             END AS PRICE_GROUP, count(*)
  FROM  PRODUCT 
 GROUP 
    BY  PRICE_GROUP
 ORDER
    BY  PRICE_GROUP ASC

 

---

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

새로운 함수가 등장해서 풀기 어려웠던 문제다. 다시 풀어봐야 할 것 같다. 

 

 

 

풀이 1 

 

1. 출력할 값: PRICE_GROUP, PRODUCTS

2. 만원 단위로 상품을 출력하기 위해서는 숫자의 천의 자릿수 이하를 버림으로 하면되기 때문에 TRUNCATE함수를 이용한다. 4이하의 정수를 버린다. 

PRODUCT 테이블에서 가져오기 때문에 FROM PRODUCT

3. count() 함수로 가격대 별 상품 개수를 센다. 

4. GROUP BY절을 활용해서 PRICE_GROUP으로 그룹화 

5. ORDER BY절로 PRICE_GROUP을 기준으로 오름차순 정렬

 

❗truncate함수: 특정 자리수 이하를 버리고자 할 때 (숫자, 버림할 자릿수) 버림할 자리수를 반드시 표시해야 한다. 

SELECT TRUNCATE(PRICE,-4) AS PRICE_GROUP, COUNT(*) AS PRODUCTS
        FROM PRODUCT
        GROUP BY PRICE_GROUP
        ORDER BY PRICE_GROUP

 

 

풀이 2 

FLOOR 함수를 이용하는 법이 있다. FLOOR는 나눗셈 후 몫의 값이다. 

SELECT FLOOR(나눠지는 수 / 나누는 수) FROM DUAL 

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

---

3. 식품분류별 가장 비싼 식품의 정보 조회하기

문제

 

풀이

1. 출력할 컬럼: CATEGORY, PRICE, PRODUCT_NAME 

- PRICE는 가장 비싼 금액이 나와야 함 -> MAX_PRICE로 별명을 붙여줌 

 

2. PRICE에 조건을 걸어주기 위해 SUBQUERY를 건다. WHERE문에서 MAX_PRICE와 일치하는 값을 찾아주고, SUBQUERY의 IN으로 '과자', '국', '김치', '식용유' 에 해당하는 값을  찾아줌. 

SUBQUERY를 걸지 않으면 빈 값만 출력됨. 

 

=이 아니라 IN 연산자를 사용하면 서브 쿼리 결과에서 여러 행과 여러 칼럼을 동시에 비교할 수 있기 때문이다.

= 연산자는 단일 값과의 비교에 사용되므로 여러 컬럼을 동시에 비교하기에는 적합하지 않다. 

 

3. 마지막으로 ORDER BY로 가격 순 내림차순

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