[PostgreSQL] solvesql LV.3 폐쇄할 따릉이 정류소 찾기 2

https://solvesql.com/problems/find-unnecessary-station-2/

https://solvesql.com/problems/find-unnecessary-station-2/

🔍 문제

2019년 10월 한 달 동안 따릉이 정류소에서 발생한 대여+반납 건수가 2018년 10월 한 달 동안 발생한 대여+반납 건수를 비교해서 50%미만인 정류소의 id, 이름, 자치구, 전 년 대비 비율을 출력하시요

---

🎯정답

-- 2018년 기록 합치기
with m2018 as (
select station_id, count(*) as cnt18
from (
select rent_station_id as station_id
from rental_history
where rent_at >= date '2018-10-01' and rent_at < date '2018-11-01'
UNION ALL
select return_station_id as station_id
FROM rental_history
where return_at >= date '2018-10-01' and return_at < date '2018-11-01'
)
group by station_id),

-- 2019년 기록 합치기
m2019 as (
select station_id, count(*) as cnt19
from (
select rent_station_id as station_id
from rental_history
where rent_at >= date '2019-10-01' and rent_at < date '2019-11-01'
UNION ALL
select return_station_id as station_id
FROM rental_history
where return_at >= date '2019-10-01' and return_at < date '2019-11-01'
)
group by station_id)

select
  a.station_id,
  s.name,
  s.local,
  round(100 * (b.cnt19::numeric / a.cnt18), 2) as usage_pct
from
  m2018 a join m2019 b on a.station_id = b.station_id
  join station s on a.station_id = s.station_id
where a.cnt18 > 0
  AND b.cnt19 > 0
  AND 100 * (b.cnt19::numeric / a.cnt18) <= 50

---

❓풀이

1. 2018년 10월의 대여, 반납 기록  합치기

-- 2018년 10월 대여+반납을 한 컬럼(station_id)로 모으기
SELECT rent_station_id   AS station_id
FROM rental_history
WHERE rent_at >= DATE '2018-10-01' AND rent_at < DATE '2018-11-01'
UNION ALL
SELECT return_station_id AS station_id
FROM rental_history
WHERE return_at >= DATE '2018-10-01' AND return_at < DATE '2018-11-01';

✅ 쿼리 결과

2018년 10월 동안 정류소별 대여,반납 기록을 보여줌

• 대여/반납 이라는 의미가 사실상 대여와 반납의 건수 합계를 의미하기 때문에 대여와 반납을 `UNION ALL`을 통해 각 대여소별 대여나 반납이 이뤄난 기록을 전부 보여주도록 한다.
• 이제 각 대여소별 그룹화하여 count하면 대여소별로 총 대여+반납 건수를 확인할 수 있다.

2. 2018년 대여소별 대여 + 반납 건수 CTE으로 테이블 생성하기

-- with문을 이용해서 대여소별(station_id) 대여+반납 건수 집계하기
WITH m2018 AS (
  SELECT rent_station_id   AS station_id
  FROM rental_history
  WHERE rent_at >= DATE '2018-10-01' AND rent_at < DATE '2018-11-01'
  UNION ALL
  SELECT return_station_id AS station_id
  FROM rental_history
  WHERE return_at >= DATE '2018-10-01' AND return_at < DATE '2018-11-01'
)
SELECT station_id, COUNT(*) AS cnt18
FROM m2018
GROUP BY station_id
ORDER BY cnt18 DESC

 

✅ 쿼리 결과

각 대여소별 대여+반납 건수가 생성되었다.

 

3. 2019년 데이터도 동일하게 대여소 별 대여 + 반납 건수 구하기

WITH m2019 AS (
  SELECT rent_station_id   AS station_id
  FROM rental_history
  WHERE rent_at >= DATE '2019-10-01' AND rent_at < DATE '2019-11-01'
  UNION ALL
  SELECT return_station_id AS station_id
  FROM rental_history
  WHERE return_at >= DATE '2019-10-01' AND return_at < DATE '2019-11-01'
)
SELECT station_id, COUNT(*) AS cnt19
FROM m2019
GROUP BY station_id;

 

