SQL공부 feat. 프로그래머스 (6) (Level 4)

보호소에서 중성화한 동물

링크: https://school.programmers.co.kr/learn/courses/30/lessons/59045

-- 코드를 입력하세요
SELECT o.animal_id,
       o.animal_type,
       o.NAME
FROM   animal_ins AS i
       RIGHT OUTER JOIN animal_outs AS o
                     ON i.animal_id = o.animal_id
WHERE  o.animal_id IS NOT NULL
       AND ( i.sex_upon_intake NOT LIKE "spayed%"
             AND i.sex_upon_intake NOT LIKE "neutered%" )
       AND ( o.sex_upon_outcome LIKE "spayed%"
              OR o.sex_upon_outcome LIKE "neutered%" )
ORDER  BY o.animal_id

위 처럼 풀었는데 다른사람 풀이를 통해 훨씬 좋은 방법을 찾았다.. ㅎㅎ

출처: https://mungto.tistory.com/282

힘들게 아우터 조인을 할 필요도 없고, 1) 그냥 조인을 해서, 2) 성별이 바뀌는 경우만 찾았음..!

SELECT i.animal_id, i.animal_type, i.name
FROM animal_ins as i
JOIN animal_outs as o
WHERE i.animal_id = o.animal_id
			AND i.sex_upon_intake != o.sex_upon_outcome
ORDER BY i.animal_id

근데 위 코드를 보면서 조금 의아한건, join을 하면 on을 무조건 걸어줘야 한다고 알고있었는데.. 아닌가보네..?

INNER JOIN을 할 떈 ON을 걸건 WHERE을 걸건 똑같다고 한다…!

5월 식품들의 총 매출 구하기

링크: https://school.programmers.co.kr/learn/courses/30/lessons/131117

-- 코드를 입력하세요
SELECT p.product_id,
       p.product_name,
       Sum(p.price * o.amount) AS TOTAL_SALES
FROM   food_product AS p
       JOIN food_order AS o
         ON p.product_id = o.product_id
WHERE  Year(o.produce_date) = 2022
       AND Month(o.produce_date) = 5
GROUP  BY p.product_id,
          p.product_name
ORDER  BY total_sales DESC,
          p.product_id;

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

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

-- 코드를 입력하세요
SELECT p.category,
       p.price AS MAX_PRICE,
       p.product_name
FROM   (SELECT category,
               ( Max(price) ) AS MAX_PRICE,
               product_name
        FROM   food_product
        WHERE  category IN ( '과자', '국', '김치', '식용유' )
        GROUP  BY category) AS sub
       JOIN food_product AS p
         ON p.category = sub.category
            AND p.price = sub.max_price
ORDER  BY price DESC;

GORUP BY로 묶은 값들 중 최댓값만 쏙쏙 뽑는 문제이다. 검색을 좀 해보면 쿼리문의 정석중의 정석인 문제인것 같다.

이정도를 할줄알면 드디어 기본은 할줄안다..! 라고 생각할 수 있는 수준인것같다.

사실 나는 어떻게 하는지 몰라서 헤매었는데, 검색을 통해 자세히 설명된 글을 만났다.

출처: https://kukuta.tistory.com/398

위 글에서 보면 먼저 각 카테고리별로 MAX(PRICE)값을 구해주는 GROUP BY를 먼저 만들어 준다.

SELECT category,
       ( Max(price) ) AS MAX_PRICE,
       product_name
FROM   food_product
WHERE  category IN ( '과자', '국', '김치', '식용유' )
GROUP  BY category

그리고 이렇게 만들어진 테이블을 자기 자신의 테이블과 다시 한번 조인해준다. (그럼 이제 그룹화되어 있지 않지만 내가 원하는 데이터만 들어있는 각 그룹별로 최대값이 들어있는 테이블이 준비된다.)

-- 코드를 입력하세요
SELECT p.category,
       p.price AS MAX_PRICE,
       p.product_name
