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_tbl12 select13      t2.day14     ,t2.week15     ,t2.work_day16     ,sum(t2.flag) over(order by t2.day rows between unbounded preceding and current row ) as work_day_rn17 from (18     select19      t1.*20     ,case when work_day = 1 then 1 else 0 end as flag21     from (22         select '2013-08-15' as day,4 as week,1 as work_day union all23         select '2013-08-16' as day,5 as week,1 as work_day union all24         select '2013-08-17' as day,6 as week,2 as work_day union all25         select '2013-08-18' as day,7 as week,2 as work_day union all26         select '2013-08-19' as day,1 as week,1 as work_day union all27         select '2013-08-20' as day,2 as week,1 as work_day union all28         select '2013-08-21' as day,3 as week,1 as work_day union all29         select '2013-08-22' as day,4 as week,1 as work_day union all30         select '2013-08-23' as day,5 as week,1 as work_day union all31         select '2013-08-24' as day,6 as week,2 as work_day union all32         select '2013-08-25' as day,7 as week,2 as work_day union all33         select '2013-08-26' as day,1 as week,1 as work_day union all34         select '2013-08-27' as day,2 as week,1 as work_day union all35         select '2013-08-28' as day,3 as week,1 as work_day36     ) t137 ) t238 ;