다양한 SQL 스타일을 활용하여 계층형(hierarchy) 쿼리를 표현하는 방법

Difference between bigquery sql and other sql to write hierarchy sql

 

I. 계층형 데이터(Hierarchical data)

WITH

employee AS (
    SELECT 40 AS id , 'london' AS name,  50 AS boss_id
        UNION ALL
    SELECT 50 AS id , 'lee' AS name,  10 AS boss_id
        UNION ALL
    SELECT 10 AS id , 'harry' AS name,  20 AS boss_id
        UNION ALL
    SELECT 20 AS id , 'leo' AS name,  NULL AS boss_id
        UNION ALL
    SELECT 70 AS id , 'lucas' AS name,  10 AS boss_id
        UNION ALL
    SELECT 60 AS id , 'james' AS name,  70 AS boss_id
        UNION ALL
    SELECT 30 AS id , 'danny' AS name,  50 AS boss_id
)

...

 다음과 같이 직원의 ID와 이름, 해당 직원의 직속상관(Direct manager) ID를 갖는 테이블이 있다고 가정해봅시다. 테이블에 의하면 10(harry)은 50(lee)과 70(lusas)의 상사입니다. 60(james)의 상사는 70(lucas)이 되고, 따라서 10(harry)은 60(james)의 간접 상사(Indirect manager) 이기도 합니다.

 

 

 

만약 위와 같은 데이터 테이블을 도식화하면 사진과 같은 형태로 구성됩니다. 데이터 양이 적다면 한 두번의 self-join으로 관계를 표현하는데 문제가 없습니다. 하지만 만약 employee id 가 1,000명, 10,000명이 넘는다면 어떻게 해결할 수 있을까요?

 

---

II. Loop In query

대부분 독자님들이 위와 같은 구조를 보자마자 for/while을 이용해 다 연결하면 되지!라고 생각하셨을겁니다. 일반적인 프로그래밍 언어와 마찬가지로 sql문 역시 lopp문을 이용해 해당 계층구조를 쿼리로 표현해낼 수 있습니다. 자주 사용되는 Snowflake SQL, Postgre SQL, MySQL, Oracel SQL 은 모두 같은 방법을 사용하기 때문에 계층구조를 만드는 방법에 대한 이해만 있다면 어떤 SQL을 이용하더라도 당황하지 않고 쿼리를 작성할 수 있습니다. (제가 써 본 것 중에는 Bigquery sql 만 문법이 조금 다릅니다.)

1. General SQL

• 대중적인 SQL 에 사용되는 방법입니다.
• Recursive 문을 활용하여 CTE 셋에 반복적으로 Self-join 하여 계층을 구현합니다.

WITH

RECURSIVE hierarchy AS (
    SELECT
        0 AS num, # id기준 계층 0 시작
        id, # employee id
        boss_id # employee id의 boss id
    FROM
        employee 

        UNION ALL(DISTINCT)

    SELECT
        num +1 AS num,
        h.id,
        e.id,
    FROM
        hierarchy AS h
    LEFT JOIN
        employee AS e
        ON h.boss_id = e.id
)

SELECT * FROM hierarchy ORDER BY id, num

2. Bigquery SQL

• Bigquery는 공식적으로 Recursive를 지원하지 않습니다. (다만 Loop/While 표현을 제공합니다.)
• 가장 낮은 계층과 최상위 계층까지 depth 가 알려져있는가에 따라 접근 방식이 달라집니다.

1) depth 를 알고 있고, depth가 깊지 않은 경우

• 이 경우 left (self) join을 반복하는 게 가장 이상적입니다.

SELECT
    ot.id,
    t1.id,
FROM employee AS ot
LEFT JOIN employee AS t1 ON t1.id = ot.boss_id
LEFT JOIN employee AS t2 ON t2.id = t1.boss_id
UNION DISTINCT
...

2) depth 를 모르는 경우

• 계층도는 끝과 끝이 연결되지 않기 때문에(단방향이기 때문에) depth 가 아무리 깊어도 전체 unique key value 개수와 같은 점을 이용하여 while 문을 사용합니다.
• 저도 빅쿼리에서 hierarchy 를 표현하기 위한 recursive query 작성 시 많은 고민을 했는데 이 글 의 도움을 많이 받았습니다.
• 논리는 Recursive 구문을 사용할 때와 동일합니다.

DECLARE counter INT64 DEFAULT 0;
DECLARE max_nums INT64;

SET max_nums = (SELECT COUNT(DISTINCT id) FROM employee); # 계층의 최대 길이는 전체 unique key 값과 같다.

CREATE TEMP TABLE result ( 
  id int64, 
  boss_ids ARRAY<int64>
);

WHILE counter <= max_nums # loop 회전수에 제한조건 
DO
  SET counter = counter + 1;

  CREATE OR REPLACE TEMP TABLE result AS (
    WITH
    base AS (
        SELECT 
            id, 
            boss_id
        FROM employee AS e
        LEFT JOIN result AS r USING(id)
        WHERE r.id IS NULL
    ),

    hierarchy_base AS (
        SELECT 
            l.id, 
            l.boss_id
        FROM base AS l
        LEFT JOIN base As r
        ON l.id = r.boss_id
        WHERE r.boss_id IS NULL
    ),

    hierarchy AS (
        SELECT 
            id, 
            ARRAY_AGG(boss_id IGNORE NULLS) AS boss_ids, 
        FROM hierarchy_base
        GROUP BY id

        UNION ALL

        SELECT 
            r.id, 
            ANY_VALUE(r.boss_ids) || COALESCE(ARRAY_AGG(DISTINCT hb.boss_id IGNORE NULLS), ARRAY<int64>[]) as boss_ids,
        FROM result AS r
        CROSS JOIN UNNEST(r.boss_ids) AS bis
        LEFT JOIN hierarchy_base AS hb
        ON hb.id = bis
        GROUP BY r.id
    )

    SELECT 
        id,
        boss_ids,
    FROM hierarchy
  );

END WHILE;

INSERT INTO result (id, boss_ids)
WITH 
bottom_level AS (
    SELECT 
        m1.boss_id AS id
    FROM employee AS m1
    LEFT JOIN employee AS m2
        ON m1.boss_id = m2.id
    WHERE m2.id IS NULL
)
SELECT 
    bl.id, 
    ARRAY<int64>[] AS boss_ids, 
FROM bottom_level AS bl
JOIN result AS r
ON r.id = bl.id
;

SELECT 
    id,
    b AS boss_id,
    COUNT(b) OVER (PARTITION BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS num
FROM result
CROSS JOIN UNNEST(boss_ids) AS b
WHERE id IS NOT NULL
ORDER BY id, num