在整个招聘过程中，我很高兴为我的团队遇到了许多才华横溢的候选人。由于我们的工作涉及处理复杂的数据集，因此衡量每个候选人寻找智能解决方案的能力对我来说很重要。我询问了他们使用 窗口函数的经验来评估他们的熟练程度。虽然大多数人都知道这些功能，但很少有人能够有效地使用它们。 SQL 尽管窗口函数已经存在了近 20 年，但许多 SQL 开发人员仍然发现它们很难掌握。即使是经验丰富的开发人员也经常从 复制并粘贴代码，而没有真正理解它的作用。这篇文章是为了帮助您！我将以易于理解的方式解释窗口函数，并提供示例来向您展示它们在现实世界中的工作原理。 StackOverflow 它们是很棒的分析工具，可以解决很多问题。例如，假设您需要计算一组共享公共属性（例如客户端 ID）的行。这就是窗口函数派上用场的地方！它们的工作方式类似于聚合函数，但让您保持每行的唯一性，而不是将它们分组在一起。另外，窗口函数的结果在输出选择中显示为额外字段。当您制作分析报告、计算移动平均值和运行总计或计算不同的归因模型时，这非常有用。 你听说过窗口函数吗？ 欢迎来到 SQL 和窗口函数的世界！如果您刚刚开始，那么您来对地方了。本文适合初学者，解释清晰，没有复杂的术语或高级概念。即使您对该主题完全陌生，也可以轻松地跟上。 内容概述 与窗口函数一起使用的函数类型 聚合函数 排名功能 价值函数 聚合窗函数 要点 与窗口函数一起使用的函数类型 窗口函数可应用于一组行（所谓的窗口）上的三种主要函数类型：聚合函数、排名函数和值函数。在下图中，您可以看到属于每个类别的不同函数的名称。  聚合函数 它们对数据组执行数学运算，产生单个累积值。它们用于计算各种聚合，包括平均值、总行数、最大值或最小值或每个窗口或分区内的总和。  SUM：将列中的所有值相加 COUNT：计算列中值的数量，不包括 NULL 值 AVG：求列中的平均值 MAX：标识列中的最高值 MIN：标识列中的最小值 排名功能 它们用于为分区中的每一行赋予排名或顺序。这是通过评估特定标准来完成的，例如分配序列号或基于特定值进行排名。  ROW_NUMBER：为分区中的每个新记录分配一个连续的排名号 RANK：指定结果集中每行的排名。在这种情况下，如果系统检测到相同的值，它将为它们分配相同的排名并跳过下一个值。  DENSE_RANK：为结果集分区内的每一行分配一个排名。与 RANK 函数不同，该函数返回相同值的排名，而不跳过任何后续值。  NTILE：允许我们确定当前行属于哪个组。组数在括号中给出。 价值函数 这些可以轻松比较组中不同行之间的值，还可以让您将值与该组中的第一个或最后一个值进行比较。这意味着您可以轻松地在窗口中的不同行之间移动，并查看窗口开头或结尾的值。  LAG 或 LEAD：访问前一行或后一行的数据，而无需执行自连接操作。当解决需要将同一结果集或分区中的一行与另一行进行比较的问题（例如计算随时间变化的差异）时，这些函数特别有用。  FIRST_VALUE 或 LAST_VALUE：从定义的窗口或分区中检索第一个或最后一个值。当您想要计算特定时间段内的差异时，这些函数特别有用。 为了开始使用窗口函数，让我们创建一个假设的“工资”表并用数据填充它。 表创建：   create table salary ( employee_id smallint, employee_name varchar(10), department varchar(20), city varchar(20), salary real ) 填表：   insert into salary values ( 1 ,'Tony' ,'R&D', 'New York', 3000); insert into salary values ( 2 ,'James' ,'Project management', 'London', 4000); insert into salary values ( 3 ,'Dina' ,'Engineering', 'Tokyo', 5000); insert into salary values ( 4 ,'Annie' ,'Security', 'London', 3000); insert into salary values ( 5 ,'Tom' ,'R&D', 'New York', 3500); insert into salary values ( 6 ,'Stan' ,'Project management', 'New York', 4200); insert into salary values ( 7 ,'Jessa' ,'Sales', 'London', 5300); insert into salary values ( 8 ,'Ronnie' ,'R&D', 'Tokyo', 2900); insert into salary values ( 9 ,'Andrew' ,'Engineering', 'New York', 1500); insert into salary values (10,'Dean' ,'Sales', 'Tokyo', 3700) 让我们检查一下“工资”表是否已成功填写：     select * from salary 下一个查询将显示我们表中员工的姓名和工资：     select employee_name, salary from salary 计算工资总和、平均工资、最高、最低和行数是聚合函数的一些常见用例：  应用聚合函数时，工资将被聚合并显示在一行中。 但是，如果我们想显示“工资”表中员工的姓名和工资，并在第三列中显示所有工资的总和，该怎么办？所有行的该值必须相同。 这是使用窗口函数的好机会！     select employee_name, salary, sum(salary) over() as sum_salary from salary 让我们仔细看看计算 每一行中工资总和的窗口函数。 sum(salary) over()   表达式定义函数在其上运行的窗口或行集。在我们的示例中，窗口是整个表，这意味着该函数将应用于所有行。 over()   表达式仅在与 之前请求的函数配对时才有效。 over() over() 例如，   ，其中 是聚合函数。整个表达式 是一个聚合窗口函数。 sum(salary) over() sum() sum(salary) over() 正如我之前所说，所有应用窗口函数的函数都可以分为三组：聚合函数、排名函数和价值函数。 聚合函数  、   、   、   、  与 表达式一起构成一组聚合窗口函数。 sum() count() avg() min() max() over() 在本文中，我们将集中讨论这种特定类型的窗口函数。 聚合窗函数 回到例子！ 让我们询问员工的姓名；他们的工资；所有工资的总和；平均工资、最高工资和最低工资；员工人数。     select employee_name, salary, sum(salary) over(), avg(salary) over(), max(salary) over(), min(salary) over(), count(*) over() from salary 现在我们更清楚了什么是窗口函数，让我们探讨一下它们在您的工作中有用的一些情况。     select employee_name, salary, sum(salary)over(), salary/sum(salary)over() as share from salary order by salary/sum(salary)over() desc 我们在第四栏中计算了每项工资占工资预算总额的百分比。 Jessa的工资几乎占整个工资预算的15%。 请注意，我们还将计算 百分比的公式放在 之后的排序中。窗口函数不仅可以在输出 中找到，还可以在排序 中找到。 salary/sum(salary)over() order by select order by 另一个例子：让我们将工资与公司的平均工资进行比较。     select employee_name, salary, avg(salary)over(), salary-avg(salary)over() as diff_salary from salary order by salary-avg(salary)over() 我们可以看到，Andrew 的工资比平均水平低 2110，而 Jessa 的工资比平均水平高 1690。 让我们请求三列：员工姓名、部门和工资。此外，我们将按部门对它们进行排序。     select employee_name, department, salary from salary order by department 现在我们将请求相同的三列，再加上一列包含所有员工的工资总和。您已经知道这可以通过窗口函数来完成。     select employee_name, department, salary, sum(salary)over() from salary order by department 但是，如果我们想要请求的不是所有工资的总和，而是每个部门的工资总和，如最后一列所示：  我们可以通过将 参数添加到 表达式来实现： partition by over()   select employee_name, department, salary, sum(salary)over(), sum(salary)over(partition by department) from salary order by department   Partition by 允许我们将窗口函数应用于列部分，而不是所有行（整个窗口）。 是不是看起来很简单的分组？为了计算每个部门的工资总和，我们将按部门（窗口函数俚语中的部分）进行分组并计算金额：     select department, sum(salary) from salary group by department 从本质上讲，分组和 的区别在于，  每组返回一行，而 虽然函数的结果与 的聚合函数的结果相同，但它为所有行提供基于组的聚合函数。 partition by group by partition by group by 让我们回到窗口函数：     select employee_name, department, salary, sum(salary)over(partition by department), salary/sum(salary)over(partition by department) as shape from salary order by department 利用窗口函数，特别  ，我们可以从部门工资总和中计算出每个员工的工资份额。或者，例如，将工资与部门的平均工资进行比较。 partition by 要点 回顾一下： 窗口函数对与当前行以某种方式相关的一组行执行计算， 应用窗口函数的主要函数类型是聚合函数、排名函数和价值函数， 要使用窗口函数，您需要应用 子句来定义查询结果集中的窗口（一组行）。然后窗口函数计算窗口中每一行的值， over() 要指定要执行聚合的列，需要在 子句中添加 子句。  有点类似于分组，但返回应用了聚合函数的所有行，而不是每组一行。 over() partition by Partition by 现在就这样了！在接下来的几篇文章中，我将通过适合初学者的简单示例来探索更高级的 SQL 概念，敬请期待！

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

The code in this story is for educational purposes. The readers are solely responsible for whatever they build with it.

Read My Stories

該音頻是用故事的原始語言製作的！

了解 SQL 窗口函数及其功能的初学者指南

About Author

註釋

標籤

这篇文章刊登在

Related Stories

Telegram：加密岛通往大陆的桥梁

Floki 的 Valhalla 成为印度环斯里兰卡赛事联合赞助商

加密货币增长：创建有效的用户角色

从论坛到信息流：社交媒体算法如何塑造数字互动

Telegram：加密岛通往大陆的桥梁

Floki 的 Valhalla 成为印度环斯里兰卡赛事联合赞助商

加密货币增长：创建有效的用户角色

从论坛到信息流：社交媒体算法如何塑造数字互动

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps