BigQuery查询执行资源超限，如何重构查询优化内存占用？

针对你遇到的每日调度任务因多次ROW_NUMBER()窗口函数导致内存耗尽的问题，以下是几种优化方案，涵盖你提到的方法并补充更高效的实现方式：

1. 用QUALIFY简化ROW_NUMBER逻辑，减少内存开销 #

原代码的嵌套子查询可以用QUALIFY子句简化，BigQuery的执行引擎会更高效地处理这种写法，避免不必要的中间结果存储：

CREATE TEMP TABLE main_info AS
WITH orders_1 AS(
    SELECT *
    FROM order_1
    QUALIFY ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY update_time DESC) = 1
),
orders_2 AS(
    SELECT *
    FROM order_2
    QUALIFY ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY update_time DESC) = 1
),
orders_3 AS(
    SELECT *
    FROM order_3
    QUALIFY ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY update_time DESC) = 1
)
SELECT *
FROM orders_1 o1
LEFT JOIN orders_2 o2 ON o1.order_id = o2.order_id
LEFT JOIN orders_3 o3 ON o1.order_id = o3.order_id;

这种写法逻辑和原代码一致，但执行计划更紧凑，能降低部分内存占用。

2. ARRAY_AGG替代ROW_NUMBER，优化内存模型 #

ARRAY_AGG通过聚合而非窗口排序获取最新记录，内存占用通常比ROW_NUMBER()更低，尤其在分区内记录较多的场景：

CREATE TEMP TABLE main_info AS
WITH orders_1 AS(
    SELECT 
        order_id,
        -- 按update_time倒序聚合，取第一个元素的所有字段
        ARRAY_AGG(STRUCT(* ORDER BY update_time DESC LIMIT 1))[OFFSET(0)].*
    FROM order_1
    GROUP BY order_id
),
orders_2 AS(
    SELECT 
        order_id,
        ARRAY_AGG(STRUCT(* ORDER BY update_time DESC LIMIT 1))[OFFSET(0)].*
    FROM order_2
    GROUP BY order_id
),
orders_3 AS(
    SELECT 
        order_id,
        ARRAY_AGG(STRUCT(* ORDER BY update_time DESC LIMIT 1))[OFFSET(0)].*
    FROM order_3
    GROUP BY order_id
)
SELECT *
FROM orders_1 o1
LEFT JOIN orders_2 o2 ON o1.order_id = o2.order_id
LEFT JOIN orders_3 o3 ON o1.order_id = o3.order_id;

注意：如果表中有重复order_id+update_time的记录，ARRAY_AGG会随机取一条，和ROW_NUMBER()的行为一致；如果需要确定性，可以在ORDER BY中加额外字段（比如主键）。

3. 分阶段物化临时表，分散内存压力 #

如果单查询处理多表窗口函数内存仍超限，可以将每张表的最新记录单独物化到临时表，再进行关联。临时表是物理存储，不会在关联阶段重复计算窗口函数：

-- 单独物化每张表的最新记录
CREATE TEMP TABLE orders_1_latest AS
SELECT *
FROM order_1
QUALIFY ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY update_time DESC) = 1;

CREATE TEMP TABLE orders_2_latest AS
SELECT *
FROM order_2
QUALIFY ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY update_time DESC) = 1;

CREATE TEMP TABLE orders_3_latest AS
SELECT *
FROM order_3
QUALIFY ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY update_time DESC) = 1;

-- 最后关联临时表
CREATE TEMP TABLE main_info AS
SELECT *
FROM orders_1_latest o1
LEFT JOIN orders_2_latest o2 ON o1.order_id = o2.order_id
LEFT JOIN orders_3_latest o3 ON o1.order_id = o3.order_id;

4. 前置数据过滤，减少处理量 #

如果业务允许，先过滤掉不必要的历史数据（比如只取最近7天的记录），再执行窗口/聚合操作，能大幅降低内存占用：

orders_1 AS(
    SELECT *
    FROM order_1
    -- 根据业务调整时间范围，只处理近期数据
    WHERE update_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
    QUALIFY ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY update_time DESC) = 1
)

5. 长期优化：利用分区/集群表特性 #

如果原表未做分区或集群，建议按update_time分区、按order_id集群。BigQuery会利用物理存储的有序性，减少窗口排序的内存开销，提升整体执行效率。

方案优先级建议 #

• 数据量中等：优先用QUALIFY简化ROW_NUMBER，代码简洁且执行计划优化充足；
• 内存仍紧张：换成ARRAY_AGG聚合写法，内存模型更高效；
• 超大表场景：分阶段物化临时表+前置过滤，分散内存压力。

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