电脑装配网

一文搞懂 SQL:基础知识和业务实践总结

 人阅读 | 作者lilintao | 时间:2023-07-11 18:02

SQL的全称是Structured Query Language(结构化查询语言),是一种古老而简洁的程序设计语言。看似平平无奇,一直被各种吐槽,但却有着众多语言所难得的漫长寿命,并展现出极好的拓展性,在不同时期衍生出不同的子语言。笔者作为腾讯TDW体系下的SQL现役运动员,对日常工作中常用的基础知识和展开的业务实践予以了总结,可供读者参考。

结构化查询语言,顾名思义,它的基础在于结构化的数据库表,最主要的应用场景在于数据查询,虽然SQL也可以像其它语言一样有一些高级的写法,但它的主战场并不在此,仍要回归到对数据库表的操作和处理中。以下分为基础知识篇和业务实践篇展开介绍,其中基础知识篇盘点了一些常用的技能点,业务实践篇则总结了几点日常工作里的思考。

第一部分 基础知识篇

围绕着数据库表,可以展开许多的主题工作,有些是比较专业性的领域,如事务处理和权限管控等,这些更多是面向底层的技术基础,部分属于DBA的工作范畴。对于使用SQL的很大部分用户群体来说,则集中于对数据库表的增删查改,聚合汇总里,这些是面向业务的数据工作。针对这一块的内容,继续将其细分到不同的子场景里,逐一展开介绍。

1.库表基本操作

库表预览: SQL最基础和最核心的两个对象,便是数据库和数据表,基于一个业务场景,可以有N个数据库,在一个数据库里面,又可以有N张数据表。数据库的连接与切换,数据表的创建与删除,是使用SQL进行库表预览的基本操作。 这些基本操作,可以通过前端的可视化界面进行,也可以从后台直连数据库展开,需由使用者所拥有的权限级别来选择。

数据增删: 除了一些常规的每日运行的计算任务外,很多时候我们只是单纯地想对一张表进行处理,比如插入几条数据,更新某个字段值,或者剔除几条数据。这些操作往往是单次的,局部的,目的清晰,所以掌握几个关键字就可以实现,如INSERT/UPDATE/DELETE等。

视图应用: 视图的引入,相当于在统计逻辑和实际库表之间提供了一种折中的方案。完成这个功能,逻辑上是必须有这么几道工序的,但又不想在每一道工序里都落地一张实际的数据表,显得繁琐而臃肿,那就引入视图吧,把这些中间的工序用视图的形式去实现和替代。

关键字: 其实SQL真的是一门很简洁的语言,市面上也不会有大本的书籍专门讲述SQL的书写方式,因为相对于其它语言来说,SQL归根到底,只是围绕着几个关键字的一些基础语句而已。只要把这几个关键字掌握了,SQL的大部分内容其实就已经展开了。

2.数据查询语句

SQL作为面向数据库表的基础语言,用户群体具有多样性,从技术底层往业务层走,往往会有DBA,数据开发,数据分析,产品经理等这些用户角色。不同用户群体对SQL的侧重点是有差异的,但无论是哪一个群体,基本都绕不开数据查询语句,是一块必要内容。

简单查询: 能写一个简单查询语句,其实就已展开了和数据库表的对话过程。不管是哪种SQL的拓展语言,简单查询里的语法基本都还是一致的。比如用*代表全量查询,用distinct去重,用top和limit对数据条数做基本限制,以及用as对原表字段名进行替换更新等。

过滤查询:在简单查询的基础上,添加一些约束条件,也就是过滤查询。比如你可以用关键字where查看其中某天的数据,用between或者in来限制一个范围,用like或者relike来做正则匹配,也可以用and或者or这两个通配符对这些约束条件进行排列组合。

排序查询: 排序查询可以细分为两个场景,一个是在查询内部的排序,即根据某个字段的属性值进行表内部分区,对分区进行排序查询后输出,可以用row_number的形式来实现;另一个是把整个查询当做一个整体,对结果表进行排序查询后输出,用order by来实现即可。

3.数据聚合与连接

前面讲数据查询语句,不管怎么查询,其实并不影响原生的表结构,即原来的表是按照什么逻辑写的数据,查询结果里的数据也是基于这种逻辑,只是筛选了局部数据而已。但数据聚合与连接就不一样了,聚合会在纵向上改变原生表结构,连接则在横向上拓展了表结构。

数据聚合: 要对一张表做数据聚合,其实理解了两个概念即可,维度和指标。维度是你要基于哪些字段来做聚合,指标是在这个维度之上,你想用什么汇总函数生成哪些指标。数据聚合的关键字是group by,维度里的属性值仍来自于原生表,指标则是新生成的汇总值。

