如何实现行转列
工作过程中,遇到因为表连接过程中过滤条件不一样,导致一张表重复关联多次,而表的数据量又很大,从而导致查询异常缓慢的问题。具体描述如下:
A表为主表(key,value1,value2),B(key,flag,NAME)表中存储A需要拓展的信息(NAME),有一个标识字段(flag)标识取得的字段意思。
- flag为’Z1’时,NAME意为销售员姓名
- flag为’Z2’时,NAME意为客服名字
- flag为’Z3’时,NAME意为销售Leader名字
因为它们通过主键key去关联,一个key如果同时存储销售员,客服名字,Leader名字时,那么势必会造成一对多的数据发散,所以如果想不发散数据,那么只能关联多次B表,并在ON 筛选条件上去筛选Z1,Z2,Z3情况下的字段意义。
如果采取这种方式,那么会有以下问题:
- 多次关联同一张表,造成时间上的浪费
- 一张表的数据量太过于庞大,同时也会造成空间浪费
因此在数仓中,我采用先建立一张临时表,通过采用行转列的方式,缩小表存储内容,同时只需要关联一次表即可取得相关数据。具体方式如下
select key,max(name1),max(name2),max(name3)
from(
select key,name as name1,null as name2,null as name3
from B
where B.flag='Z1'
Union all
select key,null,name as name2,null
from B
where B.flag='Z2'
Union all
select key,null,null,name as name3
from B
where B.flag='Z3'
)
group by key
通过这种方式,就建立了一张表结构为
B(key,name1,name2,name3)的数据表,取数据时,只需要将对应的key值关联即可拿到3个数据。
后经过同事的优化,其实有简洁易懂的代码,上面那个写复杂了…
select key
,max(case flag when 'Z1' then name end) as name1
,max(case flag when 'Z2' then name end) as name2
,max(case flag when 'Z3' then name end) as name3
from b
where flag in('Z1','Z2','Z3')
group by key
写在最后:通过上面的方式,构建出新的数据表存储后,原本需要关联3次数据量大小为900W的表,经过一次转存,优化成只需要关联1次数据量大小为300W的表。