最近在做的某个项目中有一些数据预处理的工作,用的是Oracle数据库,涉及到表的拼接操作,在此记录一下并对数据库表的拼接知识做个扩充。
具体需求是:
- 把年龄(AGE)、性别(GENDER)、客户等级(CUSLEVEL)三个字段用逗号隔开拼接成一个字段
- 为空的字段不展示,三个字段全为空则置空
图中绿色框里的是需要拼接的原始字段;红色框里的是拼接后的效果。
这个需求是表多列之间的横向拼接,由于Oracle数据库没有像Postgre里array这种数组函数,因此只能使用 concat 函数或符号 || 来拼接。
多列横向拼接
--为实现这个需求,先造出上图演示数据
create table test1(
CUS_NO varchar2(10),
AGE int,
GENDER varchar2(10),
CUSLEVEL varchar2(10)
);
insert into test1(CUS_NO,AGE,GENDER,CUSLEVEL) values('cus_596492',41,'男','黄金级');commit;
insert into test1(CUS_NO,AGE,GENDER,CUSLEVEL) values('cus_265608',null,'女','钻石级');commit;
insert into test1(CUS_NO,AGE,GENDER,CUSLEVEL) values('cus_983740',28,null,'铂金级');commit;
insert into test1(CUS_NO,AGE,GENDER,CUSLEVEL) values('cus_356291',30,'男',null);commit;
insert into test1(CUS_NO,AGE,GENDER,CUSLEVEL) values('cus_356291',null,null,null);commit;
--横向拼接字段
select CUS_NO,AGE,GENDER,CUSLEVEL,
--拼接字段末尾逗号情况的处理
decode(substr(pinjie,length(pinjie),1),',',substr(pinjie,1,length(pinjie)-1),pinjie) as pinjie
from (--使用拼接符 || 将三个字段拼接到一起
select CUS_NO,AGE,GENDER,CUSLEVEL,
decode(AGE, null, null, '年龄:'||AGE||',') ||
decode(GENDER, null, null, '性别:'||GENDER||',') ||
decode(CUSLEVEL, null, null, '客户等级:'||CUSLEVEL) as pinjie
from test1
) a
;
多行纵向拼接
字段间的横向拼接处理起来比较容易,有时还会遇到多行数据的纵向拼接,比如下图是客户信用卡交易数据。如果我们想统计每个客户都在哪些月份发生了交易,即每个客户一条,月份间以逗号隔开,得到下表这样。
CUS_NO | TRAN_MONTH |
---|---|
cus_101028 | 201910,201912,202003 |
为在Oracle中实现这种纵向按分隔符的分组拼接,可以使用Oracle的 LISTAGG 函数。
LISTAGG函数语法:
LISTAGG (measure_expr:any [, delimiter_expr:VARCHAR]) WITHIN GROUP(ORDER BY column_expr)
LISTAGG函数一般配合GROUP BY作为聚合函数(也可用于开窗函数)使用,可以传入两个参数变量
-
measure_expr:要合并处理的字段
-
delimiter_expr:给定分隔符号
-
column_expr:排序字段
--为实现这个需求,先造出上图演示数据
create table test2(
CUS_NO varchar2(10),
YEAR_MONTH varchar2(6),
TRAN_AMT numeric(20,2)
);
insert into test2(CUS_NO,YEAR_MONTH,TRAN_AMT) values('cus_101028','201910',880.00);commit;
insert into test2(CUS_NO,YEAR_MONTH,TRAN_AMT) values('cus_101028','201912',2542.00);commit;
insert into test2(CUS_NO,YEAR_MONTH,TRAN_AMT) values('cus_101028','202003',535.66);commit;
insert into test2(CUS_NO,YEAR_MONTH,TRAN_AMT) values('cus_101133','202005',3150.00);commit;
insert into test2(CUS_NO,YEAR_MONTH,TRAN_AMT) values('cus_101133','202008',19200.00);commit;
insert into test2(CUS_NO,YEAR_MONTH,TRAN_AMT) values('cus_162656','202006',8100.00);commit;
--纵向拼接字段--聚合函数用法
select CUS_NO
,LISTAGG(YEAR_MONTH,',') WITHIN GROUP(ORDER BY YEAR_MONTH) AS PINJIE
from test2
group by CUS_NO;
拼接效果如下,这里可以思考下,如果一个客户某个交易月份有重复值,该如何得到没有重复月份值的拼接结果?
--纵向拼接字段--开窗函数用法
--聚合和开窗的区别在于:聚合函数每组只会返回一条数据(按group by的字段),但开窗函数会返回原数据所有行,但会额外增加统计字段
select CUS_NO,YEAR_MONTH
--按客户编号开窗
,LISTAGG(YEAR_MONTH,',') WITHIN GROUP(ORDER BY YEAR_MONTH) OVER(PARTITION BY CUS_NO) AS PINJIE
from test2;
拼接效果如下,开窗得到的结果可以和聚合结果以及原始数据作比较