[SQL] solvesql 코딩테스트 연습10

문제1.  폐쇄할 따릉이 정류소 찾기 1

출처 : solvesql 연습문제

문제 정보 : 난이도 5 / 정답률 52.50%

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

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

 

정답 쿼리

select a.station_id, a.name
from station a
join station b 
on a.station_id != b.station_id
where a.updated_at < b.updated_at and 
(6371 * acos(cos(radians(a.lat)) * cos(radians(b.lat)) * cos(radians(b.lng) - radians(a.lng)) + sin(radians(a.lat)) * sin(radians(b.lat)))) < 0.3
group by a.station_id
having count(a.station_id) >= 5

 

문제 해설

 

1. 주요 포인트 1 - 불일치 조건 join

join station b 
on a.station_id != b.station_id

 

반경 내의 다른 따릉이 정류소에 매핑해야하기 때문에 동일한 정류소 아이디끼리 매핑하게 되면 원하는 값을 출력할 수 없다. 따라서 sqlite의 경우 '!=', mysql의 경우 '<>'를 사용해서 불일치 조건으로 칼럼을 지정해 join을 시켜준다.

 

2. 주요 포인트 2 - 반경 계산하는 수학 공식

(6371 * acos(cos(radians(a.lat)) * cos(radians(b.lat)) * cos(radians(b.lng) - radians(a.lng)) + sin(radians(a.lat)) * sin(radians(b.lat)))) < 0.3

 

서로 다른 두 지점의 위도와 경도 정보를 통해 두 지점의 거리를 구할 때는 Haversine formula를 사용한다. 위도, 경도 칼럼이 있기 때문에 이를 이용해 where 조건절에 구문을 삽입했다.