如何在BigQuery中用数组循环执行查询并解决类型匹配错误

问题背景 #

我使用last_value函数填充销售系统未记录日期（无销售）的缺失值，需要保留这些日期以统计模型的0销售额，但处理多个商品时，last_value函数无法按预期实现向下填充。

尝试通过循环数组执行查询时，在BigQuery中遇到以下错误：

Query error: No matching signature for operator = for argument types: STRING, STRUCT<f0_ STRING>. Supported signature: ANY = ANY at [215:5]

示例查询代码：

DECLARE row_count INT64 DEFAULT 0;
DECLARE item_vector_array ARRAY<STRING>;
Set item_vector_array = ARRAY(Select DISTINCT(fcast_item) as fcast_item from `gcp-vc-planning-prod.09_curation.sales_history_summary`);

FOR item IN (Select * from UNNEST(item_vector_array))
DO
SELECT   
location.fcmicro_corp_cd
,sales_history.ord_dt
,sales_history.fcast_item
,location.iso_loc_type
,MAX(item_master.GLBL_BUS_LN_DESC) as glbl_bus_ln_desc
,MAX(item_master.GLBL_CTGRY_DESC) as glbl_ctgry_desc
,MAX(item_master.GLBL_SUB_CTGRY_DESC) as glbl_sub_ctgry_desc
,MAX(item_master.GLBL_PRDCT_IND_CUT_DESC) as glbl_prdct_ind_cut_desc
,MAX(item_master.ITEM_DESC) as item_desc
,c.intro_dt
-- ,IFNULL(tna.ADJ_TNA, 0) as ADJ_TNA
-- ,IFNULL(tna.TNA, 0) as TNA
,IFNULL(promo_base.promotion_type, 'regular_item') as promotion_type
,CAST(item_price.DCOST_USD as FLOAT64) as dcost_usd
,CAST(item_price.DCOST_LOCAL as FLOAT64) as dcost_local
,sum(sales_history.ord_qty) as ord_qty
,sum(sales_history.demand_usd) as demand_usd

FROM `gcp-vc-planning-prod.09_curation.sales_history_summary` sales_history


LEFT OUTER JOIN `gcp-vc-planning-prod.09_mdm.location` location 
on sales_history.loc = location.loc

LEFT JOIN `gcp-sc-demand-plan-analytics.Demand_Planning_Ingestion.ITEM_MASTER` item_master 
on sales_history.fcast_item = item_master.ITEM_NO
and location.fcmicro_corp_cd = item_master.CORP_CD
and item_master.CORP_CD = "180"

LEFT JOIN (select distinct(ITEM_NO), MAX(INTRO_DT) as intro_dt, corp_cd 
        from gcp-sc-demand-plan-analytics.Demand_Planning_Ingestion.ITEM_MASTER
        where corp_cd = "180"
        group by ITEM_NO, corp_cd) c
        on sales_history.fcast_item = c.ITEM_NO

left join `gcp-sc-demand-plan-analytics.Demand_Planning_Ingestion.ITEM_PRICE` item_price
on sales_history.fcast_item = item_price.ITEM_NO
and PARSE_DATETIME('%Y-%m-%dT%H:%M:%S', CONCAT(CAST(item_price.YR AS STRING), '-', LPAD(CAST(item_price.MO AS STRING), 2, '0'), '-', LPAD(CAST(EXTRACT(DAY FROM LAST_DAY(DATE_TRUNC(DATE(item_price.YR, item_price.MO, 1), MONTH))) AS STRING), 2, '0'), 'T00:00:00')) = PARSE_DATETIME('%Y-%m-%dT%H:%M:%S', CONCAT(CAST(EXTRACT(YEAR from sales_history.ord_dt) AS STRING), '-', LPAD(CAST(EXTRACT(MONTH from sales_history.ord_dt) AS STRING), 2, '0'), '-', LPAD(CAST(EXTRACT(DAY FROM LAST_DAY(DATE_TRUNC(DATE(EXTRACT(YEAR from sales_history.ord_dt), EXTRACT(MONTH from sales_history.ord_dt), 1), MONTH))) AS STRING), 2, '0'), 'T00:00:00'))
and item_price.CORP_CD = "180"

