宽表转长表的几种方式以及运行效率探讨

前言

之前写过一篇博客：一条数据扩展成多条数据，里面介绍了如何从一条数据扩展出多条。这个主题与本次要写的宽表转长表乍看有异曲同工之处，本想将此篇之介绍续貂至上述博客中，然若细细揣摩，又觉得两者不可相提并论，因此新开一篇。

宽表转长表这种数据结构的重构处理，在项目中经常会碰到，本次要介绍的几种方法基于之前在某银行的项目实践。该公司的大数据平台用的底层数据库是基于postgre来开发，所以就以postgre为例来探讨。

去年做这个项目主要是给客户优化几个业务系统的历史代码，在基于对业务的理解上优化代码结构和提升代码运行效率。其中有一部分就涉及到宽表转长表，具体的业务场景暂且不表，只概述一下需求。

下图1的数据是基于每个客户自身各方面的属性计算得到的各种产品浓度值，红框里的字段就是每种产品对应的浓度值，值越大，代表客户接纳这个产品的可能性越高。考虑到客户量很多(亿级别)、产品数量基数很大(已有千种)且不断增长以及查看的便捷性，客户想将此数据结构转换成下图2的样式，每个客户每种产品占一行，去除浓度值为0的，即宽表转长表。

图1

图2

宽表转长表的几种方式

为探讨这几种方法，我们先来造出上面的测试数据

--宽表表结构
--drop table wide_table;
create table wide_table(
data_dt date, --数据日期
cust_no varchar(255), --客户编号
prd01_ratio decimal(20,2), --产品1浓度
prd02_ratio decimal(20,2), --产品2浓度
prd03_ratio decimal(20,2) --产品3浓度
);


--长表表结构
--drop table long_table;
create table long_table(
data_dt date, --数据日期
cust_no varchar(255), --客户编号
prd_no varchar(255), --产品编号
ratio decimal(20,2) --产品浓度
);

insert into wide_table(data_dt,cust_no,prd01_ratio,prd02_ratio,prd03_ratio) values
(to_date('20200101','yyyymmdd'),'cust01',0.5,0,0.8),
(to_date('20200101','yyyymmdd'),'cust02',0.5,0.9,0.8),
(to_date('20200101','yyyymmdd'),'cust03',0,0,0.8),
(to_date('20200101','yyyymmdd'),'cust04',0,0,0),
(to_date('20200101','yyyymmdd'),'cust05',0.3,0.9,0.6);
commit;

方法1:union all

select *
from (
    select data_dt,cust_no,'prd01' as prd_no,prd01_ratio as ratio from wide_table where prd01_ratio<>0
    union all
    select data_dt,cust_no,'prd02' as prd_no,prd02_ratio as ratio from wide_table where prd02_ratio<>0
    union all
    select data_dt,cust_no,'prd03' as prd_no,prd03_ratio as ratio from wide_table where prd03_ratio<>0
) a
order by cust_no,prd_no
;

方法2:cross join

select data_dt,cust_no,prd_no,cast(ratio as numeric(20,2)) as ratio
from (
    select data_dt,cust_no,prd_no
          ,case prd_no
            when 'prd01' then prd01_ratio
            when 'prd02' then prd02_ratio
            when 'prd03' then prd03_ratio
           end as ratio
    from (--每条数据按产品数扩展出对应条数
        select a.*,b.prd_no
        from wide_table a
        cross join (values('prd01'),('prd02'),('prd03')) as b(prd_no)
    ) a
) b
where ratio<>0
order by cust_no,prd_no
;

方法3：unnest+array

select *
from (
    select data_dt,cust_no
    ,unnest(array['prd01','prd02','prd03']) as prd_no
    ,unnest(array[prd01_ratio,prd02_ratio,prd03_ratio]) as ratio
    from wide_table
) a
where ratio<>0
order by cust_no,prd_no
;

方法4：lateral

select a.data_dt
      ,a.cust_no
      ,b.prd_no
      ,b.ratio
from wide_table a
join lateral (
    select b.prd_no,b.ratio
    from (
        values('prd01',a.prd01_ratio)
             ,('prd02',a.prd02_ratio)
             ,('prd03',a.prd03_ratio)
        ) as b (prd_no,ratio)
    where ratio<>0
) b
on true
order by cust_no,prd_no
;

方法5：lateral+json_each_text

select r.data_dt
      ,r.cust_no
      ,key as prd_no
      ,value as ratio
from (
    select data_dt,cust_no,row_to_json(t.*) as line
    from wide_table t
) r
join lateral json_each_text(r.line) on (key like '%ratio%' and value<>'0.00')
order by cust_no,prd_no
;

以上几种宽表转长表的方法中，经过项目期间用不同客户量级与不同产品量级的组合效率测试结果来看，方法3和4效率最高，方法1和2次之，方法5效率最差。