SQL拆分列至多行(性能对比)以及合并多行至一列

1. 拆分列

假设有如下employees数据,需要拆分skills至多行

idnameskills
1张三Java,Python,SQL
2李四C++,Java
3王五PHP,JavaScript

目标样式:

idnameskill
1张三Java
1张三Python
1张三SQL
2李四C++
2李四Java
3王五PHP
3王五JavaScript

方法1:

SELECT distinct id, name, REGEXP_SUBSTR(skills, '[^,]+', 1, LEVEL) AS skill
FROM employees
CONNECT BY LEVEL <= REGEXP_COUNT(skills, ',') + 1
ORDER BY id, LEVEL;

使用distinct去重是因为当有多行数据时,会产生重复行,如果去掉distinct,结果如下

SELECT id, name, SKILLS,LEVEL,REGEXP_SUBSTR(skills, '[^,]+', 1, LEVEL) AS skill
FROM employees
CONNECT BY LEVEL <= REGEXP_COUNT(skills, ',') + 1
ORDER BY LEVEL, id;

输出:

IDNAMESKILLSLEVELSKILL
1张三JAVA,PYTHON,SQL1JAVA
2李四C++,Java1C++
3王五PHP,JAVASCRIPT1PHP
1张三JAVA,PYTHON,SQL2PYTHON
1张三JAVA,PYTHON,SQL2PYTHON
1张三JAVA,PYTHON,SQL2PYTHON
2李四C++,Java2Java
2李四C++,Java2Java
2李四C++,Java2Java
3王五PHP,JAVASCRIPT2JAVASCRIPT
3王五PHP,JAVASCRIPT2JAVASCRIPT
3王五PHP,JAVASCRIPT2JAVASCRIPT
1张三JAVA,PYTHON,SQL3SQL
1张三JAVA,PYTHON,SQL3SQL
1张三JAVA,PYTHON,SQL3SQL
1张三JAVA,PYTHON,SQL3SQL
1张三JAVA,PYTHON,SQL3SQL
1张三JAVA,PYTHON,SQL3SQL
1张三JAVA,PYTHON,SQL3SQL
1张三JAVA,PYTHON,SQL3SQL
1张三JAVA,PYTHON,SQL3SQL


产生重复行的原因是CONNECT BY仅限制了level层级,并没有提供将子行匹配到父行的条件,因此在具有多行的表中,每一行都是其他行的子行(例如:level=2的每个ID重复数是level=1的总行数)。

上面的方法虽然可以通过distinct去除重复行得到结果,但当数据量稍微增大时,中间过程产生的重复行数将指数级增长,会严重影响性能,因此方法1只适用于极小数据量。

方法2:

在方法1的基础上,增加了将子行匹配到父行的条件PRIOR id = id,注意还需增加条件AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL,否则会提示CONNECT BY 循环错误:

SELECT id, name, REGEXP_SUBSTR(skills, '[^,]+', 1, LEVEL) AS skill
FROM employees
CONNECT BY LEVEL <= REGEXP_COUNT(skills, ',') + 1
AND PRIOR id = id
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
ORDER BY  id, LEVEL;

输出:

idnameskill
1张三Java
1张三Python
1张三SQL
2李四C++
2李四Java
3王五PHP
3王五JavaScript

方法2通过增加子行匹配到父行的条件,避免了中间过程重复行的产生,经测试可适用于较大数据量,但由于正则表达式的使用,数据量大时,运行速度仍不理想。

方法3:

SELECT id, name,
	substr(a.SKILLS, instr(a.SKILLS, ',', 1, levels.lvl) + 1, instr(a.SKILLS, ',', 1, levels.lvl + 1) -(instr(a.SKILLS, ',', 1, levels.lvl) + 1)) as SKILL
	FROM 
	(	SELECT 
		id, name,
		',' || SKILLS || ',' AS SKILLS,
		nvl(length(SKILLS),0) - nvl(length(REPLACE(SKILLS, ',')), 0) + 1 AS cnt 
		FROM EMPLOYEES
	) a,
	(	
		SELECT LEVEL AS lvl 
		FROM 
		(	SELECT MAX(length(SKILLS || ',') - nvl(length(REPLACE(SKILLS, ',')), 0)) max_len 
			FROM EMPLOYEES
		)
		CONNECT BY LEVEL <= max_len
	) levels --生成最大参数个数的行数
	WHERE levels.lvl <= a.cnt --得到每个ID的参数行数
	ORDER BY ID,levels.lvl

输出:

idnameskill
1张三Java
1张三Python
1张三SQL
2李四C++
2李四Java
3王五PHP
3王五JavaScript

方法3避免了正则表达式的使用,经测试,大数据量时性能最好,推荐使用

2. 合并多行至一列

--若有排序需求,则加上within group子句
select id,name,LISTAGG(skill,',') --within group (order by skill)
from employee
group by id,name

当输入表为上述拆分的表employee时,输出:

idnameskills
1张三Java,Python,SQL
2李四C++,Java
3王五PHP,JavaScript
  • 3
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值