如何在BigQuery现有行之间插入另一表的新行？

需求说明 #

现有两张BigQuery表：
Table 1（产品配料表）：

Table 2（餐品组成表）：

需要将Table 1中tomato juice对应的所有配料行，插入到Table 2中healthy set下的tomato juice行之后、salad行之前，最终输出表结构保持原有的两列不变，效果如下：

可行性与实现方案 #

这个需求完全可行，核心思路是通过临时生成的排序序号控制行的位置，最终输出时去掉临时序号，只保留原表的两列字段。

具体SQL实现如下：

WITH table2_with_order AS (
  -- 给Table 2的行分配临时排序序号，若原表有可靠排序字段（如自增ID、创建时间），请替换ROW_NUMBER()的排序依据
  SELECT 
    `餐品名称`, 
    `配料`,
    ROW_NUMBER() OVER() AS rn
  FROM `你的项目ID.数据集ID.table2`
),
tj_row_position AS (
  -- 定位Table 2中tomato juice所在的行号
  SELECT rn FROM table2_with_order WHERE `配料` = 'tomato juice'
),
combined_data AS (
  -- 1. 先取Table 2中tomato juice及之前的行
  SELECT `餐品名称`, `配料`, rn FROM table2_with_order WHERE rn <= (SELECT rn FROM tj_row_position)
  UNION ALL
  -- 2. 插入Table 1中tomato juice的配料行，用小数序号保证插入位置在目标行之后
  SELECT 
    '', 
    `配料`, 
    (SELECT rn FROM tj_row_position) + 0.1 + ROW_NUMBER() OVER(ORDER BY `配料`) * 0.01
  FROM `你的项目ID.数据集ID.table1` WHERE `产品名称` = 'tomato juice'
  UNION ALL
  -- 3. 再取Table 2中tomato juice之后的行
  SELECT `餐品名称`, `配料`, rn FROM table2_with_order WHERE rn > (SELECT rn FROM tj_row_position)
)
-- 按临时序号排序，输出仅保留原有的两列
SELECT `餐品名称`, `配料`
FROM combined_data
ORDER BY rn;

注意事项 #

• 若Table 2的行顺序没有固定的可靠依据（比如无自增ID、时间戳），ROW_NUMBER() OVER()生成的临时序号可能因BigQuery执行计划变化出现波动，建议优先使用原表中自带的排序字段来生成rn。
• 如果需要将结果持久化，可以把上述查询的结果写入到一张新的BigQuery表中，新表的结构会和原表一致，不会包含临时的rn列。

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