Hive解决weekofyear跨年周异常问题

-- year_weeks
select 
year_weeks
,substr(calendar_date_a,1,10) calendar_date
from(
  select ceiling(dayofyear(calendar_date)/7) year_weeks,calendar_date_a
  from(
    select
    calendar_date,calendar_date calendar_date_a
    from csv_input 
    where -- substring(calendar_date,1,4) = 2021 and
    (calendar_date between concat(substring(calendar_date,1,4),'-01-01') and (date_add(concat(substring(calendar_date,1,4),'-01-01'), (7 - weekday(concat(substring(calendar_date,1,4),'-01-01')) - 1))))
  )
  union all
  select (weekofyear(calendar_date) + 1) year_weeks ,calendar_date_a
  from(
    select
    calendar_date,calendar_date calendar_date_a
    from csv_input 
    where --substring(calendar_date,1,4) = 2021 and
    (calendar_date between (date_add(concat(substring(calendar_date,1,4),'-01-01'), (7 - weekday(concat(substring(calendar_date,1,4),'-01-01')))))
    and concat(substring(calendar_date,1,4),'-12-31'))
  )
)
as year_weeks;

贴一下weekofyear执行2021-01-01效果

SELECT weekofyear('2021-01-01');

result:
#	weekofyear_CAST_2021-01-01_AS_DATE_
0	53

正常来讲，我们想要的2021-01-01这一天对应2021年的周数应该是第一周，但是执行的结果是第53周，原因是hive底层对于weekofyear函数的周数是按照这一周在上一年的天数多还是在这一年的天数多进行划分的。例如，

先贴处理逻辑，有时间再写