hadoop - 意外行为之前的配置单元行

Question

鉴于这个非常简单的数据集:

+--------+-----+
| Bucket | Foo |
+--------+-----+
|      1 | A   |
|      1 | B   |
|      1 | C   |
|      1 | D   |
+--------+-----+

我想在上一行中查看 Foo 的值:

select
foo,
max(foo) over (partition by bucket order by foo rows between 1 preceding and 1 preceding) as prev_foo
from
...

这给了我:

+--------+-----+----------+
| Bucket | Foo | Prev_Foo |
+--------+-----+----------+
|      1 | A   | A        |
|      1 | B   | A        |
|      1 | C   | B        |
|      1 | D   | C        |
+--------+-----+----------+

为什么第一行返回“A”？我希望它为空。它在我寻找空值的地方抛出了计算。我可以通过在其中添加一个 row_number() 来解决这个问题，但我更愿意用更少的计算来处理它。

Answer 1

使用 LAG 函数获取上一行:

LAG(foo) OVER(partition by bucket order by foo) as Prev_Foo