526互联

LISTAGG函数 oracle_11g

发布时间 2023-04-01 00:06:55作者: 何苦->

LISTAGG函数 oracle_11g

LISTAGG函数语法：
LISTAGG (measure_expr:any [, delimiter_expr:VARCHAR]) WITHIN GROUP(ORDER BY column_expr)

语法注解：

LISTAGG函数一般配合GROUP BY作为聚合函数(也可用于开窗函数)使用，可以传入两个参数变量

measure_expr：要合并处理的字段

delimiter_expr：给定分隔符号

column_expr：排序字段

需求：统计每个客户纵向拼接消费的月份 多行纵向拼接

-- 纵向拼接字段-- 开窗函数用法
SELECT
	CUS_NO,
	TRAN_MONTH, -- 按客户编号开窗
	LISTAGG ( TRAN_MONTH, ',' ) WITHIN GROUP ( ORDER BY TRAN_MONTH ) OVER ( PARTITION BY CUS_NO ) AS PINJIE 
FROM
	test2;

聚合和开窗的区别在于：

​ 聚合函数每组只会返回一条数据(按group by的字段)

​ 开窗函数会返回原数据所有行，但会额外增加统计字段

扩展需求：统计每个客户纵向拼接消费的月份 多列横向拼接

-- 横向拼接字段
SELECT
	CUS_NO 客户编号,
	TRAN_MONTH 交易月份,
	TRAN_AMT 交易金额,
	-- 拼接字段末尾逗号情况的处理
	decode( substr( pinjie, length( pinjie ), 1 ), ',', substr( pinjie, 1, length( pinjie ) - 1 ), pinjie ) AS pinjie 
FROM (
	-- 使用拼接符 || 将三个字段拼接到一起
	SELECT
		CUS_NO,
		TRAN_MONTH,
		TRAN_AMT,
		decode( CUS_NO, NULL, NULL, '客户编号:' || CUS_NO || ',' ) || decode( TRAN_MONTH, NULL, NULL, '交易月份:' || TRAN_MONTH || ',' ) || decode( TRAN_AMT, NULL, NULL, '交易金额:' || TRAN_AMT ) AS pinjie 
	FROM
		test2 
	) a;