minjgziii

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE,START_DATE)+1), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVG(DATEDIFF(END_DATE,START_DATE)+1) >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

📍 문제

📍 풀이

고려해야할 조건들을 살펴보자!

1. 평균 대여 기간이 7일 이상인 자동차들

2. 평균 대여 기간은 소수점 두번째 자리에서 반올림

3. 평균 대여 기간 기준 내림차순 정렬

이 문제의 핵심은 (나는 이걸 놓쳐서 틀렸다)

1. GROUP BY, HAVING 절, AVG() 활용하여 평균 대여 기간 7일 이상인 자동차들만 가져오기

GROUP BY CAR_ID
HAVING AVG(DATEDIFF(END_DATE,START_DATE) + 1) >= 7

2. ROUND 활용하여 평균 대여 기간 소수점 두번째 자리에서 반올림

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE,START_DATE)+1), 1) AS AVERAGE_DURATION

3. ORDER BY 활용하여 평균 대여 기간 기준 내림차순 정렬

ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

이렇게 해서 잘 쓴 것 같았지만,,,, 틀렸다...

❌ 틀린 코드 ❌

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE,START_DATE)), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVG(DATEDIFF(END_DATE,START_DATE)) >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

그 이유는

총 대여 기간 : 날짜 간의 차이 + 1

쩝,,,

📍 전체 정답 코드

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE,START_DATE)+1), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVG(DATEDIFF(END_DATE,START_DATE)+1) >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

📍 문제

📍 풀이

고려해야할 조건들을 살펴보자!

1. 2022년 10월 5일에 등록된 중고거래 게시물

2. 거래 상태가
    SALE : 판매중
    RESERVED : 예약중    DONE : 거래완료

3. 게시글 ID를 기준으로 내림차순 정렬

1. WHERE 활용하여 2022년 10월 5일에 등록된 중고거래 게시물만 가져오기

WHERE CREATED_DATE = "2022-10-05"

2. CASE문 활용하여 거래 상태마다 분류하여 출력

CASE WHEN STATUS = "SALE"
THEN "판매중"
WHEN STATUS = "RESERVED"
THEN "예약중"
ELSE "거래완료"
END AS STATUS

❗️❗️ 여기서 AS STATUS 꼭 써줘야 한다는 것 잊지 말기 ❗️❗️

만약 AS STATUS 안 쓰면 다음과 같은 결과로 출력되고 당연히 틀리게 됨 (내가 안 썼다 틀림)

3. ORDER BY 활용하여 게시글 ID 기준 내림차순 정렬

ORDER BY BOARD_ID DESC

📍 전체 코드

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE, 
CASE WHEN STATUS = "SALE" THEN "판매중"
	 WHEN STATUS = "RESERVED" THEN "예약중"
	 ELSE "거래완료"
END AS STATUS
FROM USED_GOODS_BOARD
WHERE CREATED_DATE = "2022-10-05"
ORDER BY BOARD_ID DESC

📍 문제

📍 풀이

고려해야할 조건들을 살펴보자!

1. 년, 월, 성별 별로 상품을 구매한 회원수를 집계

2. 년, 월, 성별을 기준으로 오름차순 정렬

3. 성별 정보가 없는 경우 결과에서 제외

1. GROUP BY 활용하여 년, 월, 성별 별로 상품을 구매한 회원수를 집계

GROUP BY YEAR, MONTH, GENDER

2. ORDER BY 활용하여 년, 월, 성별을 기준으로 오름차순 정렬

ORDER BY YEAR, MONTH, GENDER

3. IS NOT NULL 활용하여 성별 정보가 없는 경우 결과에서 제외

WHERE U.GENDER IS NOT NULL

이렇게 모든 조건을 잘 생각해서 잘 쓴 것 같았는데,,,,,

❌ 틀린 코드 ❌

