2021年数据科学家面试：4个基本SQL窗口函数介绍以及示例

墨墨导读：在数据科学家岗位的面试中，窗口函数（WINDOW function）是SQL函数家族中经常会被问到的主题。在本文中，我会根据面试的问题，问题模式和解决问题的基本策略向你展示一些典型的窗口函数，并提供一些示例的分步解决方案。

原文出处：https://sqlpad.io/tutorial/4-essential-sql-window-functions-and-examples-for-a-data-scientist-interview-in-2021
作者：Leon
译者：ACDU翻译组（@姚雷）
校对：ACDU翻译组（@帽子菌 @Finn）

介绍

在数据科学家岗位的面试中，窗口函数（WINDOW function） 是SQL函数家族中经常会被问到的主题。用窗口函数写一个正确的SQL查询对每个候选人来讲都很有挑战性，尤其是对那些SQL的初学者。

在本文中，我会根据面试的问题，问题模式和解决问题的基本策略向你展示一些典型的窗口函数，并提供一些示例的分步解决方案。

关于作者：Leon是硅谷FAANG（Facebook, Apple, Amazon, Netflix, 谷歌）公司的一位数据科学和机器学习领域的主管。在担任现在的职务之前，他在EdTech公司Chegg负责机器学习组织，并在amazon.com担任研究科学家，负责构建大规模机器学习系统。

大纲

我将把这篇文章分为4个章节：

1. 在第一章节，我将通过常规聚合函数介绍一些基本的窗口函数，例如AVG, MIN/MAX, COUNT, SUM，来使你初步了解一些概念。

2. 在第二章节，我将专注于排序相关的函数，例如ROW_NUMBER, RANK和RANK_DENSE。这些函数在分组生成排序方面极为有用，在进行数据科学家面试之前，你应该熟练使用它们。

3. 在第三章节，我将讨论如何用NTILE函数生成统计信息（例如：百分位数，四分位数，中位数等），这是数据科学家的常见任务。

4. 在最后一个章节，让我们专注于LAG和LEAD，如果你面试的角色需要处理时序数据，这是两个超级重要的的函数。

让我们开始吧！

章节1：基于常规聚合函数（AVG，MIN/MAX，COUNT，SUM）的窗口函数

窗口函数是一系列函数，这些函数在和当前行有关的多行数据上执行运算。

这相当于聚合函数所做的运算，但和常规聚合函数不同的是，窗口函数不会将分组的多行数据合并成一行 – 这些行都保留了自己的标识。

在后台，窗口函数实际上处理的不仅仅是查询结果的当前行。

本文所有的示例都基于 movie DVD rental business data 中的数据。在第一个示例中我们的目的是在相同的MPAA分级下，比较每个电影DVD的替换成本和平均成本。

SELECT
    title,
    rating,
    replacement_cost,
    AVG(replacement_cost) OVER(PARTITION BY rating) AS avg_cost
FROM film;

对不在美国的读者解释一下，MPAA分级是一个电影分级系统，可根据电影的内容确定电影是否适合某些观众。比如，G表示适合所有年龄段的观众，同时PG-13表示包含不适合13岁以下儿童观看的内容。

在这里AVG函数没有GROUP BY 子句，但是SQL引擎如何知道哪些行需要计算平均值呢？答案是通过OVER() 工具里的PARTITION BY 子句，我们将根据一个特定的分级来计算平均值。

在最终的输出里，每一行包含相同分级下的平均成本，你可以分析这些数据，比如将替换成本除以平均成本，以对比相同评级下每部电影的相对支出。

https://sqlpad.io/playground/

本文中所有的表都可以在 SQLPad’s online SQL playground（https://sqlpad.io/playground/）中找到。如果你想提