【SQL开发实战技巧】系列（三十二）：数仓报表场景☞对表中某个字段内的值去重

系列文章目录

【SQL开发实战技巧】系列（一）:关于SQL不得不说的那些事
【SQL开发实战技巧】系列（二）：简单单表查询
【SQL开发实战技巧】系列（三）：SQL排序的那些事
【SQL开发实战技巧】系列（四）：从执行计划讨论UNION ALL与空字符串&UNION与OR的使用注意事项
【SQL开发实战技巧】系列（五）：从执行计划看IN、EXISTS 和 INNER JOIN效率，我们要分场景不要死记网上结论
【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放
【SQL开发实战技巧】系列（七）：从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起
【SQL开发实战技巧】系列（八）：聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表
【SQL开发实战技巧】系列（九）：一个update误把其他列数据更新成空了？Merge改写update！给你五种删除重复数据的写法！
【SQL开发实战技巧】系列（十）：从拆分字符串、替换字符串以及统计字符串出现次数说起
【SQL开发实战技巧】系列（十一）：拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数
【SQL开发实战技巧】系列（十二）：三问(如何对字符串字母去重后按字母顺序排列字符串？如何识别哪些字符串中包含数字？如何将分隔数据转换为多值IN列表?)
【SQL开发实战技巧】系列（十三）：讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加
【SQL开发实战技巧】系列（十四）：计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工
【SQL开发实战技巧】系列（十五）：查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL开发实战技巧】系列（十六）：数据仓库中时间类型操作（初级）日、月、年、时、分、秒之差及时间间隔计算
【SQL开发实战技巧】系列（十七）：数据仓库中时间类型操作（初级）确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数
【SQL开发实战技巧】系列（十八）：数据仓库中时间类型操作（进阶）INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算
【SQL开发实战技巧】系列（十九）：数据仓库中时间类型操作（进阶）如何一个SQL打印当月或一年的日历？如何确定某月内第一个和最后—个周内某天的日期？
【SQL开发实战技巧】系列（二十）：数据仓库中时间类型操作（进阶）获取季度开始结束时间以及如何统计非连续性时间的数据
【SQL开发实战技巧】系列（二十一）：数据仓库中时间类型操作（进阶）识别重叠的日期范围，按指定10分钟时间间隔汇总数据
【SQL开发实战技巧】系列（二十二）：数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式
【SQL开发实战技巧】系列（二十三）：数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高
【SQL开发实战技巧】系列（二十四）：数仓报表场景☞通过案例执行计划详解”行转列”,”列转行”是如何实现的
【SQL开发实战技巧】系列（二十五）：数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组
【SQL开发实战技巧】系列（二十六）：数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行
【SQL开发实战技巧】系列（二十七）：数仓报表场景☞通过对移动范围进行聚集来详解分析函数开窗原理以及如何一个SQL打印九九乘法表
【SQL开发实战技巧】系列（二十八）：数仓报表场景☞人员分布问题以及不同组(分区)同时聚集如何实现
【SQL开发实战技巧】系列（二十九）：数仓报表场景☞简单的树形(分层)查询以及如何确定根节点、分支节点和叶子节点
【SQL开发实战技巧】系列（三十）：数仓报表场景☞树形(分层)查询如何排序？以及如何在树形查询中正确的使用where条件
【SQL开发实战技巧】系列（三十一）：数仓报表场景☞分层查询如何只查询树形结构某一个分支？如何剪掉一个分支？
【SQL开发实战技巧】系列（三十二）：数仓报表场景☞对表中某个字段内的值去重

文章目录

前言

本篇文章讲解的主要内容是：现在有个问题：如何对表中某个字段内的值去重。这个问题，是日常开发中稍微常见的，同样也是一个比较困难的问题。如果仅是一个字符串的单行处理还好说，麻烦的就是对于有N(N>1)行数据的表处理。
在这篇文章之前，我曾经在第十二篇文章中提过此类问题，当时是给出的"一个字段中单个字符串去重排序的案例(例如：abccdaa去重排序后为:abcd)"，对于有N(N>1)行数据的表的解决方案，我是通过标量子查询实现的。
本篇文章，拿另外一种案例进行完整的分析，对此类问题进行一个补充，给出一个我认为比较完善的解决方案！
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

一、如何对字段内的值去重

现在有个问题：如何对表中某个字段内的值去重
这个问题，是日常开发中稍微常见的，同样也是一个比较困难的问题。如果仅是一个字符串的单行处理还好说，麻烦的就是对于有N(N>1)行数据的表处理。
在这篇文章之前，我曾经在第十二篇文章中提过此类问题，当时是给出的"一个字段中单个字符串去重排序的案例(例如：abccdaa去重排序后为:abcd)"，对于有N(N>1)行数据的表的解决方案，我是通过标量子查询实现的。
本篇文章，拿另外一种案例进行完整的分析，对此类问题进行一个补充，给出一个我认为比较完善的解决方案！

