[MySQL] 프로그래머스 SQL 고득점 Kit - Group By 16문제 풀이

카테고리 별 도서 판매량 집계하기 Lv3

-- 코드를 입력하세요 
SELECT B.CATEGORY, SUM(BS.SALES) AS TOTAL_SALES
FROM BOOK B JOIN BOOK_SALES BS
    ON B.BOOK_ID = BS.BOOK_ID 
WHERE BS.SALES_DATE LIKE '2022-01-%'
GROUP BY B.CATEGORY
ORDER BY B.CATEGORY

즐겨찾기가 가장 많은 식당 정보 출력하기 Lv3

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
)

group by food_type
order by food_type desc

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

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

👁️ 어려웠음 👁️ 카테고리 별 도서 판매량 집계하기 Lv3

SELECT food_type, rest_id, rest_name, favorites
from rest_info 
WHERE FAVORITES IN(
    SELECT MAX(FAVORITES) FROM REST_INFO
    GROUP BY FOOD_TYPE
)
GROUP BY food_type
ORDER BY food_type DESC

진료과별 총 예약 횟수 출력하기 Lv2

-- 코드를 입력하세요
SELECT MCDP_CD AS '진료과코드', COUNT(*) AS '5월예약건수'
FROM APPOINTMENT
WHERE APNT_YMD LIKE '2022-05-%'
GROUP BY MCDP_CD
ORDER BY COUNT(*), MCDP_CD ASC;

고양이와 개는 몇 마리 있을까 Lv2

-- 코드를 입력하세요
SELECT animal_type, count(*) as count
from animal_ins
group by animal_type
order by animal_type

입양 시각 구하기(1) Lv2

와,,, 대박

hour, minute, second, month, year 등등을 괄호 씌운 datetime 앞에 말하면,,

시간 분 초 달 년 만 나옴,,,!! 이제 알았어

SELECT HOUR(DATETIME), count(HOUR(DATETIME)) as count
FROM ANIMAL_OUTS
where HOUR(DATETIME) between 9 and 20
group by HOUR(DATETIME)
order by HOUR(DATETIME)

동명 동물 수 찾기 Lv2

-- 코드를 입력하세요
SELECT name, count(name) as count
from ANIMAL_INS
group by name
having count(name) >= 2
order by name

대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기 Lv3

서브쿼리를 사용하는게 아직 생각보다 너무 어렵고, AND로 다중조건을 주는 부분도 상당히 어렵다

-- 코드를 입력하세요
SELECT MONTH(START_DATE) MONTH, CAR_ID, COUNT(*) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
    GROUP BY CAR_ID
    HAVING COUNT(*) >= 5
) AND START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY MONTH, CAR_ID
ORDER BY MONTH, CAR_ID DESC;

자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기 Lv3 → 좋은 문제인듯

with as 로 서브쿼리 분리해서 만들기, case~when~then~end as 구문 연습하기

with tmp as (
    select car_id
    from CAR_RENTAL_COMPANY_RENTAL_HISTORY
   -- where start_date like '2022-10-16' and
   -- end_date like '2022-10-16')
    where start_date <= '2022-10-16' and
    end_date >= '2022-10-16')
select distinct CAR_ID,
    case 
        when car_id in (select * from tmp) then '대여중'
        else '대여 가능'
        end as AVAILABILITY
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
order by car_id desc

where start_date <= '2022-10-16' and end_date >= '2022-10-16')

대여중이니까 2022-10-16 를 포함한 전에 빌리고, 후에 반납하는 !!!! 생각을 합시다

이 부분이 제 어렵네 ㅋㅅㅋ 힘쇼,,

저자 별 카테고리 별 매출액 집계하기 Lv4

book, author, book_sales 총 3개의 테이블을 합쳐서 풀어야함

**2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성해주세요.**

이거 어떻게 풀지 ..?

BOOK left join AUTHOR → Author_id로 조인

BOOK left join SALES → Book_id로 조인

천재의 풀이 발견..

SELECT
    A.AUTHOR_ID,
    B.AUTHOR_NAME,
    A.CATEGORY,
    SUM(A.PRICE * C.SALES)AS TOTAL_SALES
FROM
    BOOK A  LEFT OUTER JOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_ID
    LEFT OUTER JOIN BOOK_SALES C ON A.BOOK_ID = C.BOOK_ID AND SALES_DATE LIKE '2022-01%'
GROUP BY
    A.AUTHOR_ID, A.CATEGORY
ORDER BY
    A.AUTHOR_ID, A.CATEGORY DESC

다른 풀이법도 찾아서 달달 외우고 이해하기

내 풀이

select b.author_id, a.author_name, b.category, sum(b.price*s.sales) as total_sales
from book b join author a on b.author_id = a.author_id
    join book_sales s on b.book_id = s.book_id
where s.sales_date like '2022-01-%'
group by a.author_id, b.category
order by a.author_id, b.category desc

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

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

중요한 것은 각 카테고리별로 가장 비싼 것을 구하는 것임

https://jaehwaseo.tistory.com/30 링크 참고하여 이해하기

내 풀이

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

년, 월, 성별 별 상품 구매 회원 수 구하기 Lv 4

이 문제에서 핵심은 distinct이다.. distinct를 확실히 알고 적용하자.

동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다. → distinct

SQL은 실제 코테에서 정답인지 아닌지 확인하기 어려우니 한문장 한문장에 집중해서 풀자

-- 코드를 입력하세요
SELECT YEAR(S.SALES_DATE) AS YEAR ,MONTH(S.SALES_DATE) AS MONTH, U.GENDER, count(DISTINCT U.USER_ID) AS USERS
FROM USER_INFO U JOIN ONLINE_SALE S ON U.USER_ID = S.USER_ID
WHERE S.SALES_AMOUNT >= 1 AND GENDER IS NOT NULL
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR, MONTH, GENDER

👁️ 👁️ 입양 시각 구하기 (2)👁️ 👁️  → 외우자

단언코,, 가장 어려움!! 방법은 두개가 있다.

1. SET 함수 사용

SET @HOUR := -1; # 변수선언 및 대입 

SELECT (@HOUR := @HOUR +1) AS HOUR, #변수 출력 
(SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @HOUR) AS COUNT 
FROM ANIMAL_OUTS
WHERE @HOUR < 23

1. 재귀함수로 풀기

WITH RECURSIVE cte (HOUR) AS
(
    SELECT 0
    UNION ALL
    SELECT HOUR + 1 FROM cte WHERE HOUR < 23
)
SELECT cte.HOUR, COUNT(ANIMAL_OUTS.ANIMAL_ID) AS 'COUNT'
FROM cte
LEFT JOIN ANIMAL_OUTS
ON cte.HOUR = HOUR(ANIMAL_OUTS.DATETIME)
GROUP BY HOUR

WITH, WITH RECURSIVE 구문 ,, 외워라

WITH CTE AS (
    SELECT 0 AS NUM
    UNION ALL
    SELECT 0 FROM SOME_TABLE # SOME_TABLE의 행 수만큼 반복된다.
)

# 0~10의 값을 갖는 임시테이블
WITH RECURSIVE CTE AS(
    SELECT 0 AS NUM # 초기값 설정
    UNION ALL
    SELECT NUM+1 FROM CTE
    WEHRE NUM < 10 # 반복을 멈추는 조건
)