droptableifexistsdefault.test_tab purge;createtabledefault.test_tab
asselect date_add(current_timestamp(),-10)as event_time,'1'as id,'A'as a,'B'as b,'C'as c
unionallselect date_add(current_timestamp(),-9)as event_time,'1'as id,'a'as a,nullas b,nullas c
unionallselect date_add(current_timestamp(),-8)as event_time,'1'as id,nullas a,'b'as b,nullas c
unionallselect date_add(current_timestamp(),-7)as event_time,'1'as id,nullas a,nullas b,'c'as c
unionallselect date_add(current_timestamp(),-6)as event_time,'2'as id,'AA'as a,nullas b,nullas c
unionallselect date_add(current_timestamp(),-5)as event_time,'2'as id,nullas a,'BB'as b,nullas c
unionallselect date_add(current_timestamp(),-4)as event_time,'2'as id,nullas a,nullas b,'CC'as c
unionallselect 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(partitionby id orderby event_time)as last_a,
last_value(b,TRUE)over(partitionby id orderby event_time )as last_b
fromdefault.test_tab t
1.创建一张测试表drop table if exists default.test_tab purge;create table default.test_tabasselect date_add(current_timestamp(),-10) as event_time,'1' as id, 'A' as a,'B' as b,'C' as cunion all select date_add(current_timestamp(),-9) as event_time,'1' as id,