--oracle写法1
select to_date(dt,'yyyy-mm-dd') "统计日期", move_channel "机构", move_path "迁徙路径",'开户' "指标类型",cn "指标项",cv "指标值"
from (select dt,
decode(grouping(move_channel)+grouping(move_path),2,null,move_channel) move_channel,
decode(grouping(move_channel)+grouping(move_path),1,'合计',2,'CA02项目总计',move_path) move_path,
sum(cust_cnt) cust_cnt,
sum(cust_cnt) - sum(cust_cnt_pre_day) cust_cnt_pre_day,
sum(cust_cnt) - sum(cust_cnt_pre_mon) cust_cnt_pre_mon
end cust_cnt_pre_mon
from tbl_ca01_move_channel_day
where move_channel_code like '01%'
group by rollup(dt,move_channel,move_path)
)
where dt is not null
model
return updated rows
partition by (dt,move_channel,move_path)
dimension by (0 as n)
measures('xxxxxxxxxxxxxxxxxxxx' as cn, 'yyyyyyyyyyyyyyyyy' as cv,cust_cnt as c1, cust_cnt_pre_day as c2, cust_cnt_pre_mon as c3)
rules upsert all
(
cn[1] = '客户数',
cn[2] = '比上日',
cn[3] = '比上月',
cv[1] = c1[0],
cv[2] = c2[0],
cv[3] = c3[0]
)
--oracle写法2: 改进SQL
select move_channel "渠道",move_path "迁徙路径",'开户' "指标类型",cn "指标项",cv "指标值"
from (select decode(grouping(move_channel)+grouping(move_path),2,null,move_channel) move_channel,
decode(grouping(move_channel)+grouping(move_path),1,'合计',2,'CA01项目总计',move_path) move_path,
sum(cust_cnt) as cust_cnt,
sum(cust_cnt) - sum(cust_cnt_pre_day) as cust_cnt_pre_day,
sum(cust_cnt) - sum(cust_cnt_pre_mon) as cust_cnt_pre_mon
from tbl_ca01_move_channel_day
where move_channel_code like '01%'
group by rollup(dt,move_channel,move_path)
)
unpivot(cv for cn in(cust_cnt as '客户数',cust_cnt_pre_day as '比上日', cust_cnt_pre_mon as '比上月')
Hive和Oracle行列转换实例总结
--hive写法1
select dt,'开户' as stat_item, if(move_channel is null,'',move_channel) as move_channel,stat_value,
case when grouping__id = '1' then '总计'
when grouping__id = '3' then '合计'
else move_path
end as move_path,
case row_number()over(partition by dt, move_channel, move_path, by stat_value)
when 1 then stat_type[0]
when 2 then stat_type[1]
when 3 then stat_type[2]
else null
end stat_type
from(select dt,move_channel,move_path,grouping__id,
(split(concat_ws(',','客户数','比上日','比上月'),',')) as stat_type,
sum(cust_cnt) as cust_cnt,
sum(cust_cnt) - sum(cust_cnt_pre_day) as cust_cnt_pre_day,
sum(cust_cnt) - sum(cust_cnt_pre_mon) as cust_cnt_pre_mon
from pabrdm.tb1_ca01_move_channel_day
where move_channel_code like '01%'
group by dt,move_channel,move_path
with rollup
)x
lateral view explode(split(concat_ws(',',cust_cnt,cust_cnt_pre_day,cust_cnt_pre_mon),',')) cust as stat_value
where dt is not null
--hive写法2: 改进SQL
select dt,'开户' as stat_item, if(move_channel is null,'',move_channel) as move_channel,stat_value,
case when grouping__id = '1' then '总计'
when grouping__id = '3' then '合计'
else move_path
end as move_path,
explode(map('客户数',cust_cnt,'比上日',cust_cnt_pre_day,'比上月', cust_cnt_pre_mon)) as stat_type
from(select dt,move_channel,move_path,grouping__id,
sum(cust_cnt) as cust_cnt,
sum(cust_cnt) - sum(cust_cnt_pre_day) as cust_cnt_pre_day,
sum(cust_cnt) - sum(cust_cnt_pre_mon) as cust_cnt_pre_mon
from pabrdm.tb1_ca01_move_channel_day
where move_channel_code like '01%'
group by dt,move_channel,move_path
with rollup
)x
where dt is not null