HiveSQL-历史数据汇总

现在有员工的职位历史变动数据

tableA 数据表字段：emp_id ,pos_id,pos_nm,job_id，date

其中date是yyyymmdd形式存放的日期

怎么将每个月的数据汇总，汇总成字符串 “0101010” 的形式？字符串的长度是这个月天数的总日期，比如1月份就有31个字符，2月是29或者28个字符？

SQL

with base as (
select 
emp_id,pos_id,pos_nm,job_id,year_month,date
,case when month_days == 31 then CONCAT(SUBSTR('0000000000000000000000000000000', 1, index-1), '1', SUBSTR('0000000000000000000000000000000', index+1))
when month_days == 30 then CONCAT(SUBSTR('000000000000000000000000000000', 1, index-1), '1', SUBSTR('000000000000000000000000000000', index+1))
when month_days == 29 then CONCAT(SUBSTR('00000000000000000000000000000', 1, index-1), '1', SUBSTR('00000000000000000000000000000', index+1))
when month_days == 28 then CONCAT(SUBSTR('0000000000000000000000000000', 1, index-1), '1', SUBSTR('0000000000000000000000000000', index+1))
end
date_bit
 from (
    select  emp_id  ,pos_id,pos_nm,job_id
    ,concat(year ,"-", month,"-",datt) as date
    ,concat(year,"-",month)  as year_month 
    ,day(concat(year,"-",month,"-",datt)) as index --这一天是第几天
    ,day(last_day(concat(year ,"-", month,"-",datt))) as month_days --一个月有多少天
    from(
         select emp_id  ,pos_id,pos_nm,job_id
         , substring(date,0,4) as year, substring(date,5,2) as month, substring(date,7,2) as datt 
        from tableA
        )
    )
)
select *,reverse(date_bit) from base