SQL窗口函数踩坑笔记

谁也不能否认，编写SQL语句是一个数据分析师的重要(甚至是最重要)的技能之一，而Python/R之类，有时候承担的只是胶水语言的功能(比方说最近有个出Excel日报的工作，Python就很好地替代了大量人工劳动)。SQL是数据分析的生产工具，因为企业级的生产数据几乎都存放在数据库或数据仓库中(企业中一般是在线存储的)，而对它们进行操作的几乎都属于SQL的某个分支。但SQL这个东西，初学容易、入门简单、精通困难，实在是事业成功的拦路虎。

我这么说的理由有如下几点：

• 各种数据库支持的SQL语句有区别，标准不统一

• 个人难以建立复杂的应用场景， 导致SQL技能在实习和工作中才能提升

• 一些SQL技巧的使用方法难以使用小型可重复性例子进行展示

• SQL非编程语言，一些编程语言几行代码能解决的任务SQL往往需要更长的代码

首先，SQL的分支实在太多了，而同一种SQL又因为不同的版本而不同。比如MySQL 8.0之前的版本是没有窗口函数的。第二点的意思是，如果你没有在公司的数据分析岗位写过SQL的话，接触到工业级应用场景的机会几乎为0。因为自己本地能搭建的数据库几乎都是玩具数据，关联逻辑简单、数据量小。而LeeCode等平台的SQL面试题类的代码往往更强调准确性，真实的应用场景则往往会非常多地考虑性能要求。第三点，不同于Python/R，如果需要复现某种SQL技巧，往往需要自己造数据。这个过程就非常复杂而且不方便。不像Python或R可以利用自带的数据集复现帮助文档中的代码。最后一点，编程语言的逻辑几乎都是 输出=函数(输入)，而SQL中没有这个等号，这种思维方式和编程语言差别还是挺大的。

我的SQL在工作前一直很烂。直到工作后我才慢慢上手，甚至能写一些复杂的逻辑了。但现在我仍不敢说自己在写SQL上都多大能耐，因为我目前只大量使用了Apache Doris(支持MySQL协议但和纯MySQL还是有区别)这一种数据库。此外我SQL差劲的原因大概是觉得写SQL真的很无聊吧。

窗口函数

SQL对于分组的情况默认会聚合结果并且减少行数，那么如何实现不聚合却分组的计算呢？比方说分组排序这种任务？这里就会用到窗口函数了。窗口函数被视作SQL的高级功能，甚至和OLAP(Online Analytical Processing)这个名称关联上了哈哈，这里稍微总结下窗口函数吧。在参加工作之前之前我完全没了解过这块内容，现在对之前踩过的坑做个总结。

比方说如下的题目：

表结构如下：
Student(Sid,Sname,Sage,Ssex) 学生表 sid为key
Course(Cid,Cname,Tid) 课程表 cid 为key
SC(Sid,Cid,score) 成绩表 (sid, cid) 为key
Teacher(Tid,Tname) 教师表 tid为key

查询选修“王五”老师所授课程的学生中，成绩最高的学生学号、姓名及其成绩

我最初给出的答案是这样：

select Sid, Sname,score
from (
  select sc.Sid as Sid