조인 후 그룹별 rank 후에 그룹별로 정렬하기 0 1 734

by 고등어통조림 [DB 기타] [2019.10.08 11:33:26]


## 셀-기상관측소 데이터 정제 : 셀-가장 가까운 기상관측소 데이터 조인

CREATE TABLE temp_ana_grid

AS

SELECT t2.*

FROM (

SELECT t1.*, rank() OVER(PARTITION BY __gid ORDER BY wthr_dist) AS rnk

FROM (

SELECT a.gid,a.__gid,a.geom, a.frtp_0_area, a.frtp_1_area, a.frtp_2_area, a.frtp_3_area, a.frtp_4_area,

a.myo_dist, a.non_dist, a.bat_dist, a.avg_altitude, a.ffp_cnt, a.damage_area,a.age0_200, 

     a.age_65, a.age65_, a.cctv_dt, a.choso_dt, a.mun_dist, a.mun_cnt, a.mun_kuk_dist, a.mun_bo_dist,

a.mun_etc_dist, a.wthr_sttn_dt, b.gid AS wthr_gid, b.station, b.station_nm, b.temp_jan, b.temp_feb,

b.temp_mar, b.temp_apr, b.temp_may, b.temp_jun, b.temp_jul, b.temp_aug, b.temp_sep, b.temp_oct,           b.temp_nov, b.temp_dec, b.humi_jan, b.humi_feb, b.humi_mar, b.humi_apr, b.humi_may, b.humi_jun,             b.humi_jul, b.humi_aug, b.humi_sep, b.humi_oct, b.humi_nov, b.humi_dec, b.wind_jan, b.wind_feb, 

     b.wind_mar, b.wind_apr, b.wind_may, b.wind_jun, b.wind_jul, b.wind_aug, b.wind_sep, b.wind_oct,                 b.wind_nov, b.wind_dec, ST_Distance(a."geom", b."geom") AS wthr_dist

FROM ana_grid a, geo_wthr_sttn b

) t1 

) t2

WHERE t2.rnk = 1;

------------------------------------------------------------------------------------------

 

테이블 2개를 조인하는 경우이구요.

라임색, 분홍색 이렇게 두개의 테이블입니다.

 

라임색=a, 분홍색=b 로 별칭을 만들었구요

 

a 는 춘천 지역인데 그걸 두부 썰듯이 썰어서 격자로 만들었고, b 는 그 춘천 지역에 있는 기상 관측소의 위치(위경도 x, y 좌표)와 각 관측소에서 측정한 온도, 습도, 풍속 이런 데이터가 들어 있어요. 

b 테이블에 들어있는... 즉 춘천 내부의 기상 관측소는 5개 입니다.

(그래서 b 테이블은 행이 5개입니다.)

 

 

전체적인 개념은

 

1.

일단 a, b 테이블을 크로스 조인합니다 ㅜㅜ

(FROM ana_grid a, geo_wthr_sttn b 

조인할때 그냥 이렇게만 쓰니깐 크로스조인 맞는듯...)

 

1-1.

그리고 조인하면서 열을 새로 만드는데 a, b 에 geom 이라는 열이 위치가 들어있는 열인데 그걸 함수 ST_DISTANCE 를 사용해서 거리를 구합니다.

 

2.

그리고 나서 격자의 아이디(__gid 고유값입니다) 별로 그룹을 짓고 그 그룹별로 거리에 순위를 매깁니다.

즉 격자 1개에 기상 관측소 5개가 매칭이 되니깐.... 1~5 위까지 나옵니다. 그 변수가 rnk 이구요

 

3. 

격자 아이디 별로 매긴 순위중에서 1순위만 뽑아서(즉, 젤 가까운 거리에 있는 데이터만 가져온다는 뜻)

temp_ana_grid 테이블을 만듦

 

 

이런 개념인데요...

 

사실 이렇게 하고 싶었던 이유는 일단 a 테이블은 약 1,000개의 행이 있고(춘천 땅을 1,000개의 격자로 나눠놨다는 뜻), b 테이블은 5개의 행이 있는데

 

어떤 격자(지역)에 어떤 기상 관측소 데이터를 매칭 시킬지 때문인데요

단순하게 각 격자하고 기상 관측소 중에서 가까운 것을 찾아서 그 행에 기상 데이터를 새로운 열로 붙여 넣는 거에요

 

그래서 이런 과정을 거치게 되었는데 문제는...

 

1 을 하고 나면

일단 크로스 조인은 제대로 됩니다. 1,000 x 5 = 5,000 개의 행이 나오는데

 

1-1 을 하고 결과를 보면

 

모든 행에 가까운 순위가 오름차순으로 정렬이 되서 붙어 있더라구요.

제가 원하는 건 이게 아니거든요

위치를 나타내는 a 테이블의 geom(위경도) 열 중 1번째 행과 b 테이블의 geom(위경도) 열 중 1번째 행과 거리가 계산되서 그게 결과 테이블의 1번째 행에 맞춰서 들어가길 바라거든요. 물론 그럴 경우 ㅠ 테이블의 다른 모든 기상 정보도 결과 테이블의 1번째 행에 들어가야 하구요....

 

마찬가지로

 

a 테이블의 geom 열 1번째 행 - b 테이블의 geom 열 2번째 행과 계산된 값은

결과 테이블의 2번째 행에 들어 가길 원하구요

 

그 후에

결과 테이블에 새로운 rnk 열에는 그 값들의 a 테이블의 __gid 별로 그룹지어서 순위가 들어가고

 

그 후 다시

그 __gid 그룹 중 1순위 행들만 추출하여서 

최종 결과 테이블에 저장 되길 원하거든요

 

그런데 일단 저 쿼리는 그렇게 나오질 않습니다........

 

쿼리를 어떻게 바꿔야 할까요?

 

지금 그냥 생각으로는

 

1 단계에서 크로스 조인만 하고

 

조인하면서 거리 계산하는 함수를 사용하지 말고

 

조인이 다 끝난 후에 2 단계에서 별도로 거리를 계산하는 과정을 거치는 방식으로 해야 하나 싶기도 하고.....

 

 

의견 부탁드립니다.

by 마농 [2019.10.08 14:19:22]

글쎄요? 잘 하신 듯 한데요?
결과가 어떻길래? 원하는게 아니라는 건지요?

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입