Athena与Spark中FIRST_VALUE函数使用问题及实现方案咨询

问题场景 #

执行以下SQL代码：

select id
      ,id2
      ,FIRST_VALUE(CASE WHEN app THEN date0 ELSE NULL END) IGNORE NULLS  OVER (PARTITION BY id ORDER BY date0) as date_result
from (
select 1 id, 22 as id2, false app, Date'2019-03-13' as date0
union
select 1 id, 23 as id2, true app, Date'2019-03-14' as date0
union
select 1 id, 23 as id2, true app, Date'2019-03-15' as date0
)

在Athena中得到的实际结果：

预期结果是所有行的date_result均为2019-03-14。

错误原因 #

FIRST_VALUE函数的窗口默认范围是**RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW**，即只包含当前行及之前的行：

• 第一行的date0最早，对应的CASE结果为NULL，此时窗口内没有非NULL值，即便用了IGNORE NULLS也无法返回有效结果；
• 后面的行因为窗口已经包含了2019-03-14这条非NULL记录，所以能正确取到值。

你误以为IGNORE NULLS会扫描整个分区的所有行，但实际上默认窗口范围限制了扫描范围。

解决方法 #

方法1：修改窗口范围为整个分区 #

通过显式指定窗口范围，让FIRST_VALUE扫描分区内的所有行：

select id
      ,id2
      ,FIRST_VALUE(CASE WHEN app THEN date0 ELSE NULL END) IGNORE NULLS  
        OVER (PARTITION BY id ORDER BY date0 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as date_result
from (
select 1 id, 22 as id2, false app, Date'2019-03-13' as date0
union
select 1 id, 23 as id2, true app, Date'2019-03-14' as date0
union
select 1 id, 23 as id2, true app, Date'2019-03-15' as date0
)

方法2：使用MIN函数简化实现 #

你要的是分区内第一个app=true的日期，本质就是分区里最小的app=true的日期，用MIN函数更直观，无需考虑窗口范围：

select id
      ,id2
      ,MIN(CASE WHEN app THEN date0 ELSE NULL END) OVER (PARTITION BY id) as date_result
from (
select 1 id, 22 as id2, false app, Date'2019-03-13' as date0
union
select 1 id, 23 as id2, true app, Date'2019-03-14' as date0
union
select 1 id, 23 as id2, true app, Date'2019-03-15' as date0
)

Spark中的实现 #

上述两种方法在Spark中同样适用，推荐用MIN或者显式指定范围的FIRST_VALUE，写法和Athena完全一致。

内容的提问来源于stack exchange，提问作者Prabu