FROM   (SELECT category,
               ( Max(price) ) AS MAX_PRICE,
               product_name
        FROM   food_product
        WHERE  category IN ( '과자', '국', '김치', '식용유' )
        GROUP  BY category) AS sub
       JOIN food_product AS p
         ON p.category = sub.category
            AND p.price = sub.max_price
ORDER  BY price DESC;

여기에서 위 쿼리문과 같이 카테고리가 같으면서 price는 max_price인 애들만을 남겨준다.

검색을 해보니 이 정도 문제가 기본적인, 정석적인 문제인것같다. 다음에 한번 더 사용해봐야겠다.

⁉️ 근데 위 방법으로 하면 DB가 뻗을수있다고 한다… ㅠ

그래서 위 출처 글에서 제시한 방법은 union all을 활용해서 각 데이터들을 합쳐주는 방식이 있다고 한다. 이 방법이 DB에 부하도 크게 주지 않으면서 직관적인 방법이라고 한다. 변수지정을 통해서 하면된다고 한다.!

우선은 문제 풀이에는 직결되지 않는 문제니 추후 공부하기로 하고 넘긴다.

우유와 요거트가 담긴 장바구니

링크: https://school.programmers.co.kr/learn/courses/30/lessons/62284

-- 코드를 입력하세요
SELECT M.cart_id
FROM   (SELECT cart_id,
               id,
               NAME
        FROM   cart_products
        WHERE  NAME = "milk") AS M
       JOIN (SELECT cart_id,
                    id,
                    NAME
             FROM   cart_products
             WHERE  NAME = "yogurt") AS Y
         ON M.cart_id = Y.cart_id
ORDER  BY M.cart_id

우유를 담은 장바구니 테이블과, 요거트를 담은 장바구니 테이블을 만들어서 둘의 교집합을 찾으면 되겠다고 생각해서 해봤다.

이게 좋은 쿼리인지 판단할 능력은 아직 안되서 검색을 해보았다.

출처:https://velog.io/@jokwed/우유와-요거트가-담긴-장바구니SummerWinter-Coding2019

위 출처를 보면 GROUP BY해서 해빙으로 DISTINCT NAME을 카운트해서 2가 나오는 애들만 픽하는 방식이 있다. 나도 첨에 IN으로 걸러서 2개 모두 있는애들만 해야지 했는데 어떻게 할지 몰랐었다.

GROUP BY해서 중복제거한 COUNT가 2이면 된다는걸 몰랐는데, 일단 다른사람을 통해 배웠으니 다음에 사용해봐야겠다.

CONCAT도 생각은 했지만 이건 뭔가 데이터 너무 커지면 메모리 터지겠다 싶어서 안했었음..! 메모리에 테이블 두개 만드는게 안좋을까 메모리에 NAMES 엄청 큰 테이블 만드는게 나쁠까? where절로 컬러낸거라 전자가 나을듯…?

둘다 나쁠지도 ㅎㅎ

DISTINCT 사용한게 가장 좋아보인다.

취소되지 않은 진료 예약 조회하기

링크: https://school.programmers.co.kr/learn/courses/30/lessons/132204

-- 코드를 입력하세요
SELECT a.apnt_no,
       p.pt_name,
       a.pt_no,
       a.mcdp_cd,
       d.dr_name,
       a.apnt_ymd
FROM   appointment AS a
       JOIN patient AS p
         ON a.pt_no = p.pt_no
       JOIN doctor AS d
         ON d.dr_id = a.mddr_id
WHERE  Date_format(a.apnt_ymd, "%y-%m-%d") = "2022-04-13"
       AND a.mcdp_cd = "cs"
       AND a.apnt_cncl_yn = "n"
ORDER  BY apnt_ymd

서울에 위치한 식당 목록 출력하기

링크: https://school.programmers.co.kr/learn/courses/30/lessons/131118

