[Day 15] solvesql | Advent of SQL 2024 | 폐쇄할 따릉이 정류소 찾기 2⭐🔥(논리)

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

 

✔️0211 1015 복습
확실히 2번째 풀어볼 때 훨씬 효율적이고 논리적으로 쿼리를 작성함 -> 아직도 완전 부족함 😵‍💫

문제 조건

1. 2019년 10월 한 달 동안 정류소에서 발생한 대여/반납 건수가 2018년 10월 같은 정류소에서 발생한 대여/반납 건수의 50% 이하인 정류소를 출력

2. 2018년 10월 또는 2019년 10월 한 달간 대여/반납 건수가 0건인 정류소는 결과에서 제외

 

Solution

 

✅강사님

/*
1. 정류소별 18년, 19년 반납 건수
2. 정류소별 18년, 19년 대여 건수

뭐 9월 대여 10월 반납, 10월에 대여 10월에 반납, 10월에 대여 11월에 반납 등 복잡하게 경우를 따질 이유가 없음
*/

WITH return_stats AS (
  SELECT return_station_id AS station_id
       , SUM(CASE WHEN return_at BETWEEN '2018-10-01 00:00:00' AND '2018-10-31 23:59:59' THEN 1 ELSE 0 END) AS return_2018
       , SUM(CASE WHEN return_at BETWEEN '2019-10-01 00:00:00' AND '2019-10-31 23:59:59' THEN 1 ELSE 0 END) AS return_2019
  FROM rental_history
  WHERE return_at BETWEEN '2018-10-01 00:00:00' AND '2018-10-31 23:59:59'
     OR return_at BETWEEN '2019-10-01 00:00:00' AND '2019-10-31 23:59:59'
  GROUP BY return_station_id
  -- LIMIT 10
), rent_stats AS (
  SELECT rent_station_id AS station_id
       , SUM(CASE WHEN rent_at BETWEEN '2018-10-01 00:00:00' AND '2018-10-31 23:59:59' THEN 1 ELSE 0 END) AS rent_2018
       , SUM(CASE WHEN rent_at BETWEEN '2019-10-01 00:00:00' AND '2019-10-31 23:59:59' THEN 1 ELSE 0 END) AS rent_2019
  FROM rental_history
  WHERE rent_at BETWEEN '2018-10-01 00:00:00' AND '2018-10-31 23:59:59'
     OR rent_at BETWEEN '2019-10-01 00:00:00' AND '2019-10-31 23:59:59'
  GROUP BY rent_station_id
)
SELECT station.station_id
     , name
     , local
     , ROUND((rent_2019 + return_2019) * 100.0 / (rent_2018 + return_2018), 2) AS usage_pct
FROM return_stats
  JOIN rent_stats ON return_stats.station_id = rent_stats.station_id
  JOIN station ON return_stats.station_id = station.station_id
WHERE (rent_2019 + return_2019) != 0 -- (AUB)^c = A^c Π B^c BY 드모르간 법칙
  AND (rent_2018 + return_2018) != 0
  AND (rent_2019 + return_2019) * 100.0 / (rent_2018 + return_2018) <= 50

 

 

✅MySQL (실행시간 : 약 2초)

/*
  10월에 대여 또는 반납이 발생한다 -> 10월에 대여해서 11월에 반납한 경우도 있을 것이고, 9월에 대여해서 10월에 반납한 경우도 있을텐데 싹 다 포함이 되는 것임
  즉, 꼭 10월 한 달동안에 발생한 대여 또는 반납이 아니어도 된다

  10월에 대여한 경우 + 10월에 반납한 경우
*/

