玖叶教程网

前端编程开发入门

2024年04月18日

举个例子，说说用SQL怎么处理文本数据

从数据格式上讲，类似商品标题、用户评论、文章等文本型数据看起来杂乱无章，充满了噪声，充满了不确定性。这样的数据，处理起来比较棘手。我们只能不断优化处理的规则，以提高处理的准确率，例如，处理10000条文本数据，之前准确处理了9000条数据，准确率90%，优化规则后，准确率提高至95%。实际情况下处理准确率很难达到100%。除非穷举并加以特殊处理，但这显然不是有效的办法。

下面的两个例子，分享一些在HIVE上如何写SQL处理文本数据的经验（在其它数据库或数据平台写SQL也可作以参考）。

文本处理常用到的几个HIVE函数：

regexp_extract(string A,string B,int i)：从字符串A中提取出第i+1次满足条件的子字符串B，字符串B通常以正则表达式出现
regexp_replace(string A,string B,string C)：将字符串A中所有的子字符串B替换为字符串C
split(string A,string B)：将字符串A以字符串B中的任一个字符为分隔符切分成几个部分，以数组形式返回，split(string A,string B)[0]返回第一部分，split(string A,string B)[1]返回第二部分
reverser(string A)：将字符串A进行逆序显示
concat(string A,string B,string C......)：将字符串按顺序拼接起来
instr(string A,string B)：查找字符串B在字符串A中第一次出现的位置
substr(string A,start i,int j)：在字符串A中，从第i个字符开始截取j个长度的字符

备注：正则表达式的使用这里不做讲解。

-- 判断文本中是否含有字母

title rlike '[a-zA-Z]' 或者 upper(title) rlike '[A-Z]'

-- 判断文本中是否全为字母，1-是，0-否

case when upper(title) rlike '^[a-z]+

then 1 else 0 end

-- 判断文本中是否含有汉字

title rlike '[一-龥]'

-- 判断文本中是否全为汉字，1-是，0-否

case when title rlike '^[一-龥]+

then 1 else 0 end

-- 判断文本中是否含有数字

title rlike '[0-9]'

-- 判断文本中是否全为数字，1-是，0-否

case when title rlike '^[0-9]+

then 1 else 0 end

-- 判断整数

case when title rlike '^-?[1-9]+

then 1 else 0 end

-- 判断正整数

case when title rlike '^[1-9]+

then 1 else 0 end

-- 判断负整数

case when title rlike '^-[1-9]+

then 1 else 0 end

-- 判断浮点数

case when title rlike '^-?[0-9]+[\.][0-9]+

then 1 else 0 end

-- 判断正的浮点数

case when title rlike '^[0-9]+[\.][0-9]+ then 1 else 0 end

案例一、从商品标题中提取出规格、数量

实现逻辑：利用regexp_extract()函数从商品标题中提取出满足模式“整数 / 浮点数 + 规格单位 / 数量单位”的字符串。

如下图示例，是不是很简单？这里主要是要把规格单位和数量单位尽量列举完全，这样才能避免出现有时候没有处理的文本数据。

select title

,regexp_extract(upper(title),'(([0-9]+[\.][0-9]+)|([0-9]+))(ML|KG|L|G|CM|M|毫升|升|抽|斤|段|米|周)',0) as spec

,regexp_extract(upper(title),'(([0-9]+[\.][0-9]+)|([0-9]+))(个|份|只|片|包|卷|双|块|张|把|支|条|杯|枚|根|粒|袋|颗|罐|盒|瓶)',0) as qty

from items t

order by title desc

;

不用regexp_extract()函数是不是也可以做？

可以，但有点麻烦。

步骤如下：

Step_1：判断出商品标题中出现的单位；

Step_2：以单位为分割符，将商品标题进行切分（用split函数），提取第一部分；

例如，标题“陕西富士*4个600g起”，切分之后，分成“陕西富士*4个600”和“起”两部分

备注：第一部分末尾的数字即为单位对应的大小

Step_3：将第一部分进行逆序（用reverse函数）；

逆序之后，目标就变为提取字符串中开头几位的整数或者浮点数。

