在GBQ中创建月末日期数组并循环计算sku月末数量的问题排查

• 循环内的独立SELECT语句无输出/存储目标
  BigQuery 脚本执行时，FOR 循环内部的独立 SELECT 语句不会像普通单次查询一样直接返回结果集，也不会自动拼接多次循环的输出。你没有指定将每次查询的结果写入临时表、永久表或者存入变量，所以执行后要么看不到任何输出，要么直接触发执行错误。
• （优化项）循环统计效率极低，无需循环即可实现需求
  逐月末累计统计的逻辑完全可以通过集合运算实现，避免循环带来的额外性能开销：当 warehouse 表数据量较大时，循环相当于要全表扫描N次（N为统计的月份数量），执行效率会非常差。

方案1：保留循环逻辑的修改版 #

提前创建临时表存储每次循环的统计结果，执行完成后统一查询临时表即可拿到所有月份的统计数据：

DECLARE eom_ranges ARRAY<DATE>;
-- 生成月末数组，第一个值为2021-01-31，符合你的需求
SET eom_ranges =   (SELECT ARRAY_AGG(LAST_DAY(dt, MONTH)) AS eoms 
                    FROM UNNEST(GENERATE_DATE_ARRAY('2021-01-01',CURRENT_DATE(), INTERVAL 1 MONTH)) AS dt);

-- 提前创建临时表存储统计结果
CREATE TEMP TABLE IF NOT EXISTS sku_monthly_stats (
    extraction_date DATE,
    sku_id STRING, -- 可根据你实际的sku_id字段类型调整
    invoiced_quantity INT64
);

FOR field IN
  (SELECT * from UNNEST(eom_ranges) AS `date`)
DO
    -- 将单次循环的统计结果插入临时表
    INSERT INTO sku_monthly_stats
    SELECT
        field AS extraction_date
        , wrh.sku_id
        , SUM(wrh.amount) AS invoiced_quantity
    FROM `xxx.xxx.warehouse` as wrh
    WHERE wrh.modified_date <= field
    GROUP BY 1,2
    HAVING invoiced_quantity <> 0;
END FOR;

-- 查询所有月份的合并统计结果
SELECT * FROM sku_monthly_stats ORDER BY extraction_date, sku_id;

方案2：更高效的无循环实现（推荐） #

用CROSS JOIN关联生成的月末日期序列，一次查询即可完成所有月份的统计，性能远高于循环实现：

WITH eom_ranges AS (
    -- 生成所有需要统计的月末日期
    SELECT LAST_DAY(dt, MONTH) AS extraction_date
    FROM UNNEST(GENERATE_DATE_ARRAY('2021-01-01',CURRENT_DATE(), INTERVAL 1 MONTH)) AS dt
)
SELECT
    eom.extraction_date,
    wrh.sku_id,
    SUM(wrh.amount) AS invoiced_quantity
FROM `xxx.xxx.warehouse` wrh
CROSS JOIN eom_ranges eom
WHERE wrh.modified_date <= eom.extraction_date
GROUP BY 1,2
HAVING invoiced_quantity <> 0
ORDER BY 1,2;

内容的提问来源于stack exchange，提问作者L-square