WITH usage_2018 AS ( -- 18년 10월에 각 정류소에서 발생한 대여 건수 + 반납 건수 
  SELECT rent_station_id AS station_id
       , COUNT(*) AS cnt_2018
  FROM rental_history
  WHERE rent_at BETWEEN '2018-10-01 00:00:00' AND '2018-10-31 23:59:59'
  GROUP BY rent_station_id

  UNION ALL

  SELECT return_station_id
       , COUNT(*) 
  FROM rental_history
  WHERE return_at BETWEEN '2018-10-01 00:00:00' AND '2018-10-31 23:59:59'
  GROUP BY return_station_id

  ORDER BY station_id

), usage_2019 AS (
  SELECT rent_station_id AS station_id
       , COUNT(*) AS cnt_2019
  FROM rental_history
  WHERE rent_at BETWEEN '2019-10-01 00:00:00' AND '2019-10-31 23:59:59'
  GROUP BY rent_station_id

  UNION ALL

  SELECT return_station_id
       , COUNT(*) 
  FROM rental_history
  WHERE return_at BETWEEN '2019-10-01 00:00:00' AND '2019-10-31 23:59:59'
  GROUP BY return_station_id

  ORDER BY station_id

), agg_usage_2018 AS (
  SELECT station_id
       , SUM(cnt_2018) AS cnt_2018
  FROM usage_2018
  GROUP BY station_id

), agg_usage_2019 AS (
  SELECT station_id
       , SUM(cnt_2019) AS cnt_2019
  FROM usage_2019
  GROUP BY station_id

), agg AS (
  SELECT a.station_id
       , ROUND(cnt_2019*100.0 / cnt_2018, 2) AS usage_pct 
  FROM agg_usage_2018 a
    LEFT JOIN agg_usage_2019 aa ON a.station_id = aa.station_id
)
SELECT a.station_id
     , s.name
     , s.local
     , a.usage_pct
FROM agg a
  JOIN station s ON a.station_id = s.station_id
WHERE a.usage_pct <= 50

 

 

✅SQLite (실행시간 : 약 20초)

WITH T1 AS ( -- 대여 장소와 반납 장소가 다른 경우가 있기 때문에 각각을 고려
  SELECT rent_station_id as station_id,
         rent_at as used_at
  FROM rental_history

  UNION ALL

  SELECT return_station_id,
         return_at
  FROM rental_history
  
  ), used_count AS ( -- 각 정류소에서 대여/반납이 일어난 건수 통합하여 집계
  SELECT station_id,
         strftime('%Y-%m', used_at) as ym,
         COUNT(*) as cnt
  FROM T1
  GROUP BY 1, 2
  
  ), used_2018 AS ( 
  SELECT station_id,
         cnt as cnt_2018 
  FROM used_count
  WHERE ym = '2018-10'
  
  ), used_2019 AS ( 
  SELECT station_id, 
         cnt as cnt_2019
  FROM used_count
  WHERE ym = '2019-10'
 
 ), final AS ( -- 2018년 10월 또는 2019년 10월 한 달간 대여/반납 건수가 0건인 정류소 제외
  SELECT a.station_id,
         a.cnt_2018,
         b.cnt_2019,
         ROUND((b.cnt_2019 * 100.0)/a.cnt_2018, 2) as usage_pct
  FROM used_2018 a
  JOIN used_2019 b ON a.station_id = b.station_id
  WHERE a.cnt_2018 > 0 AND b.cnt_2019 > 0

)
SELECT f.station_id,
       s.name, 
       s.local,
       f.usage_pct
FROM final f
  JOIN station s ON f.station_id = s.station_id
WHERE f.usage_pct <= 50

 

▶ T1 CTE:

대여 장소와 반납 장소의 데이터를 모두 통합하여 하나의 컬럼으로 생성한다.

 

▶ used_count CTE:

각 station_id별로 연-월 단위의 대여 및 반납 횟수를 집계한다. 

 

▶ used_2018 CTE:

2018년 10월 한 달간 대여/반납 건수 필터링한다.

 

▶ used_2019 CTE:

2019년 10월 한 달간 대여/반납 건수 필터링한다.

 

▶ final CTE:

2018년 10월 대비 2019년 10월 대여/반납 건수의 비율을 계산한다. 

2018년 10월 또는 2019년 10월 한 달간 대여/반납 건수가 0건인 경우는 제외한다. 

 

▶ 최종 SELECT:

정류소 이름과 위치를 추가하여, 2018년 10월 대비 2019년 10월 사용 비율이 50% 이하인 정류소를 출력한다. 

 

피드백

이 문제에 접근하면서 내가 간과했던 2가지가 있다.

 

1) 대여 정류소와 반납 정류소가 동일한 경우와 그렇지 않은 경우가 존재한다는 점

2) 9월 30일 대여, 10월 1일 반납인 경우와 10월 31일 대여, 11월 1일 반납인 경우가 있다는 점

 

따라서 처음에 반납 시각과 반납 정류소를 고려하지 않고, 대여 시각과 대여 정류소만을 고려하여 접근한 것이 가장 큰 문제였다.