避免 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 Standard 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 中的相关子查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！