Lv.1 포스팅을 마쳤으니 이제 Lv.2 포스팅을 해보겠다!
Lv.2은 내용이 좀 많으므로 2개로 나누어 포스팅해보겠다!
Let's go!
다음은 어느 자동차 대여 회사의 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY
테이블입니다. CAR_RENTAL_COMPANY_RENTAL_HISTORY
테이블은 아래와 같은 구조로 되어있으며, HISTORY_ID
, CAR_ID
, START_DATE
, END_DATE
는 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.
Column name | Type | Nullable |
---|---|---|
HISTORY_ID | INTEGER | FALSE |
CAR_ID | INTEGER | FALSE |
START_DATE | DATE | FALSE |
END_DATE | DATE | FALSE |
CAR_RENTAL_COMPANY_RENTAL_HISTORY
테이블에서 평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간(컬럼명: AVERAGE_DURATION
) 리스트를 출력하는 SQL문을 작성해주세요. 평균 대여 기간은 소수점 두번째 자리에서 반올림하고, 결과는 평균 대여 기간을 기준으로 내림차순 정렬해주시고, 평균 대여 기간이 같으면 자동차 ID를 기준으로 내림차순 정렬해주세요.
예를 들어 CAR_RENTAL_COMPANY_RENTAL_HISTORY
테이블이 다음과 같다면
HISTORY_ID | CAR_ID | START_DATE | END_DATE |
---|---|---|---|
1 | 1 | 2022-09-27 | 2022-10-01 |
2 | 1 | 2022-10-03 | 2022-11-04 |
3 | 2 | 2022-09-05 | 2022-09-05 |
4 | 2 | 2022-09-08 | 2022-09-10 |
5 | 3 | 2022-09-16 | 2022-10-15 |
6 | 1 | 2022-11-07 | 2022-12-06 |
자동차 별 평균 대여 기간은
CAR_ID | AVERAGE_DURATION |
---|---|
3 | 30.0 |
1 | 22.7 |
-- 내 답안
-- MySQL에서는 날짜끼리 연산하면 숫자로 인식함
SELECT CAR_ID, ROUND(AVG(END_DATE - START_DATE), 2) AS AVERAGE_DURARION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVG(END_DATE - START_DATE) >= 7
ORDER BY 2 DESC, 1 ASC;
-- 수정한 답안
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 AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;
주석에 적은 것처럼 MySQL에서는 날짜끼리 연산하면 숫자로 인식하기 때문에
HAVING
절의 숫자 조건에 정수로 날짜를 적어야 한다.
그리고 자세히 보면 AVERAGE_DURATION
에 +1을 해야하는데
그 이유는 아래와 같다.
참고로 필자는 아직도 이 내용이 어렵다...
다음은 어느 의류 쇼핑몰에서 판매중인 상품들의 정보를 담은 PRODUCT
테이블입니다. PRODUCT
테이블은 아래와 같은 구조로 되어있으며, PRODUCT_ID
, PRODUCT_CODE
, PRICE
는 각각 상품 ID, 상품코드, 판매가를 나타냅니다.
Column name | Type | Nullable |
---|---|---|
PRODUCT_ID | INTEGER | FALSE |
PRODUCT_CODE | VARCHAR(8) | FALSE |
PRICE | INTEGER | FALSE |
상품 별로 중복되지 않는 8자리 상품코드 값을 가지며, 앞 2자리는 카테고리 코드를 의미합니다.
PRODUCT
테이블에서 상품 카테고리 코드(PRODUCT_CODE
앞 2자리) 별 상품 개수를 출력하는 SQL문을 작성해주세요. 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬해주세요.
예를 들어 PRODUCT
테이블이 다음과 같다면
PRODUCT_ID | PRODUCT_CODE | PRICE |
---|---|---|
1 | A1000011 | 10000 |
2 | A1000045 | 9000 |
3 | C3000002 | 22000 |
4 | C3000006 | 15000 |
5 | C3000010 | 30000 |
6 | K1000023 | 17000 |
상품 카테고리 코드 별 상품은 아래와 같으므로,
A1
: PRODUCT_ID
가 1, 2 인 상품C3
: PRODUCT_ID
가 3, 4, 5 인 상품K1
: PRODUCT_ID
가 6 인 상품다음과 같은 결과가 나와야 합니다.
CATEGORY | PRODUCTS |
---|---|
A1 | 2 |
C3 | 3 |
K1 | 1 |
SELECT LEFT(PRODUCT_CODE, 2) AS CATEGORY,
COUNT(LEFT(PRODUCT_CODE, 2)) AS PRODUCTS FROM PRODUCT
GROUP BY 1
ORDER BY PRODUCT_CODE;
이번에는 MySQL에서 쿼리가 어떻게 수행되는지 알지 못해서
GROUP BY
를 사용하는데에 애를 먹었다.
그래서 MySQL에서의 쿼리 수행 순서를 한번 알아보도록 하겠다.
다음은 어느 자동차 대여 회사에서 대여중인 자동차들의 정보를 담은 CAR_RENTAL_COMPANY_CAR
테이블입니다. CAR_RENTAL_COMPANY_CAR
테이블은 아래와 같은 구조로 되어있으며, CAR_ID
, CAR_TYPE
, DAILY_FEE
, OPTIONS
는 각각 자동차 ID, 자동차 종류, 일일 대여 요금(원), 자동차 옵션 리스트를 나타냅니다.
Column name | Type | Nullable |
---|---|---|
CAR_ID | INTEGER | FALSE |
CAR_TYPE | VARCHAR(255) | FALSE |
DAILY_FEE | INTEGER | FALSE |
OPTIONS | VARCHAR(255) | FALSE |
자동차 종류는 '세단', 'SUV', '승합차', '트럭', '리무진' 이 있습니다. 자동차 옵션 리스트는 콤마(',')로 구분된 키워드 리스트(옵션 리스트 값 예시: '열선시트', '스마트키', '주차감지센서')로 되어있으며, 키워드 종류는 '주차감지센서', '스마트키', '네비게이션', '통풍시트', '열선시트', '후방카메라', '가죽시트' 가 있습니다.
CAR_RENTAL_COMPANY_CAR
테이블에서 '통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함된 자동차가 자동차 종류 별로 몇 대인지 출력하는 SQL문을 작성해주세요. 이때 자동차 수에 대한 컬럼명은 CARS
로 지정하고, 결과는 자동차 종류를 기준으로 오름차순 정렬해주세요.
예를 들어 CAR_RENTAL_COMPANY_CAR
테이블이 다음과 같다면
CAR_ID | CAR_TYPE | DAILY_FEE | OPTIONS |
---|---|---|---|
1 | 세단 | 16000 | 가죽시트,열선시트,후방카메라 |
2 | SUV | 14000 | 스마트키,네비게이션,열선시트 |
3 | SUV | 22000 | 주차감지센서,후방카메라 |
4 | 트럭 | 35000 | 주차감지센서,네비게이션,열선시트 |
5 | SUV | 16000 | 가죽시트,네비게이션,열선시트,후방카메라,주차감지센서 |
'통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함된 자동차는 자동차 ID가 1, 2, 4, 5인 자동차이고, 자동차 종류 별로 몇 대인지 구하고 자동차 종류를 기준으로 오름차순 정렬하면 다음과 같은 결과가 나와야 합니다.
CAR_TYPE | CARS |
---|---|
SUV | 2 |
세단 | 1 |
트럭 | 1 |
SELECT CAR_TYPE, COUNT(CAR_TYPE) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%시트%'
GROUP BY 1
ORDER BY 1;
SELECT CAR_TYPE AS CAR_TYPE, COUNT(CAR_TYPE) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS REGEXP ('통풍시트|열선시트|가죽시트')
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE ASC;
이번에는 LIKE
와 REGEXP
(정규표현식)을 번갈아 사용해보았다.
정규표현식을 한번에 정리하기에는 길기 때문에 다음에 시간이 남으면 좀 더 정리해보겠다!