数据连接: 对两张表或者N张表做连接,是SQL里面非常重要的一个内容,也是最容易埋坑的一个坑点。尽管数据连接只涉及四种方式,七个语法,但其仍然是绝大部分SQL脚本的核心内容。选择合适的可靠的数据连接方式,应该是一个SQL运动员的基本功了。

4.函数应用

函数库,其实就像是一个数据处理与分析的百宝箱,收藏着各种场景下需要用到的车轮子。对函数库的熟悉和掌握,可以较好地提升工作效率,也让计算脚本显得轻量而简洁。毕竟站在通用的函数的肩膀上,很多统计逻辑是可以一步到位的,不需要沉迷于山重水复的自主构造里。以下参考TDW的函数库分类,将日常所用的函数细分为几个子类别。

4.1数学函数: SQL里的数学函数主要和数值处理有关,有取值函数和变换函数等。取值函数包括round四舍五入,abs取绝对值,ceil向上取整等,主要用于对具体数值的细节调整;变换函数则会改变该字段的数据分布形态,如正弦sin,余弦cos,或者开根号sqrt等。

4.2聚合函数: 在数据聚合中,选择了具体字段作为聚合维度后,之后便是应用各种聚合函数得到汇总值的过程。其中有简单聚合函数如count计数,sum求和,avg求平均,也可以基于分布特征,max/min取极值,std取标准差,variance取方差,另外若在聚合过程中涉及分区处理的话,也有rank,first/last_value,row_number等函数可以应用。

4.3时间和日期函数: 对时间数据的处理,同样也是SQL里的一个重要课题,主要细分为时间的加减,取值和转换这么三类。其中时间加减里,又涉及不同的时间维度,比如按日维度有date_diff,date_add,date_sub等,按月维度有month_between,add_months等。时间取值函数则是在一个详细的时间戳里,取出自己想要的部分,如year,month,day,hour等。时间转换函数则是时间形式的切换,如日期格式,格林尼治时间戳格式等。

4.4文本处理: 数据类型可以粗糙地分为数值数据和文本数据,对于文本数据的处理,也有很多对应的函数。其中有一些简单取值函数,如通过length和size获得字段长度和数组大小,通过upper和lower可以切换大小写;字符串的切割与拼接,由浅入深有split,substr,concat,wm_concat等;最后正则表达式也是文本处理中一个特别重要的模块。

4.5其它函数: 除了以上所盘点的一些通用函数外,其实在日常工作中会有很多垂直的业务场景,在这些特定场景下也有一些特定的函数逻辑。比如涉及数组结构拆分与重构时,可以应用later view函数;涉及字段编码时,也有加密与解析函数;除此之外,还有逻辑函数,转换函数等多种特殊函数,在特定的场景下,这些函数其实也是必要的。

5.具体开发环境的注意点

和其它众多语言一样,SQL的编写也不能脱离其自身的开发环境,不同的数据库形态,不同的IDE,都会有一些差异点和新特性。同样的一段脚本,在A环境下可能跑的疾速如飞,在B环境下却可能满屏报错,可以拿出来讨论的,往往只是一些通用的逻辑和思考。除此之外,具体开发环境里的注意事项,一些细节的加速点,则是要在具体环境里去发现和探索。

第二部分 业务实践篇

一种语言,一个函数库,就像是厨房里的各种厨具,它们可能或优良或劣质,但本质上还是一些标准化的组件。基于这些公共的组件,如何去烹制自己的美食,以及在烹制过程中的心得和思考,则属于业务实践的篇章。在业务实践的过程中,不管怎样去规范化,标准化,每个人输出的脚本内容,难免还是要带上私人的特质的,这同样也是一个需要修习的部分。

以下通过三个问题点,来引出笔者在实际工作中的一些反思。其中如何尽量地少给未来挖坑,介绍了一些反面的案例,这些反面的细节在积累之后,容易引起整个系统的不稳定性;如何健康地做数据规划,则是从一个创建者的身份,展开几点数据规划的思考;但不管做了多么缜密和丰富的准备,随着时间推移,总还是会出现变化的,所以在破旧与立新之间要找到平衡点。这些反思是基于工作实践的层面,难免会有幼稚和纰漏之处,还请读者轻拍。

1.如何尽量地少给未来挖坑

不要起一些奇奇怪怪的名字: SQL里的数据库表,就像是其它语言里的对象,往往是数量极大的,并在时间的推进,业务的发展中,基数会持续放大。所以对于数据库名,数据表名,字段名,尤其是一些主键,索引的命名,务必要有一套相对统一的规范。缺乏规范的约束时,你无法想象人的想象力会多么发散,这些奇奇怪怪的名字,最终会把人深深地困住的。

