Google BigQuery SQL在Power BI中报错：global_trade_item_number列歧义排查

问题原因 #

你的SQL查询结果中出现了重复的global_trade_item_number列：

• 你在SELECT语句中同时指定了sd.global_trade_item_number（来自销售数据）和id.*（来自商品数据），而商品表d_item本身也包含global_trade_item_number列。
• BigQuery允许结果集存在重复列名，但Power BI使用的ODBC驱动对列名唯一性要求更严格，因此触发了歧义错误。

解决方案 #

方案1：明确指定商品数据所需列（推荐） #

避免使用id.*，直接列出需要的商品表列，排除重复的global_trade_item_number：

WITH
  sale_data AS (
  SELECT
    d_date_order,
    store_site_id,
    selling_entity_id,
    selling_entity_type,
    global_trade_item_number,
    number_of_items,
    receipt_line_amount,
    consumer_spend_amount,
    store_sale_amount,
    margin_amount,
    store_identifier
  FROM
    `ac16-p-conlaybi-prd-4257.consumer_sales_secure.f_sale_receipt_line_day`
  WHERE
    d_date_sale >= "2023-11-01"
    AND selling_entity_type = "Special" ),
  store_data AS (
  SELECT
    store_identifier,
    store_name
  FROM
    `ac16-p-conlaybi-prd-4257.store.d_store` ),
  item_data AS (
  SELECT
    -- 替换为你实际需要的商品表列，不要包含global_trade_item_number
    item_name,
    category,
    brand,
    unit_price
    -- 其他所需列...
  FROM
    `ac16-p-conlaybi-prd-4257.item.d_item` )
SELECT
  sd.d_date_order,
  sd.store_site_id,
  sd.selling_entity_id,
  sd.selling_entity_type,
  sd.global_trade_item_number,
  sd.number_of_items,
  sd.receipt_line_amount,
  sd.consumer_spend_amount,
  sd.store_sale_amount,
  sd.margin_amount,
  sd.store_identifier,
  st.store_name,
  id.*
FROM
  sale_data sd
JOIN
  store_data st
ON
  sd.store_identifier = st.store_identifier
LEFT JOIN
  item_data id
ON
  sd.global_trade_item_number = id.global_trade_item_number

方案2：重命名或只保留一个来源的重复列 #

如果必须保留商品表的所有列，可选择：

1. 只保留其中一个来源的global_trade_item_number
2. 给两个重复列分别重命名，确保列名唯一

示例代码（重命名+排除重复列）：

WITH
  sale_data AS (
  SELECT
    d_date_order,
    store_site_id,
    selling_entity_id,
    selling_entity_type,
    global_trade_item_number,
    number_of_items,
    receipt_line_amount,
    consumer_spend_amount,
    store_sale_amount,
    margin_amount,
    store_identifier
  FROM
    `ac16-p-conlaybi-prd-4257.consumer_sales_secure.f_sale_receipt_line_day`
  WHERE
    d_date_sale >= "2023-11-01"
    AND selling_entity_type = "Special" ),
  store_data AS (
  SELECT
    store_identifier,
    store_name
  FROM
    `ac16-p-conlaybi-prd-4257.store.d_store` ),
  item_data AS (
  SELECT
    *
  FROM
    `ac16-p-conlaybi-prd-4257.item.d_item` )
SELECT
  sd.d_date_order,
  sd.store_site_id,
  sd.selling_entity_id,
  sd.selling_entity_type,
  sd.global_trade_item_number AS sale_gtin, -- 重命名销售端的列
  sd.number_of_items,
  sd.receipt_line_amount,
  sd.consumer_spend_amount,
  sd.store_sale_amount,
  sd.margin_amount,
  sd.store_identifier,
  st.store_name,
  id.* EXCEPT(global_trade_item_number) -- 排除商品表中重复的列
FROM
  sale_data sd
JOIN
  store_data st
ON
  sd.store_identifier = st.store_identifier
LEFT JOIN
  item_data id
ON
  sd.global_trade_item_number = id.global_trade_item_number

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