난이도 | 풀기 | 문제 이름 |
난이도 2 | 0개 | x |
난이도 3 | 0개 | x |
난이도 4 | 0개 | x |
난이도 5 | 1개 | - 폐쇄할 따릉이 정류소 찾기 1 |
폐쇄할 따릉이 정류소 찾기 1
WITH cross_tab AS (
SELECT s1.station_id AS s1_id
, s1.name AS s1_name
, RADIANS(s1.lat) AS s1_lat
, RADIANS(s1.lng) AS s1_lng
, s1.updated_at AS s1_at
, s2.station_id AS s2_id
, s2.name AS s2_name
, RADIANS(s2.lat) AS s2_lat
, RADIANS(s2.lng) AS s2_lng
, s2.updated_at AS s2_at
FROM station AS s1
CROSS JOIN station AS s2
WHERE s1.station_id != s2.station_id
) -- 1. CROSS JOIN으로 모든 정류소 조합 만들기
SELECT s1_id AS station_id
, s1_name AS name
FROM (
SELECT s1_id
, s1_name
, SUM(CASE WHEN s1_at < s2_at THEN 1 ELSE 0 END) AS cnt_close
FROM (
SELECT *
, 2*6356*ASIN(SQRT((POW(SIN((s1_lat-s2_lat)/2), 2)) + (COS(s1_lat)*COS(s2_lat)*POW(SIN((s1_lng-s2_lng)/2), 2)))) AS dist
FROM cross_tab
) AS tmp -- 2. 하버사인 공식으로 구한 거리 컬럼 추가하기
WHERE dist <= 0.3
GROUP BY s1_id, s1_name
) AS tmp2 -- 3. 300m 이내 정류소만 포함 -> 나중에 업데이트된 정류소 세기
WHERE cnt_close >= 5 -- 4. 나중에 업데이트된 정류소가 5개 이상인 경우만 포함
[작성 순서]
1. CROSS JOIN을 사용하여 station 테이블의 모든 정류소의 조합을 만드는 테이블을 CTE로 생성한다.
-- (1) 뒤에서 하버사인 공식(Haversine formula)으로 정류소간 거리를 구해야 하기 때문에, 위도와 경도는 RADIANS() 함수를 이용하여 degree에서 radian으로 단위를 변경한다.
-- (2) 같은 정류소 조합은 제외한다.
2. 위에서 만든 테이블을 이용하여 하버사인 공식(Haversine formula)으로 거리를 구하는 컬럼을 추가한다. 위도와 경도는 반드시 라디안 단위로 변환하여 계산해야 한다.
-- SIN(), COS(), ASIN(), POW(), SQRT() 함수 사용
Q. 하버사인 공식이란?
A. 하버사인 공식(Haversine Formula)은 지구와 같은 구형 표면 위에서 두 지점 간의 최단 거리인 대원거리(great-circle distance)를 계산하는 데 사용되는 수학 공식. 이 공식은 위도와 경도 정보를 이용하여 두 지점 사이의 곡률을 고려한 거리를 정확하게 구할 수 있어, 항공 항법, GPS 기반 거리 측정, 지도 서비스 등 다양한 분야에서 활용된다.
하버사인 공식은 지구를 완전한 구형으로 가정하므로, 극지방이나 매우 긴 거리에서는 약간의 오차가 발생할 수 있다는 점을 주의해야 한다. 더 높은 정확도가 필요한 경우, 지구를 타원체로 모델링한 Vincenty 공식 등을 사용할 수도 있다고 한다.
3. 300m 이내에 있는 정류소만 포함되도록 필터링한 후, 더 나중에 업데이트된 정류소가 몇 개인지 카운트해 준다.
4. 최종적으로 나중에 업데이트된 정류소가 5개 이상인 정류소만 남도록 필터링해준다.
[참고]
https://limhm4907.tistory.com/148
[MySQL] JOIN
1. JOINSQL에서 JOIN은 2개 이상의 테이블을 서로 연결하여 관련된 데이터를 검색할 때 사용하는 매우 중요한 기능이다. - 근데 왜 중요할까?JOIN은 실무에서 정말 많이 사용하니까. - 그럼 JOIN을 왜 많
limhm4907.tistory.com
'데이터 처리 도구 > SQL 연습 기록' 카테고리의 다른 글
DAY 23 / [solvesql] 연습문제 5개 풀기 (0) | 2025.06.10 |
---|---|
DAY 22 / [solvesql] 연습문제 6개 풀기 (0) | 2025.06.09 |
DAY 19 / [solvesql] 연습문제 8개 풀기 (0) | 2025.06.03 |
DAY 18 / [solvesql] 연습문제 10개 풀기 (1) | 2025.06.02 |
DAY 17 / [solvesql] 연습문제 10개 풀기 (0) | 2025.06.01 |