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

문제

공유 자전거 서비스인 따릉이를 운영하는 팀에서는 지역별로 이용률이 감소하고 있는 따릉이 정류소를 폐쇄해 따릉이 서비스의 운영비용을 줄이려고 합니다. 따릉이 데이터를 다루는 당신은 정류소 정보가 담겨있는 station 테이블과 대여 기록이 담겨 있는 rental_history 테이블을 확인해 폐쇄를 검토할 따릉이 정류소 목록을 추려내는 업무를 받았습니다.

폐쇄할 정류소를 검토하기 위해 2019년 10월 한 달 동안 정류소에서 발생한 대여/반납 건수가 2018년 10월 같은 정류소에서 발생한 대여/반납 건수의 50% 이하인 정류소를 출력하는 쿼리를 작성해주세요. 2018년 10월 또는 2019년 10월 한 달간 대여/반납 건수가 0건인 정류소는 이미 폐쇄된 정류소이거나 새로이 생긴 정류소 일 수 있으므로 쿼리 결과에 포함되지 않도록 해주세요. 쿼리 결과에는 아래 컬럼이 포함되어 있어야 합니다.

 

결론부터 말하면 여태 푼 solvesql 문제 중에서는 가장 어려웠다. 우선 나의 초기 쿼리

WITH
  TEMP1 AS (
    SELECT
      rent_station_id,
      COUNT() AS cnt
    FROM
      rental_history
    WHERE
      strftime ("%Y-%m", rent_at) = "2018-10"
    GROUP BY
      rent_station_id
  ),
  TEMP2 AS (
    SELECT
      return_station_id,
      COUNT() AS cnt2
    FROM
      rental_history
    WHERE
      strftime ("%Y-%m", return_at) = "2018-10"
    GROUP BY
      return_station_id
  ),
  TEMP3 AS (
    SELECT
      rent_station_id,
      COUNT() AS cnt3
    FROM
      rental_history
    WHERE
      strftime ("%Y-%m", rent_at) = "2019-10"
    GROUP BY
      rent_station_id
  ),
  TEMP4 AS (
    SELECT
      return_station_id,
      COUNT() AS cnt4
    FROM
      rental_history
    WHERE
      strftime ("%Y-%m", return_at) = "2019-10"
    GROUP BY
      return_station_id
  )
SELECT
  TEMP1.rent_station_id AS station_id,
  station.name,
  station.local,
  ROUND ( ( (cnt3 + cnt4) * 1.0 / (cnt + cnt2) * 100.0 ), 2 ) AS usage_pct
FROM
  TEMP2
  JOIN TEMP1 ON TEMP1.rent_station_id = TEMP2.return_station_id
  JOIN TEMP3 ON TEMP3.rent_station_id = TEMP1.rent_station_id
  JOIN TEMP4 ON TEMP4.return_station_id = TEMP1.rent_station_id
  JOIN station ON station.station_id = TEMP2.return_station_id
WHERE (cnt3 + cnt4) * 1.0 / (cnt + cnt2) * 100.0 <= 50 AND (cnt3 + cnt4 != 0 AND cnt + cnt2 != 0)

이 쿼리는 오답이다. 2018년에 대여는 했지만 반납은 하지않은 특수한 경우를 고려하지 않았기 때문이다. 이걸 해결하려면?

COALESCE 와 FULL OUTER JOIN을 사용해야 한다. 우선 정답 쿼리부터 보자

WITH
  TEMP1 AS (
    SELECT
      rent_station_id,
      COUNT(*) AS cnt
    FROM
      rental_history
    WHERE
      strftime ("%Y-%m", rent_at) = "2018-10"
    GROUP BY
      rent_station_id
  ),
  TEMP2 AS (
    SELECT
      return_station_id,
      COUNT(*) AS cnt2
    FROM
      rental_history
    WHERE
      strftime ("%Y-%m", return_at) = "2018-10"
    GROUP BY
      return_station_id
  ),
  TEMP3 AS (
    SELECT
      rent_station_id,
      COUNT(*) AS cnt3
    FROM
      rental_history
    WHERE
      strftime ("%Y-%m", rent_at) = "2019-10"
    GROUP BY
      rent_station_id
  ),
  TEMP4 AS (
    SELECT
      return_station_id,
      COUNT(*) AS cnt4
    FROM
      rental_history
    WHERE
      strftime ("%Y-%m", return_at) = "2019-10"
    GROUP BY
      return_station_id
  )
SELECT
  COALESCE(TEMP1.rent_station_id, TEMP2.return_station_id) AS station_id,
  station.name,
  station.local,
  ROUND ( ( (COALESCE(cnt3, 0) + COALESCE(cnt4, 0)) * 1.0 / (COALESCE(cnt, 0) + COALESCE(cnt2, 0)) * 100.0 ), 2 ) AS usage_pct
