【已解决】doris如何实现行转列,类似于postgresql中的unnest函数

Viewed 923

以下是postgresql中的SQL实现

select 
	substr(c_orgbh,1,4) as zq_code,'' as tj_nd,'doris指标测试' as c_zbgroup,
	unnest(array['用户巡护时长','用户巡护里程']) as c_zbtitle,'用户巡护数据指标' as c_zbname,'小时' as c_zbunit,unnest(array[sum(time),sum(distence)]) as d_zbvalue,2 as i_zbjingdu,unnest(array[55,55]) as i_xh 
from xh_yw.xh_user_work_date_tb 
where time > 0 and length(c_orgbh) >= 4 
group by zq_code,tj_nd,c_zbgroup,c_zbname

不使用union 处理 目前尝试过在doris中使用 lateral view explode(array('用户巡护时长','用户巡护里程')) table1 as c_zbtitle的形式 但是效果不尽人意 而且unnest(array[sum(time),sum(distence)])这步无法实现

select 
	substr(c_orgbh,1,4) as zq_code,'' as tj_nd,'doris指标测试' as c_zbgroup, c_zbtitle,'用户巡护数据指标' as c_zbname,'小时' as c_zbunit,sum(time) * 1 / 1.0 as d_zbvalue,2 as i_zbjingdu,i_xh 
from ds_xh_user_work_date_tb
	lateral view explode(array('用户巡护时长','用户巡护里程')) table1 as c_zbtitle
	lateral view explode(array(55,56)) table2 as i_xh
where time > 0 and length(c_orgbh) >= 4 
group by zq_code,tj_nd,c_zbgroup,c_zbname,c_zbtitle,i_xh
1 Answers

行转列可以看下官网文档:LATERAL VIEW

可以使用表函数可以实现相关逻辑