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

下面两个表为我提供了每个用户拥有的文件路径:

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
);

例如，对于用户1，其目录 struct 如下所示:

MyFile1
MyFile2
MyFolder/
    SomeFile

我正在try 构建一个递归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