google-analytics - 使用 IN ( ) 进行子查询时使用多个字段

Question

在 Google BigQuery 中，我必须执行以下操作:

SELECT hits.item.productName
FROM [‘Dataset Name’ ]
WHERE date, visitId, fullVisitorId IN (
  SELECT date, visitId, fullVisitorId
  FROM [‘Dataset Name’ ]
  WHERE hits.item.productName CONTAINS 'Product Item Name A'
  AND totals.transactions>=1)

但是，这似乎不受支持。除了使用 JOIN 之外，我还有什么选择？

Answer 1

改为加入 JOIN。

相当于:

SELECT COUNT(*), stn, a.wban, FIRST(name) name, FIRST(country) country
FROM [fh-bigquery:weather_gsod.gsod2014] a
WHERE stn, wban IN
  (SELECT usaf, wban FROM [fh-bigquery:weather_gsod.stations] WHERE country='UK')
GROUP BY 2, 3
ORDER BY 1 DESC

将是:

SELECT COUNT(*), stn, a.wban, FIRST(name) name, FIRST(country) country
FROM [fh-bigquery:weather_gsod.gsod2014] a
JOIN [fh-bigquery:weather_gsod.stations] b
ON a.stn=b.usaf AND a.wban=b.wban
WHERE country='UK'
GROUP BY 2, 3
ORDER BY 1 DESC