SELECT YEAR(O.SALES_DATE) AS YEAR, MONTH(O.SALES_DATE) AS MONTH, U.GENDER, COUNT(O.USER_ID) AS USERS
FROM USER_INFO AS U
JOIN ONLINE_SALE AS O
ON U.USER_ID = O.USER_ID
WHERE U.GENDER IS NOT NULL
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR, MONTH, GENDER

오류가 뜨는 것도 아니고 도대체 어디때문에 틀린 건지 몰랐다...

결국 다른 분들이 푼 정답 코드를 보면서 깨달았다.

딱 하나를 안 써서 틀렸는데,,

DISTINCT

로 중복 제거를 안 해 줘서 틀렸던 것이다.

상품을 구매한 회원 수를 집계하는 것이고 ONLINE_SALE 테이블에는 판매 정보를 담고 있어 USER_ID가 중복되는 데이터들이 있다.

=> ⭐⭐ DISTINCT를 써서 중복 제거해 주어야 함! ⭐ ⭐

📍 정답 코드

SELECT YEAR(O.SALES_DATE) AS YEAR, MONTH(O.SALES_DATE) AS MONTH, U.GENDER, COUNT(DISTINCT O.USER_ID) AS USERS
FROM USER_INFO AS U
JOIN ONLINE_SALE AS O
ON U.USER_ID = O.USER_ID
WHERE U.GENDER IS NOT NULL
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR, MONTH, GENDER

📍 문제

📍 풀이

고려해야할 조건들을 살펴보자!

1. 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서

2. 해당 기간 동안(2022년 8월부터 2022년 10월까지)의 월별 자동차 ID 별 총 대여 횟수 리스트 출력

3. 월을 기준으로 오름차순 정렬, 월이 같다면 자동차 ID 기준 내림차순 정렬

1. 서브쿼리를 활용하여 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들만 조회

WHERE CAR_ID IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE >= '2022-08-01' AND START_DATE <= '2022-10-31'
    GROUP BY CAR_ID
    HAVING COUNT(HISTORY_ID) >= 5)

이때, 문제에서

1) 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 라고 했으므로

WHERE START_DATE >= '2022-08-01' AND START_DATE <= '2022-10-31'

2) 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 라고 했으므로

- CAR_ID로 그룹화 하고

- COUNT(HISTORY_ID) 로 총 대여횟수가 5회 이상인 것들만 가져온다

SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE >= '2022-08-01' AND START_DATE <= '2022-10-31'
GROUP BY CAR_ID
HAVING COUNT(HISTORY_ID) >= 5

2. 메인 쿼리에도 날짜 조건을 걸어 해당 기간 동안 (2022년 8월부터 2022년 10월까지) 의 월별 자동차 ID 별 총 대여 횟수 리스트 출력

이때, ❕❗ 서브쿼리(내부 쿼리)뿐만 아니라 메인 쿼리(외부 쿼리) 에서도 날짜 조건을 걸어주어야 한다. ❕❗

그렇지 않으면 전체 기간 동안 대여된 차량의 ID를 반환하게 된다!

즉, 서브 쿼리에만 날짜 조건을 걸면

예를 들어 자동차 ID 1이 8~10월 사이에 대여 횟수가 5건 이상이 존재한다고 할 때,

자동차 1이 11월에도 대여건이 존재하면 11월에 해당하는 대여건까지 적용되어 결과가 반환된다.

내가 처음에 이렇게 잘못 써서 틀렸다. 

3. ORDER BY를 활용하여 월을 기준으로 오름차순 정렬, 월이 같다면 자동차 ID 기준 내림차순 정렬

ORDER BY MONTH, CAR_ID DESC

📍 전체 코드

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(HISTORY_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE >= '2022-08-01' AND START_DATE <= '2022-10-31'
    GROUP BY CAR_ID
    HAVING COUNT(HISTORY_ID) >= 5)
AND START_DATE >= '2022-08-01' AND START_DATE <= '2022-10-31'
GROUP BY MONTH(START_DATE), CAR_ID
ORDER BY MONTH, CAR_ID DESC

📍 문제

📍 풀이

