增加工作日排序字段

 

 

 1 drop table work_day_tbl;
 2 create table work_day_tbl (
 3      day                   string comment '日期'
 4     ,week                  int    comment '周一1，周日7'
 5     ,work_day              int    comment '1工作日2周末3节假日'
 6     ,work_day_rn           int    comment '工作日排序'
 7 ) 
 8 stored as orc
 9 ;
10 
11 insert overwrite table work_day_tbl
12 select
13      t2.day
14     ,t2.week
15     ,t2.work_day
16     ,sum(t2.flag) over(order by t2.day rows between unbounded preceding and current row ) as work_day_rn
17 from (
18     select
19      t1.*
20     ,case when work_day = 1 then 1 else 0 end as flag
21     from (
22         select '2013-08-15' as day,4 as week,1 as work_day union all
23         select '2013-08-16' as day,5 as week,1 as work_day union all
24         select '2013-08-17' as day,6 as week,2 as work_day union all
25         select '2013-08-18' as day,7 as week,2 as work_day union all
26         select '2013-08-19' as day,1 as week,1 as work_day union all
27         select '2013-08-20' as day,2 as week,1 as work_day union all
28         select '2013-08-21' as day,3 as week,1 as work_day union all
29         select '2013-08-22' as day,4 as week,1 as work_day union all
30         select '2013-08-23' as day,5 as week,1 as work_day union all
31         select '2013-08-24' as day,6 as week,2 as work_day union all
32         select '2013-08-25' as day,7 as week,2 as work_day union all
33         select '2013-08-26' as day,1 as week,1 as work_day union all
34         select '2013-08-27' as day,2 as week,1 as work_day union all
35         select '2013-08-28' as day,3 as week,1 as work_day
36     ) t1
37 ) t2
38 ;

 

 

posted @ 2018-07-10 10:12  chenzechao  阅读(229)  评论(0)    收藏  举报