Hive QLで現在および以前のレベルを取得する

トリッキーな部分は前のレベルであり、1パスでは可能だとは思いません。このようなものを試してみてください

with setup AS ( select ID , Level , row_number() over (partition by ID order by End_dt desc) as row_num , MIN(Level) over (Partition by ID order by End_dt desc ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as previous_level from my_table ) SELECT ID , Level , previous_level , case when Level = previous_level THEN 'No Upgrade/Downgrade' WHEN Level < previous_level THEN 'Upgrade' WHEN Level > previous_level THEN 'Downgrade' ELSE 'Unknown' END AS upgrade_downgrade_description FROM setup WHERE row_num = 1 ;

LAGを使用して以前の行の値を取得できます。参考資料LAG

create table table_1(ID string,Level int,start_dt timestamp,End_dt timestamp);
insert into table_1 values
('A',1,'2018-03-12 18:39:10','2020-01-01 00:00:00'),
('A',1,'2018-01-17 13:21:26','2018-03-12 18:39:10'),
('A',2,'2018-01-14 13:21:17','2018-01-17 13:21:26');

SQL：

select id,curr_level,prev_level,
case when curr_level=prev_level then 'No Ups - Downs'
when curr_level>prev_level then 'Downgrade'
when curr_level<prev_level then 'Up-Downgrade'
when prev_level is null then 'No-Previous Level'
else 'Unkonwn state'
end upgrade_downgrade_description
from(
select table_1.id,
table_1.level as curr_level,
lag(table_1.level,1) over (partition by table_1.id order by table_1.end_dt desc) prev_level
from table_1) s;

出力：

id  curr_level  prev_level  upgrade_downgrade_description
A   1           NULL        No-Previous Level
A   1           1           No Ups - Downs
A   2           1           Downgrade