在数据处理过程中,有一需求也是经常性遇到:对字符串的聚合操作,一般来说,聚合类函数只会在数值类型上进行计算,但有时为了人的阅读需求,将字符串进行聚合操作,得到一个用于备注性的信息查阅,还是有必要的。
在Excel环境中,可以用PowerQuery轻松实现,但在数据库层面,实现这样的需求也还是颇曲折的。
本篇正要行文时,百度了一下,竟然最新版Sqlserver2017已经支持本篇的实现效果,技术的进步,过往的方案将被丢入封尘角落,不过还好,官方实现的效果还是有一点点不是太完美,还可以拿出来再讲解并使用的。
使用场景
聚合后的字符串,很难再有分析的价值,正如引文所述,更多地用来作一些备注性浏览使用。
在一个常见的场景中,不同用户分别购买过哪些的订单,将其所有订单号合并起来展示,这样的需求,就类似于将不同客户的购买数量汇总一样,只是后者是可真实地相加的汇总,前者是字符串的拼接,中间一般用分隔符隔开方便阅读。
虽然说在Excel环境或PowerBI环境可以同样实现这些功能,但有时方案的可行性需要考虑客观情况,用户电脑是否有高版本的Excel支持PowerQuery或有PowerBI可使用,若没有时,使用Sqlserver上将数据加工好,直接让用户端调用此查询的结果,这样就非常有应用价值了。
所以很多时候不是自己会做,更多时候还要让其他不会做的人更轻松更低成本地获得所要效果,这就衍生了许多IT级的解决方案来了。
Sqlserver官方实现函数
以下两个函数,可用于字符串的聚合和重新拆分,都是高版本Sqlserver所提供的,STRING_AGG是Sqlserver2017提供,STRING_SPLIT是Sqlserver2016提供。
STRING_AGG (Transact-SQL) – SQL Server | Microsoft Docs https://docs.microsoft.com/zh-cn/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017
STRING_SPLIT (Transact-SQL) – SQL Server | Microsoft Docs https://docs.microsoft.com/zh-cn/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017
自定义函数安装
和上一篇一样,只需打开sql文件,点击运行即可(因程序集已经在上一篇中安装过,程序集内部已经有此篇的函数,此篇的sql只是对程序集内的函数作外部引用说明,故真正需要使用时,需安装上一篇正则表达式的程序集,再运行本篇的sql)。
字符串函数安装
当安装成功后,在【可编程性】=》【函数】=》【聚合函数】的位置会出现本篇所提供的4个自定义函数。
自定义函数所在位置及参数调用
4个函数其实都是实现字符串聚合的效果,区别在于,若需要对聚合的字符串进行排序,可以传入排序的依赖列,而依赖列的数据类型不同,所需要使用的函数也不同(水平有限,只能实现到这个地步,官方的原生函数效果更佳)。
字符串聚合函数实现效果,和官方函数一致
比官方函数实现更多的一个选项是可以去重,在实际需求中,去重的效果还是蛮常见的需求。
自定义函数有去重效果
最后也演示下官方的单行拆多行的效果
官方拆解函数
结语
本篇带领大家领略Sqlserver的CLR开放接口,让.NET的能力可以在Sqlserver得到延伸,区别于正则函数属于标量函数,本篇的自定义函数属于聚合函数。
虽然说Sqlserver在新的版本里不断地加入新的功能,但作为企业级环境,一般更换升级数据库版本,不像更换OFFICE软件那么容易,涉及到生产业务系统的切换大工程,同时也是产生沉重的成本负担的。
一般来说都是将产品用到尽头,即微软宣布不再维护如2019年宣Sqlserver2008R2已到生命周期,停止维护了企业才有动力去更换。
当然对于业务分析类需求,自行安装一个用于分析类需求的数据库,切换成本就很低,可以尽可能地升级到最新版本享受高版本强大功能的红利。
没有这样的条件,使用自行CLR扩展也是一条非常可行的路线,另外可以尝试下类似OFFICE365那样永远保持最新版本的Azure SQL,也是非常棒的体验,永远最新版,不用担心切换升级的成本和风险。
笔者未来聚焦在数据领域的分享,不限于Excel,会分享更多Sqlserver、dotNET、Azure、PowerBI等话题,升级数据分析的能力,欢迎继续关注。
相关阅读
「Sqlserver」数据分析师有理由爱Sqlserver之一-好用的插件工具推荐https://www.jianshu.com/p/637aba4aeee5
「Sqlserver」数据分析师有理由爱Sqlserver之二-像使用Excel一般地使用Sqlserver https://www.jianshu.com/p/b40f8a36055e
「Sqlserver」数据分析师有理由爱Sqlserver之三-最值得使有低投入高产出的Sqlsever https://www.jianshu.com/p/8a5396a7a402
「Sqlserver」数据分析师有理由爱Sqlserver之四-七大数据库产品测评Sqlserver完胜 https://www.jianshu.com/p/243115b797ff
「Sqlserver」数据分析师有理由爱Sqlserver之五-数据库环境使用正则表达式不再是梦 https://www.jianshu.com/p/5900cfaa1300
免责声明:本站所有文章内容,图片,视频等均是来源于用户投稿和互联网及文摘转载整编而成,不代表本站观点,不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益,请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权,请联系删除,如若转载,请注明出处:https://itzsg.com/8260.html