Hive last_value/first_value实现ignore nulls

db_guy

于 2020-12-14 20:21:47 发布

阅读量3k

点赞数 2

CC 4.0 BY-SA版权

分类专栏：商业智能-ETL 数据库技术-Oracle SQL 文章标签： hadoop spark hdfs 大数据 hive

本文链接：https://blog.youkuaiyun.com/db_guy/article/details/111184452

商业智能-ETL 同时被 3 个专栏收录

8 篇文章

订阅专栏

数据库技术-Oracle

1 篇文章

订阅专栏

SQL

1 篇文章

订阅专栏

本文介绍如何创建测试表并使用SQL查询，包括使用`last_value`函数按ID和时间顺序获取最新值，展示在默认test_tab表中的数据处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建一张测试表

drop table if exists default.test_tab purge;
create table default.test_tab
as
select date_add(current_timestamp(),-10) as event_time,'1' as id, 'A' as a,'B' as b,'C' as c
union all select date_add(current_timestamp(),-9) as event_time,'1' as id, 'a' as a, null as b,null as c
union all select date_add(current_timestamp(),-8) as event_time,'1' as id,null as a, 'b' as b,null as c
union all select date_add(current_timestamp(),-7) as event_time,'1' as id,null as a, null as b,'c' as c
union all select date_add(current_timestamp(),-6) as event_time,'2' as id,'AA' as a, null as b,null as c
union all select date_add(current_timestamp(),-5) as event_time,'2' as id,null as a, 'BB' as b,null as c
union all select date_add(current_timestamp(),-4) as event_time,'2' as id,null as a, null as b,'CC' as c
union all select date_add(current_timestamp(),-3) as event_time,'2' as id,'a' as a, 'b' as b,'c' as c;

测试

select t.*, 
    last_value(a) over( partition by id order by event_time) as last_a, 
    last_value(b,TRUE) over( partition by id order by event_time ) as last_b 
from default.test_tab t

结果

在这里插入图片描述