google-bigquery - 在 BigQuery 中计算移动最大值

Question

给定一个带有一些排序和一些数字的 BigQuery 表，我想计算数字的“移动最大值”——类似于移动平均值，但取而代之的是最大值。来自 Trying to calculate EMA (exponential moving average) using BigQuery似乎最好的方法是使用 LEAD()然后自己进行聚合。 ( Bigquery moving average 基本上暗示了 CROSS JOIN ，但考虑到数据的大小，这似乎会很慢。)

理想情况下，我可能只能从内部查询返回一个重复字段，而不是 20 个单独的字段，然后在重复字段上使用正常聚合，但我还没有想出办法做到这一点，所以我我坚持滚动我自己的聚合。虽然这对于求和或平均来说很容易，但计算最大内联非常棘手，而且我还没有找到一个好的方法来做到这一点。

(为了使用公共(public)数据集，下面的示例当然有些人为。它们还对 3 个元素进行最大滚动，而我想在 20 左右进行。我已经以编程方式生成查询，所以让它短不是什么大问题。)

一种方法是执行以下操作:

SELECT word,
  (CASE
    WHEN word_count >= word_count_1 AND word_count >= word_count_2 THEN word_count
    WHEN word_count_1 >= word_count AND word_count_1 >= word_count_2 THEN word_count_1
    ELSE word_count_2 END
    ) AS max_count
FROM (
  SELECT word, word_count,
    LEAD(word_count, 1) OVER (ORDER BY word) AS word_count_1,
    LEAD(word_count, 2) OVER (ORDER BY word) AS word_count_2,
  FROM [publicdata:samples.shakespeare]
  WHERE corpus = 'macbeth'
)

这是 O(n^2)，但它至少有效。我也可以做 IF 的嵌套链s，像这样:

SELECT word,
  IF(word_count >= word_count_1,
    IF(word_count >= word_count_2, word_count, word_count_2),
    IF(word_count_1 >= word_count_2, word_count_1, word_count_2)) AS max_count
FROM ...

这是 O(n) 来评估，但查询大小是 n 的指数，所以我认为这不是一个好的选择；当然它会超过 n=20 的 BigQuery 查询大小限制。我也可以做 n 个嵌套查询:

SELECT word,
  IF(word_count_2 >= max_count, word_count_2, max_count) AS max_count
FROM (
  SELECT word,
    IF(word_count_1 >= word_count, word_count_1, word_count) AS max_count
  FROM ...
)

不过，从性能方面看，执行 20 个嵌套查询似乎不是一个好主意。

有没有做这种查询的好方法？如果不是，我是否纠正了 n 大约 20，第一个是最不坏的？

Answer 1

我用于滚动窗口的技巧:带有数字表的 CROSS JOIN。在这种情况下，为了有一个 3 年的移动窗口，我用数字 0、1、2 交叉连接。然后，您可以为每个组创建一个 ID( ending_at_year == year-i )并按此分组。

SELECT ending_at_year, MAX(mean_temp) max_temp, COUNT(DISTINCT year) c
FROM 
(
 SELECT mean_temp, year-i ending_at_year, year
 FROM [publicdata:samples.gsod] a
 CROSS JOIN 
  (SELECT i FROM [fh-bigquery:public_dump.numbers_255] WHERE i<3) b
 WHERE station_number=722860
)
GROUP BY ending_at_year
HAVING c=3
ORDER BY ending_at_year;