测试数据如下：

with t as(select1as id,'ab,b,c,ab,d,e'as str from dual unionallselect2as id,'11,2,3,4,11,2'as str from dual 
)select*from t;
        ID STR
---------- -------------1 ab,b,c,ab,d,e
         211,2,3,4,11,2

上述临时表中用逗号分隔的各值都有重复。
现在有个需求：要求其中的重复值只保留一个即可。
对于这种需求，首先要把字符串拆分为多行,再去重。
如果只有一行,拆分很简单：

with t as(select1as id,'ab,b,c,ab,d,e'as str from dual)select id, str, regexp_substr(str,'[^,]+',1,level)as news
  from t
connectbylevel<=(regexp_count(str,',')+1);
        ID STR                              NEWS
---------- -------------------------------- ----------------------------------------------------------------1 ab,b,c,ab,d,e                    ab
         1 ab,b,c,ab,d,e                    b
         1 ab,b,c,ab,d,e                    c
         1 ab,b,c,ab,d,e                    ab
         1 ab,b,c,ab,d,e                    d
         1 ab,b,c,ab,d,e                    e

6rows selected

如果有多行,那么这个语句就有问题了：

with t as(select1as id,'ab,b,c,ab,d,e'as str from dual unionallselect2as id,'11,2,3,4,11,2'as str from dual 
 )select id, str, regexp_substr(str,'[^,]+',1,level)as news,count(*)over()as 总条数,sys_connect_by_path(id,',')as paths
  from t
connectbylevel<=(regexp_count(str,',')+1);

        ID STR           NEWS                              总条数 PATHS
