Hive QL 依据日期维表将一年中的每个月补齐成指定天数（例如33天），补齐的日期用改月最后一日的日期

本文链接：https://blog.youkuaiyun.com/az9996/article/details/116005745

该博客内容涉及构建日期维表的SQL查询，旨在为那些只有28天的年-月组合补全直到33天的所有日期。通过CASE WHEN语句检查不同天数，然后使用UNION ALL将结果合并，创建了一个包含28到33天的完整日期序列。查询还排除了日期为空的行。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

日期维表结构如下
在这里插入图片描述

思路：

依据天数来进行补齐，小于33天的依次用最后日期拼上对应的天数
当天数为28天时，年-月-日 29天，
年-月-日 30天，
年-月-日 31天，
年-月-日 32天，
年-月-日 33天，
使用union all 将其它天数的处理结果上下拼接
排除日期为null的行

select
f.zdate,
f.num
from
(select
case when count(t1.zdate) = '28' then concat(t1.yearmonth,'-28') else null end as zdate,
'29' as num
from db_cfg_dev.cfg_period_d t1
group by
yearmonth
union all
select
case when count(t1.zdate) = '28' then concat(t1.yearmonth,'-28') else null end as zdate,
'30' as num
from db_cfg_dev.cfg_period_d t1
group by
yearmonth
union all
select
case when count(t1.zdate) = '28' then concat(t1.yearmonth,'-28') else null end as zdate,
'31' as num
from db_cfg_dev.cfg_period_d t1
group by
yearmonth
union all
select
case when count(t1.zdate) = '28' then concat(t1.yearmonth,'-28') else null end as zdate,
'32' as num
from db_cfg_dev.cfg_period_d t1
group by
yearmonth
union all
select
case when count(t1.zdate) = '28' then concat(t1.yearmonth,'-28') else null end as zdate,
'33' as num
from db_cfg_dev.cfg_period_d t1
group by
yearmonth
union all
select
case when count(t1.zdate) = '29' then concat(t1.yearmonth,'-29') else null end as zdate,
'30' as num
from db_cfg_dev.cfg_period_d t1
group by
yearmonth
union all
select
case when count(t1.zdate) = '29' then concat(t1.yearmonth,'-29') else null end as zdate,
'31' as num
from db_cfg_dev.cfg_period_d t1
group by
yearmonth
union all
select
case when count(t1.zdate) = '29' then concat(t1.yearmonth,'-29') else null end as zdate,
'32' as num
from db_cfg_dev.cfg_period_d t1
group by
yearmonth
union all
select
case when count(t1.zdate) = '29' then concat(t1.yearmonth,'-29') else null end as zdate,
'33' as num
from db_cfg_dev.cfg_period_d t1
group by
yearmonth
union all
select
case when count(t1.zdate) = '30' then concat(t1.yearmonth,'-30') else null end as zdate,
'31' as num
from db_cfg_dev.cfg_period_d t1
group by
yearmonth
union all
select
case when count(t1.zdate) = '30' then concat(t1.yearmonth,'-30') else null end as zdate,
'32' as num
from db_cfg_dev.cfg_period_d t1
group by
yearmonth
union all
select
case when count(t1.zdate) = '30' then concat(t1.yearmonth,'-30') else null end as zdate,
'33' as num
from db_cfg_dev.cfg_period_d t1
group by
yearmonth
union all
select
case when count(t1.zdate) = '31' then concat(t1.yearmonth,'-31') else null end as zdate,
'32' as num
from db_cfg_dev.cfg_period_d t1
group by
yearmonth
union all
select
case when count(t1.zdate) = '31' then concat(t1.yearmonth,'-31') else null end as zdate,
'33' as num
from db_cfg_dev.cfg_period_d t1
group by
yearmonth
union all
select
t1.zdate,
substr(t1.zdate,9,2)
from db_cfg_dev.cfg_period_d t1) f
where f.zdate is not null;