SQL是这样的

---历年人数变动

with aa as (SELECT year yearr,add_num,reduse_num,avg_num,year_num FROM dw.dw_hr_emp_num),

---今年新增总人数

     bb as (select SUBSTR(yearmonth,1,4) yearr, count(worknum) add_num from dw.dw_hr_new_emp where substr(yearmonth,1,4)=to_char(now(),'YYYY') group by substr(yearmonth,1,4)),

---今年离职总人数

     cc as (select substr(yearmonth,1,4) yearr,count(worknum) reduse_num from dw.dw_hr_departure_emp where substr(yearmonth,1,4)=to_char(now(),'YYYY') group by substr(yearmonth,1,4)),

---今年员工总数

     dd as (select substr(yearmonth,1,4) yearr,count(worknum) year_num from dw.dw_hr_persionnel where substr(yearmonth,1,4)=to_char(now(),'YYYY') group by substr(yearmonth,1,4)),

---今年在册员工平均人数

---年份维度

     ee as (select yearr from aa

             union 

            select yearr from bb

             union 

            select yearr from cc

             union 

            select yearr from dd)

 select e.yearr,a.*,b.add_num,c.reduse_num,d.year_num from 

        (select yearr from ee) e

        full join 

        (select * from aa) a

           on e.yearr=a.yearr

        full join 

         (select yearr,add_num from bb) b

           on e.yearr=b.yearr

        full join 

        (select yearr,reduse_num from cc) c

           on e.yearr=c.yearr

        full join 

        (select yearr,year_num from dd) d

           on e.yearr=d.yearr