在Snowflake层级查询中，如何替代Oracle的CONNECT_BY_ISCYCLE和CONNECT_BY_ISLEAF？

Let's walk through how to mimic these two hierarchical query features in Snowflake, since it doesn't have native support for them. I'll use a typical employee hierarchy table (EMPLOYEES with EMP_ID, NAME, MANAGER_ID) as an example for both scenarios.

1. Replacing CONNECT_BY_ISLEAF #

CONNECT_BY_ISLEAF flags rows that are leaf nodes (no child nodes under them). Here are two straightforward ways to do this in Snowflake:

Method 1: Use a LEFT JOIN with a subquery #

First, identify all parent nodes, then mark rows that aren't in that parent set as leaves:

WITH emp_hierarchy AS (
    SELECT 
        emp_id,
        name,
        manager_id
    FROM employees
    START WITH manager_id IS NULL  -- root nodes (top-level managers)
    CONNECT BY PRIOR emp_id = manager_id
)
SELECT 
    eh.*,
    CASE WHEN p.emp_id IS NULL THEN 1 ELSE 0 END AS is_leaf
FROM emp_hierarchy eh
LEFT JOIN (SELECT DISTINCT manager_id AS emp_id FROM employees) p
    ON eh.emp_id = p.emp_id;

Method 2: Use QUALIFY with COUNT in a recursive CTE #

If you're building the hierarchy with a recursive CTE, you can track child counts directly:

WITH RECURSIVE emp_hierarchy AS (
    SELECT 
        emp_id,
        name,
        manager_id,
        0 AS level,
        (SELECT COUNT(*) FROM employees e WHERE e.manager_id = emp.emp_id) AS child_count
    FROM employees emp
    WHERE manager_id IS NULL
    UNION ALL
    SELECT 
        e.emp_id,
        e.name,
        e.manager_id,
        eh.level + 1,
        (SELECT COUNT(*) FROM employees e2 WHERE e2.manager_id = e.emp_id) AS child_count
    FROM employees e
    JOIN emp_hierarchy eh ON e.manager_id = eh.emp_id
)
SELECT 
    emp_id,
    name,
    manager_id,
    level,
    CASE WHEN child_count = 0 THEN 1 ELSE 0 END AS is_leaf
FROM emp_hierarchy;

2. Replacing CONNECT_BY_ISCYCLE #

CONNECT_BY_ISCYCLE detects cycles in the hierarchy (e.g., Employee A reports to B, who reports back to A). To replicate this, we need to track the path of nodes we've traversed and check if the current node already exists in that path.

Using an Array to Track Paths #

Snowflake's array functions make this easy. We'll build an array of EMP_IDs as we recurse, then check if the current node is already in the array:

WITH RECURSIVE emp_hierarchy AS (
    SELECT 
        emp_id,
        name,
        manager_id,
        ARRAY_CONSTRUCT(emp_id) AS path,
        0 AS is_cycle
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT 
        e.emp_id,
        e.name,
        e.manager_id,
        ARRAY_APPEND(eh.path, e.emp_id) AS path,
        CASE WHEN ARRAY_CONTAINS(eh.path, e.emp_id::VARIANT) THEN 1 ELSE 0 END AS is_cycle
    FROM employees e
    JOIN emp_hierarchy eh ON e.manager_id = eh.emp_id
    WHERE is_cycle = 0  -- stop recursing once a cycle is found
)
SELECT 
    emp_id,
    name,
    manager_id,
    path,
    is_cycle
FROM emp_hierarchy;

Key Notes: #

• The ARRAY_CONTAINS check works because we're adding each node's ID to the path array as we go. If the current node's ID is already in the path, we've hit a cycle.
• We add WHERE is_cycle = 0 to the recursive join to prevent infinite loops once a cycle is detected.

内容的提问来源于stack exchange，提问作者Sayantan Bhattacharya