本文介绍了避免BigQuery中的相关子查询错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个简单的查询来获取创建交易时使用的货币汇率:
I have a simple query to obtain the currency rate in use at the time a transaction was created:
SELECT t.orderid, t.date, (SELECT rate FROM sources.currency_rates r WHERE currencyid=1 AND r.date>=t.date ORDER BY date LIMIT 1) rate FROM sources.transactions t这会触发错误:
Error: Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.'我尝试了几种类型的联接和命名子查询,但是似乎都没有用.做到这一点的最佳方法是什么?似乎是一个非常常见的场景,应该很容易在BQ的标准Sql中实现.
I've tried with several types of joins and named subqueries, but none seem to work. What is the best way to accomplish this? Seems like a very common scenario that should be quite straightforward to implement in BQ's Standard Sql.
推荐答案以下是BigQuery标准SQL
Below is for BigQuery Standard SQL
#standardSQL SELECT t.orderid AS orderid, t.date AS date, ARRAY_AGG(r.rate ORDER BY r.date LIMIT 1)[SAFE_OFFSET(0)] AS rate FROM `sources.transactions` AS t JOIN `sources.currency_rates` AS r ON currencyid = 1 AND r.date >= t.date GROUP BY orderid, date更多推荐
避免BigQuery中的相关子查询错误
发布评论