[ 답안 1 ] - CASE문 사용

SELECT CAR_ID,
CASE
WHEN CAR_ID IN (SELECT CAR_ID
               FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
               WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE)
               THEN '대여중'
               ELSE '대여 가능'
               END 'AVAILABILITY'
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

CASE문을 사용해 각 CAR_ID에 대해 '2022-10-16'이라는 날짜가 START_DATE와 END_DATE 사이에 있는지 확인한다.

있다면 '대여중' 을 반환하고, 없으면 '대여 가능'을 반환하도록 하였다.

이때, 결과는 'AVALIABILITY' 라는 별칭으로 표시된다.

[ 답안 2 ] - IF문 사용

❌ 내가 쓴 틀린 코드 ❌

SELECT CAR_ID, IF('2022-10-16' BETWEEN START_DATE AND END_DATE, '대여중', '대여 가능') AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

⭕ 정답 코드 ⭕

SELECT CAR_ID, MAX(IF('2022-10-16' BETWEEN START_DATE AND END_DATE, '대여중', '대여 가능')) AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

첫번째 코드는 왜 틀릴까?

GROUP BY를 사용해서 각 CAR_ID별로 그룹화하고, 동시에 각 그룹 내에서 어떤 값이 '대여중'인지 '대여 가능'인지를 결정해야 한다.

GROUP BY가 적용된 후에는 각 그룹에 대한 단일 값만 반환할 수 있다.
따라서 SQL의 집계 함수인 MAX, MIN, AVG, SUM 등을 사용해야 한다.

(이런 집계 함수 없이는 GROUP BY를 사용 후 그룹화된 각 행에서 하나의 결과를 얻을 수 없다.)

애초에 ❌ 저 틀린 코드 ❌ 는 문법적으로 틀린 SQL 구문이다 !! 하지만, 프로그래머스 상에서는 틀렸다고 뜨지만 오류를 뱉지는 않는다. 이 부분 관련해서 읽어보면 아주 좋을 만한 글이다 => https://school.programmers.co.kr/questions/38703

그렇다면 집계 함수 중 왜 MAX 함수를 써야 하는 것일까?

MAX를 쓰게 되면 각 CAR_ID 그룹에서 '대여중' 과 '대여 가능' 중 사전 순으로 뒤에 오는 값을 선택한다.

이때, '대여중'이 '대여 가능' 보다 사전 순으로 뒤에 오기 때문에

해당 날짜에 차량이 한 번이라도 대여되었다면 '대여중'이 선택된다.

=> 즉, 특정 날짜에 자동차를 대여할 수 있는지 여부를 파악하기 위해 MAX가 사용되는 것이다.

📍 문제

📍 풀이

❌ 내가 처음 쓴 틀린 코드 ❌

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

이걸 실행하면 다음과 같이 오류가 뜬다.

왜냐하면!

SQL에서는 WHERE절에서 집계 함수를 사용할 수 없기 때문이다.

WHERE절은 각 행에 대해 조건을 검사하지만 SUM과 같은 집계 함수들은 여러 행에 걸쳐 작동하기 때문에 이 둘을 혼합하여 사용하면 문제가 발생한다.

따라서, SUM(B.PRICE) >= 700000  는 WHERE절에서 사용할 수 없고

HAVING절을 활용해야 한다.

📍 정답 코드

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

📍 문제

📍 풀이

고려해야 할 조건들부터 살펴보자!

1. 음식 종류별로 즐겨찾기 수가 가장 많은 식당의
    => 음식 종류마다 즐겨찾기 수가 가장 많은 식당 하나만 가져오기

2. 음식 종류를 기준으로 내림차순 정렬

내가 처음 쓴 코드는

1번 조건을 위해 MAX()를 이용하였다.

SELECT FOOD_TYPE, REST_ID, REST_NAME, MAX(FAVORITES) AS FAVORITES
FROM REST_INFO
GROUP BY FOOD_TYPE
ORDER BY FOOD_TYPE DESC

