让你的 SQL 从优秀走向卓越：第 4 部分
神译局是36氪旗下编译团队，关注科技、商业、职场、生活等领域，重点介绍国外的新技术、新观点、新风向。
编者按：做开发的多少都要懂点SQL。但是只会点皮毛跟精通的差别还是很大的。有很多事情如果用SQL处理的话，会让你的后端开发工作事半功倍。那有哪些堪称神奇的SQL技巧呢？Taylor Brownlow用4篇系列文章总结了四个方面的SQL使用技巧，相信你会用得上的。此为第四部分，原文发表在Medium上，标题是：Take Your SQL from Good to Great: Part 4。
这是最有价值的SQL技巧系列文章的第 4 部分。本系列包括：
- 第 4 部分：Window函数
这是系列文章的最后一篇！
介绍
Window函数是 SQL 用户最喜爱的功能之一。这种函数非常强大，有了它，嵌套子查询和烦人的交叉连接就不需要了。
然而，由于这个玩意儿的实现极其的啰嗦，关键字又没有什么帮助，很多人都不敢蹚Window函数这浑水。在深入研究一些我最喜欢的Windows函数用例之前，本文先简要介绍一下Windows函数的工作原理。
本notebook的所有的例子都是用BigQuery语法开发的，但不同的SQL语言之间几乎没有根本性的区别。完整代码你可以在此处查看和编辑。
什么是Window函数
“在 SQL 当中，window函数或分析函数是用一到多行里面的值来针对每一行返回一个值的函数。”
——BigQuery
理解windows函数的关键是这种函数会针对指定表的每一行计算一个值。我们可以跟聚合函数对比一下，后者会跨指定的不同组计算汇总值。
我们可以用《神秘的古庙》（Legends of the Hidden Temple）的数据来具体看看函数是怎么用的。为了找出每支团队获取成绩最好的赛事，我们可以看到怎么用一个简单的聚合函数 MAX 来返回每支队伍的最高分，但这并没有告诉我们他们拿到最好成绩的是哪场比赛。
如果只能用聚合函数做到这一点的话，我们得将初始结果连接回原始表，以找出 Red Jaguars 是在哪场比赛中拿到了 98 分。这可有点麻烦。
相比之下，如果用窗口函数的话，很快就可以找出每支队伍拿到最好成绩是哪场比赛。下面我们就来拆解一下看它是怎么实现的。
一个window函数的剖析
窗口函数乍一看似乎很复杂，但如果拆解来看的话，其实没你想象的那么复杂。
基本上如果要创建一个窗口函数的话，你得做两件事：
- 定义返回的内容
- 定义你的窗口
定义返回的内容
一般来说，窗口函数可以分为3种类型：
- 导航函数：返回给定特定位置条件的值（比方说first_value、lag、lead）
- 编号函数：根据每行在指定窗口所处的位置为其分配一个数字（比方说 rank、row_number ）
- 分析函数：对一组值（比方说 sum、avg 、max）执行计算
支持什么样的功能要取决于数据库。你可以看参考部分的指南查看看具体数据库支持什么样的功能：
定义窗口
在你的窗口函数里面，OVER 关键字之后的一切就代表你的窗口定义。
OVER 子句可以有以下一些组成部分：
- PARTITION BY：每个窗口由什么组成
- ORDER BY：对每个窗口里面的行如何进行排序
- ROWS BETWEEN X PRECEEDING AND Y FOLLOWING：使用每个窗口里面的哪些行
掌握了这些基础知识之后，我们再来看看可以用这些东西来做什么……
Window函数作弊码
1.结果排名
类似“买得最多的前 5 件商品是什么”这样的问题很简单，但如果要你“按地区”提供的话，查询可能会变得更加复杂了。
能够对特定分组的数据的结果进行排名，为我们开启了各种新的分析可能性，比方说查找用户购买的第二件东西，或者对用户第一次和第二次访问你网站的使用模式进行比较等。
例子：如果我想找到这三位流行歌星的热门曲目：德雷克（Drake）、泰勒·斯威夫特（T Swift） 以及波兹·马龙（Post Malone），我可以利用排名函数，按艺术家进行分组，再按播放量排序来计算每首曲目的排名。
然后我们很容易就可以对比每位艺术家的前 3 大金曲的情况，可以看出德雷克和波兹·马龙排名第三的歌都要比斯威夫特排名第一的金曲播放量要高。
2. 计算两个事件之间的时间间隔
数据在结构上往往是一行代表一个事件。但这会导致想要查找事件之间的时间间隔变得棘手，因为这牵涉到要查找 2 行而不是 2 列之间的时间间隔。
不过，这正是窗口函数的用武之地！尤其是是lag和lead。
例子：我想找出Spotify两次拿到排名第一的热门歌曲的时间间隔最大的艺术家是谁。为此，我得：
- 找出每天排名第一的热门金曲。
- 用lag找出艺术家拿到#1时上一次拿到#1是什么时候。
- 然后再计算这两次的时间间隔。
3.“移动”计算
在进行任何类型的时间序列分析时，你通常要检查在很长一段时间内发生的一系列事件。这些数据往往“噪声”很大，因为其间每天都会有各种变化，很容易只见树木不见森林。
移动计算（如移动平均）是将时间序列数据汇总到一定程度的好办法，这种计算可让你确定某些高级模式，但又不会失去跟底层细节的联系。
例子：假设我想以30 天的移动平均天数按天查看每日的Spotify播放情况。这让我可以很快看出流媒体播放情况是不是随着时间的推移而增加，以及有多少明显的异常值等等。
窗口函数的注意事项及最佳实践
⚠窗口函数不能放在查询的 WHERE 或 HAVING 部分，所以你得用 CTE 或子查询，根据窗口函数的结果进行过滤。至于为什么不能把窗口函数添加到查询的WHERE和HAVING部分，可参阅本系列第 3 部分里面的图表。
同一个查询把窗口函数和典型的聚合函数结合到一起用不是不可能，但会引起混乱。如果可以的话，请尽量避免。
如果你想对窗口函数查询的结果进行“分组”，请使用DISTINCT。
窗口函数的结果一定要检查一下，因为如果数据的结构不符合你的预期的话，更有可能会得到错误的结果。
参考：
MySQL 的窗口函数(文档)
PostgreSQL 的窗口函数 (文档)
Redshift 的窗口函数 (文档)
译者：boxi。
本文来自翻译, 如若转载请注明出处。