[solvesql] 폐쇄할 따릉이 정류소 찾기 2

문제

정답

WITH a AS (
  SELECT rent_station_id,count(rent_station_id) rent_sum_19
  FROM rental_history
  WHERE rent_at BETWEEN '2019-10-01' AND '2019-11-01' 
  GROUP BY rent_station_id
),
b AS (
  SELECT return_station_id,count(return_station_id) return_sum_19
  FROM rental_history
  WHERE return_at BETWEEN '2019-10-01' AND '2019-11-01'
  GROUP BY return_station_id
),
c AS (
  SELECT rent_station_id,count(rent_station_id) rent_sum_18
  FROM rental_history
  WHERE rent_at BETWEEN '2018-10-01' AND '2018-11-01' 
  GROUP BY rent_station_id
),
d AS (
  SELECT return_station_id,count(return_station_id) return_sum_18
  FROM rental_history
  WHERE return_at BETWEEN '2018-10-01' AND '2018-11-01'
  GROUP BY return_station_id
)
SELECT c.rent_station_id station_id, e.name, e.local, round((100 * (ifnull(rent_sum_19,0) + ifnull(return_sum_19,0) +0.00)/(ifnull(rent_sum_18,0) + ifnull(return_sum_18,0) +0.00)),2) usage_pct
FROM c
JOIN d ON c.rent_station_id = d.return_station_id
LEFT JOIN a ON c.rent_station_id = a.rent_station_id
LEFT JOIN b ON c.rent_station_id = b.return_station_id
JOIN station e ON c.rent_station_id = e.station_id
WHERE 100 * (ifnull(rent_sum_19,0) + ifnull(return_sum_19,0) +0.00)/(ifnull(rent_sum_18,0) + ifnull(return_sum_18,0) +0.00) BETWEEN 1 AND 51

🚨 error

WITH a AS (
  SELECT rent_station_id,count(*) rent_sum_19
  FROM rental_history
  WHERE rent_at BETWEEN '2019-10-01' AND '2019-11-01' 
  GROUP BY rent_station_id
),
b AS (
  SELECT return_station_id,count(*) return_sum_19
  FROM rental_history
  WHERE return_at BETWEEN '2019-10-01' AND '2019-11-01'
  GROUP BY return_station_id
),
c AS (
  SELECT rent_station_id,count(*) rent_sum_18
  FROM rental_history
  WHERE rent_at BETWEEN '2018-10-01' AND '2018-11-01' 
  GROUP BY rent_station_id
),
d AS (
  SELECT return_station_id,count(*) return_sum_18
  FROM rental_history
  WHERE return_at BETWEEN '2018-10-01' AND '2018-11-01'
  GROUP BY return_station_id
)
SELECT a.rent_station_id, e.name, e.local, round(100 * (rent_sum_19 + return_sum_19 +0.00)/(rent_sum_18 + return_sum_18 +0.00),2) usage_pct
FROM a
JOIN b ON a.rent_station_id = b.return_station_id
JOIN c ON a.rent_station_id = c.rent_station_id
JOIN d ON a.rent_station_id = d.return_station_id
JOIN station e ON a.rent_station_id = e.station_id
WHERE 100 * (rent_sum_19 + return_sum_19 +0.00)/(rent_sum_18 + return_sum_18 +0.00) <= 50

이 코드에서 계속 에러가 났다.
정답 컬럼 수는 17개인데 16개의 컬럼만 출력되었다.

문제는 19년도 10월에 대여/반납하거나 18년도 10월에 대여/반납한 경우가 없을 경우, null로 처리되기 때문에,

round(100 * (rent_sum_19 + return_sum_19 +0.00)/(rent_sum_18 + return_sum_18 +0.00),2) usage_pct

이 코드에서 rent_sum_19 이 값이 있더라도, return_sum_19 이 값이 없어 null 값이면, 합했을 때 null이 되므로 값이 아예 null 처리가 되어버린다.
따라서 ifnull 을 사용해 null값 처리를 해주어야 했다.
ifnull → null일 때 0이 되도록 해주었다.

💁‍♀️ 다른 방식으로도 풀어보자 !