문서 목적
해당 문서는 데분당태 챌린지를 진행하는 겸, 개인적으로 쿼리를 작성할 때 접근을 어떻게 했는지 정리하고자 작성한 문서입니다.
요약
- 개인적으로 SQL로 데이터를 추출할 때 주로 아래와 같이 진행한다.
요구사항 정리 → 필요한 테이블 선정 → 테이블 내용 확인 → 쿼리 작성 → EXPLAIN → 결과 확인
문서 내용
맨 처음 SQL을 이용해서 데이터를 추출할 때, 어떻게 쿼리를 작성해야 원하는 값을 뽑을 수 있는지 고민을 많이 한 경험이 있습니다. 이러한 경험을 살려서 현재 제가 쿼리를 작성할 때 어떻게 접근하고 있는지 정리하고자 합니다. 물론 이는 지극히 주관적이므로 다를 수 있음을 미리 알려 드립니다.
예제 데이터
예제 데이터의 경우, 제가 아직 한번도 풀어보지 않고 정답률이 낮은 데이터로 시작하면 좋을 것 같아 아래 예제를 선정했습니다.
- 사이트 : 프로그래머스
- 문제 : 자동차 대여 기록 별 대여 금액 구하기(작성일 2023.04.25 기준 정답률 34%)
요구사항 정리
맨 처음, 제가 진행하는 내용은 어떤 데이터를 뽑아야 하는가 입니다. 현재 주어진 문제는 아래와 같습니다.
CAR_RENTAL_COMPANY_CAR테이블과CAR_RENTAL_COMPANY_RENTAL_HISTORY테이블과CAR_RENTAL_COMPANY_DISCOUNT_PLAN테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명:FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.
제가 생각했을 때 이를 정리하면 아래와 같습니다.
- 자동차 종류가
트럭인 자동차의 대여 기록을 구한다. - 대여 기록에는 금액(할인 적용된)이 들어가야 한다.
- 최종 결과에는 두 가지 컬럼
대여 기록 ID(HISTORY_ID),대여 금액(FEE)만 있으면 된다. - 최종 결과는 대여 기록과 금액에 대해 정렬되어야 한다.(FEE DESC, HISTORY_ID DESC)
필요한 테이블 선정
우선 테이블에 무엇이 있어야 하는지를 알아야 합니다. 현재 문제 설명에 테이블에 대한 설명이 아래와 같이 있습니다.
-
CAR_RENTAL_COMPANY_CAR- 대여 중인 자동차들의 정보
- 컬럼 : CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS
(자동차 ID, 자동차 종류, 일일 대여 요금(원), 자동차 옵션 리스트)
-
CAR_RENTAL_COMPANY_RENTAL_HISTORY- 테이블과 자동차 대여 기록 정보
- 컬럼 : HISTORY_ID, CAR_ID, START_DATE, END_DATE
(자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일)
-
CAR_RENTAL_COMPANY_DISCOUNT_PLAN- 자동차 종류 별 대여 기간 종류 별 할인 정책 정보
- 컬럼 : PLAN_ID, CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE
(요금 할인 정책 ID, 자동차 종류, 대여 기간 종류, 할인율(%))
현업에서도 잘 정리되어 있으면 좋겠으나, 정리되어 있지 않다면 우선 시도해 볼 것은 아래의 쿼리 입니다.
SELECT
table_name, column_name, column_comment
FROM
information_schema.columns
WHERE
table_schema = 'schema name'
AND table_name = 'table name';이렇게 하면 처음 테이블을 생성했을 때, 입력한 코멘트 들을 확인할 수 있습니다. 물론 이렇게 해도 알 수 없는 경우가 종종 있습니다. 이럴 때는 테이블의 내용 확인 + 서비스 코드 확인 + 담당자와 미팅 등으로 파악할 수 있습니다.(시간이 진짜 오래걸립니다.)
우선 여기서 우리가 요금 계산에 사용해야 할 것은 CAR_RENTAL_COMPANY_CAR의 DAILY_FEE컬럼과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN의 DISCOUNT_RATE로 보입니다. 왜냐하면 기간 별 할인율이 있고, 그렇다면 할인율을 적용한 금액이 대여 기간의 금액일 것이기 때문입니다.
그렇다면 자동차 종류가 트럭인 자동차의 대여 기록은 어떻게 구해야할까요? 저의 생각은 CAR_RENTAL_COMPANY_RENTAL_HISTORY와 CAR_RENTAL_COMPANY_CAR를 사용하면 될 것으로 보입니다. 대여 기록에서의 자동차 ID를 CAR_RENTAL_COMPANY_CAR에서 찾아 트럭인지 파악하면 될 것으로 보입니다.
테이블 내용 확인
항상 쿼리를 짜기 전 각 테이블에 대해서 아래와 같은 쿼리들을 실행하는 습관이 있습니다. 프로그래밍으로 치면 디버깅이라고 볼 수 있는데, 항상 LIMIT로 내가 생각하는 구조로 잘 있는지 확인하는 습관이 생겼습니다. 얼마나 데이터가 있고, 인덱스가 어디에 걸려 있는지 등 파악하는데 도움이 됩니다.
-- table 내용 확인
SELECT *
FROM CAR_RENTAL_COMPANY_CAR
LIMIT 10;
-- 인덱스 확인
SHOW INDEX FROM CAR_RENTAL_COMPANY_CAR;
-- 대략적인 ROWS수
SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema = 'sql_runner_run'
AND table_name = 'CAR_RENTAL_COMPANY_CAR';쿼리 작성
우리가 구해야할 내용은 아래와 같이 정리되었습니다.
- 자동차 종류가
트럭인 자동차의 대여 기록을 구한다. - 대여 기록에는 금액(할인 적용된)이 들어가야 한다.
1.자동차 종류가 트럭인 자동차의 대여 기록을 구한다.
그렇다면 제일 먼저 구해야 할 것은 대여 기록 중 트럭인 자동차인 대여기록입니다. 하지만 대여기록인 CAR_RENTAL_COMPANY_RENTAL_HISTORY에는 대여한 차의 ID가 있으나 차종에 대한 정보가 없습니다. 이를 위해 CAR_RENTAL_COMPANY_CAR와 연결이 필요합니다.
연결 할 수 있는 방법에는 무엇이 있는가 고민이 필요한데, 저는 서브쿼리로 작성하는것을 선호하지 않으므로 JOIN방식을 사용하고자 합니다. 그렇다면 어떤 JOIN이 필요할지 고민이 필요합니다.
만약에 특정 CAR_ID가 CAR_RENTAL_COMPANY_RENTAL_HISTORY에 존재하지만 CAR_RENTAL_COMPANY_CAR에 존재하지 않는다고 가정해봅시다. (물론 이런일은 없어야 겠지만... 현실에는 그런경우가 존재하죠...). 이 때 저희는 해당 CAR_ID가 트럭인지 판단할 수가 없습니다. 그렇다면 요청 사항인 자동차 종류가 '트럭'인 자동차의 대여 기록을 구한다.에 어긋납니다. 그래서 저희는 그런 CAR_ID는 필요가 없게 됩니다. 따라서 여기서 우리는 INNER JOIN을 사용하면 되는구나!를 알 수 있습니다.(물론 EXISTS 방식도 있습니다.)
이를 이용해서 쿼리를 작성하면 아래와 같습니다.
SELECT *
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS rental_history
INNER JOIN CAR_RENTAL_COMPANY_CAR AS car_information
ON rental_history.car_id = car_information.car_id
LIMIT 10;LIMIT 10을 입력함으로써 제가 원하는 결과가 나왔는지 확인해봅니다. 각 기록에 따라 car_type이 붙은 것을 확인할 수 있었습니다. 여기서 필터링과 필요 컬럼을 선정하여 1차 적으로 원하는 데이터를 뽑을 수 있습니다.(언제나 인덴트로 쿼리가 읽기 편하게 만들어주세요)
SELECT
history_id,
rental_history.car_id,
car_type,
start_date,
end_date,
daily_fee
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS rental_history
INNER JOIN CAR_RENTAL_COMPANY_CAR AS car_information
ON rental_history.car_id = car_information.car_id
WHERE car_information.car_type = '트럭'
LIMIT 10;여기서 daily_fee를 포함한 이유는 최종적으로 필요한 것은 금액이기 때문입니다. 또한 car_type, start_date, end_date를 포함한 이유는 차종, 대여 기간에 따라서 금액이 다르기 때문입니다.
LIMIT 10을 계속해서 붙여주는 이유는 체크포인트처럼 현재 내가 원하는 내용대로 나왔는지 쿼리를 실행하고자 함입니다.
이쯤에서 쿼리 코스트를 한번 확인합니다. 이 때 EXPLAIN을 이용하는데 아래와 같습니다.
EXPLAIN
SELECT
history_id,
rental_history.car_id,
car_type,
start_date,
end_date,
daily_fee
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS rental_history
INNER JOIN CAR_RENTAL_COMPANY_CAR AS car_information
ON rental_history.car_id = car_information.car_id
WHERE car_information.car_type = '트럭'현재 결과는 아래와 같이 나옵니다. 여기서 type ALL부분은 최대한 피해야합니다. ALL이 의미하는 바는 풀스캔이라서 리소스가 많이 듭니다. 하지만, 현재 예제에서는 car_type이 인덱스가 걸려져 있지 않으나, 트럭으로 필터링 할 수 있는 부분이 없어서 이 부분은 넘어갑니다.
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | car_information | ALL | PRIMARY | 30 | 10 | Using where | ||||
| 1 | SIMPLE | rental_history | ALL | 162 | 10 | Using where; Using join buffer (hash join) |
2. 대여 기록에 할인율을 구해야 한다.
이제 구해야 할것은 각 기록마다 할인된 금액을 구해야 합니다. CAR_RENTAL_COMPANY_DISCOUNT_PLAN에는 차종과 대여 기간 동안 얼마나 할인이 되는지 정리되어 있습니다. 그렇다면 우리는 각 기록에서 차량의 대여 기간을 알아야합니다.
위에서 진행한 내용에서 대여 시작일인 start_date, 대여 종료일인 end_date가 있습니다. 이로 우리는 몇일 빌렸는지 확인하기 위해서는 날짜 차이를 구해야합니다. 이때 구글에서 MySQL 날짜 차이 구하는 법으로 검색해보니 DATEDIFF라는 함수를 찾았습니다. 이를 적용해봅니다.
SELECT
history_id,
rental_history.car_id,
car_type,
start_date,
end_date,
daily_fee,
DATEDIFF(end_date, start_date) AS rental_duration
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS rental_history
INNER JOIN CAR_RENTAL_COMPANY_CAR AS car_information
ON rental_history.car_id = car_information.car_id
WHERE car_information.car_type = '트럭'
LIMIT 10;결과를 확인해보니 저희가 생각하는 값과 다르다는 것을 확인했습니다. 예를 들어 2022-08-03 이 start_date이고, 2022-08-04이 end_date이라하면 DATEDIFF함수는 1이라는 값을 내뱉습니다. 하지만 대여 기간의 경우는 2일이 되어야 합니다. 따라서 아래와 같이 DATEDIFF의 결과에 1을 더해줍니다.
SELECT
history_id,
rental_history.car_id,
car_type,
start_date,
end_date,
daily_fee,
(DATEDIFF(end_date, start_date) + 1)AS rental_duration
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS rental_history
INNER JOIN CAR_RENTAL_COMPANY_CAR AS car_information
ON rental_history.car_id = car_information.car_id
WHERE car_information.car_type = '트럭'
LIMIT 10;이제 이를 이용해서 할인율을 계산하면 될것으로 보입니다. 그런데, CAR_RENTAL_COMPANY_DISCOUNT_PLAN을 보게되면 DURATION_TYPE이 N일 이상이런식으로 작성되어 있는 것을 확인할 수 있습니다. DURATION_TYPE 에는 '7일 이상' (대여 기간이 7일 이상 30일 미만인 경우), '30일 이상' (대여 기간이 30일 이상 90일 미만인 경우), '90일 이상' (대여 기간이 90일 이상인 경우)의 경우만 존재한다고 합니다. 따라서 위의 결과를 아래와 같이 변경해 줄 수 있습니다.
SELECT
history_id,
rental_history.car_id,
car_type,
start_date,
end_date,
daily_fee,
(DATEDIFF(end_date, start_date) + 1)AS rental_duration,
CASE
WHEN (DATEDIFF(end_date, start_date) + 1) >= 90
THEN '90일 이상'
WHEN (DATEDIFF(end_date, start_date) + 1) >= 30
THEN '30일 이상'
WHEN (DATEDIFF(end_date, start_date) + 1) >= 7
THEN '7일 이상'
ELSE '7일 미만'
END AS rental_duration
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS rental_history
INNER JOIN CAR_RENTAL_COMPANY_CAR AS car_information
ON rental_history.car_id = car_information.car_id
WHERE car_information.car_type = '트럭'
LIMIT 10;3. 대여 기록에는 금액(할인 적용된)이 들어가야 한다.
이제 대여 기록과 할인 정보를 연결하면 되는데 연결 조건에 대해 고민이 필요합니다. 1번의 결과와 CAR_RENTAL_COMPANY_DISCOUNT_PLAN을 연결하기 위해서는 조건이 아래와 같습니다.
- 차종이 같다.
- 대여 기간 종류가 같다.
위의 2가지 조건을 만족하는 것이 ON절로 가면 됩니다. 그렇다면 JOIN의 형태는 어떻게 되어야 할까요? 예를 들어 대여 기록에 트럭이 있지만, 할인 기록에는 해당 차종이 없다고 해봅시다. 이 때 대여 기록에 대한 데이터는 삭제되면 안됩니다. 따라서 INNER가 아닌 LEFT가 사용 되어야 합니다. 이 때 중요한 것은 조건절을 잘 설정해야 1:1 대응이 되어서 데이터가 중복이 일어나지 않게됩니다. 이를 쿼리로 바꾸면 아래와 같습니다.(제가 사용하기 편한 서브쿼리로 진행했습니다.)
SELECT
*
FROM (
SELECT
history_id,
rental_history.car_id,
car_type,
start_date,
end_date,
daily_fee,
(DATEDIFF(end_date, start_date) + 1)AS rental_duration,
CASE
WHEN (DATEDIFF(end_date, start_date) + 1) >= 90
THEN '90일 이상'
WHEN (DATEDIFF(end_date, start_date) + 1) >= 30
THEN '30일 이상'
WHEN (DATEDIFF(end_date, start_date) + 1) >= 7
THEN '7일 이상'
ELSE '7일 미만'
END AS duration_type
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS rental_history
INNER JOIN CAR_RENTAL_COMPANY_CAR AS car_information
ON rental_history.car_id = car_information.car_id
WHERE car_information.car_type = '트럭'
) AS rental_history
LEFT JOIN (
SELECT
car_type,
duration_type,
discount_rate
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
) AS rental_discount_plan
ON rental_history.car_type = rental_discount_plan.car_type
AND rental_history.duration_type = rental_discount_plan.duration_type
LIMIT 10;이렇게 했을 때, 저희가 생각한 대로 데이터 연결이 잘 이루어짐을 확인하였습니다.
이제 할인된 금액을 구하기 위해서는 daily_fee에서 (100 - discount_rate) / 100을 곱해주면 됩니다. 그 후, 렌탈한 기간 rental_duration을 곱해주면 원하는 할인된 금액을 구할 수 있게 됩니다.
이를 쿼리로 나타내면 아래와 같습니다.
SELECT
*,
(100 - discount_rate) AS non_discount_rate,
rental_duration * daily_fee * ((100 - discount_rate) / 100) AS fee
FROM (
SELECT
history_id,
rental_history.car_id,
car_type,
start_date,
end_date,
daily_fee,
(DATEDIFF(end_date, start_date) + 1)AS rental_duration,
CASE
WHEN (DATEDIFF(end_date, start_date) + 1) >= 90
THEN '90일 이상'
WHEN (DATEDIFF(end_date, start_date) + 1) >= 30
THEN '30일 이상'
WHEN (DATEDIFF(end_date, start_date) + 1) >= 7
THEN '7일 이상'
ELSE '7일 미만'
END AS duration_type
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS rental_history
INNER JOIN CAR_RENTAL_COMPANY_CAR AS car_information
ON rental_history.car_id = car_information.car_id
WHERE car_information.car_type = '트럭'
) AS rental_history
LEFT JOIN (
SELECT
car_type,
duration_type,
discount_rate
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
) AS rental_discount_plan
ON rental_history.car_type = rental_discount_plan.car_type
AND rental_history.duration_type = rental_discount_plan.duration_type
LIMIT 10;여기서 보이는 점은 discount_rate이 NULL인 경우 fee를 구할 수 없음입니다. 이를 해결하기 위해서는 null일 경우 discount_rate를 0으로 치환하면 됩니다. 또한 소숫점으로 나오게 되는데 이를 정수형으로 치환하면 원하는 결과가 나오게 됩니다. 이를 쿼리로 변환하면 아래와 같습니다.
SELECT
*,
(100 - discount_rate) AS non_discount_rate,
CAST(
rental_duration * daily_fee * ((100 - ifnull(discount_rate,0)) / 100) AS DECIMAL
) AS fee
FROM (
SELECT
history_id,
rental_history.car_id,
car_type,
start_date,
end_date,
daily_fee,
(DATEDIFF(end_date, start_date) + 1)AS rental_duration,
CASE
WHEN (DATEDIFF(end_date, start_date) + 1) >= 90
THEN '90일 이상'
WHEN (DATEDIFF(end_date, start_date) + 1) >= 30
THEN '30일 이상'
WHEN (DATEDIFF(end_date, start_date) + 1) >= 7
THEN '7일 이상'
ELSE '7일 미만'
END AS duration_type
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS rental_history
INNER JOIN CAR_RENTAL_COMPANY_CAR AS car_information
ON rental_history.car_id = car_information.car_id
WHERE car_information.car_type = '트럭'
) AS rental_history
LEFT JOIN (
SELECT
car_type,
duration_type,
discount_rate
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
) AS rental_discount_plan
ON rental_history.car_type = rental_discount_plan.car_type
AND rental_history.duration_type = rental_discount_plan.duration_type
LIMIT 10;4. 최종 결과에는 두 가지 컬럼 대여 기록 ID(HISTORY_ID), 대여 금액(FEE)만 있으면 된다.
이제 3의 결과에서 불필요한 컬럼을 날리면 아래와 같습니다.
SELECT
history_id,
CAST(
rental_duration * daily_fee * ((100 - ifnull(discount_rate,0)) / 100) AS DECIMAL
) AS fee
FROM (
SELECT
history_id,
rental_history.car_id,
car_type,
start_date,
end_date,
daily_fee,
(DATEDIFF(end_date, start_date) + 1)AS rental_duration,
CASE
WHEN (DATEDIFF(end_date, start_date) + 1) >= 90
THEN '90일 이상'
WHEN (DATEDIFF(end_date, start_date) + 1) >= 30
THEN '30일 이상'
WHEN (DATEDIFF(end_date, start_date) + 1) >= 7
THEN '7일 이상'
ELSE '7일 미만'
END AS duration_type
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS rental_history
INNER JOIN CAR_RENTAL_COMPANY_CAR AS car_information
ON rental_history.car_id = car_information.car_id
WHERE car_information.car_type = '트럭'
) AS rental_history
LEFT JOIN (
SELECT
car_type,
duration_type,
discount_rate
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
) AS rental_discount_plan
ON rental_history.car_type = rental_discount_plan.car_type
AND rental_history.duration_type = rental_discount_plan.duration_type;5. 최종 결과는 대여 기록과 금액에 대해 정렬되어야 한다.(FEE DESC, HISTORY_ID DESC)
이제 마지막으로 결과에서 정렬하면 됩니다. 저의 경우는 주로 컬럼명 보다 번호로 진행을 합니다. 이는 좋지 않은 습관일 수도 있습니다...ㅎㅎ
SELECT
history_id,
CAST(
rental_duration * daily_fee * ((100 - ifnull(discount_rate,0)) / 100) AS DECIMAL
) AS fee
FROM (
SELECT
history_id,
rental_history.car_id,
car_type,
start_date,
end_date,
daily_fee,
(DATEDIFF(end_date, start_date) + 1)AS rental_duration,
CASE
WHEN (DATEDIFF(end_date, start_date) + 1) >= 90
THEN '90일 이상'
WHEN (DATEDIFF(end_date, start_date) + 1) >= 30
THEN '30일 이상'
WHEN (DATEDIFF(end_date, start_date) + 1) >= 7
THEN '7일 이상'
ELSE '7일 미만'
END AS duration_type
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS rental_history
INNER JOIN CAR_RENTAL_COMPANY_CAR AS car_information
ON rental_history.car_id = car_information.car_id
WHERE car_information.car_type = '트럭'
) AS rental_history
LEFT JOIN (
SELECT
car_type,
duration_type,
discount_rate
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
) AS rental_discount_plan
ON rental_history.car_type = rental_discount_plan.car_type
AND rental_history.duration_type = rental_discount_plan.duration_type
ORDER BY 2 DESC, 1 DESC;EXPLAIN으로 쿼리 효율 확인
해당 쿼리는 ALL로 풀스캔이지만, 인덱스 탈 수 없는 부분이 있어서 해당 부분은 넘어갔습니다.
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | car_information | ALL | PRIMARY | 30 | 10 | Using where; Using temporary; Using filesort | ||||
| 1 | SIMPLE | rental_history | ALL | 162 | 10 | Using where; Using join buffer (hash join) | |||||
| 1 | SIMPLE | CAR_RENTAL_COMPANY_DISCOUNT_PLAN | ALL | 15 | 100 | Using where; Using join buffer (hash join) |
결과