이 코드를 실행하면 다음과 같은 결과가 나온다.

얼핏 보면 정답과 일치해 보이지만, 일식의 경우 정답과 다른 결과가 나온다.

이런 결과가 나오는 이유는

GROUP BY로 묶으면 가장 상단에 있는 데이터들을 임의로 가져온다.

따라서, SELECT에 MAX를 해도 최대값을 가져오는 게 아니라 그룹화된 테이블 가장 상단에 있는 값을 가져온다.

그렇다면 어떻게 해야 할까?

이 문제의 핵심은,

❗ 서브 쿼리를 사용해 최대값을 따로 찾아주어야 한다 ❗

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

복잡해 보이는 이 쿼리가 어떤 순서로 진행되는지 살펴보자.

그 전에 참고할 SQL의 쿼리 실행 순서 관련 :

1. FROM REST_INFO : REST_INFO 테이블에서 데이터 가져오기

2. GROUP BY FOOD_TYPE : 'FOOD_TYPE' 칼럼을 기준으로 데이터 그룹화
    => 각 'FOOD_TYPE'의 식당들이 하나의 그룹으로 묶임

3.  SELECT FOOD_TYPE, MAX(FAVORITES) : 각 그룹에서 'FAVORITES'의 최대갑승ㄹ 찾고 해당하는 'FOOD_TYPE' 선택

4. IN : 서브 쿼리가 완성되었고, 이 결과가 바깥쪽 쿼리의 WHERE 조건과 매치되는지 확인하기 위해 사용

5. WHERE (FOOD_TYPE, FAVORITES) IN : 바깥 쿼리에서 'FOOD_TYPE'과 'FAVORITES'가 내부 쿼리의 결과와 일치하는 행들을 선택합니다. 이는 각 'FOOD_TYPE' 마다 즐겨찾기 수가 가장 많은 식당만을 추출하는 작업

6. SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES : 선택된 행들에서 'FOOD_TYPE', 'REST_ID', 'REST_NAME', 'FAVORITES' 칼럼을 선택

7. ORDER BY FOOD_TYPE DESC : 결과를 'FOOD_TYPE' 을 기준으로 내림차순으로 정렬

📍 문제

📍 풀이

이 문제의 핵심은 UNION 을 사용하는 것!

오프라인/온라인의 쿼리 결과를 합치기 위해 UNION을 사용해 쿼리의 결과를 합치고, 중복된 행은 제거한다.

이때, UNION 연산을 사용하면 중복값은 제거되어 하나만 추가된 새로운 테이블을 만든다.

두 개의 결과를 결합하는 연산자에는

UNION과 UNION ALL 연산이 있는데,

두 연산의 차이점을 요약하자면

• UNION : 중복된 행을 제거하여 보여줌 ( 결과는 정렬되어 반환됨)
• UNION ALL : 중복된 행 제거하지 않고 모든 행을 포함하여 보여줌 ( 결과는 정렬되지 않고 반환됨)

그렇다면, 고려해야할 조건들을 살펴보자!

1. 2022년 3월의 오프라인/온라인 상품

2. SALES_DATE 출력 형태 "2022-03-01" 

3. ❗️OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시❗️

4. 판매일, 상품ID, 유저ID 순으로 오름차순 정렬

1. YEAR(), MONTH() 를 활용하여 2022년 3월의 상품만 조회

WHERE YEAR(SALES_DATE) = 2022 AND MONTH(SALES_DATE) = 3

2. DATE_FORMAT 활용하여 "%Y-%m-%d" 로 출력 형태 지정

SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT

3.  NULL AS 활용하여 OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL로 표시

SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE, 
PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT

4. ORDER BY 를 활용하여 판매일, 상품ID, 유저ID 순으로 오름차순 정렬

ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

📍 전체 코드

SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE YEAR(SALES_DATE) = 2022 AND MONTH(SALES_DATE) = 3
UNION
SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE YEAR(SALES_DATE) = 2022 AND MONTH(SALES_DATE) = 3
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID