안녕하세요, 계층 구조를 가진 데이터를 동적으로 필터링하고 집계하는 복잡한 SQL 쿼리에 대해 감이 전혀 잡히지 않아 질문 글 남기게 되었습니다. 문제 상황은 다음과 같습니다:

테이블 구조

부서 테이블 (DEPARTMENT):

CREATE TABLE DEPARTMENT (
    DEPT_ID INT PRIMARY KEY,
    DEPT_NAME VARCHAR(100),
    PARENT_DEPT_ID INT,
    HIERARCHY_LEVEL INT,
    FOREIGN KEY (PARENT_DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID)
);

카테고리 테이블 (CATEGORY): (어플리케이션 단에서 ENUM 계층 구조로 관리하였는데 TABLE로도 추가해놓은 상태)

CREATE TABLE CATEGORY (
    CATEGORY_ID INT PRIMARY KEY,
    CATEGORY_NAME VARCHAR(100),
    PARENT_CATEGORY_ID INT,
    HIERARCHY_LEVEL INT,
    FOREIGN KEY (PARENT_CATEGORY_ID) REFERENCES CATEGORY(CATEGORY_ID)
);

A 테이블 (데이터 저장 테이블):

CREATE TABLE A (
    ID INT PRIMARY KEY,
    DEPT_ID INT,
    CATEGORY_ID INT,
    -- 기타 필요한 컬럼들
    FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID),
    FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORY(CATEGORY_ID)
);

예시 데이터

DEPARTMENT 테이블:

INSERT INTO DEPARTMENT VALUES
(1, '전체', NULL, 1),
(2, '영업본부', 1, 2),
(3, '영업1팀', 2, 3),
(4, '영업2팀', 2, 3),
(5, '고객서비스본부', 1, 2),
(6, 'CS팀', 5, 3);

CATEGORY 테이블:

INSERT INTO CATEGORY VALUES
(1, '전체', NULL, 1),
(2, '제품', 1, 2),
(3, '품질', 2, 3),
(4, '배송', 1, 2),
(5, '지연', 4, 3),
(6, '파손', 4, 3);

A 테이블:

INSERT INTO A (ID, DEPT_ID, CATEGORY_ID) VALUES
(1, 3, 3),  -- 영업1팀, 품질
(2, 4, 3),  -- 영업2팀, 품질
(3, 6, 5),  -- CS팀, 지연
(4, 3, 6),  -- 영업1팀, 파손
(5, 4, 3),  -- 영업2팀, 품질
(6, 6, 5),  -- CS팀, 지연
(7, 3, 3),  -- 영업1팀, 품질
(8, 4, 6);  -- 영업2팀, 파손

요구사항

사용자는 화면에서 여러 부서 ID와 여러 카테고리 ID를 필터로 선택할 수 있습니다.

선택된 부서 ID들은 서로 다른 계층 레벨일 수 있습니다 (예: 영업본부와 CS팀을 동시에 선택 가능).

선택된 카테고리 ID들도 서로 다른 계층 레벨일 수 있습니다 (예: 제품과 지연을 동시에 선택 가능).

쿼리는 선택된 부서와 그 하위 부서의 데이터를 모두 포함해야 합니다.

쿼리는 선택된 카테고리와 그 하위 카테고리의 데이터를 모두 포함해야 합니다.

결과는 선택된 부서별, 선택된 카테고리별로 집계되어야 합니다.

예시 시나리오

사용자가 다음과 같이 필터를 선택했다고 가정하면:

선택된 부서 ID: 2 (영업본부), 6 (CS팀)

선택된 카테고리 ID: 2 (제품), 4 (배송)

이 경우, 원하는 결과는 다음과 같습니다:

설명:

영업본부의 '제품' 카테고리 개수 4는 영업1팀과 영업2팀의 '품질' 관련 데이터를 모두 포함합니다 (품질은 제품의 하위 카테고리).

영업본부의 '배송' 카테고리 개수 2는 영업1팀과 영업2팀의 '파손' 관련 데이터를 포함합니다 (파손은 배송의 하위 카테고리).

CS팀의 '배송' 카테고리 개수 2는 '지연' 관련 데이터입니다 (지연은 배송의 하위 카테고리).

질문

위의 테이블 구조와 요구사항을 바탕으로, 어떻게 효율적인 SQL 쿼리를 작성할 수 있을까요? SQL로만 해결이 불가능하다면 어디까지 SQL로 처리하고 어플리케이션에서 어느 부분을 처리해야 할지 도움 주시면 감사하겠습니다. 특히 다음 사항들에 대해 말씀 남겨주시면 감사하겠습니다:

계층 구조를 가진 부서와 카테고리를 어떻게 효과적으로 쿼리할 수 있을까요?

선택된 부서와 카테고리의 모든 하위 항목을 포함하여 집계하는 방법은 무엇인가요?

동적으로 변하는 필터 조건을 어떻게 쿼리에 반영할 수 있을까요?

이러한 복잡한 요구사항을 충족하면서도 성능을 최적화할 수 있는 방법이 있을까요?

사용 중인 데이터베이스는 Tibero입니다. 어떤 조언이든 감사하게 받아들이겠습니다. 감사합니다!