[ 알고리즘 ] LeetCode 1645. Hopper Company Queries II

블로그를 이전 중이라 완료되기 전까지는 벨로그에 작성할 계획입니다.
이후 모든 글은 https://weekwith.me 에 작성 예정이니 다른 글이 궁금하시다면 해당 링크를 통해 방문해주세요.

본 글은 [ LeetCode ] 1645. Hopper Company Queries II를 풀고 작성한 글입니다.

문제

테이블

Table: Drivers

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| driver_id   | int     |
| join_date   | date    |
+-------------+---------+
driver_id is the primary key for this table.
Each row of this table contains the driver's ID and the date they joined the Hopper company.

Table: Rides

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| ride_id      | int     |
| user_id      | int     |
| requested_at | date    |
+--------------+---------+
ride_id is the primary key for this table.
Each row of this table contains the ID of a ride, the user's ID that requested it, and the day they requested it.
There may be some ride requests in this table that were not accepted.

Table: AcceptedRides

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| ride_id       | int     |
| driver_id     | int     |
| ride_distance | int     |
| ride_duration | int     |
+---------------+---------+
ride_id is the primary key for this table.
Each row of this table contains some information about an accepted ride.
It is guaranteed that each accepted ride exists in the Rides table.

요구사항

Write an SQL query to report the percentage of working drivers ( working_percentage ) for each month of 2020 where:

Note that if the number of available drivers during a month is zero, we consider the working_percentage to be 0 .

Return the result table ordered by month in ascending order, where month is the month's number (January is 1 , February is 2 , etc.). Round working_percentage to the nearest 2 decimal places .

풀이

접근법

처음에 이전 [ 알고리즘 ] LeetCode 1635. Hopper Company Queries I
문제와 동일하게 접근해서 단지 나누기만 하면 되는 걸로 생각했다. 그런데 제출결과 몇 가지 테스트케이스를 실패했는데 중복되는 driver_id 필드의 값 때문이었다.

나의 풀이

접근법을 토대로 푼 쿼리 결과는 아래와 같다.

이때 유의할 점은 횟수를 셈해줄 때 중복되는 driver_id 필드의 값을 제거해주기 위해 DISTINCT 키워드를 사용하는 것과 추가적으로 MySQL에서 NULL 값에 대한 모든 사칙연산의 결괏값은 NULL 이기 때문에 이를 다루기 위해 IFNULL 함수를 사용하는 부분이다.

WITH RECURSIVE Months (month) AS (
    SELECT 1 AS month
    UNION ALL
    SELECT month + 1 AS month
    FROM Months
    WHERE month BETWEEN 1 AND 11
), AvailableDrivers (month, available_drivers_count) AS (
    SELECT
        Months.month,
        SUM(available_drivers_count) AS available_drivers_count
    FROM Months
    LEFT JOIN (
        SELECT
            IF(YEAR(join_date) <> 2020, 1, MONTH(join_date)) AS month,
            COUNT(driver_id) AS available_drivers_count
        FROM Drivers
        WHERE YEAR(join_date) <= 2020
        GROUP BY IF(YEAR(join_date) <> 2020, 1, MONTH(join_date))
    ) AS AvailableDriversCount
    ON Months.month >= AvailableDriversCount.month
    GROUP BY Months.month
), AcceptedDrivers (month, working_percentage) AS (
    SELECT
        AvailableDrivers.month,
        ROUND(
            IFNULL(AcceptedDriversCount.available_drivers_count / AvailableDrivers.available_drivers_count, 0) * 100,
            2
        ) AS working_percentage
    FROM AvailableDrivers
    LEFT JOIN (
        SELECT
            MONTH(Rides.requested_at) AS month,
            COUNT(DISTINCT AcceptedRides.driver_id) AS available_drivers_count
        FROM Rides
        JOIN AcceptedRides
        USING (ride_id)
        WHERE YEAR(Rides.requested_at) = 2020
        GROUP BY MONTH(Rides.requested_at)
    ) AS AcceptedDriversCount
    USING (month)
)

SELECT
    month,
    working_percentage
FROM AcceptedDrivers
ORDER BY month ASC;

기타

기본적으로 대부분의 프로그래밍 언어는 만약 어떤 수를 0 으로 나눌 경우 오류를 반환한다. MySQL 또한 마찬가지인데 유의할 점은 이를 어떻게 다룰 것인지 여러 가지 방법을 사용자가 SQL 모드를 통해 직접 선택할 수 있다는 것이다.

기본적으로 엄격한 SQL 모드(Strict SQL Mode)와 ERROR_FOR_DIVISION_BY_ZERO 옵션 선택에 따라 아래와 같이 그 결과가 달라진다.

더 자세한 내용은 MySQL 공식문서인 5.1.11 Server SQL Modes: ERROR_FOR_DIVISION_BY_ZERO 부분을 확인하길 바란다.

1. ERROR_FOR_DIVISION_BY_ZERO 를 끈 경우

먼저 ERROR_FOR_DIVISION_BY_ZERO 옵션을 끈 경우 결괏값으로 NULL 을 반환하며 어떠한 경고 또는 오류 문구를 보여주지 않는다.

2. ERROR_FOR_DIVISION_BY_ZERO 를 켠 경우

다음으로 ERROR_FOR_DIVISION_BY_ZERO 옵션을 켠 경우 결괏값으로 NULL 값을 반환하며 이때 경고(Warning) 문구 또한 함께 반환한다.

3. ERROR_FOR_DIVISION_BY_ZERO 및 엄격한 SQL 모드를 전부 켠 경우

ERROR_FOR_DIVISION_BY_ZERO 옵션은 물론 엄격한 SQL 모드까지 함께 킨 경우 연산 자체를 수행하지 않으며 오류(Error)를 문구와 함께 반환한다.

그러나 이때 INSERT IGNORE 또는 UPDATE IGNORE 과 같이 IGNORE 키워드를 사용할 경우 NULL 값과 함께 경고(Warning) 문구를 반환한다.