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

1. 拆分列

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

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

目标样式：

id	name	skill
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;

输出：

ID	NAME	SKILLS	LEVEL	SKILL
1	张三	JAVA,PYTHON,SQL	1	JAVA
2	李四	C++,Java	1	C++
3	王五	PHP,JAVASCRIPT	1	PHP
1	张三	JAVA,PYTHON,SQL	2	PYTHON
1	张三	JAVA,PYTHON,SQL	2	PYTHON
1	张三	JAVA,PYTHON,SQL	2	PYTHON
2	李四	C++,Java	2	Java
2	李四	C++,Java	2	Java
2	李四	C++,Java	2	Java
3	王五	PHP,JAVASCRIPT	2	JAVASCRIPT
3	王五	PHP,JAVASCRIPT	2	JAVASCRIPT
3	王五	PHP,JAVASCRIPT	2	JAVASCRIPT
1	张三	JAVA,PYTHON,SQL	3	SQL
1	张三	JAVA,PYTHON,SQL	3	SQL
1	张三	JAVA,PYTHON,SQL	3	SQL
1	张三	JAVA,PYTHON,SQL	3	SQL
1	张三	JAVA,PYTHON,SQL	3	SQL
1	张三	JAVA,PYTHON,SQL	3	SQL
1	张三	JAVA,PYTHON,SQL	3	SQL
1	张三	JAVA,PYTHON,SQL	3	SQL
1	张三	JAVA,PYTHON,SQL	3	SQL

产生重复行的原因是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;

输出：

id	name	skill
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

输出：

id	name	skill
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时，输出：

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