---------- ------------- -------------------------- ---------- --------------------------------------------------------------------------------1 ab,b,c,ab,d,e ab                                126,11 ab,b,c,ab,d,e b                                 126,1,11 ab,b,c,ab,d,e c                                 126,1,1,11 ab,b,c,ab,d,e ab                                126,1,1,1,11 ab,b,c,ab,d,e d                                 126,1,1,1,1,11 ab,b,c,ab,d,e e                                 126,1,1,1,1,1,1211,2,3,4,11,22126,1,1,1,1,1,2211,2,3,4,11,211126,1,1,1,1,21 ab,b,c,ab,d,e e                                 126,1,1,1,1,2,1211,2,3,4,11,22126,1,1,1,1,2,2211,2,3,4,11,24126,1,1,1,21 ab,b,c,ab,d,e d                                 126,1,1,1,2,11 ab,b,c,ab,d,e e                                 126,1,1,1,2,1,1211,2,3,4,11,22126,1,1,1,2,1,2211,2,3,4,11,211126,1,1,1,2,21 ab,b,c,ab,d,e e                                 126,1,1,1,2,2,1211,2,3,4,11,22126,1,1,1,2,2,2211,2,3,4,11,23126,1,1,21 ab,b,c,ab,d,e ab                                126,1,1,2,11 ab,b,c,ab,d,e d                                 126,1,1,2,1,11 ab,b,c,ab,d,e e                                 126,1,1,2,1,1,1211,2,3,4,11,22126,1,1,2,1,1,2211,2,3,4,11,211126,1,1,2,1,21 ab,b,c,ab,d,e e                                 126,1,1,2,1,2,1211,2,3,4,11,22126,1,1,2,1,2,2211,2,3,4,11,24126,1,1,2,21 ab,b,c,ab,d,e d                                 126,1,1,2,2,11 ab,b,c,ab,d,e e                                 126,1,1,2,2,1,1211,2,3,4,11,22126,1,1,2,2,1,2211,2,3,4,11,211126,1,1,2,2,21 ab,b,c,ab,d,e e                                 126,1,1,2,2,2,1211,2,3,4,11,22126,1,1,2,2,2,2211,2,3,4,11,22126,1,21 ab,b,c,ab,d,e c                                 126,1,2,11 ab,b,c,ab,d,e ab                                126,1,2,1,11 ab,b,c,ab,d,e d                                 126,1,2,1,1,11 ab,b,c,ab,d,e e                                 126,1,2,1,1,1,1211,2,3,4,11,22126,1,2,1,1,1,2211,2,3,4,11,211126,1,2,1,1,21 ab,b,c,ab,d,e e                                 126,1,2,1,1,2,1211,2,3,4,11,22126,1,2,1,1,2,2211,2,3,4,11,24126,1,2,1,21 ab,b,c,ab,d,e d                                 126,1,2,1,2,11 ab,b,c,ab,d,e e                                 126,1,2,1,2,1,1211,2,3,4,11,22126,1,2,1,2,1,2211,2,3,4,11,211126,1,2,1,2,21 ab,b,c,ab,d,e e                                 126,1,2,1,2,2,1211,2,3,4,11,22126,1,2,1,2,2,2211,2,3,4,11,23126,1,2,21 ab,b,c,ab,d,e ab                                126,1,2,2,11 ab,b,c,ab,d,e d                                 126,1,2,2,1,11 ab,b,c,ab,d,e e                                 126,1,2,2,1,1,1211,2,3,4,11,22126,1,2,2,1,1,2211,2,3,4,11,211126,1,2,2,1,21 ab,b,c,ab,d,e e                                 126,1,2,2,1,2,1211,2,3,4,11,22126,1,2,2,1,2,2211,2,3,4,11,24126,1,2,2,21 ab,b,c,ab,d,e d                                 126,1,2,2,2,11 ab,b,c,ab,d,e e                                 126,1,2,2,2,1,1211,2,3,4,11,22126,1,2,2,2,1,2211,2,3,4,11,211126,1,2,2,2,21 ab,b,c,ab,d,e e                                 126,1,2,2,2,2,1211,2,3,4,11,22126,1,2,2,2,2,2211,2,3,4,11,211126,21 ab,b,c,ab,d,e b                                 126,2,11 ab,b,c,ab,d,e c                                 126,2,1,11 ab,b,c,ab,d,e ab                                126,2,1,1,11 ab,b,c,ab,d,e d                                 126,2,1,1,1,11 ab,b,c,ab,d,e e                                 126,2,1,1,1,1,1211,2,3,4,11,22126,2,1,1,1,1,2211,2,3,4,11,211126,2,1,1,1,21 ab,b,c,ab,d,e e                                 126,2,1,1,1,2,1211,2,3,4,11,22126,2,1,1,1,2,2211,2,3,4,11,24126,2,1,1,21 ab,b,c,ab,d,e d                                 126,2,1,1,2,11 ab,b,c,ab,d,e e                                 126,2,1,1,2,1,1211,2,3,4,11,22126,2,1,1,2,1,2211,2,3,4,11,211126,2,1,1,2,21 ab,b,c,ab,d,e e                                 126,2,1,1,2,2,1211,2,3,4,11,22126,2,1,1,2,2,2211,2,3,4,11,23126,2,1,21 ab,b,c,ab,d,e ab                                126,2,1,2,11 ab,b,c,ab,d,e d                                 126,2,1,2,1,11 ab,b,c,ab,d,e e                                 126,2,1,2,1,1,1211,2,3,4,11,22126,2,1,2,1,1,2211,2,3,4,11,211126,2,1,2,1,21 ab,b,c,ab,d,e e                                 126,2,1,2,1,2,1211,2,3,4,11,22126,2,1,2,1,2,2211,2,3,4,11,24126,2,1,2,21 ab,b,c,ab,d,e d                                 126,2,1,2,2,11 ab,b,c,ab,d,e e                                 126,2,1,2,2,1,1211,2,3,4,11,22126,2,1,2,2,1,2211,2,3,4,11,211126,2,1,2,2,21 ab,b,c,ab,d,e e                                 126,2,1,2,2,2,1211,2,3,4,11,22126,2,1,2,2,2,2211,2,3,4,11,22126,2,21 ab,b,c,ab,d,e c                                 126,2,2,11 ab,b,c,ab,d,e ab                                126,2,2,1,11 ab,b,c,ab,d,e d                                 126,2,2,1,1,11 ab,b,c,ab,d,e e                                 126,2,2,1,1,1,1211,2,3,4,11,22126,2,2,1,1,1,2211,2,3,4,11,211126,2,2,1,1,21 ab,b,c,ab,d,e e                                 126,2,2,1,1,2,1211,2,3,4,11,22126,2,2,1,1,2,2211,2,3,4,11,24126,2,2,1,21 ab,b,c,ab,d,e d                                 126,2,2,1,2,11 ab,b,c,ab,d,e e                                 126,2,2,1,2,1,1211,2,3,4,11,22126,2,2,1,2,1,2211,2,3,4,11,211126,2,2,1,2,21 ab,b,c,ab,d,e e                                 126,2,2,1,2,2,1211,2,3,4,11,22126,2,2,1,2,2,2211,2,3,4,11,23126,2,2,21 ab,b,c,ab,d,e ab                                126,2,2,2,11 ab,b,c,ab,d,e d                                 126,2,2,2,1,11 ab,b,c,ab,d,e e                                 126,2,2,2,1,1,1211,2,3,4,11,22126,2,2,2,1,1,2211,2,3,4,11,211126,2,2,2,1,21 ab,b,c,ab,d,e e                                 126,2,2,2,1,2,1211,2,3,4,11,22126,2,2,2,1,2,2211,2,3,4,11,24126,2,2,2,21 ab,b,c,ab,d,e d                                 126,2,2,2,2,11 ab,b,c,ab,d,e e                                 126,2,2,2,2,1,1211,2,3,4,11,22126,2,2,2,2,1,2211,2,3,4,11,211126,2,2,2,2,21 ab,b,c,ab,d,e e                                 126,2,2,2,2,2,1211,2,3,4,11,22126,2,2,2,2,2,2126rows selected

