🎈前言:
本次在进行大数据需求分析时,接到了一个“简单”的需求,统计多个检测人员的工作量。即成品检测时,我们需分析80-90个检测指标来判定它是否合格,合格后才允许出厂放行。当然要完成这些检测项目,需要多人协作。比如完成主含量的检测,需要甲乙丙丁四个人,完成杂质的检测需要甲乙魑魅魍魉6人、完成粒度检测需要乙丙丁魅魍5人。。。。等等等。假设一共就甲乙丙丁魑魅魍魉8人,因涉及80-90个检测项目,他们都有涉及对应的检测项目,但是具体谁的工作量饱和,谁的不饱和无从得知,因此通过表单统计将检测项目和对应检测人员的信息保存到了表里,只需分析,每个人每天对应每个检测项目检测了多少次,就可以分析对应的工作量饱和情况。哈哈哈,这个需求是不是超级easy~,然而噩梦才刚刚开始。。。
🎈一、多种分隔符的一个字段拆分多行
首先我们来看,怎么将一行数据拆分为多行,在这里我们用到了 REGEXP_SUBSTR的这个函数,通过 REGEXP_SUBSTR和对应正则表达式来完成我们拆分的目的。
为了让大家快速理解代码,对应代码中[^]代表不是某某开头的,即我们拆分的时候,只会拆分列如“1,2,3”的数据,而不会去拆分“,1,2,3”的数据,其中的“+”代表多次匹配,“|”是或者的意思,即当我们有多种分隔符的时候就可以用到,例如拆分“1,2\3,4,5\6”的数据时就需要用到了。具体用法感兴趣的可以去理解oracle正则表达式的内容。
🎨1、拆分的第一种办法
🍋代码:
--第一种写法
SELECT
REGEXP_SUBSTR ('1,2,3',
'[^,]+',
rownum)
FROM dual
CONNECT BY
rownum <= LENGTH ('1,2,3') - LENGTH (regexp_replace('1,2,3', ',', ''))+ 1;
🍋效果:
🍋解析:
如上图所示我们成功,将‘1,2,3’拆分为了1 2 3 的3行,但是此种办法细心的小伙伴会发现有很多空行。因此强烈不推荐!!!
🎨2、第二种办法
🍋代码:
--第二种写法
SELECT
REGEXP_SUBSTR ('1,2,3','[^,]+',1,
rownum)
FROM dual
CONNECT BY REGEXP_SUBSTR ('1,2,3','[^,]+',1,LEVEL) is not null
🍋效果:
🍋解析:
此种写法代码简洁,出来的结果和我们预想的一致,只出现了我们需要的,1,2,3的3行数据。因此推荐此种写法。
🎨3、多种分隔符的写法
🍋代码:
--第二种写法
SELECT
REGEXP_SUBSTR ('1,2\3,4,5\6','[^,|\]+',1,
rownum)
FROM dual
CONNECT BY REGEXP_SUBSTR ('1,2\3,4,5\6','[^,|\]+',1,LEVEL) is not null
🍋效果:
🍋解析:
如图所示,我们只需在正则表达式中将[^,]改成[^,|\]即可,即通过“|”分隔将分隔符依次罗列即可。是不是脑瓜子嗡嗡嗡的,哈哈哈,别急,更嗡嗡嗡的在后面。
🎈二、多行多列多种分隔符拆多行
哈哈哈,经过上面的学习,相信普通的拆分已经难不住你了,因此估计你已经觉得自己可以上天了,此时业务部门告诉你这种情况不知一列,有多列,而且有多种分隔符,此时你会发现你的小脑袋瓜子不够用了。怎么办?不急,我们先瞅瞅现场。
🎨1、单行多列多分隔符拆分
🍋代码:
--多列拆分
SELECT
REGEXP_SUBSTR ('1,2\3,4,5\6','[^,|\]+',1,LEVEL) a,
REGEXP_SUBSTR ('7,8\3,5,9,0,6,4','[^,|\]+',1,LEVEL) a1
FROM dual
CONNECT BY REGEXP_SUBSTR ('1,2\3,4,5\6','[^,|\]+',1,LEVEL) is not null
or REGEXP_SUBSTR ('7,8\3,5,9,0,6,4','[^,|\]+',1,LEVEL) is not null
🍋效果:
🍋解析:
如图所示,当我们拆分单行,多列的数据时,最终的数据会按最多数据列拆分为多行,然后数据一一对应,依次是[(1,7),(2,8),(3,3),(4,5),(5,9),(6,0),('',6),('',4)] ,为了显示所有的数据,因此在CONNECT BY中,用or将多列连接计算。将有3...n列时也是通过or分隔。