不要并行维护多个版本的数据: 因为业务的拓展,数据背后的口径可能有所变更,基于旧有的数据报表,简单修改后出一份新数据,是一种成本较低的实现方式。但最好不要并行维护多个版本的数据,当版本超过3个的时候,维护的成本是直线拉升的。所以当要做数据变更时,一方面可以降低变更的频率,另一方面尽量在原有报表里修改,并替换掉原有口径。

不要在单个脚本里写过多内容: 统计逻辑的实现,就像是传统工业里的不同工序,这个过程里存在两种极端。一种是把一个逻辑在横向/纵向细分为太多的工序,部署过多计算任务,形成很大冗余;另一种是完全打包在一个大脚本里,这种情况也不利于问题定位和中间数据处理。所以不要在单个脚本里写过多内容,可以将它拆分进最优数量的计算任务中。

要有一些基本的约束条件: 做一些事情时,不仅要立足于眼下的问题点,也要考虑一下未来可能发生的变化。就像是造一座桥,修一条路,总要考虑极限情况下的压力。很多的数据异常,往往是在业务变化时,旧有的逻辑不能适应当前的场景。所以在一开始写脚本时,要考虑一下未来的场景,有一些基本的约束条件,这样会让所部署的任务会有较好的稳定性。

要采用尽量简洁的写法: 能够一步到位的统计逻辑,就采用尽量简洁的写法,千万不要去绕圈子。尤其是一些核心脚本,是要在不同环节,不同阶段的同事之间传承的,很多人并不了解当时的业务背景和需求逻辑,如果写法太绕圈子的话,最终就把大家一起绕进去了。

2.如何健康地做数据规划

数据规划是一个层级比较中等的概念,往下一层,做需求开发时,往往只聚焦于特定的需求点,并不涉及其它内容;往上一层,做数据工程的话,又是基于整个部门,整个产品形态的框架搭建。但数据规划更多是应对一个相对独立的业务场景,所做的规划与设计。

一个不够好的数据规划,可能会引发后续的诸多问题点,比如:

痛点1: PM提出要在视图上扩展一个细分字段,觉得很简单。我也觉得很简单,但就是更改不了,因为这个字段在数据源处理中就舍弃了,无法从上一层数据表中获得。

痛点2: 想要重跑一个时间范围内的数据,但这张表不是分区表,无法并行处理;想要剔除某个日期内的数据,但不同表中时间格式不一致,导致处理结果有差漏等。

痛点3: 同样的一条统计链路,部分为了保障每日推送而独立出去,部分为了特性统计而独立出去,由此产生了众多的细分链路,此后的变更也要在不同链路之间同步处理。

以上列出的三个痛点,分别对应了原始信息的保留,技术实现的最优路径,以及计算任务的细分问题等,不过也只是数据规划需要思考的其中一部分问题点。在不同的业务场景下里,可以有不同的数据规划思路。粗糙地讲,可以分为数据基础层和业务细分层独立处理。

数据基础层: 做一个数据规划,首先应该要考虑数据本身,在数据基础层里,应暂时抛开具体的业务细节,以数据为重。这个时候应在处理中尽量地保留原始信息,同时要对数据源做好质量检验,第一道防线,往往是很重要的一道防线。原始信息要完整,数据质量要合格,任务部署要轻便,这些是数据基础层的一些目标,也是后续工作的一个前提。

业务细分层: 数据基础要独立而完整,面向数据本身;业务细分层则可以去细分实现,面向业务细节。基于不同的业务目标,可以从源表中筛选不同的内容,用于应对特定的场景。这样的数据+业务层级,形成了一种“总-分”结构,是数据规划的其中一种实现方式。

3.如何在破旧与立新之间寻找平衡点

很多的工作,都是基于当下的场景,即使做了详尽的规划和思考,也不可能应对未来的所有问题。当业务逐渐地深入发展时,很多的内容也需要做一些同步处理,小的层面上是一些数据表和视图的表更,大的层面上可能涉及计算平台的迁移,视图系统的重建等。

破旧与立新,往往是一个长期存在的问题点。不需要每天都进行自我“革命”,但改良和优化,则是一个长期过程。在这个长期过程里,我们需要在破旧与立新之间寻找到平衡点。

以上是笔者在TDW体系下写SQL的一些实践和思考,欢迎评论区留言讨论~

作者:cooperyjli,腾讯 CDG 数据分析师


文章标签:

本文链接:『转载请注明出处』