创意电子

标题: 一文搞懂 SQL：基础知识和业务实践总结 [打印本页]

作者: 腾讯技术工程 时间: 2020-6-23 17:34
标题: 一文搞懂 SQL：基础知识和业务实践总结

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 数据分析师

作者: 星尘184790416 时间: 2020-6-23 21:43
转发了

作者: 老顽童之周伯通 时间: 2020-6-23 21:54
转发了

作者: 窝窝头50 时间: 2020-6-23 22:00
转发了

作者: 未来海阔天空8 时间: 2020-6-23 23:53
转发了

作者: 麦火柴的小鸡 时间: 2020-6-24 03:36
转发了

作者: 笑死人的湾湾 时间: 2020-6-24 06:17
转发了

作者: 一切皆模型模型即一切 时间: 2020-6-24 06:45
转发了

作者: 小小的三毛 时间: 2020-6-24 06:59
转发了

作者: 追星星的猫 时间: 2020-6-24 08:25
转发了

作者: FW146568251 时间: 2020-6-24 11:51
转发了

作者: bIueager 时间: 2020-6-24 18:57
转发了

作者: 孙ae 时间: 2020-6-25 09:54
转发了

作者: 仙侣步惊云 时间: 2020-6-26 07:41
就沒见到一条sql语句，空对空

作者: 果园村大苹果 时间: 2020-6-26 09:42
转发了

欢迎光临创意电子 (https://wxcydz.cc/)