FROM
  TEMP1
  FULL OUTER JOIN TEMP2 ON TEMP1.rent_station_id = TEMP2.return_station_id
  LEFT JOIN TEMP3 ON COALESCE(TEMP1.rent_station_id, TEMP2.return_station_id) = TEMP3.rent_station_id
  LEFT JOIN TEMP4 ON COALESCE(TEMP1.rent_station_id, TEMP2.return_station_id) = TEMP4.return_station_id
  JOIN station ON station.station_id = COALESCE(TEMP1.rent_station_id, TEMP2.return_station_id)
WHERE (COALESCE(cnt3, 0) + COALESCE(cnt4, 0)) * 1.0 / (COALESCE(cnt, 0) + COALESCE(cnt2, 0)) * 100.0 <= 50 
  AND (COALESCE(cnt3, 0) + COALESCE(cnt4, 0) > 0 AND COALESCE(cnt, 0) + COALESCE(cnt2, 0) > 0)

COALESCE

COALESCE는 SQL에서 NULL 값을 처리하는 함수다. 여러 개 값중에서 첫 번째로 NULL이 아닌 값을 반환한다.

모든 값이 NULL이면 NULL을 반환한다. 이 쿼리에서는 왜 사용할까? LEFT 쿼리를 썼을 떄 겹치지 않는 부분이 있으면 NULL을 반환하는데 이것이 문제다. 만약 정류소 A에 2018년 대여는 있지만 반잡이 없으면 TEMP2에 정류소 A가 없어서 cnt2가 NULL이 된다. cnt + cnt2는 10 + NULL = NULL 이 되어서 계산 오류가 난다. 그러나 COALESCE(cnt, 0) + COALESCE(cnt2, 0)로 하면 10 + 0 = 10으로 정상 계산이 된다. 

그리고 아래 부분이 해깔릴건데 이부분은 클로드 도움을 받았다. 시각화 설명이다.

🔍 SQL 쿼리 실행 여정

1

원시 데이터에서 4개의 집계 테이블 생성

먼저 rental_history 테이블에서 필요한 데이터를 추출합니다. 마치 큰 창고에서 특정 시기의 상품들을 분류하는 것과 같습니다.

TEMP1 (2018-10 대여)

station_id | cnt

101 | 50

102 | 30

103 | 25

TEMP2 (2018-10 반납)

station_id | cnt2

101 | 40

102 | 20

104 | 15

TEMP3 (2019-10 대여)

station_id | cnt3

101 | 20

102 | 10

103 | 5

TEMP4 (2019-10 반납)

station_id | cnt4

101 | 15

102 | 8

104 | 5

🎯 핵심 포인트: 각 TEMP 테이블은 서로 다른 정류소 조합을 가질 수 있습니다. 예를 들어, 정류소 103은 2018년에는 대여만 있고, 정류소 104는 반납만 있습니다.

⬇️

2

TEMP1과 TEMP2를 FULL OUTER JOIN으로 결합

2018년의 대여와 반납 데이터를 하나로 합칩니다. FULL OUTER JOIN은 두 테이블의 모든 정류소를 보존합니다.

TEMP1

101 | 50

102 | 30

103 | 25

⚡ FULL OUTER JOIN ⚡

TEMP2

101 | 40

102 | 20

104 | 15

결합 결과 (2018년)

station_id | cnt | cnt2

101 | 50 | 40

102 | 30 | 20

103 | 25 | NULL

104 | NULL | 15

✅ 마법의 순간: 정류소 103과 104도 모두 보존됩니다! 한쪽에만 있는 데이터는 NULL로 채워지지만 정보가 사라지지 않습니다.

⬇️

3

LEFT JOIN으로 2019년 데이터 연결

이제 2018년 결과에 2019년 데이터를 LEFT JOIN으로 연결합니다. 2018년에 있던 모든 정류소를 유지하면서 2019년 데이터를 추가합니다.

LEFT JOIN TEMP3 ON COALESCE(TEMP1.rent_station_id, TEMP2.return_station_id) = TEMP3.rent_station_id LEFT JOIN TEMP4 ON COALESCE(TEMP1.rent_station_id, TEMP2.return_station_id) = TEMP4.return_station_id

최종 결합 결과

station_id | cnt | cnt2 | cnt3 | cnt4

101 | 50 | 40 | 20 | 15

102 | 30 | 20 | 10 | 8

103 | 25 | NULL | 5 | NULL

104 | NULL | 15 | NULL | 5

🔄 COALESCE 동작: COALESCE(TEMP1.rent_station_id, TEMP2.return_station_id)는 첫 번째가 NULL이면 두 번째 값을 사용합니다. 정류소 104의 경우 TEMP1이 NULL이므로 TEMP2의 return_station_id(104)를 사용합니다.

