12/08 일지 : 서브쿼리, 중복 체크와 sum(), right(left) join, 서브쿼리 where column _name not in ()

카타

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

https://school.programmers.co.kr/learn/courses/30/lessons/131116

프로그래머스

다음은 식품의 정보를 담은 FOOD_PRODUCT 테이블입니다. FOOD_PRODUCT 테이블은 다음과 같으며 PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE는 식품 ID, 식품 이름, 식품코드, 식품분류, 식품 가격을 의미합니다.

 

문제

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

 

오답 :

select category,
       price,
       product_name
from food_product
where category in ('과자', '국', '김치', 식용유) and price in (select max(price) from food_product)
group by 1

=>김치 카테고리의 최고가인 맛있는배추김치만 출력

 

select category,
       price,
       product_name
from food_product
where category in ('과자', '국', '김치' '식용유')
group by category
having max(price)
order by price desc

=>맛있는 미역국(2400), 맛있는 포카칩(1500)만 출력

 

select category,
       max(price) max_price,
       product_name
from food_product
where category in ('과자', '국', '김치', '식용유')
group by category
order by price desc

=>group by에 의해 카테고리로 묶인 뒤 출력되는 product_name이 max(price)에 해당되는 것이 아님!

=>카테고리별 최고/최저수치를 묻는 문제의 경우, 최고/최저치 다음의 것을 앞의 칼럼에 맞춰 출력하기 위해서는 서브쿼리 이용해야함(카테고리명-최고/최저치-이름 등)

 

서브쿼리로 해결하기!

1) where in

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 price desc

 

with temp as (select max(price) max_price,
              category
            from food_product
            group by category)
select t.category,
       t.max_price,
       product_name
from food_product f inner join temp t on t.max_price=f.price and f.category=t.category
where f.category in ('과자', '국', '김치', '식용유')
order by price desc

 

 

 

48. 5월 식품들의 총매출 조회하기

 

크게 어려운 문제는 아니지만, 반복해서 습관을 들일 필요가 있는 유형의 문제였다. food_order 테이블에서 product_id가 중복이 있을 수 있다는 것을 생각하지 못하고 단순하게 price와 amount를 곱하기만 했다가 오답이 나왔고, 다시 생각해봤다가 price와 amount의 값에 sum()함수를 대입해서 정답을 출력할 수 있었다.

#food_order 테이블에 product_id 중복이 있을 수 있다는 것을 간과해 price와 amount를 곱했다가 오답나옴
SELECT b.product_id,
       a.product_name,
       a.price*b.amount total_sales
from food_product a inner join food_order b on a.product_id=b.product_id
where month(b.produce_date) = 05
order by total_sales desc, product_id asc

 

select a.product_id,
       a.product_name,
       sum(a.price*b.amount) total_sales
from food_product a left join food_order b on a.product_id=b.product_id
where month(b.produce_date) = 05
group by a.product_id
order by total_sales desc, product_id asc

*주문서 등에서 값을 계산할 때 항목의 중복이 있을 수 있다는 것 생각하기! =>sum() 사용 습관들이기!

 

 

51. 없어진 기록 찾기 : left join, right(left) join, 서브쿼리 where column_name not in ()

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다. ANIMAL_OUTS 테이블의 ANIMAL_ID는 ANIMAL_INS의 ANIMAL_ID의 외래 키입니다.

NAMETYPENULLABLE

문제 : 천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.

 

 

join 혹은 서브쿼리를 활용해서 푸는 문제.

내가 기억하기 쉽게 공부하려고 왜곡되게 이해했던 건지, 아님 찾아보니 right join은 실제로 거의 쓸 일이 없어서 강사님이 그렇게 설명하셨던 건지는 모르겠지만 엑셀보다 쉬운 SQL 강의에서 강사님이 left join은 데이터값이 없는 셀도 모두 그대로 남기고(leave-left) 출력하는 함수라고 하셨던 것으로 기억해서 left join을 별 생각없이 아래와 비슷한 형식으로 몇차례 썻다가 빈 테이블만 출력돼서 당황했었다.

그런데 '질문하기'에 게시된 답들을 찾아보니 right join 함수가 등장했고, left join 함수와 뭐가 다른거지?하고 한참 생각하고 찾아보니 별 큰 차이는 없었다. 단지 from 뒤에 나란히 언급되는 두 병합 테이블 중 왼쪽에 언급되는 테이블을 기준으로 두 테이블을 병합하고 빈 셀을 남길 것이냐, 아니면 오른쪽의 것을 기준으로 할 것이냐 하는 차이였다. 즉, left join, right join 둘 다 inner join과 대비되는 함수고, 그 기준만 다른 것이었다. 그러니 right join을 사용해도, 작성한 것처럼 left를 기준으로 작성해도 select와 where 부분만 맞춰서 작성해주면 어떤 함수든 무방하다.

다른 풀이 방식 하나는 where 절에 서브쿼리를 활용해주는 것. 텍스트에서 드러나는 대로 animal_ins에는 없지만 animal_outs에는 있는 animal_id를 찾으라는 방식으로 함수를 입력하기만 하면 된다. 오히려 위의 방식보다 쉬운 방식인 셈.

SELECT a.animal_id,
       b.name
from animal_ins a left join animal_outs b on a.animal_id=b.animal_id
where a.animal_id is null and b.animal_id is not null

#다시 보니까 뭐라쓴거지..left join 사용했다고 animal_id가 좌우에 나란히 출력될 것이라 생각한건가...?

SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_OUTS O
LEFT JOIN ANIMAL_INS I
ON O.ANIMAL_ID = I.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL
ORDER BY O.ANIMAL_ID;



SELECT ANIMAL_ID, NAME
FROM ANIMAL_OUTS
WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_INS)
ORDER BY ANIMAL_ID;