学习
实践
活动
专区
工具
TVP
写文章

数据分析师有理由爱Sqlserver之六-让Sqlserver拥有字符串聚合函数

在数据处理过程中,有一需求也是经常性遇到:对字符串的聚合操作,一般来说,聚合类函数只会在数值类型上进行计算,但有时为了人的阅读需求,将字符串进行聚合操作,得到一个用于备注性的信息查阅,还是有必要的。

在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等话题,升级数据分析的能力,欢迎继续关注。

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20190723A03I6400?refer=cp_1026
  • 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

关注

腾讯云开发者公众号
10元无门槛代金券
洞察腾讯核心技术
剖析业界实践案例
腾讯云开发者公众号二维码

扫码关注腾讯云开发者

领取腾讯云代金券