sql - 使用 Stitch 进行 Shopify 集成的简单 BigQuery 连接错误

Question

使用 Stitch 数据集成在 BigQuery 中编写了以下内容，以从 Shopify 中提取表。非常简单，但我不熟悉嵌套，我怀疑这可能会导致我的查询失败。

任何帮助将不胜感激。查询和错误如下：

询问

SELECT distinct
order_number
,so.created_at
,buyer_accepts_marketing
,currency
,value. sku
,value. vendor
,value. variant_title
,value. gift_card
,value. product_id
,customer. id
,shipping_address. province
,shipping_address. country
,shipping_address. city
,shipping_address. longitude
,shipping_address. country_code
,shipping_address. latitude
,sum(total_price_usd) as price_usd
,sum(total_price) as total_price
,sum(total_discounts) as total_discounts
FROM `shopifytest-272721.testconn.orders` as so
CROSS JOIN UNNEST(line_items)

left join

(SELECT distinct
created_at
,id
,product_type
,title
,value.sku
,value.fulfillment_service
,value.inventory_quantity
FROM `shopifytest-272721.testconn.products` 
CROSS JOIN UNNEST(variants)) as sp 

on so.product_id = sp.id 
and so.created_at = sp.created_at

group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16

在 [36:7] 内部未找到错误 名称 product_id

标签： sqlgoogle-bigqueryleft-join

Answer 1

value.product_id在您拥有但您正在加入的第一个选择中so.product_id = sp.id，该字段product_id在此级别未知，但如果您设置别名，它可能会起作用：

value.product_id as product_id

另一个嘈杂的字段是so.created_at在第一个查询中。让我为您的查询建议以下结构：

SELECT distinct
order_number
,created_at
,buyer_accepts_marketing
,currency
,value. sku
,value. vendor
,value. variant_title
,value. gift_card
,value. product_id as product_id
,customer. id
,shipping_address. province
,shipping_address. country
,shipping_address. city
,shipping_address. longitude
,shipping_address. country_code
,shipping_address. latitude
,sum(total_price_usd) as price_usd
,sum(total_price) as total_price
,sum(total_discounts) as total_discounts
FROM `shopifytest-272721.testconn.orders` as so --if line_items is contained into so:
CROSS JOIN UNNEST(so.line_items) as items

left join

(SELECT distinct
created_at
,id
,product_type
,title
,value.sku
,value.fulfillment_service
,value.inventory_quantity
FROM `shopifytest-272721.testconn.products` as p
CROSS JOIN UNNEST(p.variants)) as sp 

on items.product_id = sp.id 
and items.created_at = sp.created_at

group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16

此外，由于您不熟悉嵌套，我建议您检查UNNEST、Flattening arrays、Join Types。