폐쇄할 따릉이 정류소 찾기 2
방법 1
-- 2019년 10월 한 달 동안 정류소에서 발생한 대여/반납 건수가
-- 2018년 10월 같은 정류소에서 발생한 대여/반납 건수의 50% 이하인 정류소를 출력하는 쿼리
-- 단, 2018년 10월 또는 2019년 10월 한 달간 대여/반납 건수가 0건인 정류소는 제외
WITH cal 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 cnt_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 cnt_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
UNION ALL
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 cnt_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 cnt_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
)
SELECT tmp.station_id
, s.name
, s.local
, ROUND(tmp.cnt_2019*100/tmp.cnt_2018, 2) AS usage_pct
FROM (
SELECT station_id
, SUM(cnt_2018) AS cnt_2018
, SUM(cnt_2019) AS cnt_2019
FROM cal
GROUP BY station_id
) AS tmp
INNER JOIN station AS s ON tmp.station_id = s.station_id
WHERE tmp.cnt_2018 > 0
AND tmp.cnt_2019 > 0
AND tmp.cnt_2019 <= tmp.cnt_2018*0.5
작성 순서
1. 2018년 10월, 2019년 10월만 필터링해서 대여정류소id X 기간별 대여 건수 구하기
2. 2018년 10월, 2019년 10월만 필터링해서 반납정류소id X 기간별 반납 건수 구하기
3. 1번과 2번 테이블을 합치기 (UNION ALL)
4. 3번에서 합친 테이블에서 대여/반납 구분없이 정류소id별로 그룹핑하여 총 대여/반납 건수 합계를 계산하기
5. station 테이블과 조인하면서, 건수가 0인 것 제외하고 & 2019년이 2018년보다 50% 이하인건만 필터링하여 최종 결과 출력
EXPLAIN으로 실행계획 확인해보기
방법 2
WITH tmp AS (
SELECT rent_station_id AS station_id -- 2018년 10월 대여 건수
, COUNT(*) AS cnt_2018
, 0 AS cnt_2019
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 rent_station_id AS station_id -- 2019년 10월 대여 건수
, 0 AS cnt_2018
, 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 AS station_id -- 2018년 10월 반납 건수
, COUNT(*) AS cnt_2018
, 0 AS cnt_2019
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
UNION ALL
SELECT return_station_id AS station_id -- 2019년 10월 반납 건수
, 0 AS cnt_2018
, COUNT(*) AS cnt_2019
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
)
SELECT agg.station_id
, s.name
, s.local
, ROUND(sum_2019*100/sum_2018, 2) AS usage_pct
FROM (
SELECT station_id
, SUM(cnt_2018) AS sum_2018
, SUM(cnt_2019) AS sum_2019
FROM tmp
GROUP BY station_id
) AS agg
INNER JOIN station AS s ON agg.station_id = s.station_id
WHERE sum_2018 > 0
AND sum_2019 > 0
AND sum_2019 <= sum_2018*0.5
작성 순서
1. 총 4개의 테이블을 생성
(1) 2018년 10월 대여정류소id별 대여 건수 구하기
(2) 2019년 10월 대여정류소id별 대여 건수 구하기
(3) 2018년 10월 반납정류소id별 반납 건수 구하기
(4) 2019년 10월 반납정류소id별 반납 건수 구하기
2. 위의 4개 테이블을 모두 합치기 (UNION ALL)
3. 2번에서 합친 테이블에서 대여/반납 구분없이 정류소id별로 그룹핑하여 총 대여/반납 건수 합계를 계산하기
4. station 테이블과 조인하면서, 건수가 0인 것 제외하고 & 2019년이 2018년보다 50% 이하인건만 필터링하여 최종 결과 출력
EXPLAIN으로 실행계획 확인해보기
실행계획 비교
| 항목 | 방법 1의 Query | 방법 2의 Query |
| 구조 | 복잡한 중첩 집계 | 단순한 UNION + 집계 |
| 성능 | 병렬 처리 구조지만 비용 높음 | 해시 집계 기반으로 비용 낮음 |
| 권장 여부 | ❌ 성능 및 유지보수 불리 | ✅ 효율적이고 명확한 로직 |
결론
- 방법 2의 Query 는 간결하고 빠르며, 실행 계획도 더 효율적
- 방법 1의 Query 는 성능은 떨어지지만, 병렬 처리에 의존하거나 복잡한 집계 로직이 필요한 상황에서 쓸 수 있을 것.