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

Question

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

 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,执行查询时返回错误

Missing function in Analytic Expression

在大查询上。

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

谢谢你帮助我!

Answer 1

这对我来说可以。也许你忘了 enable 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;