尔尔辞挽

导航

HIVE如何获取当前日期的前两个工作日及后三个工作日 

 1 create temporary table if not exists tmp as
 2 select 
 3     cur_date,
 4     if_workday,
 5     sum(if_workday) over (order by cur_date asc) lg
 6 FROM
 7 (
 8      select
 9         cur_date,
10         case when is_workday=1 then 1 else 0 end as if_workday
11     from crisps_dim.dim_com_date_info f1
12 ) t
13 order by cur_date asc;
14 
15 select
16     a.*,
17     b.cur_date
18 from tmp  a
19 left join tmp b on if(a.if_workday=1,(a.lg-2),(a.lg-1)) = b.lg and b.if_workday = 1;
cur_date if_workday lg cur_date
2021-05-14 1 341 2021-05-12
2021-05-13 1 340 2021-05-11
2021-05-12 1 339 2021-05-10
2021-05-11 1 338 2021-05-08
2021-05-10 1 337 2021-05-07 
 1 set hive.execution.engine = spark;
 2 create temporary table if not exists tmp as
 3 select
 4     cur_date,
 5     if_workday,
 6     sum(if_workday) over (order by cur_date asc) lg
 7 FROM
 8 (
 9      select
10         cur_date,
11         case when is_workday=1 then 1 else 0 end as if_workday
12     from crisps_dim.dim_com_date_info f1
13 ) t
14 order by cur_date asc;
15 
16 select
17     a.*,
18     b.cur_date
19 from tmp  a
20 left join tmp b on (a.lg + 2) = b.lg and b.if_workday = 1;
cur_date if_workday lg cur_date
2021-05-14 1 341 2021-05-18
2021-05-13 1 340 2021-05-17
2021-05-12 1 339 2021-05-14
2021-05-11 1 338 2021-05-13
2021-05-10 1 337 2021-05-12

 

posted on 2022-12-22 14:18  尔尔辞挽  阅读(354)  评论(0)    收藏  举报