SQL开发总结

0、SQL基本用法

　　SQL是一个非常容易上手的语言，它类似自然语言，用直观的逻辑去查询数据。最简单的SQL就是select * from table where *** order by ***，非常符合直觉。

　　稍微复杂一点，就是两张表去进行关联，此时只要确定两张表关联的字段就可以了。一般是拿一张事实表关联一张维度表（外键关系），比如订单表关联商品表，比如持仓表关联产品表。此时只要区分左关联、右关联、全关联即可。在实战使用中，几乎90%的场景都是左关联。全关联一般在对比数据时使用到，比如比较两个新旧表是否完全一致。

　　其他常见的写法有聚合group by，这个也是非常符合直觉，通过将数据按照指定的维度进行聚合即可统计各类指标，比如统计每日订单量，每个部门人数。统计指标的函数常用的有sum/count/max/avg/方差/标准差等。除了针对数值类字段进行统计外，另外常见的操作是针对文本类型进行聚合，比如展示各部门员工名单列表。

　　进阶一点的SQL就是分析函数或者窗口函数了，这是对数据进行分组处理。常用的有分组排序row_number、分组求和、分组前后记录Lead/Lag, 分区最大最小记录First/Last。用好分析函数可以简洁高效的实现很多功能，比如排名问题，topN问题，数据空值填充，计算连续天数，时间区间比率问题。高级SQL还有pivot语法，可以快速实现行列转换。

　　另外就是标量子查询，当一个子查询介于select与from之间，这种子查询就叫标量子查询。标量子查询类似于函数，在结果集返回的每一行增加一个函数列（如果子查询返回多行，则会报错）。标量子查询会使SQL比较简洁，但只在特定的SQL方言中被支持。

　　最后大部分SQL方言都支持子查询，通过子查询嵌套可以使得代码结构更清晰，编写大量的子查询也导致了SQL代码变得超长。

1、SQL执行顺序

(8)SELECT (9)DISTINCT (11)<Top Num> <select list>
(1)FROM [left_table]
(3)<join_type> JOIN <right_table>
(2)ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH <CUBE | RollUP>
(7)HAVING <having_condition>
(10)ORDER BY <order_by_list>

2、SQL方言

　　市面上常用的关系型数据库多大数十种，常用的有Oracle/SQL Server/Mysql/PostgreSQL等，不同的数据库支持的SQL基本大体相同，但是也有一些自己的特色，如Oracle中connect by函数，SQL Sever中for xml 函数，这些特色的SQL方言需要SQL开发者额外的花时间与精力去单独掌握与学习。

　　近些年，国产数据库也逐渐开始流行起来，为了支持信创切换，国产数据库通常声称会选择尽量兼容某一个版本的传统关系型数据库，当然实际情况如何还要看具体实操。除了传统关系型数据库，在大数据系统中，为了便于传统数据分析人员便捷使用，往往也开发了SQL接口，如HiveSQL、Spark SQL、Impala SQL等，这些SQL方言与传统数据库SQL方言差异都不大，花一定的时间即可掌握。

3、实战SQL开发

　　在实际项目中，不同业务背景的项目复杂性差异很大。在电商等业务中，逻辑相对简单，数据围绕着订单、商品、用户、优惠券、渠道、支付、退单、库存等数十个关键实体进行展开，SQL相对会比较简单直接；而在金融领域，仅仅是主题域就多达十几个，如主体、品种、估值、渠道、资产、财富、资管、自营、风控等，每个主题域下又有数十个业务实体。这种情况下，就会导致SQL代码会非常的复杂，实战中一个存储过程可能超过上万行代码。

　　除了业务逻辑本身的复杂性以外，数仓项目中，源系统数据质量往往参差不齐，为了在上层报表展示时数据能统一，SQL开发不得不进行兜底。80%的数据正常的场景只要20%的代码就可以处理完成，而剩下80%的代码则用来处理那些20%异常的数据场景。因此，对数据质量及数据源业务的了解是写好SQL的关键。

　　除了要考虑业务逻辑，当数据量达到一定的规模时，SQL运行的性能会明显的下降。此时会需要使用一些SQL优化技巧，常用的有增加索引、避免数据重分布等。由于SQL优化依赖实际的物理数据库，因此针对不同的数据库有不同的技巧，常见的技巧可以总结成以下口诀（AI生成）：列少过滤早，索引覆盖好；小表驱动大，深翻游标跑；分区要裁剪，物化降频扰；统计常更新，慢查 Top 消！

4、基于AI大模型的SQL开发

　　如今AI进行代码辅助开发非常流行，针对单一明确功能的场景，只要将逻辑描述清楚，大模型生成的代码质量就很高，只要稍作修改，即可直接发布到生产环境中进行使用。

　　那么在SQL领域，是否也可行呢？从目前使用下来，AI开发SQL代码的效率还比较低，大量的代码还得人去手工编写。因为SQL开发的前提是对数据的了解，由于数据安全问题，目前还不能给AI大模型赋予较大的数据权限，因此AI无法进行完整的数据探查，这就影响了AI发挥其威力。不过现在已经出现了一些将AI与数据进行结合的工具，如ChatBI，ChatDB，在业务逻辑清晰，数据质量高的前提下，已经能生成一个不错的SQL。

　　除此之外，功能逻辑复杂，但是业务逻辑却很简单的SQL，也可以考虑尝试用AI生成，如财务领域基于FIFO先入先出算法计算资产的损益。该场景中只需要交易数据即可进行计算处理指标，源数据质量高且逻辑算法清晰，AI应该可以快速生成SQL代码达到期望的效果。