前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL括号字符串计数

MySQL括号字符串计数

作者头像
用户1148526
发布2022-11-14 19:25:25
1.3K0
发布2022-11-14 19:25:25
举报
文章被收录于专栏:Hadoop数据仓库

目录

问题提出:

解决方案:

1. 使用正则表达式

2. 不使用正则表达式

3. 字典表 + 自定义函数

4. 字典表 + 递归查询

总结:

参考:

问题提出:

有一张表 t1 存储用户评论内容,如下所示(只列出相关列):

现在想得出每种评论字数的个数,每个字符包括标点、空格、表情符号都算一个字,但每对中括号连同其中的内容只算一个字。对于上面的数据行,结果为:

解决方案:

1. 使用正则表达式

第一感觉这是使用正则表达式的场景。只要将每对中括号连同其中的内容替换为单个字符,再用char_length函数求长度即可。查询语句如下:

代码语言:javascript
复制
select char_length(regexp_replace(Content,'\\[.*?\\]', 'A')) r,count(*) 
  from t1 group by char_length(regexp_replace(Content,'\\[.*?\\]', 'A')) 
 order by r;

\\[ 和 \\] 用于将中括号转义为普通字符。正则表达式中,“.”表示表示匹配除换行符 \n 之外的任何单字符,“*”表示零次或多次。所以 “.*” 连在一起就表示任意字符出现零次或多次。没有“?”表示贪婪模式。比如a.*b,它将会匹配最长的以a开始,以b结束的字符串。如果用它来搜索aabab的话,它会匹配整个字符串aabab。这被称为贪婪匹配。又比如模式src=`.*`, 它将会匹配以 src=` 开始,以`结束的最长的字符串。用它来搜索 <img src=``test.jpg` width=`60px` height=`80px`/> 时,将会返回 src=``test.jpg` width=`60px` height=`80px`

