google-bigquery - 为什么 LAST_VALUE 不返回最后一个值？

Question

我想找到 y 的最后一个值使用这样的查询在有序分区上:

SELECT
  x,
  LAST_VALUE(y) OVER (PARTITION BY x ORDER BY y ASC)
FROM table

但是 LAST_VALUE返回许多不是 y 的最后一个值(在本例中为最大值)的值对于给定的分区。为什么？

(在这种情况下，可以使用 MAX 代替 LAST_VALUE 来查找最大值，但为什么 LAST_VALUE 也不会返回最大值？)

Answer 1

TLDR:你想要的查询是:

SELECT
  x,
  LAST_VALUE(y) OVER (PARTITION BY x ORDER BY y ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM table

可能后跟 GROUP BY折叠来自分析函数的重复输出行。

当然，使用 MAX 更简单如果这就是您所需要的，那么在无序分区上:

SELECT
  x,
  MAX(y) OVER (PARTITION BY x)
FROM table

在回答这个问题之前，这里有一些关于解析函数(又名窗口函数)的背景知识。以下所有内容都是标准 SQL，并非特定于 BigQuery。

首先，分析函数不是聚合函数。聚合函数将多个输入行合并为一个输出行，而分析函数为每个输入行计算一个输出行。所以你需要确保你正在考虑每个输入行的输出是什么。

其次，分析函数在作为行所属“分区”子集的行“窗口”上运行。输入行的分区由 PARTITION BY 确定子句，或者如果您希望分区是整个输入行集，则可以省略它。窗口由 ROWS 给出子句，但如果您不指定它(用户通常不指定)，则默认为整个分区(未应用排序时)或分区中从第一行到当前行的行集(当 ORDER BY 存在时)。请注意，分区中每个输入行的窗口可能不同!

现在，回到 LAST_VALUE .尽管上述默认窗口在许多情况下是合理的(例如，计算累积总和)，但它在 LAST_VALUE 上的效果非常差。 . LAST_VALUE函数返回窗口最后一行的值，默认情况下窗口最后一行是当前行。

因此，要解决此问题，您需要明确指定 LAST_VALUE 的窗口是整个分区，而不仅仅是直到当前行的行。你可以这样做:

SELECT x, LAST_VALUE(y) OVER (PARTITION BY x ORDER BY y ASC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM table

为了测试这一点，这里有一个例子:

SELECT
  x,
  FIRST_VALUE(x) OVER (ORDER BY x ASC) first_asc,
  FIRST_VALUE(x) OVER (ORDER BY x DESC) first_desc,
  LAST_VALUE(x) OVER (ORDER BY x ASC) last_asc,
  LAST_VALUE(x) OVER (ORDER BY x DESC) last_desc,
FROM
  (SELECT 4 as x),
  (SELECT 2 as x),
  (SELECT 1 as x),
  (SELECT 3 as x)

x,first_asc,first_desc,last_asc,last_desc
1,1,4,1,1
2,1,4,2,2
3,1,4,3,3
4,1,4,4,4

请注意 LAST_VALUE返回 1, 2, 3, 4 而不仅仅是 4，因为每个输入行的窗口都会发生变化。

现在让我们指定一个窗口，它是整个分区:

SELECT
  x,
  FIRST_VALUE(x) OVER (ORDER BY x ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) first_asc,
  FIRST_VALUE(x) OVER (ORDER BY x DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) first_desc,
  LAST_VALUE(x) OVER (ORDER BY x ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) last_asc,
  LAST_VALUE(x) OVER (ORDER BY x DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) last_desc,
FROM
  (SELECT 4 as x),
  (SELECT 2 as x),
  (SELECT 1 as x),
  (SELECT 3 as x)

x,first_asc,first_desc,last_asc,last_desc
1,1,4,4,1
2,1,4,4,1
3,1,4,4,1
4,1,4,4,1

现在我们得到 4 个 LAST_VALUE正如预期的那样。