- 전체
- Sample DB
- database modeling
- [표준 SQL] Standard SQL
- G-SQL
- 10-Min
- ORACLE
- MS SQLserver
- MySQL
- SQLite
- postgreSQL
- 데이터아키텍처전문가 - 국가공인자격
- 데이터 분석 전문가 [ADP]
- [국가공인] SQL 개발자/전문가
- NoSQL
- hadoop
- hadoop eco system
- big data (빅데이터)
- stat(통계) R 언어
- XML DB & XQuery
- spark
- DataBase Tool
- 데이터분석 & 데이터사이언스
- Engineer Quality Management
- [기계학습] machine learning
- 데이터 수집 및 전처리
- 국가기술자격 빅데이터분석기사
- 암호화폐 (비트코인, cryptocurrency, bitcoin)
postgreSQL [postgreSQL] PostgreSQL 계층형 쿼리 구현 방법
2023.01.29 16:19
[postgreSQL] PostgreSQL 계층형 쿼리 구현 방법
오늘은 계층형 쿼리를 PostgreSQL에서는 어떻게 구현할 수 있는지 정리해 보려 한다.
먼저 오라클에서는 START WITH ~ CONNECT BY PRIOR 를 사용하여 계층형 쿼리를 구현 할수 있다.
PostgreSQL에서는 WITH RECURSIVE 를 제공한다. PostgreSQL로 구현하다 보면 WITH 구문을 많이
사용하여 구현한다는 걸 알 수 있다. 계층형 쿼리 구현에서도 WITH 구문을 사용한다.
오라클에서 구현방식과 PostgreSQL구현 방식을 비교해서 정리하면 다음과 같다.
▶ 오라클
|
SELECT 컬럼명
FROM 테이블명 START WITH 계층구조 시작 조건(루트 노드 식별) CONNECT BY PRIOR 계층구조 상하위 조건(부모 자식 노드간의 관계) |
||
|
SELECT A.CONTS_ID,
A.CONTS_NM, A.UP_CONTS_ID, A.MENU_ORD, LEVEL /* 계층구조에서 단계, 레벨을 나타내주는 함수 */ FROM CLT_MENU A WHERE A.MENU_INCL_YN = 'Y' AND LEVEL IN (2,4) START WITH A.CONTS_ID = 'voc' /* 계층구조의 시작조건을 주는 조건절 */ CONNECT BY PRIOR A.CONTS_ID = A.UP_CONTS_ID /* 계층구조의 상,하위 간의 관계 조건 */ ORDER SIBLINGS BY A.MENU_ORD /* 계층구조를 유지하면서 정렬해주는 구문 */ |
▶ PostgreSQL
|
WITH RECURSIVE [view 명] (보여주고 싶은 컬럼) as (
부모쿼리 작성, 데이터의 시작조건을 구하는 쿼리 lUNION ALL 계층구조 작성, 하위 데이터를 찾아가기 위한 반복 쿼리, ) view 쿼리 |
||
|
WITH RECURSIVE CODE_LIST(CONTS_ID, CONTS_NM, UP_CONTS_ID, MENU_ORD, DEPTH, PATH, CYCLE) as (
/* 계층구조의 시작조건 쿼리 */ SELECT A.CONTS_ID, A.CONTS_NM, A.UP_CONTS_ID, A.MENU_ORD, 1, ARRAY[A.CONTS_ID::text], false FROM CLT_MENU A WHERE A.CONTS_ID = 'voc' AND A.MENU_INCL_YN = 'Y' UNION ALL /*하위 데이터를 찾아가기 위한 반복조건 쿼리*/ SELECT A.CONTS_ID, A.CONTS_NM, A.UP_CONTS_ID, A.MENU_ORD, B.DEPTH + 1, ARRAY_APPEND(B.PATH, A.CONTS_ID::text), A.CONTS_ID = any(B.PATH) FROM CLT_MENU A, CODE_LIST B WHERE A.UP_CONTS_ID = B.CONTS_ID AND A.MENU_INCL_YN = 'Y' AND NOT CYCLE ) /*View 쿼리*/ SELECT CONTS_ID, CONTS_NM, UP_CONTS_ID, MENU_ORD, DEPTH AS A_MENU_LEVEL, PATH FROM CODE_LIST WHERE DEPTH IN (2,4) ORDER BY PATH |
- CYCLE은 RECURSIVE를 통한 재귀 쿼리 수행 시 성능 상의 문제를 해결하기 위함
UNION ALL 다음의 반복조건 쿼리가 수행되면 CYCLE이 false이기 때문에 SELECT문이 수행 되고 검색된 자식 node의 ID 값이 배열(ARRAY[A.CONTS_ID::text])에 추가(ARRAY_APPEND(B.PATH, A.CONTS_ID::text)) 됨.
- ANY(B.PATH)는 PATH 배열에 자신의 ID값이 있는 지를 검사하여, 이미 찾은 값에 대해서는 더 이상 데이터 검색을 수행하지 않도록 함.
- 배열에는 DataType이 int, text인 형태만 담을 수 있으므로 배열에 담을 varchar타입의 컬럼 뒤에 ::text 를 붙여 형태를 변환 해줌.
오라클로 구현할때도 쉽지 않은 계층형 쿼리... 역시 PostgreSQL로 작성하려 해도 쉽지는 않다.
오라클과 달리 WITH RECURSIVE를 이용해서 트리 구조의 계층형 쿼리 구현하는 방법에 대해 정리해
보았다. 다음에 시간되면 데이터를 행에서 열로 전환하는 PIVOT 기능 구현에 대해 정리해 보고자 한다.
오늘 포스팅은 여기까지...
[출처] https://blog.naver.com/wiseyoun07/221135850258
광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.
