SQL/HiveQL如何创建包含指定ORG_ID与月度末日期的组合表

核心逻辑 #

你需要的是6个固定ORG_ID与2018年1月至2021年6月所有月末日期的笛卡尔积，无需依赖现有表数据，直接通过Hive内置函数即可生成。

1. 生成固定ORG_ID列表 #

用union all拼接你指定的6个机构ID：

select 'C2222' as org_id union all
select 'D3333' as org_id union all
select 'W2345' as org_id union all
select 'E2111' as org_id union all
select 'T7232' as org_id union all
select 'U8967' as org_id

2. 生成指定区间的月末日期序列 #

利用posexplode函数生成连续月份索引，通过last_day函数计算每个月的月末日期：

select 
    last_day(add_months('2018-01-01', t.pos)) as time_period
from posexplode(split(space(41), ' ')) t
-- 2018年1月到2021年6月共42个月，space(41)生成41个空格，split后得到42个元素，索引从0到41
where last_day(add_months('2018-01-01', t.pos)) <= '2021-06-30'

3. 关联生成全量数据并建表 #

将两个列表做cross join（笛卡尔积关联），直接写入目标表：

create table if not exists org_month_dim (
    org_id string comment '机构ID',
    time_period date comment '月末日期'
) comment '机构月度维度表'
stored as parquet
as
with org_list as (
    select 'C2222' as org_id union all
    select 'D3333' as org_id union all
    select 'W2345' as org_id union all
    select 'E2111' as org_id union all
    select 'T7232' as org_id union all
    select 'U8967' as org_id
),
month_list as (
    select 
        last_day(add_months('2018-01-01', t.pos)) as time_period
    from posexplode(split(space(41), ' ')) t
    where last_day(add_months('2018-01-01', t.pos)) <= '2021-06-30'
)
select 
    a.org_id,
    b.time_period
from org_list a
cross join month_list b
order by a.org_id, b.time_period;

注意事项 #

• 执行后生成的总数据量为6*42=252行，顺序和你提供的样例完全一致
• 若Hive版本不支持posexplode，可替换为已有连续数字辅助表生成月份序列，逻辑保持不变

内容的提问来源于stack exchange，提问作者Dan