Snowflake中实现DFS及自定义排序查询的技术问询

一、在Snowflake中实现DFS排序 #

Snowflake虽不支持SEARCH DEPTH FIRST语法，但可通过递归CTE构建层级路径模拟DFS排序逻辑，核心是为每个节点生成包含父节点顺序信息的路径字符串，最终按该路径排序即可得到DFS结果。

假设你的表名为FILE_SYSTEM，根节点的PARENT_ID为NULL，可使用以下SQL实现：

WITH RECURSIVE dfs_path AS (
    -- 初始化：获取所有根节点，路径由自身的ORDER和ITEM_ID组成
    SELECT
        ITEM_ID,
        PARENT_ID,
        "ORDER",
        -- 用固定长度的ORDER值拼接ITEM_ID，避免排序时的字符串比较问题
        CONCAT(LPAD(CAST("ORDER" AS VARCHAR), 5, '0'), '|', ITEM_ID) AS dfs_sort_path
    FROM FILE_SYSTEM
    WHERE PARENT_ID IS NULL

    UNION ALL

    -- 递归：子节点继承父节点的路径，追加自身的ORDER和ITEM_ID
    SELECT
        child.ITEM_ID,
        child.PARENT_ID,
        child."ORDER",
        CONCAT(parent.dfs_sort_path, '|', LPAD(CAST(child."ORDER" AS VARCHAR), 5, '0'), '|', child.ITEM_ID) AS dfs_sort_path
    FROM FILE_SYSTEM child
    JOIN dfs_path parent ON child.PARENT_ID = parent.ITEM_ID
)
SELECT ITEM_ID, PARENT_ID, "ORDER"
FROM dfs_path
ORDER BY dfs_sort_path;

说明：

• 用LPAD将ORDER字段补成固定长度（示例为5位），避免因数字长度差异导致的排序错误（比如10排在2前面的问题）
• 路径中用|分隔各层级标识，确保排序时层级顺序正确

二、利用API返回的排序数组排序 #

若已通过API拿到最终的ITEM_ID排序数组，可通过两种方式传入查询实现排序：

方法1：直接用ARRAY_POSITION函数排序 #

将API返回的数组用ARRAY_CONSTRUCT构建，通过ARRAY_POSITION获取每个ITEM_ID在数组中的位置，以此作为排序依据：

SELECT ITEM_ID, PARENT_ID, "ORDER"
FROM FILE_SYSTEM
ORDER BY ARRAY_POSITION(
    ARRAY_CONSTRUCT('item_1', 'item_2', 'item_3', ...), -- 替换为API返回的ITEM_ID数组
    ITEM_ID
);

注意：若数组长度过大，此方式性能可能不如临时表，但实现简单。

方法2：将数组转为临时表关联排序 #

先把API返回的数组拆成带排序序号的临时表，再与原表关联排序，适合数组较大的场景：

-- 创建临时表存储排序信息
CREATE TEMPORARY TABLE ITEM_SORT_ORDER (
    ITEM_ID VARCHAR, -- 替换为你的ITEM_ID实际类型
    SORT_ORDER INT
);

-- 插入API返回的排序数据，按数组顺序依次插入，SORT_ORDER递增
INSERT INTO ITEM_SORT_ORDER (ITEM_ID, SORT_ORDER)
VALUES
    ('item_1', 1),
    ('item_2', 2),
    ('item_3', 3),
    ...;

-- 关联原表并排序
SELECT fs.ITEM_ID, fs.PARENT_ID, fs."ORDER"
FROM FILE_SYSTEM fs
JOIN ITEM_SORT_ORDER iso ON fs.ITEM_ID = iso.ITEM_ID
ORDER BY iso.SORT_ORDER;

若API返回的是JSON数组，也可直接用FLATTEN函数解析生成临时表：

WITH api_sort_array AS (
    SELECT PARSE_JSON('["item_1","item_2","item_3"]') AS sort_array -- 替换为API返回的JSON数组
),
item_sort_order AS (
    SELECT
        value::VARCHAR AS ITEM_ID,
        INDEX + 1 AS SORT_ORDER -- INDEX从0开始，+1转为从1开始的序号
    FROM api_sort_array, LATERAL FLATTEN(input => sort_array)
)
SELECT fs.ITEM_ID, fs.PARENT_ID, fs."ORDER"
FROM FILE_SYSTEM fs
JOIN item_sort_order iso ON fs.ITEM_ID = iso.ITEM_ID
ORDER BY iso.SORT_ORDER;

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