拆分后应该是12行,而不是126,显然这是错误的。看上面的查询结果，我们通过

sys_connect_by_path

产生的paths列这些结果可以看出,树形跨行在ID=1与ID=2之间产生了上下级关系。所以需要加一个限制：只能在本行循环拆分。
那么接下来我们加一下这个限制条件试试：

with t as(select1as id,'ab,b,c,ab,d,e'as str from dual unionallselect2as id,'11,2,3,4,11,2'as str from dual
 )select id, str, regexp_substr(str,'[^,]+',1,level)as news,count(*)over()as 总条数,sys_connect_by_path(id,',')as paths
  from t
connectbylevel<=(regexp_count(str,',')+1)and(prior id)=id

ORA-01436: 用户数据中的 CONNECTBY 循环

SQL>

可以看到，报错啦！！
这个提示其实是误报,实际上,上面的语句并不存在死循环,所以只需要想法骗过Oracle
就可以,为此增加条件

"(PRIORdbms_random.value())IS NOT NULL"

语句改为如下：

with t as(select1as id,'ab,b,c,ab,d,e'as str
    from dual
  unionallselect2as id,'11,2,3,4,11,2'as str
    from dual)select id,
       str,
       regexp_substr(str,'[^,]+',1,level)as news,count(*)over()as 总条数,
       sys_connect_by_path(id,',')as paths
  from t
connectby nocycle (level<=(regexp_count(str,',')+1)and(prior id)= id and(prior dbms_random.value()isnotnull));

        ID STR           NEWS                              总条数 PATHS
---------- ------------- -------------------------- ---------- --------------------------------------------------------------------------------1 ab,b,c,ab,d,e ab                                 12,11 ab,b,c,ab,d,e b                                  12,1,11 ab,b,c,ab,d,e c                                  12,1,1,11 ab,b,c,ab,d,e ab                                 12,1,1,1,11 ab,b,c,ab,d,e d                                  12,1,1,1,1,11 ab,b,c,ab,d,e e                                  12,1,1,1,1,1,1211,2,3,4,11,21112,2211,2,3,4,11,2212,2,2211,2,3,4,11,2312,2,2,2211,2,3,4,11,2412,2,2,2,2211,2,3,4,11,21112,2,2,2,2,2211,2,3,4,11,2212,2,2,2,2,2,212rows selected

从上面的结果来看，ID=1与ID=2之间并未再产生上下级关系了。而且拆分后的总条数就是12条了。
既然我们已经解决了多行数据间数据拆分存在的上下级关系问题，后面如何去重就很简单了！

拆分成功后,后面的操作就很简单了,去重后直接用listagg合并字符串就可以：

with t as(select1as id,'ab,b,c,ab,d,e'as str
    from dual
  unionallselect2as id,'11,2,3,4,11,2'as str
    from dual),
t1 as(select id,
         str,
         regexp_substr(str,'[^,]+',1,level)as news,count(*)over()as 总条数,
         sys_connect_by_path(id,',')as paths
    from t
  connectby nocycle(level<=(regexp_count(str,',')+1)and(prior id)= id
                 and(prior dbms_random.value()isnotnull)))select id, str, listagg(news,',')withingroup(orderby news)as lis
  from(select id, str, news from t1 groupby id, str, news) t2
 groupby id, str;
        ID STR           LIS
---------- ------------- --------------------------------------------------------------------------------1 ab,b,c,ab,d,e ab,b,c,d,e
         211,2,3,4,11,211,2,3,4SQL>

看到了这里，可能有部分小伙伴会问，为啥11排在了2前面，因为str是varchar类型的，就算拆分后，我们也没用to_number进行(也不能如此，还要考虑id=1的数据)类型转换，所以是按照首字符字典顺序排序的，如果你的表里str字段都是用逗号分隔的数字，那就可以to_number然后再排序拼接。

好了，本章加上第十二章，是对单行或N(N>1)行数据的表，某个字段进行去重排序的实现方案总结，工作中的此类问题，应该不出其右。

总结

本章加上第十二章，是对单行或N(N>1)行数据的表，某个字段进行去重排序的实现方案总结，工作中的此类问题，应该不出其右。