SQL | SOLVESQL 코딩테스트 : Lv5. (ANALYTICS)

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

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

WITH T1 AS ( -- 정류장 id가 같지 않은 것 중에 0.3KM 안에 있는 것들
  SELECT s1.station_id, s1.name, s2.station_id AS next_s_id, s2.name AS next_s_name
  FROM station AS s1
    LEFT JOIN station AS s2 ON s1.station_id != s2.station_id
      AND s1.updated_at < s2.updated_at
      AND (6356 * acos(
          cos(radians(s1.lat)) * cos(radians(s2.lat)) * 
          cos(radians(s2.lng) - radians(s1.lng)) + 
          sin(radians(s1.lat)) * sin(radians(s2.lat))
          )) <= 0.3
)

SELECT station_id, name
FROM T1
GROUP BY station_id
HAVING COUNT(*) >= 5

❗GROUP BY절에 기준이 될 station_id 넣기
❗HAVING 절에 개수가 5개 이상이라는 조건 넣기
❗참고 : https://nowolver.tistory.com/39

+) WITH 절

❗LEFT JOIN으로 셀프 조인 (같은 테이블이지만 테이블 별칭 다르게 지정)
❗JOIN 조건 걸기

❗나중에 생기거나 업데이트 된 다른 정류소

• 만들어진 날짜끼리, 업데이트 된 날짜끼리 비교하려 했는데 업데이트 된 날짜만 있어서 그것만 비교
• 업데이트가 무조건 나중에 된 것만 비교 (같다 x, 크다 o)

❗300M안에 있다 (하버사인 비교)

 

시도한 방법 1 : 하버사인 공식을 역으로 계산해서 적절한 위경도를 뽑자 (실패)

하버사인 공식은 아래와 같다

대충 정리하면 d = 2r arcsin [sqrt(@@@)] 인데, 이게 300m 이하여야 한다.
하버사인은 km 기준이고, 문제에서 주어진 r(반지름) 은 6356km, sin()의 반대는 asin() 이니 이걸 대입하면

 

0.3km = 2 * 6356 arcsin [sqrt(@@@)]
0.15km / 6356 = arcsin [sqrt(@@@)]
sin(0.15km / 6356) = [sqrt(@@@)]
sin(0.15km / 6356)^2 = @@@

 

" sin(0.15km / 6356)^2 = @@@ " 일것 이다. SQL로 계산하면 아래 사진처럼 나온다.

 

???

 

이 방법은 버린다...

 

시도한 방법 2 : 조인 조건으로 하버사인을 넣자 (구글링 : 성공코드)

다른 사람들 풀이를 참고하니 조인을 걸 때부터 하버사인 공식을 사용하는 경우가 많았다.
JOIN 후 ON 조건에 ‘=’이 아니라 ‘≠’를 하면 같지 않은 것끼리 다 붙어진다!
이 상태에서 하버사인 공식을 계산한 것들이 많았다.
구글링에서 찾은 하버사인 공식을 넣었다.

 

Lv5. 지역별 자전거 대여 현황

https://solvesql.com/problems/bike-rent-stats/

WITH T1 AS (
  SELECT rh.bike_id
    , rh.rent_at,s1.local AS rent_local
    , rh.return_at, s2.local AS return_local
  FROM rental_history AS rh 
    LEFT JOIN station AS s1 ON rh.rent_station_id = s1.station_id
    LEFT JOIN station AS s2 ON rh.return_station_id = s2.station_id
  WHERE rh.rent_at BETWEEN '2021-01-01 00:00:00' AND '2021-01-31 23:59:59'
    AND rh.return_at BETWEEN '2021-01-01 00:00:00' AND '2021-01-31 23:59:59'
)

SELECT rent_local AS local
  , COUNT(CASE WHEN rent_local = return_local THEN bike_id END) + COUNT(CASE WHEN rent_local != return_local THEN bike_id END) AS all_rent
  , COUNT(CASE WHEN rent_local = return_local THEN bike_id END) AS same_local
  , COUNT(CASE WHEN rent_local != return_local THEN bike_id END) AS diff_local
FROM T1
GROUP BY rent_local
ORDER BY all_rent DESC

❗COUNT(), CASE WHEN 사용해서 빌린 지역 = 반납 지역 일 때, 빌린 지역 != 반납 지역 일 때 개수 세기
❗빌린 지역구 기준으로 GROUP BY
❗전체 빌린 횟수 기준으로 ORDER BY DESC

+) WITH 절

❗rental_history에 station LEFT JOIN 두 번 실시

• 두 번 할 때 테이블 이름 다르게 지정
• s1은 렌탈 기준, s2는 반납 기준으로 테이블 결합

❗WHERE 절에 렌탈 시간, 반납 시간 조건 걸기

• '0000-00-00 00:00:00' 기준으로 조건 걸어야 함
• 데이터 자체가 년월일시 기준이기 떄문
• 두 조건이 동시에 만족해야하므로 AND 로 연결