[SolveSQL] 폐쇄할 따릉이 정류소 찾기 1

오..solvesql의 마지막 문제이다. 폐쇄할 따릉이 정류소 찾기 - https://solvesql.com/problems/find-unnecessary-station-1/

solvesql

Q. 공유 자전거 서비스인 따릉이를 운영하는 팀에서는 서로 가까운 따릉이 정류소 들을 찾아내고 그 중 오래된 정류소를 폐쇄해 따릉이 서비스의 운영 비용을 줄이려고 합니다. 따릉이 데이터를 다루는 당신은 정류소 정보를 확인해 폐쇄를 검토할 따릉이 정류소 목록을 추려내는 업무를 받았습니다.

업무를 수행하기 위해 정류소 정보가 저장된 station 테이블을 사용해 반경 300m 이내에 나중에 생기거나 업그레이드 된 다른 정류소가 5개 이상 있는 따릉이 대여소의 아이디(station_id)와 이름(name)을 출력하는 쿼리를 작성해주세요.

서로 다른 두 지점의 위도와 경도 정보를 통해 두 지점의 거리를 구할 때는 Haversine formula를 사용합니다. 장소 1의 위치를 나타내는 라디안(radian) 단위의 위도와 경도를 각각 ρ1​, λ1​, 장소 2의 위치를 나타내는 라디안(radian) 단위의 위도와 경도를 각각 ρ2​, λ2​, 그리고 지구의 반지름을 r=6356km라고 할 때, 두 장소의 거리(d)는 아래와 같습니다.

A. 일단 공식이 나와서 좀 쫄...고 시작했다. 그리고 거리 계산 공식을 sql 상에서 어떻게 해야 할지 정말 전혀 감이 안 와서...SQLite 상에서 distance를 삼각함수를 사용해 구하는 함수나 다른 패키지 등이 있는지 구글링했다. 결과적으로는 아래와 같은 코드가 완성됐다,,, 진짜 개어려웠음

SELECT station_id, name
FROM (
  SELECT s.station_id, s.name, count(s_near.station_id) as cnt
  FROM station s 
  LEFT JOIN station s_near 
    ON s.station_id != s_near.station_id
      AND s_near.updated_at > s.updated_at
      AND ( 6371 * acos( cos( radians(s.lat) ) * cos( radians( s_near.lat ) ) 
             * cos( radians( s_near.lng ) - radians(s.lng) )
             + sin( radians(s.lat) ) * sin( radians( s_near.lat ) ) ) ) <= 0.3 
  group by s.station_id
  having cnt >= 5
)t
ORDER BY station_id

음 어쨌든 결과가 나오긴 했는데 정말 개어렵다^_^ 

암튼 solvesql 이렇게 끝 :) 굳이 유료로...적지 않은 돈을 주고 문제를 다 조회하고 싶진 않아서 그냥 리트코드로 넘어가려고 한다. 이제 파이썬도 기록해야지 !