Aster的PIVOT函数脚本迁移为Hive可执行函数问题求助

原脚本用Aster专属的PIVOT函数实现行转列宽表生成，逻辑拆解如下：

• 分组维度：it_ref_no、file_type、comb_id、startyear，每个维度组内按moorder字段升序排序
• 行转换规则：每个分组最多取前45行，对应45个顺序期次
• 转换指标：cum_cashflow、relcum_cf、relcum_cinf三个字段会按顺序期次展开为宽表列，列名规则为[指标名]_[期次序号]，比如cum_cashflow_1对应分组内排序第一行的cum_cashflow值，依次类推到第45期
• 最终表存储规则：按it_ref_no字段做哈希分桶

Hive没有对应原生PIVOT函数，可通过窗口函数打行号+分组聚合行转列的方式实现等价逻辑，代码如下：

create table coll_ledger_cashfprofile_ta 
-- 分桶数可根据实际数据量调整，对应原Aster的hash分桶规则
clustered by (it_ref_no) into 32 buckets 
stored as orc -- 存储格式可按需修改
as
select 
  it_ref_no,
  file_type,
  comb_id,
  startyear,
  -- 展开cum_cashflow的45期
  max(case when rn=1 then cum_cashflow end) as cum_cashflow_1,
  max(case when rn=2 then cum_cashflow end) as cum_cashflow_2,
  -- 中间第3到44期按相同规则补全即可
  max(case when rn=45 then cum_cashflow end) as cum_cashflow_45,
  -- 展开relcum_cf的45期
  max(case when rn=1 then relcum_cf end) as relcum_cf_1,
  max(case when rn=2 then relcum_cf end) as relcum_cf_2,
  -- 中间第3到44期按相同规则补全即可
  max(case when rn=45 then relcum_cf end) as relcum_cf_45,
  -- 展开relcum_cinf的45期
  max(case when rn=1 then relcum_cinf end) as relcum_cinf_1,
  max(case when rn=2 then relcum_cinf end) as relcum_cinf_2,
  -- 中间第3到44期按相同规则补全即可
  max(case when rn=45 then relcum_cinf end) as relcum_cinf_45
from (
  select 
    *,
    row_number() over(partition by it_ref_no, file_type, comb_id, startyear order by moorder) as rn
  from coll_ledger_cashfprofile_tb
) t
where rn <=45
group by it_ref_no, file_type, comb_id, startyear;

注：上述写法兼容性最好、性能稳定，省略的中间期次列按相同规则批量生成补全即可。

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