谷歌大查询行之间的差异 [英] Differences between row in 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，执行查询时返回错误

在分析表达式中缺少函数

在Big Query上。

我也尝试将（）放入此函数中，但它不起作用。

感谢您的帮助！

解决方案

它适用于我。也许你忘了启用标准SQL ？这里是一个例子：

pre $ WITH输入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输入
）
GROUP BY 1， 2，3，4
1，2 ASC命令;


I'm currently attempting to calculate differences between rows in google big query. I actually have a working 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

But I'm working with big data and sometimes I have memory limit warning that does not let me execute the query. So, I would like to understand why I cant do an optimized query like bellow. I think it will allow work with more records without memory limit warning.

   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

This kind of function level - LAG(level, 1) OVER (ORDER BY id, record_time) as diff, when the query is executed returns the error

Missing function in Analytic Expression

on Big Query.

I also tried to put ( ) into this function but it does not work as well.

Thanks for helping me!

解决方案

It works fine for me. Maybe you forgot to enable standard SQL? Here is an example:

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;

这篇关于谷歌大查询行之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！