bugfix> hive > 投稿

私は以下の詳細を含む表を持っています。現在のレベルと前のレベルを取得する必要があります。

ID   Level       start_dt                End_dt  
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

私の終了状態テーブルは次のとおりです。 ID、current_level、previous_level、アップグレード/ダウングレードフラグ

END_dt descに基づいてランキングしてみました。しかし、2番目の行は2としてランク付けされ、それは前のレベルではありません。これを単一のクエリで処理できますか?それともシングルホップ?

回答 2 件
  • トリッキーな部分は前のレベルであり、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
    
    

あなたの答え