主页 / coding / 问题 / 77252047
Accepted
David542
Asked: 2023-10-08 09:55:06 +0800 CST

使用递归 CTE 在 BigQuery 中获取文件路径

  • 772

我有以下两个表来提供每个用户拥有的文件路径：

create temp table object as (
  select 1 id, 'MyFile1' name union all
  select 2 id, 'MyFile2' name union all
  select 3 id, 'MyFolder' name union all
  select 4 id, 'SomeFile' name union all
  select 5 id, 'Hello' name
);
create temp table path as (
  select 1 user_id, 1 object_id, NULL parent_folder union all
  select 1 user_id, 2 object_id, NULL parent_folder union all
  select 1 user_id, 3 object_id, NULL parent_folder union all
  select 1 user_id, 4 object_id, 3 parent_folder union all
  select 2 user_id, 5 object_id, NULL parent_folder union all
  select 2 user_id, 1 object_id, NULL parent_folder
);

例如，对于 user-1，他的目录结构如下所示：

MyFile1
MyFile2
MyFolder/
    SomeFile

我正在尝试构建一个递归 cte，以便它显示用户及其路径。以下是查询应返回的内容：

user_id           path
1                 MyFile1
1                 MyFile2
1                 MyFolder
1                 MyFolder/SomeFile
2                 Hello
2                 MyFile1

我目前有以下工作查询，但想知道是否可以在可读性或性能方面改进它：

create temp table object as (
  select 1 id, 'MyFile1' name union all
  select 2 id, 'MyFile2' name union all
  select 3 id, 'MyFolder' name union all
  select 4 id, 'SomeFile' name union all
  select 5 id, 'Hello' name
);

create temp table path as (
  select 1 user_id, 1 object_id, NULL parent_folder union all
  select 1 user_id, 2 object_id, NULL parent_folder union all
  select 1 user_id, 3 object_id, NULL parent_folder union all
  select 1 user_id, 4 object_id, 3 parent_folder union all
  select 2 user_id, 5 object_id, NULL parent_folder union all
  select 2 user_id, 1 object_id, NULL parent_folder
);

WITH RECURSIVE all_paths AS (
    SELECT user_id, id, name AS path FROM path JOIN object ON path.object_id=object.id WHERE parent_folder IS NULL
    UNION ALL
    SELECT all_paths.user_id, null, CONCAT(all_paths.path, '/', object.name) 
    FROM all_paths JOIN path ON all_paths.id = path.parent_folder join object ON path.object_id=object.id
) SELECT * FROM all_paths ORDER BY user_id, path

在此输入图像描述

1 个回答

  1. Best Answer

    您选择的方法有效且良好。RECURSIVE 连接部分还有另一个连接。这确实使查询不易阅读。一个好的做法是在 RECURSIVE 连接之前或之后连接数据，这在您的情况下是可能的。将连接表构建为临时表或使其永久化select * from ... where false将减少处理能力和时间。这里我全部使用了CTE语句，你可以通过注释掉注释轻松切换到临时表。

    WITH RECURSIVE
#create temp table 
object as (
  select 1 id, 'MyFile1' name union all
  select 2 id, 'MyFile2' name union all
  select 3 id, 'MyFolder' name union all
  select 4 id, 'SomeFile' name union all
  select 5 id, 'Hello' name
),

#create temp table 
path as (
  select 1 user_id, 1 object_id, NULL parent_folder union all
  select 1 user_id, 2 object_id, NULL parent_folder union all
  select 1 user_id, 3 object_id, NULL parent_folder union all
  select 1 user_id, 4 object_id, 3 parent_folder union all
  select 2 user_id, 5 object_id, NULL parent_folder union all
  select 2 user_id, 1 object_id, NULL parent_folder
),

#WITH RECURSIVE
path_name as ( # This includes path information and the name
  SELECT user_id, id, name AS path, parent_folder
  FROM path JOIN object ON path.object_id=object.id
  union all Select * from path_name where false # make this table permanent
),

 all_paths AS ( #RECURSIVE add all data
    SELECT * except(parent_folder) from path_name 
    WHERE parent_folder IS NULL
    UNION ALL
    SELECT all_paths.user_id, null, CONCAT(all_paths.path, '/', path_name.path) 
    FROM all_paths JOIN path_name ON all_paths.id = path_name.parent_folder  
)
SELECT * FROM all_paths ORDER BY user_id, path
    • 0

相关问题