“?”跟在“*”后边用时,表示懒惰模式,也称非贪婪模式,就是匹配尽可能少的字符。这就意味着匹配任意数量的重复,但是在能使整个匹配成功的前提下使用最少的重复。a.*?b匹配最短的,以a开始,以b结束的字符串。如果把它应用于aabab的话,它会匹配aab(第一到第三个字符)和ab(第四到第五个字符)。又比如模式 src=`.*?`,它将会匹配 src=` 开始,以 ` 结束的尽可能短的字符串,且开始和结束中间可以没有字符,因为*表示零到多个。用它来搜索 <img src=``test.jpg` width=`60px` height=`80px`/> 时,将会返回 src=``。

2. 不使用正则表达式

MySQL 5.6版本中还没有提供正则表达式功能,无捷径可循,只能用常规SQL解决。查询语句如下:

代码语言:javascript
复制
select f,count(*) 
  from (select commentid,
               sum(case when l1=0 then char_length(s)
                        when locate('[',s) = 0 then char_length(s)
                        when substring(s,1,1)='[' then 1 
                        else char_length(substring(s,1,locate('[',s)-1)) + 1 end) f
          from (select commentid,content,substring_index(substring_index(content,']',id),']',-1) s,l1,id 
                  from (select commentid,content,char_length(content)-char_length(replace(content,']','')) l1 
                          from (select commentid,case when locate(']',content) and substring(content,-1,1)<>']' then concat(content,']') 
                                                      else content end content 
                                  from t1) t) t1,nums 
                 where id<=(case when l1=0 then 1 else l1 end)) t 
         group by commentid) t 
 group by f 
 order by f;

没有正则表达式的加持,实现起来比较麻烦,但整个思路还是很清晰。总的想法是,首先对评论字符串以“]”为分隔符转多行,然后针对不同情况对每行求字符长度,之后按每条评论ID分组求和,得到符合规则的每条评论的长度,最后按评论长度分组进行二次聚合,得到每种长度的个数。

下面我们一层层分析。

9-11行中的子查询为每个带有“]”符号,并且最后一个字符不是“]”的评论尾部拼接一个“]”字符。这是针对类似ID为44132703的这种中括号出现在评论字符串中间的情况,只有这样才能用统一方法进行转多行的操作。数字辅助表nums是只有一列ID的1、2、3......数列,关联它用笛卡尔积由原表的一行制造出多行。

8-11行中的子查询,得出每条评论中成对中括号的个数(l1列),0表示评论字符串中没有成对的中括号,结果如下:

7-12行中的子查询,结果为使用以“]”为分隔符转的多行:

2-13行中的子查询,针对不同情况对每行求字符长度。l1=0 时直接求长度,如“舞姿优美”、“[礼物b,永远支持你 [礼物b,,”;否则,字符串中没有出现“[”的,也直接求长度,如“ 赞赞赞赞赞”;否则,“[”是第一个字符的,表示是中括号中的字符串,按规则其长度为1,如“[满分'”、“[握手'”、“[手套”;否则,取“[”前面字符串的长度加1,如“谢谢友友的支持和鼓励[握手'”。之后按每条评论ID分组求和,得到符合规则的每条评论的长度,结果如下:

最外层查询按评论长度分组进行二次聚合,得到每种长度的个数。

3. 字典表 + 自定义函数

代码语言:javascript
复制
-- 创建字典表
create table dict as
select distinct concat(case when locate('[',s) = 1 then s else substring(s,locate('[',s)) end,']') s 
  from (select commentid,content,substring_index(substring_index(content,']',id),']',-1) s,l1,id 
          from (select commentid,content,char_length(content)-char_length(replace(content,']','')) l1 
                  from (select commentid,case when locate(']',content) and substring(content,-1,1)<>']' then concat(content,']') 
                                                      else content end content 
                          from t1) t) t1,nums 
         where id<=(case when l1=0 then 1 else l1 end)) t where l1>0 and instr(s,'[')>0;
 
-- 创建替换函数
delimiter //
 
create function translate(ps varchar(6000)) returns varchar(6000)
begin
    declare rs varchar(6000);
    declare done int default 0;
    declare cs varchar(200);
    declare c cursor for select s from dict;
    declare continue handler for not found set done=1;
    
    set rs=ps;
    
    if instr(ps,'[')>0 and instr(ps,']')>0 then
        open c;
        while done=0 do 
            fetch c into cs;
            set rs=replace(rs,cs,'A');
        end while;
        close c;
    end if;
    return rs;
end;
//
 
delimiter ;
 
-- 查询
select char_length(translate(content)) f,count(*) 
  from t1 
 group by char_length(translate(content))
 order by f;

4. 字典表 + 递归查询

代码语言:javascript
复制
-- 创建字典表(同3)
 
-- 递归查询
with recursive cte (commentid,content, cnt) as 
( 
  select distinct commentid,content, 1 as cnt from t1 left join dict on instr(content,s) > 0 
    union all 
  select commentid,replace(content,s,'A'), cnt + 1 from cte left join dict on instr(content,s) > 0 where content is not null
) 
select char_length(content) f,count(*) 
  from (select distinct t1.commentid,t1.content 
          from cte t1,(select commentid,max(cnt) rn from cte where content is not null group by commentid) t2 
         where t1.commentid=t2.commentid and t1.cnt=t2.rn and t1.content is not null) t 
 group by char_length(content) order by f;

总结:

按某种模式匹配或替换字符串,通常是正则表达式大显身手的场景。在本例中,不使用正则表达式的解决方案不但冗长,而且由于用到笛卡尔积由单行转多行,之后再聚合,性能比正则表达式差的多。实际表中有55107行记录,方案1秒出结果,方案2需要执行50多秒。

参考:

SQL用正则表达式替换 括号以及括号内的内容为空

解析正则表达式中的.*,.*?,.+?的含义

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2022-11-02,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL(TencentDB for MySQL)为用户提供安全可靠,性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性,包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL,可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理,不仅经济实惠,而且稳定可靠,易于运维。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档