쇼핑몰의 상품 관련 기능을 구현하고 있다.
카테고리가 3계층으로 이루어져 있고, 상위 카테고리 클릭시 하위 카테고리의 상품들도 함께 조회 되어야 한다.
이를 위해 원래는 000(대), 000111(중), 000111222(소) 와 같이 category id를 지정하여 '000'을 포함하는 category id를 가진 상품들을 조회할 생각이었다.
그러나 위와 같이 할 경우 카테고리가 변경 또는 이동될 때 id, 즉 PK를 수정해야 하고, 하위 카테고리 id가 상위 카테고리 id를 포함하여 복잡하다는 등의 문제가 있었다.
이 글은 이를 해결하기 위해 재귀 구조를 활용한 과정을 담고 있으며 본인은 현업자가 아니고, 또 경험이 부족하기에 올바른 방법이 아닐 수 있음을 염두에 두고 읽어주기를 바란다.
계층적 데이터
계층적 데이터, 즉 재귀 구조는 트리 구조나 그래프 구조를 가지며, 각 노드가 부모-자식 관계로 연결된다.
SQL에서 이를 표현하는 방법에는 여러 가지가 있으며 대표적으로 인접 목록 모델을 많이 사용하며, 그 외 다른 모델로는 중첩 집합, 경로 열거 등이 있다.
인접 목록(Adjacency List) 모델
인접 목록 모델은 각 노드가 자신의 부모를 참조하는 방식이다. 간단하고 직관적이지만 재귀 쿼리를 통해 계층 구조를 탐색해야 하기 때문에 복잡한 쿼리가 필요할 수 있다.
쇼핑몰의 상품을 분류하는 카테고리를 예시로 들며 설명하겠다.
재귀쿼리
WITH RECURSIVE 쿼리문을 작성하고 내부에 UNION을 통해 재귀를 구성한다.
가상의 테이블을 구성하면서 그 자신을 참조하여 값을 결정할 때 유용하게 사용할 수 있다.
공통 테이블 표현식(Common Table Expressions, CTE)은 일시적인 결과 집합으로, 복잡한 쿼리를 단순화하고 가독성을 높이는데 사용한다. 자기 자신을 참조하여 계층적 데이터를 처리할 수 있도록 돕는다.
기본적으로 다음과 같이 구성된다.
- 앵커 멤버(Anchor Member): 재귀의 시작점을 정의한다.
- 재귀 멤버(Recursive Member): 이전 단계의 결과를 바탕으로 다음 단계를 정의한다.
WITH RECURSIVE cate_tree AS (
-- 엥커 멤버
SELECT ... FROM ... WHERE ...
UNION ALL
-- 재귀 멤버
SELECT ... FROM ... JOIN cate_tree ON ...
)
SELECT * FROM cate_tree;
재귀가 끝나려면 재귀 멤버에서 더 이상 새로운 행이 추가되지 않아야 한다. 무한 재귀가 되지 않도록 주의해야 한다.
Category 테이블
이제 본격적으로 실제 사례를 보며 이해해 보겠다.
다음과 같은 간단한 카테고리 테이블이 있다.
CREATE TABLE Category {
cate_id INT PRIMARY KEY,
name VARCHAR(100),
parent_id INT DEFAULT NULL,
FOREIGN KEY (parent_id) REFERENCES Caregory(cate_id)
}
해당 카테고리는 다음과 같은 계층 구조를 형성한다. 다음은 카테고리 이름(카테고리 id) 형태이다.
남성(1)
- 상의(2)
- 셔츠(3)
- 하의(4)
- 청바지(5)하위 카테고리 조회
다음은 cate_id가 1인 카테고리부터 모든 하위 카테고리를 포함한 트리를 조회하는 SQL이다.
조건으로 c.parent_id = ct.cate_id 를 사용했다.
이는 Category 테이블의 parent_id가 현재 category_tree의 cate_id와 일치하는 경우, c가 ct의 하위 카테고리임을 의미한다.
WITH RECURSIVE category_tree AS (
-- 앵커 멤버
SELECT cate_id, name, parent_id
FROM Category
WHERE cate_id = 1
UNION ALL
-- 재귀 멤버
SELECT c.cate_id, c.name, c.parent_id
FROM Category c
INNER JOIN category_tree ct ON c.parent_id = ct.cate_id
)
SELECT *
FROM category_tree;
단계적으로 category_tree가 확장되는 과정을 살펴보겠다.
초기의 임시 데이터를 넣은 Category 테이블은 다음과 같다.
| cate_id | name | parent_id |
| 1 | 남성 | NULL |
| 2 | 상의 | 1 |
| 3 | 셔츠 | 2 |
| 4 | 하의 | 1 |
| 5 | 청바지 | 4 |
앵커 멤버 실행 후 결과는 다음과 같다.
SELECT cate_id, name, parent_id
FROM Category
WHERE cate_id = 1- 결과
| cate_id | name | parent_id |
| 1 | 남성 | NULL |
- category_tree
| cate_id | name | parent_id |
| 1 | 남성 | NULL |
재귀 멤버 1회 실행 후 결과는 다음과 같다.
SELECT c.cate_id, c.name, c.parent_id
FROM Category c
INNER JOIN category_tree ct ON c.parent_id = ct.cate_id- 결과
| cate_id | name | parent_id |
| 2 | 상의 | 1 |
| 4 | 하의 | 1 |
- category_tree
| cate_id | name | parent_id |
| 1 | 남성 | NULL |
| 2 | 상의 | 1 |
| 4 | 하의 | 1 |
변경 사항이 있으므로 재귀가 다시 실행된다.
재귀 멤버 2회 실행 후 결과는 다음과 같다.
- 결과
| cate_id | name | parent_id |
| 3 | 셔츠 | 2 |
| 5 | 바지 | 4 |
- category_tree
| cate_id | name | parent_id |
| 1 | 남성 | NULL |
| 2 | 상의 | 1 |
| 4 | 하의 | 1 |
| 3 | 셔츠 | 2 |
| 5 | 바지 | 4 |
이번에도 마찬가지로 변경사항이 있으므로 다시 실행된다.
그러나 재귀 멤버 3회 실행 후 결과는 다음과 같이 category_tree의 변동 사항이 없기 때문에 비로소 재귀가 종료된다.
- 결과
조회 내용 없음
- category_tree
변동 사항 없음
| cate_id | name | parent_id |
| 1 | 남성 | NULL |
| 2 | 상의 | 1 |
| 4 | 하의 | 1 |
| 3 | 셔츠 | 2 |
| 5 | 바지 | 4 |
정상적으로 동작하는지 MySQL Workbench를 이용해 확인해 보겠다.
Category 테이블에 다음과 같이 데이터를 넣어두었다. 위에서 했던 것과 동일한 데이터이다.
그리고 크롤링을 통해 Product 테이블에도 cate_id가 1~5인 상품을 각각 4개씩 넣어두었다.
cate_id='1' 의 하위 상품
cate_id='2' 의 하위 상품
cate_id='3' 의 하위 상품
cate_id='4' 의 하위 상품
cate_id='5' 의 하위 상품
모두 정상적으로 조회된다.