海量數(shù)據(jù)——這就是我們現(xiàn)在正在處理的問題。除了這一主要挑戰(zhàn)外，各種來源的復(fù)雜性也隨之而來。在這樣的環(huán)境中，SQL仍然是英雄，是我們不可或缺的工具，用于從這片數(shù)據(jù)海洋中導(dǎo)航和提取有價(jià)值的見解。
在 SQL 提供的許多強(qiáng)大功能中，窗口函數(shù)特別值得注意。這些函數(shù)支持跨表行集進(jìn)行復(fù)雜的計(jì)算，使它們對于高級數(shù)據(jù)分析和改變我們與數(shù)據(jù)交互的方式至關(guān)重要。
在今天的文章中，我們將剖析和理解 SQL 中窗口函數(shù)的概念。我們將探討何時(shí)使用窗口函數(shù)，以及如何在我們的 SQL 查詢中有效地實(shí)現(xiàn)它們。在本指南結(jié)束時(shí)，您將對窗口函數(shù)的強(qiáng)大功能和靈活性有更深入的理解，并且您將配備實(shí)際示例來提高您的數(shù)據(jù)分析技能。
— — 準(zhǔn)備好了嗎—— —？走吧 🚀 — — —— —
這個(gè)窗口功能是怎么回事？
每個(gè)數(shù)據(jù)愛好者，無論他們的經(jīng)驗(yàn)水平如何，都可能聽說過甚至使用過窗口函數(shù)。這些強(qiáng)大的工具在每門 SQL 課程中無處不在，在任何與數(shù)據(jù)打交道的人的日常生活中都是必不可少的。
讓我們在 Google 上做一個(gè)快速搜索......過了一會兒，也許是電視上的廣告，我們發(fā)現(xiàn)窗口功能是：
一個(gè)函數(shù)，它使用一行或多行的值來返回每行的值 - 基于維基百科
或
一種強(qiáng)大的工具，通過提供一種計(jì)算跨行子集（稱為“窗口”）的值的方法，使數(shù)據(jù)分析師和開發(fā)人員能夠?qū)?shù)據(jù)集執(zhí)行完整的分析計(jì)算，該方法基于 Analytics Vidhya
有人說過語法嗎？
是的，沒錯(cuò)。這個(gè)超級強(qiáng)大的工具帶有一些技巧，例如特定的語法。
— 不要害怕，到本文結(jié)束時(shí)，一切都會被馴服
正如我們在上圖中看到的，窗口函數(shù)的語法可以分為四個(gè)部分：
聚合/函數(shù)：這是通過放置聚合（如 、）或特定窗口函數(shù)（如 、、、或 ）來啟動操作的地方。還有一些，但這些是最常用的（或者至少是我使用最多的😁）AVGSUMLAG()LEAD()ROW_NUMBER()RANK()DENSE_RANK()
OVER：此關(guān)鍵字專門用于向 IDE “宣布”您將使用窗口函數(shù)。這就像在說，“我要在這里做點(diǎn)什么，你最好為復(fù)雜的事情做好準(zhǔn)備。
PARTITION BY：此子句將結(jié)果劃分為分區(qū)或窗口，在此之上我們將應(yīng)用我們在開始時(shí)建立的聚合或函數(shù)。寫完這部分之后，你還需要開發(fā)你所基于的分區(qū)字段。這不與排名函數(shù)一起使用。
訂購 BY：在某些情況下這可能是可選的，但了解它的作用是值得的。這用于對每個(gè)分區(qū)內(nèi)的行進(jìn)行排序，并且在使用排名函數(shù)（如 、 和 ）時(shí)顯示了它的有用性。RANK()DENSE_RANK()ROW_NUMBER()
許多窗口功能以實(shí)現(xiàn)您的目標(biāo)
在上一節(jié)中，我們討論了窗口函數(shù)語法。我們提到了一些函數(shù)，這些函數(shù)永遠(yuǎn)不會獨(dú)立于窗口函數(shù)語法工作。
有些稱為排名函數(shù)，因?yàn)樗鼈優(yōu)榉謪^(qū)中的每一行返回一個(gè)排名值;其他是時(shí)間序列窗口函數(shù)。
排名功能：
等級（） — 為結(jié)果集的分區(qū)中的每一行分配一個(gè)等級，其中值相等的行將獲得相同的等級。
DENSE_RANK（） —類似于 **RANK（），**但具有連續(xù)的秩值。相等的值獲得相同的等級;下一個(gè)排名值是下一個(gè)連續(xù)的整數(shù)。
NTILE（） — 它將結(jié)果集劃分為相等的組，并為每行分配一個(gè)數(shù)字以指示它屬于哪個(gè)組。
ROW_NUMBER（） — 為結(jié)果集的分區(qū)內(nèi)的行分配一個(gè)唯一的順序整數(shù)，每個(gè)分區(qū)中的第一行從 1 開始。
時(shí)間序列函數(shù)：
永恒的問題：為什么......
我們?yōu)槭裁匆@樣做？我們?yōu)槭裁匆獙W(xué)習(xí)這一點(diǎn)？我們?yōu)槭裁匆褂盟浚?/em>
這些是我們對很多事情提出的常見問題，SQL 中的窗口函數(shù)也不例外。為了了解窗口函數(shù)可以為您節(jié)省時(shí)間和精力的情況，讓我們來探討一下：
為什么以及何時(shí)我們應(yīng)該使用窗口函數(shù)？
讓我們從WHEN開始。我們什么時(shí)候使用窗口函數(shù)？好吧，每當(dāng)我們需要時(shí)：
不要把 “為什么”留在外面。 當(dāng)情況需要時(shí)，我們?yōu)槭裁匆褂么翱诤瘮?shù)？
因?yàn)榇翱诠δ埽?/p>
維護(hù)行級別詳細(xì)信息 — 允許我們在不折疊數(shù)據(jù)的情況下執(zhí)行計(jì)算，使您能夠在保持原始數(shù)據(jù)完好無損的情況下跨多行執(zhí)行計(jì)算。
簡化復(fù)雜查詢— 該工具幫助我們簡化最復(fù)雜的查詢，使它們更易于閱讀、編寫，最重要的是，易于維護(hù)。
提高性能 — 通常可以帶來更好的性能，尤其是對于大型數(shù)據(jù)集，因?yàn)樗鼈冇?SQL 引擎優(yōu)化。
啟用高級分析 — 允許我們運(yùn)行高級分析操作，例如運(yùn)行總計(jì)、移動平均線等。
對數(shù)據(jù)進(jìn)行分區(qū)以進(jìn)行詳細(xì)分析 — 根據(jù)特定條件對數(shù)據(jù)進(jìn)行分區(qū)，從而在組內(nèi)進(jìn)行詳細(xì)分析，而無需聚合整個(gè)數(shù)據(jù)集。
支持時(shí)間序列和更改檢測 — 為訪問上一行或下一行值提供內(nèi)置支持，這對于時(shí)間序列數(shù)據(jù)和更改檢測非常有用。
真實(shí)世界的用例
作為一名在銀行業(yè)工作的數(shù)據(jù)工程師，我收到了一個(gè)請求，要求識別合同“階段”發(fā)生變化的記錄，并捕獲此變化的日期。
說起來容易做起來難，對吧？不完全是，因?yàn)榇翱诤瘮?shù)幫助我完成了請求并快速交付了結(jié)果。
假設(shè)我們有兩個(gè)表：
source.data_records
以及temp.data_records：
我們需要?jiǎng)?chuàng)建一個(gè)表，在其中保存以下信息：
標(biāo)識符
標(biāo)識符的當(dāng)前級別
當(dāng)前階段的基準(zhǔn)日期
標(biāo)識符的上一級
上一個(gè)參考日期
標(biāo)識符更改其級別的日期
該表是根據(jù)以下代碼創(chuàng)建的：
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;
現(xiàn)在，讓我們深入了解一下解釋：
首先，我創(chuàng)建了主語句，在該語句中我獲取了標(biāo)識符（貸款標(biāo)識符）、水平和date_ref等信息（這 2 個(gè)是貸款的實(shí)際水平和當(dāng)前階段的參考日期）：SELECT
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
之后，我使用函數(shù)來獲取貸款的前一級和每筆貸款的上一個(gè)參考日期。使用 ，我根據(jù)標(biāo)識符 將數(shù)據(jù)集劃分為小分區(qū)，并按date_ref對每個(gè)分區(qū)內(nèi)的記錄進(jìn)行排序.
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
并使用函數(shù)為分區(qū)中的每條記錄分配一個(gè)等級：DENSE_RANK()
dense_rank() over (partition by fct.identifier order by fct.date_ref desc) as ranks
此代碼將返回以下結(jié)果：
更進(jìn)一步，我們編寫另一條語句，以便能夠?qū)ι弦粋€(gè)結(jié)果（上表）應(yīng)用一些過濾器：SELECT
select distinct * from (
---- the previous select as a subquery ----
) x
where ranks = 1
and level <> previous_level
and previous_date <> change_level_date
并且僅獲取每個(gè)標(biāo)識符的最新記錄（ranks=1 對應(yīng)于說明前面的最新記錄），即當(dāng)前級別與上一級別（級別 <> previous_level）不同的記錄，并確保更改日期有效且與上一個(gè)參考日期不同。根據(jù)這些過濾器，我們將結(jié)果插入到新的表tmp_change_level_date中（我們使用著名的 CREATE TABLE table_name AS 語法創(chuàng)建的表）：
從這些結(jié)果中，我們看到：
結(jié)論
SQL 窗口函數(shù)簡化了復(fù)雜的數(shù)據(jù)分析并增強(qiáng)了性能。本文介紹了它們的基礎(chǔ)知識、語法、常見用法（如排名和時(shí)間序列分析）以及一個(gè)真實(shí)示例。掌握這些功能有助于簡化 SQL 查詢，從而實(shí)現(xiàn)高效且有洞察力的數(shù)據(jù)工作。
該文章在 2024/7/24 23:33:41 編輯過
400 186 1886
