获取基于另一列的条件的每一行的最新记录(Hive SQL)

Question

抱歉，如果这是一个重复的问题，但我还没有能够找到直接回答我的问题的东西。我有一个表，看起来像:

Person | Date       | In-office 
-------------------------------
1      | 01-01-2021 | 0
1      | 01-02-2021 | 1
1      | 01-04-2021 | 0
1      | 01-08-2021 | 1
2      | 01-02-2021 | 1
2      | 01-05-2021 | 0
2      | 01-09-2021 | 0
3      | 01-01-2021 | 0
3      | 01-02-2021 | 1
3      | 01-06-2021 | 0
3      | 01-09-2021 | 1

我想添加第四列，对于每行，最近的日期早于该行中Person在职的日期:

Person | Date       | In-office | Most recent in-office 
-------------------------------------------------------
1      | 01-01-2021 | 0         | Null
1      | 01-02-2021 | 1         | Null
1      | 01-04-2021 | 0         | 01-02-2021
1      | 01-08-2021 | 1         | 01-02-2021
2      | 01-02-2021 | 1         | Null
2      | 01-05-2021 | 0         | 01-02-2021
2      | 01-09-2021 | 0         | 01-02-2021
3      | 01-01-2021 | 0         | Null
3      | 01-02-2021 | 1         | Null
3      | 01-06-2021 | 1         | 01-02-2021
3      | 01-09-2021 | 1         | 01-06-2021

是否有一个简单的方法来做到这一点在Hive?我还没有能够找出一种方法来做到这一点使用窗口函数/分区。原则上，将表连接到person上并应用一些明智的过滤器和聚合应该可以工作，但我的实际数据是在数千万行的数量级上，在实践中，考虑到我的资源限制，这是不可行的。

任何帮助都将非常感激!

Answer 1

您可以尝试使用MAX窗口函数并使用(RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)将边界限制在前面的行。通过根据该人员在case表达式(CASE WHEN in_office=1 THEN cdate END)中是否在职来过滤日期，并使用over子句的顺序(ORDER BY date)和范围，我们可以检索该人员最在职的日期，例如

select
*,
MAX(CASE WHEN in_office=1 THEN date END) OVER (
PARTITION BY person 
ORDER BY date 
RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING 
) as most_recent_in_office
from sample_data