unnest和join |大查询

izkcnapc  于 2021-08-01  发布在  Java
关注(0)|答案(1)|浏览(396)

我可以使用以下代码取消大查询中的一个表:

SELECT * EXCEPT(instance, line_items) FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY id) AS instance
  FROM `shopify.orders`
), UNNEST(line_items) as item

其中示例=1
此外,我还可以使用以下代码将此表(unnested)与另一个表连接起来:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY id) AS instance
  FROM `shopify.orders`
  JOIN `google_analytics.GA`
  ON name = TransactionID

)

WHERE instance = 1

但是,我不知道如何连接这两个表,同时仍然从我的第一个查询中取消对值的测试。你知道怎么把一个不受欢迎的表和另一个表连接起来吗?

zfycwa2u

zfycwa2u1#

join 工作?

SELECT * EXCEPT(instance, line_items)
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY id) AS instance
      FROM `shopify.orders`
     ) o CROSS JOIN
     UNNEST(o.line_items) as item JOIN
     `google_analytics.GA` ga
      ON name = TransactionID

相关问题