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 |