这里的难度在于，不知道开始具体多少位为整数或浮点数，每条数据都不一样。所以，换一种思维，

Step_4：以中文或一些特殊字符作为分隔符，将Step_3的字符串切分成两部分（用split函数），提取第一部分，就得到了整数或浮点数；

例如，“006个4*士富西陕”，切分之后，分成“006”、“4*”两部分

Step_5：将Step_4字符串进行逆序（用reverse函数）；

Step_6：为保证整数或浮点数的有效性，将Step_5中非数字或小数点.的字符去掉（用regexp_replace函数）；

Step_7：最后将Step_6结果与Step_1的单位拼接起来（用concat函数）。

select title

,step_1

,split(upper(title),step_1)[0] as step_2

,reverse(split(upper(title),step_1)[0]) as step_3

,split(reverse(split(upper(title),step_1)[0]),'[一-龥A-Z|\\(|\\)|（|）]')[0] as step_4

,reverse(split(reverse(split(upper(title),step_1)[0]),'[一-龥A-Z|\\(|\\)|（|）]')[0]) as step_5

,regexp_replace(reverse(split(reverse(split(upper(title),step_1)[0]),'[一-龥A-Z|\\(|\\)|（|）]')[0]),'[^(0-9|\.)]','') as step_6

,concat(regexp_replace(reverse(split(reverse(split(upper(title),step_1)[0]),'[一-龥A-Z|\\(|\\)|（|）]')[0]),'[^(0-9|\.)]',''),step_1) as step_7

from (select title

,case

when upper(title) rlike '[0-9]ML' then 'ML'

when upper(title) rlike '[0-9]KG' then 'KG'

when upper(title) rlike '[0-9]L' then 'L'

when upper(title) rlike '[0-9]G' then 'G'

when upper(title) rlike '[0-9]CM' then 'CM'

when upper(title) rlike '[0-9]M' then 'M'

when upper(title) rlike '[0-9]毫升' then '毫升'

when upper(title) rlike '[0-9]升' then '升'

when upper(title) rlike '[0-9]抽' then '抽'

when upper(title) rlike '[0-9]斤' then '斤'

when upper(title) rlike '[0-9]段' then '段'

when upper(title) rlike '[0-9]米' then '米'

when upper(title) rlike '[0-9]周' then '周'

else null end step_1

案例二：从商品标题中提取出品牌

从案例一可以看出，regexp_extract()函数只能提取出具有一定模式的信息，类似品牌、产地等信息，写法不固定，就很难用regexp_extract()函数了。

可以采取这样的做法，类似分词处理的方式，预先维护一个关键词维表，然后用维表中的每个关键词去模糊匹配文本，如果某一个关键词匹配上，就将该关键词对应的信息与文本对应上。

Step_1：首先维护一个品牌维表，类似下表，包括品牌名、关键词、优先级。一般来说，关键词可以取品牌中文名、英文名，还有缩写。关键词在表中取值唯一，相当于这个表的主键ID。

优先级可以根据关键词长度和关键词本身进行排序。

Step_2：用关键词模糊匹配商品标题

select t1.title

,t2.brand_name

,t2.keyword

,t2.rn

from items t1 left join brands t2 on 1=1

where t1.title rlike t2.keyword

结果如下，从中发现，从同一标题中可能会匹配出多个品牌，例如“农夫山泉1.5L*2瓶”，匹配出了农夫山泉、农夫两个品牌，这时提取优先级最高的那个。

Step_3：同一个标题下提取优先级最高的品牌

select title,brand_name

from (

select title,brand_name,row_number() over(partition by title order by rn) as rn

from (select t1.title,t2.brand_name,t2.keyword,t2.rn from items t1 left join brands t2 on 1=1 where t1.title rlike t2.keyword) t

) t

where rn=1

;

声明：??本文来自微信公众号“土拨鼠的菜园地”（ID：gh_30a852e13a3a），系原创文章，如需转载，请注明出处。部分图表来自网络。

原文链接：https://mp.weixin.qq.com/s/gcifwnFCePMPbSgjs4BjIA

标签：sqlinstr

发表评论:取消回复

控制面板: 您好，欢迎到访网站！
登录后台查看权限

网站分类

技术精选

最新留言