谷歌大查询中行之间的差异

我目前正在尝试计算谷歌大查询中行之间的差异。我实际上有一个有效的查询。

 SELECT
    id, record_time, level, lag,
    (level - lag) as diff
 FROM (
   SELECT
      id, record_time, level, 
      LAG(level) OVER (ORDER BY id, record_time) as lag
   FROM (
      SELECT
        *
      FROM
        TABLE_QUERY(MY_TABLES))
   ORDER BY
      1, 2 ASC
    )
 GROUP BY 1, 2, 3, 4
 ORDER BY 1, 2 ASC

但我正在处理大数据，有时我会收到内存限制警告，不允许我执行查询。所以，我想了解为什么我不能像下面这样进行优化查询。我认为它将允许处理更多记录而不会出现内存限制警告。

   SELECT
      id, record_time, level,
      level - LAG(level, 1) OVER (ORDER BY id, record_time) as diff
   FROM (
      SELECT
        *
      FROM
        TABLE_QUERY(MY_TABLES))
   ORDER BY
      1, 2 ASC

这种功能level - LAG(level, 1) OVER (ORDER BY id, record_time) as diff,执行查询时返回错误

解析表达式中缺少函数

关于大查询。

我也尝试把( )进入这个功能，但它不起作用。

谢谢你帮助我！

这对我来说可以。也许你忘记了启用标准 SQL https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql？这是一个例子：

WITH Input AS (
  SELECT 1 AS id, TIMESTAMP '2017-10-17 00:00:00' AS record_time, 2 AS level UNION ALL
  SELECT 2, TIMESTAMP '2017-10-16 00:00:00', 3 UNION ALL
  SELECT 1, TIMESTAMP '2017-10-16 00:00:00', 4
)
SELECT
 id, record_time, level, lag,
 (level - lag) as diff
FROM (
  SELECT
    id, record_time, level, 
    LAG(level) OVER (ORDER BY id, record_time) as lag
  FROM Input
)
GROUP BY 1, 2, 3, 4
ORDER BY 1, 2 ASC;