我想在Google BigQuery上运行以下查询:
SELECT SUM(var1) AS Revenue
FROM [table1]
WHERE timeStamp = (SELECT MAX(timeStamp) FROM [table1])
我遇到了以下错误:
错误:在第3行第19列遇到了“”,期望其中之一:
这在BigQuery中不被支持吗?如果不支持,是否有更优雅的替代方案?
我想在Google BigQuery上运行以下查询:
SELECT SUM(var1) AS Revenue
FROM [table1]
WHERE timeStamp = (SELECT MAX(timeStamp) FROM [table1])
不支持在比较谓词中使用子查询,但是您可以使用 IN。
SELECT SUM(var1) AS Revenue
FROM [table1]
WHERE timeStamp IN (SELECT MAX(timeStamp) FROM [table1])
select SUM(var1) AS Revenue
From
(SELECT var1
,RANK() OVER (ORDER BY timestamp DESC) as RNK
FROM [table1]
)
where RNK=1
我不知道在BQ中如何运作,但在其他数据库技术中,这种方法更有效,因为它只涉及单个表扫描而不是两个。
SELECT SUM(var1) AS Revenue FROM [table1] t WHERE NOT EXISTS (SELECT * FROM [table1] tt WHERE tt.timeStamp > t.timeStamp)
- Sergey Kalinichenko