-- 코드를 입력하세요
SELECT i.rest_id,
       i.rest_name,
       i.food_type,
       i.favorites,
       i.address,
       sc.score AS SCORE
FROM   rest_info AS i
       JOIN (SELECT rest_id,
                    Round(Avg(review_score), 2) AS SCORE
             FROM   rest_review
             GROUP  BY rest_id) AS sc
         ON i.rest_id = sc.rest_id
WHERE  i.address LIKE "서울%"
ORDER  BY score DESC,
          i.favorites DESC;

위 처럼 풀어서 정답은 나왔다. 근데 …

출처: https://velog.io/@sheltonwon/SQL연습-서울에-위치한-식당-목록-출력하기-프로그래머스

검색 해보니 서브쿼리 없이 GROUP BY로 간단하게 풀었더라.

-- 정답
SELECT A.REST_ID, B.REST_NAME, B.FOOD_TYPE, B.FAVORITES, B.ADDRESS, ROUND(AVG(A.REVIEW_SCORE),2) AS SCORE
FROM REST_REVIEW A
JOIN REST_INFO B ON A.REST_ID = B.REST_ID
GROUP BY A.REST_ID
HAVING B.ADDRESS LIKE '서울%'
ORDER BY SCORE DESC, B.FAVORITES DESC

확실히 GROUP BY에 대한 개념이 약한것 같다. 문제 중 GROUP BY문제를 항상 제일 어려워했다. 많이 풀어보면 되는가? 아니면 개념을 다시 제대로 파악해야하나?

둘다 해봐야겠다.

입양 시각 구하기 (2)

링크: https://school.programmers.co.kr/learn/courses/30/lessons/59413

-- 코드를 입력하세요
SELECT Hour(datetime)   AS HOUR,
       Count(animal_id) AS COUNT
FROM   animal_outs
GROUP  BY hour
ORDER  BY hour

이렇게 하면 7시부터 19시까지, 데이터가 있을 때만 나오고 0인 데이터 그룹은 생성이 안된다.. 데이터를 세팅해줘야 할것 같다. 근데 전혀 방법을 모르겠다…

출처: https://jaaamj.tistory.com/155

검색해보니 위 글에서 변수를 생성하고, 해당 변수를 활용해서 0부터 23까지의 그룹을 만든다. 그리고 count해서 뿌려주더라.

대충 감만 익히고 사용해보자. → 전혀 혼자 못풀었고 위 출처 코드를 참고해서 해결했다… 레벨 4문제인데… 역시 레벨 3부턴 쭉 쉽지않았다. 일단 10문제 남은거 다 풀어보자.

-- 코드를 입력하세요
SET @hour = 0;
SELECT (@hour := @hour + 1) AS HOUR, 
       (SELECT Count(HOUR(DATETIME))
        FROM ANIMAL_OUTS
        WHERE HOUR(DATETIME) = @hour) AS COUNT
FROM ANIMAL_OUTS
WHERE @hour < 23;

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

링크: https://school.programmers.co.kr/learn/courses/30/lessons/133027

-- 코드를 입력하세요
SELECT fh.FLAVOR
FROM (SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_JULY
        FROM JULY
        GROUP BY FLAVOR) AS j
JOIN (SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_FIRST_HALF
        FROM FIRST_HALF
        GROUP BY FLAVOR) AS fh
        ON j.FLAVOR = fh.FLAVOR
GROUP BY fh.FLAVOR
ORDER BY SUM(j.TOTAL_JULY + fh.TOTAL_FIRST_HALF) DESC
LIMIT 3;

이렇게 풀었는데.. 다른 풀이를 보니 정말 형편없는 수준이란걸 알게 되었다.

출처: https://velog.io/@zinu/프로그래머스-주문량이-많은-아이스크림들-조회하기MySQLJOIN

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

이렇게 간단한 문제였는데.. 쓸데없이 테이블을 두개나 만들다니.. 😅