left join promo_base as promo_base
    on sales_history.fcast_item = promo_base.ITEM_NO

WHERE
right(sales_history.fcast_item, 3) not in ('PAT', 'SVC')
and sales_history.fcast_item <> ""
and location.fcmicro_corp_cd = "180"
and sales_history.ord_dt <= DATE_ADD(DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY), INTERVAL 1 YEAR)
and sales_history.fcast_item = item


GROUP BY
fcmicro_corp_cd
,sales_history.ord_dt
,sales_history.fcast_item
,location.iso_loc_type
,c.intro_dt
,item_price.DCOST_USD 
,item_price.DCOST_LOCAL
,promo_base.promotion_type
-- ,ADJ_TNA
-- ,TNA

ORDER BY
location.iso_loc_type
,sales_history.fcast_item
,sales_history.ord_dt

END FOR;

错误原因及修复 #

错误原因 #

循环语句FOR item IN (Select * from UNNEST(item_vector_array))中，SELECT *会将数组中的STRING元素包装成一个**STRUCT<f0_ STRING>**类型的变量，而sales_history.fcast_item是STRING类型，直接用=做比较会触发类型不匹配错误。

修复方式 #

有两种简单的修复方法：

1. 直接遍历数组元素，去掉SELECT *，让item成为STRING类型：

FOR item IN UNNEST(item_vector_array)
DO
-- 后续查询逻辑不变，WHERE子句中的`and sales_history.fcast_item = item`可直接使用

2. 引用结构体的字段，如果必须保留SELECT *，则修改WHERE子句的过滤条件：

and sales_history.fcast_item = item.f0_

更优方案：避免循环，用窗口函数一次性补全缺失值 #

BigQuery是分布式数据库，循环处理效率极低，推荐用以下方式一次性完成多商品的缺失日期填充：

1. 用GENERATE_DATE_ARRAY生成统计范围内的所有日期
2. 提取所有需要统计的商品维度
3. 将日期数组与商品、地区等维度做笛卡尔积，生成完整的日期-维度组合
4. LEFT JOIN销售数据，用IFNULL填充0销售额

示例核心逻辑：

WITH date_range AS (
  SELECT date 
  FROM UNNEST(GENERATE_DATE_ARRAY(
    DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 YEAR),
    DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY)
  )) AS date
),
item_dim AS (
  SELECT DISTINCT fcast_item, loc 
  FROM `gcp-vc-planning-prod.09_curation.sales_history_summary`
  WHERE right(fcast_item, 3) not in ('PAT', 'SVC')
    and fcast_item <> ""
),
full_combinations AS (
  SELECT dr.date, id.fcast_item, id.loc
  FROM date_range dr
  CROSS JOIN item_dim id
)
SELECT
  loc.fcmicro_corp_cd,
  fc.date as ord_dt,
  fc.fcast_item,
  loc.iso_loc_type,
  MAX(im.GLBL_BUS_LN_DESC) as glbl_bus_ln_desc,
  -- 其他维度字段...
  IFNULL(sum(sh.ord_qty), 0) as ord_qty,
  IFNULL(sum(sh.demand_usd), 0) as demand_usd
FROM full_combinations fc
LEFT JOIN `gcp-vc-planning-prod.09_curation.sales_history_summary` sh
  ON fc.date = sh.ord_dt
  AND fc.fcast_item = sh.fcast_item
  AND fc.loc = sh.loc
-- 其他JOIN逻辑...
WHERE loc.fcmicro_corp_cd = "180"
GROUP BY
  loc.fcmicro_corp_cd,
  fc.date,
  fc.fcast_item,
  loc.iso_loc_type
  -- 其他分组字段...
ORDER BY
  loc.iso_loc_type,
  fc.fcast_item,
  fc.date

这种方式无需循环，利用BigQuery的并行处理能力，效率远高于逐个商品循环查询，同时完美解决多商品缺失日期的0销售额填充问题。

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