Greenplum에서 hierarchy, Path와 같이 Recursive 쿼리 샘플입니다.
1. 쿼리 패턴
1) with recursive 쿼리
- 메뉴와 조직과 같이 UI 에서 사용하며 테이블의 사이즈가 작을 경우 권고
- 쿼리상의 root 변경시 편리 함.
2) self left outer join
- 웹로그, 센서로그와 같이 대용량의 데이터의 path 분석시 권고
- 대용량 처리시에 left outer join의 성능이 recursive 쿼리 보다 훨씬 좋기 때문
2. 쿼리 샘플
create table public.places
(
place text,
is_in text
)
distributed by (place);
insert into public.places
values
('한국', null)
, ('서울', '한국')
, ('강남구', '서울')
, ('삼성동', '강남구')
, ('경기', '한국')
, ('의왕', '경기')
, ('내손동', '의왕')
, ('양산', '한국')
, ('삼성동', '양산')
, ('미국', null)
, ('캘리포니아','미국')
, ('샌프란시스코','캘리포니아')
, ('플로리다','미국')
, ('마이에미','플로리다')
;
with recursive q (place, is_in, depth)
as
(
select place, is_in, 1
from places
where is_in is null
union all
select m.place, m.is_in, q.depth + 1
from places m
join q on q.place = m.is_in
)
select * from q;
place | is_in | depth
--------------+------------+-------
한국 | | 1
양산 | 한국 | 2
경기 | 한국 | 2
서울 | 한국 | 2
삼성동 | 양산 | 3
의왕 | 경기 | 3
강남구 | 서울 | 3
내손동 | 의왕 | 4
삼성동 | 강남구 | 4
미국 | | 1
캘리포니아 | 미국 | 2
플로리다 | 미국 | 2
샌프란시스코 | 캘리포니아 | 3
마이에미 | 플로리다 | 3
with recursive q
(place, is_in, depth, path, cycle)
as
(
select place, is_in, 1 as depth, array[place], false as cycle from places
where is_in is null
union all
select m.place, m.is_in
, q.depth + 1 as depth
, path || m.place, m.place = any(path) from places m
join q on q.place = m.is_in
where not cycle
)
select place, is_in, depth, array_to_string(path, '>'), cycle from q;
place | is_in | depth | array_to_string | cycle
--------------+------------+-------+------------------------------+-------
미국 | | 1 | 미국 | f
플로리다 | 미국 | 2 | 미국>플로리다 | f
캘리포니아 | 미국 | 2 | 미국>캘리포니아 | f
마이에미 | 플로리다 | 3 | 미국>플로리다>마이에미 | f
샌프란시스코 | 캘리포니아 | 3 | 미국>캘리포니아>샌프란시스코 | f
한국 | | 1 | 한국 | f
양산 | 한국 | 2 | 한국>양산 | f
서울 | 한국 | 2 | 한국>서울 | f
경기 | 한국 | 2 | 한국>경기 | f
삼성동 | 양산 | 3 | 한국>양산>삼성동 | f
강남구 | 서울 | 3 | 한국>서울>강남구 | f
의왕 | 경기 | 3 | 한국>경기>의왕 | f
삼성동 | 강남구 | 4 | 한국>서울>강남구>삼성동 | f
내손동 | 의왕 | 4 | 한국>경기>의왕>내손동 | f
(14 rows)
select path_str, path_arr, depth
, path_arr[1] d1
, path_arr[2] d2
, path_arr[3] d3
, path_arr[4] d4
from (
select array_to_string(path_arr, '>') path_str
, string_to_array(array_to_string(path_arr, '>'), '>') path_arr
, array_length(string_to_array(array_to_string(path_arr, '>'), '>'), 1) depth
from (
select array[e.place, d.place, c.place, b.place, a.place] path_arr
from places a
left outer join places b
on a.is_in = b.place
left outer join places c
on b.is_in = c.place
left outer join places d
on c.is_in = d.place
left outer join places e
on d.is_in = e.place
) a1
) a2
--where path_arr[1] = '한국'
order by 2
;
path_str | path_arr | depth | d1 | d2 | d3 | d4
------------------------------+--------------------------------+-------+------+------------+--------------+--------
미국 | {미국} | 1 | 미국 | | |
미국>플로리다 | {미국,플로리다} | 2 | 미국 | 플로리다 | |
미국>플로리다>마이에미 | {미국,플로리다,마이에미} | 3 | 미국 | 플로리다 | 마이에미 |
미국>캘리포니아 | {미국,캘리포니아} | 2 | 미국 | 캘리포니아 | |
미국>캘리포니아>샌프란시스코 | {미국,캘리포니아,샌프란시스코} | 3 | 미국 | 캘리포니아 | 샌프란시스코 |
한국 | {한국} | 1 | 한국 | | |
한국>경기 | {한국,경기} | 2 | 한국 | 경기 | |
한국>경기>의왕 | {한국,경기,의왕} | 3 | 한국 | 경기 | 의왕 |
한국>경기>의왕>내손동 | {한국,경기,의왕,내손동} | 4 | 한국 | 경기 | 의왕 | 내손동
한국>서울 | {한국,서울} | 2 | 한국 | 서울 | |
한국>서울>강남구 | {한국,서울,강남구} | 3 | 한국 | 서울 | 강남구 |
한국>서울>강남구>삼성동 | {한국,서울,강남구,삼성동} | 4 | 한국 | 서울 | 강남구 | 삼성동
한국>양산 | {한국,양산} | 2 | 한국 | 양산 | |
한국>양산>삼성동 | {한국,양산,삼성동} | 3 | 한국 | 양산 | 삼성동 |
(14 rows)
