Éviter l'erreur des sous-requêtes corrélées dans BigQuery

Question

J'ai une requête simple pour obtenir le taux de change en vigueur au moment de la création d'une transaction :

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

Cela déclenche une erreur :

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.' 

J'ai essayé plusieurs types de jointures et de sous-requêtes nommées, mais aucune ne semble fonctionner. Quelle est la meilleure façon d'y parvenir ? Il s'agit d'un scénario très courant qui devrait être assez simple à mettre en œuvre dans Standard Sql de BQ.

Answer 1

Ci-dessous, pour 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

Answer 2

J'ai remarqué un comportement similaire avec d'autres sous-requêtes corrélées. Elles sont utiles, mais ne peuvent pas toujours être automatiquement modélisées en JOINs par BigQuery.

Cas similaire qui fonctionne :

#standardSQL
SELECT name, (
  SELECT AVG(temp) 
  FROM `bigquery-public-data.noaa_gsod.gsod2017` b
  WHERE a.usaf=b.stn  
) temp
FROM `bigquery-public-data.noaa_gsod.stations` a
LIMIT 10

Ne fonctionne pas :

#standardSQL
SELECT name, (
  SELECT temp 
  FROM `bigquery-public-data.noaa_gsod.gsod2017` b
  WHERE a.usaf=b.stn  
  ORDER BY da 
  LIMIT 1
) temp
FROM `bigquery-public-data.noaa_gsod.stations` a
LIMIT 10

Fixer :

#standardSQL
SELECT name, ARRAY_AGG(temp ORDER BY da LIMIT 1) temp
FROM `bigquery-public-data.noaa_gsod.stations` a
JOIN `bigquery-public-data.noaa_gsod.gsod2017` b
ON a.usaf=b.stn  
GROUP BY 1
LIMIT 10

(donnez-moi un ensemble de données publiques et j'écrirai une requête qui fonctionnera avec vos données)