⬇️

4

COALESCE로 NULL 값을 0으로 변환

계산에 앞서 모든 NULL 값을 0으로 바꿔줍니다. 이는 수학 계산에서 NULL이 전체 결과를 NULL로 만드는 것을 방지합니다.

원본 값
cnt3 = NULL
cnt4 = NULL

COALESCE 적용
COALESCE(NULL, 0) = 0
COALESCE(NULL, 0) = 0

계산 가능
0 + 0 = 0
(정상 계산)

COALESCE 적용 후

station_id | 2018총합 | 2019총합 | 비율

101 | 90 (50+40) | 35 (20+15) | 38.9%

102 | 50 (30+20) | 18 (10+8) | 36.0%

103 | 25 (25+0) | 5 (5+0) | 20.0%

104 | 15 (0+15) | 5 (0+5) | 33.3%

⬇️

5

WHERE 절로 최종 필터링

이제 두 가지 조건을 동시에 만족하는 정류소만 선별합니다. 마치 두 개의 체를 통과하는 것과 같습니다.

WHERE (COALESCE(cnt3, 0) + COALESCE(cnt4, 0)) * 1.0 / (COALESCE(cnt, 0) + COALESCE(cnt2, 0)) * 100.0 <= 50 AND (COALESCE(cnt3, 0) + COALESCE(cnt4, 0) > 0 AND COALESCE(cnt, 0) + COALESCE(cnt2, 0) > 0)

조건 1: 이용량 50% 이하
2019년 이용량 ÷ 2018년 이용량 × 100 ≤ 50

조건 2: 두 해 모두 이용량 존재
2018년 이용량 > 0 AND 2019년 이용량 > 0

조건 검사 결과

station_id | 비율 | 조건1 | 조건2 | 최종결과

101 | 38.9% | ✅ | ✅ | 선택됨

102 | 36.0% | ✅ | ✅ | 선택됨

103 | 20.0% | ✅ | ✅ | 선택됨

104 | 33.3% | ✅ | ✅ | 선택됨

⬇️

🎉 최종 결과

모든 조건을 만족하는 정류소들이 station 테이블과 JOIN되어
정류소명, 지역정보와 함께 최종 결과를 출력합니다!

선택된 정류소: 101, 102, 103, 104
폐쇄 검토 대상: 4개 정류소

💡

핵심 학습 포인트

1. FULL OUTER JOIN의 중요성: 데이터 손실 없이 모든 정류소를 보존합니다.

2. COALESCE의 역할: NULL 값으로 인한 계산 오류를 방지하고 의미 있는 결과를 만듭니다.

3. WHERE 절의 이중 검증: 비즈니스 요구사항(50% 이하)과 데이터 품질(0이 아닌 값) 모두를 확인합니다.

4. 단계적 접근: 복잡한 쿼리도 작은 단계들로 나누어 이해하면 명확해집니다.

문제는 이 쿼리는 쓰레기다. WITH을 4번이나 쓴건 심각한 성능 문제가 있다. 어떻게 최적화 해야할지 몰랐다. 이 부분도 클로드 도움을 받았다.

WITH station_usage AS (
  SELECT 
    COALESCE(rent_station_id, return_station_id) AS station_id,
    SUM(
      CASE 
        WHEN strftime("%Y-%m", COALESCE(rent_at, return_at)) = "2018-10" THEN 1 
        ELSE 0 
      END
    ) AS usage_2018,
    SUM(
      CASE 
        WHEN strftime("%Y-%m", COALESCE(rent_at, return_at)) = "2019-10" THEN 1 
        ELSE 0 
      END
    ) AS usage_2019
  FROM (
    SELECT rent_station_id, NULL as return_station_id, rent_at, NULL as return_at
    FROM rental_history 
    WHERE strftime("%Y-%m", rent_at) IN ("2018-10", "2019-10")
    UNION ALL
    SELECT NULL as rent_station_id, return_station_id, NULL as rent_at, return_at
    FROM rental_history 
    WHERE strftime("%Y-%m", return_at) IN ("2018-10", "2019-10")
  ) combined_data
  GROUP BY COALESCE(rent_station_id, return_station_id)
  HAVING usage_2018 > 0 AND usage_2019 > 0
)
SELECT 
  su.station_id,
  s.name,
  s.local,
  ROUND((su.usage_2019 * 100.0 / su.usage_2018), 2) AS usage_pct
FROM station_usage su
JOIN station s ON s.station_id = su.station_id
WHERE (su.usage_2019 * 100.0 / su.usage_2018) <= 50.0
ORDER BY usage_pct ASC;