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. 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가 사용되는 것이다.

📍 문제

📍 풀이

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

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

💡 LIKE()

• 문자열 내에서 원하는 문자열을 찾기 위해 사용
• WILDCARDS 문자들과 함께 쓰임

WILDCARDS는 무엇인가? 🧐

- 컴퓨터에서 특정 명령어로 명령을 내릴 때, 여러 파일을 한꺼번에 지정할 목적어로 사용하는 기호

- 주로 특정한 패턴이 있는 문자열 혹은 파일을 찾거나, 긴 이름을 생략할 때 쓰임

- SQL에서의 WILDCARDS(와일드 카드) : 문자열에서 간단하게 하나 이상의 문자를 대체하는 데 사용되는 문자

1️⃣ % (Percentage)

1. %m

- m 앞에 무엇이 오든 상관없이 맨 마지막이 m으로 끝나는 문자열

- him, aiem, m 등

2. c%

- c 뒤에 무엇이 오든 상관없이 맨 앞이 c으로 시작하는 문자열

- camera, cinema, c 등

3. %w%

- w 앞뒤에 무엇이 오든 상관없이 중간에 w를 포함하는 문자열

- abwl, hewo, w 등

2️⃣ _ (UnderBar)

이때, _ 는 하나의 문자열만을 표현!!!!

1. _don

- 총 4개의 글자로 구성된 문자열

- 2, 3, 4번째 문자는 각각 d, o, n로 고정

- 1번째 문자열은 모든 문자 허용

- ldon, 2don, wdon 등

2. p_rpl_

- 총 5개의 글자로 구성된 문자열

- 1, 3, 4번째 문자는 각각 p, r, p, l 로 고정

- 2, 5번째 문자는 어떤 문자도 허용

- purple, parplw, porpl2 등

💡 사용 예시

SELECT *
FROM city
WHERE NAME LIKE 'KO_EA'

SELECT *
FROM student
WHERE Name LIKE 'he%'

검색하려는 문자열이 WILDCARD인 _ 또는 &일 때는 어떻게 해야할까?

WILDCARD 앞에 \(역슬래쉬) 넣어주면 된다.

예시)   _가 들어가는 값들을 모두 불러오고 싶은 경우

SELECT *
FROM STUDENT
WHERE NAME LIKE "%\_%"

- 참고 사이트

📌 GROUP BY

• 그룹으로 묶어주는 역할
• 집계 함수(Aggregate Function)를 함께 사용
  • AVG() : 평균
  • MIN() : 최소값
  • MAX() : 최대값
  • COUNT() : 행의 개수
  • COUNT(DISTINCT) : 중복 제외된 행의 개수
  • STDEV() : 표준 편차
  • VARIANCE() : 분산
• 효율적으로 데이터 그룹화 (Grouping)
• 읽기 좋게 하기 위해 별칭(Alias) 사용

// CountryCode를 그룹으로 묶으면서 Populiaton이 가장 큰 것만 조회
SELECT CountryCode, MAX(Population)
FROM city
GROUP BY CountryCode

📌 HAVING

• WHERE과 비슷한 개념으로 조건 제한
• 집계 함수에 대해서 조건 제한하는 편리한 개념
• HAVING절은 반드시 GROUP BY절 다음에 나와야 함

SELECT CountryCode, MAX(Population)
FROM city
GROUP BY CountryCode
HAVING MAX(Population) > 8000000

📌 GROUP BY 절에서 조건을 주려면 WHERE이 아닌, HAVING절 사용해야 함 📌

왜 일까?!🧐

SELECT 실행 순서를 보면 WHERE 절이 GROUP BY 보다 먼저 실행되기 때문에,

GROUP BY에 대응되는 HAVING 절이 있음

수행 순서

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT

HAVING은 GROUP BY 뒤에 작성하며, WHERE과 동일한 형식으로 조건 작성 가능