最近在做的某個(gè)項(xiàng)目中有一些數(shù)據(jù)預(yù)處理的工作，用的是Oracle數(shù)據(jù)庫(kù)，涉及到表的拼接操作，在此記錄一下并對(duì)數(shù)據(jù)庫(kù)表的拼接知識(shí)做個(gè)擴(kuò)充。
具體需求是：
- 把年齡(AGE)、性別(GENDER)、客戶等級(jí)(CUSLEVEL)三個(gè)字段用逗號(hào)隔開拼接成一個(gè)字段
- 為空的字段不展示，三個(gè)字段全為空則置空
圖中綠色框里的是需要拼接的原始字段；紅色框里的是拼接后的效果。
這個(gè)需求是表多列之間的橫向拼接，由于Oracle數(shù)據(jù)庫(kù)沒有像Postgre里array這種數(shù)組函數(shù)，因此只能使用 concat 函數(shù)或符號(hào) || 來(lái)拼接。
多列橫向拼接
--為實(shí)現(xiàn)這個(gè)需求，先造出上圖演示數(shù)據(jù)
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,'男','黃金級(jí)');commit;
insert into test1(CUS_NO,AGE,GENDER,CUSLEVEL) values('cus_265608',null,'女','鉆石級(jí)');commit;
insert into test1(CUS_NO,AGE,GENDER,CUSLEVEL) values('cus_983740',28,null,'鉑金級(jí)');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,
--拼接字段末尾逗號(hào)情況的處理
decode(substr(pinjie,length(pinjie),1),',',substr(pinjie,1,length(pinjie)-1),pinjie) as pinjie
from (--使用拼接符 || 將三個(gè)字段拼接到一起
select CUS_NO,AGE,GENDER,CUSLEVEL,
decode(AGE, null, null, '年齡:'||AGE||',') ||
decode(GENDER, null, null, '性別:'||GENDER||',') ||
decode(CUSLEVEL, null, null, '客戶等級(jí):'||CUSLEVEL) as pinjie
from test1
) a
;
多行縱向拼接
字段間的橫向拼接處理起來(lái)比較容易，有時(shí)還會(huì)遇到多行數(shù)據(jù)的縱向拼接，比如下圖是客戶信用卡交易數(shù)據(jù)。如果我們想統(tǒng)計(jì)每個(gè)客戶都在哪些月份發(fā)生了交易，即每個(gè)客戶一條，月份間以逗號(hào)隔開，得到下表這樣。
| CUS_NO | TRAN_MONTH |
|---|---|
| cus_101028 | 201910,201912,202003 |
為在Oracle中實(shí)現(xiàn)這種縱向按分隔符的分組拼接，可以使用Oracle的 LISTAGG 函數(shù)。
LISTAGG函數(shù)語(yǔ)法：
LISTAGG (measure_expr:any [, delimiter_expr:VARCHAR]) WITHIN GROUP(ORDER BY column_expr)
LISTAGG函數(shù)一般配合GROUP BY作為聚合函數(shù)(也可用于開窗函數(shù))使用，可以傳入兩個(gè)參數(shù)變量
-
measure_expr：要合并處理的字段
-
delimiter_expr：給定分隔符號(hào)
-
column_expr：排序字段
--為實(shí)現(xiàn)這個(gè)需求，先造出上圖演示數(shù)據(jù)
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;
--縱向拼接字段--聚合函數(shù)用法
select CUS_NO
,LISTAGG(YEAR_MONTH,',') WITHIN GROUP(ORDER BY YEAR_MONTH) AS PINJIE
from test2
group by CUS_NO;
拼接效果如下，這里可以思考下，如果一個(gè)客戶某個(gè)交易月份有重復(fù)值，該如何得到?jīng)]有重復(fù)月份值的拼接結(jié)果？
文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-415650.html
--縱向拼接字段--開窗函數(shù)用法
--聚合和開窗的區(qū)別在于：聚合函數(shù)每組只會(huì)返回一條數(shù)據(jù)(按group by的字段)，但開窗函數(shù)會(huì)返回原數(shù)據(jù)所有行，但會(huì)額外增加統(tǒng)計(jì)字段
select CUS_NO,YEAR_MONTH
--按客戶編號(hào)開窗
,LISTAGG(YEAR_MONTH,',') WITHIN GROUP(ORDER BY YEAR_MONTH) OVER(PARTITION BY CUS_NO) AS PINJIE
from test2;
拼接效果如下，開窗得到的結(jié)果可以和聚合結(jié)果以及原始數(shù)據(jù)作比較
文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-415650.html
到了這里，關(guān)于【ORACLE】數(shù)據(jù)拼接那些事-多行或多列按指定分隔符拼接的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
