墨墨导读:在数据科学家岗位的面试中,窗口函数(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个章节:
在第一章节,我将通过常规聚合函数介绍一些基本的窗口函数,例如AVG, MIN/MAX, COUNT, SUM,来使你初步了解一些概念。
在第二章节,我将专注于排序相关的函数,例如ROW_NUMBER, RANK和RANK_DENSE。这些函数在分组生成排序方面极为有用,在进行数据科学家面试之前,你应该熟练使用它们。
在第三章节,我将讨论如何用NTILE函数生成统计信息(例如:百分位数,四分位数,中位数等),这是数据科学家的常见任务。
在最后一个章节,让我们专注于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/)中找到。如果你想提