✅ 쿼리 결과

2019년 데이터도 동일하게 작업

 

4. 2018년, 2019년 합계 테이블, 대여소 테이블 합치고 비율 구하기

-- 2018년 기록 합치기
with m2018 as (
select station_id, count(*) as cnt18
from (
select rent_station_id as station_id
from rental_history
where rent_at >= date '2018-10-01' and rent_at < date '2018-11-01'
UNION ALL
select return_station_id as station_id
FROM rental_history
where return_at >= date '2018-10-01' and return_at < date '2018-11-01'
)
group by station_id),

-- 2019년 기록 합치기
m2019 as (
select station_id, count(*) as cnt19
from (
select rent_station_id as station_id
from rental_history
where rent_at >= date '2019-10-01' and rent_at < date '2019-11-01'
UNION ALL
select return_station_id as station_id
FROM rental_history
where return_at >= date '2019-10-01' and return_at < date '2019-11-01'
)
group by station_id)

select
  a.station_id,
  s.name,
  s.local,
  round(100 * (b.cnt19::numeric / a.cnt18), 2) as usage_pct
from
  m2018 a join m2019 b on a.station_id = b.station_id
  join station s on a.station_id = s.station_id
where a.cnt18 > 0
  AND b.cnt19 > 0
  AND 100 * (b.cnt19::numeric / a.cnt18) <= 50

 

✅ 쿼리 결과

 

총 17개의 결과가 나와야 한다.

• 비율을 구하고 소수 둘째 자리까지 표현하기 위해 numeric 타입으로 변경해주고 나눠주면 된다.
• 마지막 where절에서 50 이하를 확인하지 못하고 계속 50 미만으로 계산하다가 16개만 출력되길래 한참을 찾았다.

아래 쿼리문은 union all을 사용하지 않고 처음에 시도했던 코드인데, 

with rent2018 as (
-- 2018년 10월 대여 기록
select rent_station_id as "station_id", count(*) as "rent_count"
from rental_history
where rent_at >= DATE '2018-10-01' and rent_at < date '2018-11-01'
group by rent_station_id),
-- 2018년 10월 반납 기록
return2018 as (
select return_station_id as "station_id", count(*) as "return_count"
from rental_history
where return_at >= DATE '2018-10-01' and return_at < date '2018-11-01'
group by return_station_id),
-- 2019년 10월 대여 기록
rent2019 as (
select rent_station_id as "station_id", count(*) as "rent_count"
from rental_history
where rent_at >= DATE '2019-10-01' and rent_at < date '2019-11-01'
group by rent_station_id),
-- 2019년 10월 반납 기록
return2019 as (
select return_station_id as "station_id", count(*) as "return_count"
from rental_history
where return_at >= DATE '2019-10-01' and return_at < date '2019-11-01'
group by return_station_id),
-- 4개 테이블 병합
merged_table as (
select
  a.station_id,
  (((c.rent_count + d.return_count)::float) / ((a.rent_count + b.return_count)::float)) as "usage_pct"
from
  rent2018 a join return2018 b
  on a.station_id = b.station_id
  join rent2019 c
  on a.station_id = c.station_id
  join return2019 d
  on a.station_id = d.station_id)

select m.station_id, s.name, s.local, round(100 * m.usage_pct::numeric, 2) as "usage_pct"
from merged_table m join station s on m.station_id = s.station_id
where m.usage_pct <= 0.5

• 복잡하기도 한데, 무엇보다 중요한 연도별 폐쇄되거나 신설된 정류소에 대한 계산이 안되고 있었다.
• 그 결과 50% 이하로 설정했음에도 불구하고 계속 16개의 데이터만 탐색하고 있었다.
• 평소에 union all을 사용하는 문제를 많이 안풀어봤는데, 이번 기회에 제대로 알게 되었다