海量数据——这就是我们现在正在处理的问题。除了这一主要挑战外,各种来源的复杂性也随之而来。在这样的环境中,SQL仍然是英雄,是我们不可或缺的工具,用于从这片数据海洋中导航和提取有价值的见解。
在 SQL 提供的许多强大功能中,窗口函数特别值得注意。这些函数支持跨表行集进行复杂的计算,使它们对于高级数据分析和改变我们与数据交互的方式至关重要。
在今天的文章中,我们将剖析和理解 SQL 中窗口函数的概念。我们将探讨何时使用窗口函数,以及如何在我们的 SQL 查询中有效地实现它们。在本指南结束时,您将对窗口函数的强大功能和灵活性有更深入的理解,并且您将配备实际示例来提高您的数据分析技能。
— — 准备好了吗—— —?走吧 🚀 — — —— —
每个数据爱好者,无论他们的经验水平如何,都可能听说过甚至使用过窗口函数。这些强大的工具在每门 SQL 课程中无处不在,在任何与数据打交道的人的日常生活中都是必不可少的。
让我们在 Google 上做一个快速搜索......过了一会儿,也许是电视上的广告,我们发现窗口功能是:
一个函数,它使用一行或多行的值来返回每行的值 - 基于维基百科
或
一种强大的工具,通过提供一种计算跨行子集(称为“窗口”)的值的方法,使数据分析师和开发人员能够对数据集执行完整的分析计算,该方法基于 Analytics Vidhya
是的,没错。这个超级强大的工具带有一些技巧,例如特定的语法。
— 不要害怕,到本文结束时,一切都会被驯服
正如我们在上图中看到的,窗口函数的语法可以分为四个部分:
在上一节中,我们讨论了窗口函数语法。我们提到了一些函数,这些函数永远不会独立于窗口函数语法工作。
有些称为排名函数,因为它们为分区中的每一行返回一个排名值;其他是时间序列窗口函数。
排名功能:
时间序列函数:
我们为什么要这样做?我们为什么要学习这一点?我们为什么要使用它??
这些是我们对很多事情提出的常见问题,SQL 中的窗口函数也不例外。为了了解窗口函数可以为您节省时间和精力的情况,让我们来探讨一下:
为什么以及何时我们应该使用窗口函数?
让我们从WHEN开始。我们什么时候使用窗口函数?好吧,每当我们需要时:
不要把 “为什么”留在外面。 当情况需要时,我们为什么要使用窗口函数?
因为窗口功能:
作为一名在银行业工作的数据工程师,我收到了一个请求,要求识别合同“阶段”发生变化的记录,并捕获此变化的日期。
说起来容易做起来难,对吧?不完全是,因为窗口函数帮助我完成了请求并快速交付了结果。
假设我们有两个表:
source.data_records
以及temp.data_records:
我们需要创建一个表,在其中保存以下信息:
该表是根据以下代码创建的:
create table tmp_change_level_date as
(
select distinct * from (
select
fct.identifier, fct.level, fct.date_ref,
lag(fct.level) over (partition by fct.identifier order by fct.date_ref) as previous_level,
lag(fct.date_ref) over (partition by fct.identifier order by fct.date_ref) as previous_date,
case
when lag(fct.level) over (partition by fct.identifier order by fct.date_ref) is not null then fct.date_ref
else NULL
end as change_level_date,
dense_rank() over (partition by fct.identifier order by fct.date_ref desc) as ranks
from source.data_records fct join temp.data_records TFCT
on fct.identifier = TFCT.identifier
where TFCT.amount <> 0 and TFCT.account in (select account_code from accounts_list)
) x
where ranks = 1
and level <> previous_level
and previous_date <> change_level_date
)
commit;
现在,让我们深入了解一下解释:
select
fct.identifier, fct.level, fct.date_ref,
lag(fct.level) over (partition by fct.identifier order by fct.date_ref) as previous_level,
lag(fct.date_ref) over (partition by fct.identifier order by fct.date_ref) as previous_date,
case
when lag(fct.level) over (partition by fct.identifier order by fct.date_ref) is not null then fct.date_ref
else NULL
end as change_level_date,
dense_rank() over (partition by fct.identifier order by fct.date_ref desc) as ranks
from source.data_records fct join temp.data_records TFCT
on fct.identifier = TFCT.identifier
where TFCT.amount <> 0 and TFCT.account in (select account_code from accounts_list)
) x
之后,我使用函数来获取贷款的前一级和每笔贷款的上一个参考日期。使用 ,我根据标识符 将数据集划分为小分区,并按date_ref对每个分区内的记录进行排序.
lag(fct.level) over (partition by fct.identifier order by fct.date_ref) as previous_level,
lag(fct.date_ref) over (partition by fct.identifier order by fct.date_ref) as previous_date
并使用函数为分区中的每条记录分配一个等级:DENSE_RANK()
dense_rank() over (partition by fct.identifier order by fct.date_ref desc) as ranks
此代码将返回以下结果:
更进一步,我们编写另一条语句,以便能够对上一个结果(上表)应用一些过滤器:SELECT
select distinct * from (
---- the previous select as a subquery ----
) x
where ranks = 1
and level <> previous_level
and previous_date <> change_level_date
并且仅获取每个标识符的最新记录(ranks=1 对应于说明前面的最新记录),即当前级别与上一级别(级别 <> previous_level)不同的记录,并确保更改日期有效且与上一个参考日期不同。根据这些过滤器,我们将结果插入到新的表tmp_change_level_date中(我们使用著名的 CREATE TABLE table_name AS 语法创建的表):
从这些结果中,我们看到:
SQL 窗口函数简化了复杂的数据分析并增强了性能。本文介绍了它们的基础知识、语法、常见用法(如排名和时间序列分析)以及一个真实示例。掌握这些功能有助于简化 SQL 查询,从而实现高效且有洞察力的数据工作。