海量數據——這就是我們現在正在處理的問題。除了這一主要挑戰外，各種來源的復雜性也隨之而來。在這樣的環境中，SQL仍然是英雄，是我們不可或缺的工具，用于從這片數據海洋中導航和提取有價值的見解。
在 SQL 提供的許多強大功能中，窗口函數特別值得注意。這些函數支持跨表行集進行復雜的計算，使它們對于高級數據分析和改變我們與數據交互的方式至關重要。
在今天的文章中，我們將剖析和理解 SQL 中窗口函數的概念。我們將探討何時使用窗口函數，以及如何在我們的 SQL 查詢中有效地實現它們。在本指南結束時，您將對窗口函數的強大功能和靈活性有更深入的理解，并且您將配備實際示例來提高您的數據分析技能。
— — 準備好了嗎—— —？走吧 🚀 — — —— —
這個窗口功能是怎么回事？
每個數據愛好者，無論他們的經驗水平如何，都可能聽說過甚至使用過窗口函數。這些強大的工具在每門 SQL 課程中無處不在，在任何與數據打交道的人的日常生活中都是必不可少的。
讓我們在 Google 上做一個快速搜索......過了一會兒，也許是電視上的廣告，我們發現窗口功能是：
一個函數，它使用一行或多行的值來返回每行的值 - 基于維基百科
或
一種強大的工具，通過提供一種計算跨行子集（稱為“窗口”）的值的方法，使數據分析師和開發人員能夠對數據集執行完整的分析計算，該方法基于 Analytics Vidhya
有人說過語法嗎？
是的，沒錯。這個超級強大的工具帶有一些技巧，例如特定的語法。
— 不要害怕，到本文結束時，一切都會被馴服
正如我們在上圖中看到的，窗口函數的語法可以分為四個部分：
聚合/函數：這是通過放置聚合（如 、）或特定窗口函數（如 、、、或 ）來啟動操作的地方。還有一些，但這些是最常用的（或者至少是我使用最多的😁）AVGSUMLAG()LEAD()ROW_NUMBER()RANK()DENSE_RANK()
OVER：此關鍵字專門用于向 IDE “宣布”您將使用窗口函數。這就像在說，“我要在這里做點什么，你最好為復雜的事情做好準備。
PARTITION BY：此子句將結果劃分為分區或窗口，在此之上我們將應用我們在開始時建立的聚合或函數。寫完這部分之后，你還需要開發你所基于的分區字段。這不與排名函數一起使用。
訂購 BY：在某些情況下這可能是可選的，但了解它的作用是值得的。這用于對每個分區內的行進行排序，并且在使用排名函數（如 、 和 ）時顯示了它的有用性。RANK()DENSE_RANK()ROW_NUMBER()
許多窗口功能以實現您的目標
在上一節中，我們討論了窗口函數語法。我們提到了一些函數，這些函數永遠不會獨立于窗口函數語法工作。
有些稱為排名函數，因為它們為分區中的每一行返回一個排名值;其他是時間序列窗口函數。
排名功能：
等級（） — 為結果集的分區中的每一行分配一個等級，其中值相等的行將獲得相同的等級。
DENSE_RANK（） —類似于 **RANK（），**但具有連續的秩值。相等的值獲得相同的等級;下一個排名值是下一個連續的整數。
NTILE（） — 它將結果集劃分為相等的組，并為每行分配一個數字以指示它屬于哪個組。
ROW_NUMBER（） — 為結果集的分區內的行分配一個唯一的順序整數，每個分區中的第一行從 1 開始。
時間序列函數：
永恒的問題：為什么......
我們為什么要這樣做？我們為什么要學習這一點？我們為什么要使用它？？
這些是我們對很多事情提出的常見問題，SQL 中的窗口函數也不例外。為了了解窗口函數可以為您節省時間和精力的情況，讓我們來探討一下：
為什么以及何時我們應該使用窗口函數？
讓我們從WHEN開始。我們什么時候使用窗口函數？好吧，每當我們需要時：
不要把 “為什么”留在外面。 當情況需要時，我們為什么要使用窗口函數？
因為窗口功能：
維護行級別詳細信息 — 允許我們在不折疊數據的情況下執行計算，使您能夠在保持原始數據完好無損的情況下跨多行執行計算。
簡化復雜查詢— 該工具幫助我們簡化最復雜的查詢，使它們更易于閱讀、編寫，最重要的是，易于維護。
提高性能 — 通常可以帶來更好的性能，尤其是對于大型數據集，因為它們由 SQL 引擎優化。
啟用高級分析 — 允許我們運行高級分析操作，例如運行總計、移動平均線等。
對數據進行分區以進行詳細分析 — 根據特定條件對數據進行分區，從而在組內進行詳細分析，而無需聚合整個數據集。
支持時間序列和更改檢測 — 為訪問上一行或下一行值提供內置支持，這對于時間序列數據和更改檢測非常有用。
真實世界的用例
作為一名在銀行業工作的數據工程師，我收到了一個請求，要求識別合同“階段”發生變化的記錄，并捕獲此變化的日期。
說起來容易做起來難，對吧？不完全是，因為窗口函數幫助我完成了請求并快速交付了結果。
假設我們有兩個表：
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;
現在，讓我們深入了解一下解釋：
首先，我創建了主語句，在該語句中我獲取了標識符（貸款標識符）、水平和date_ref等信息（這 2 個是貸款的實際水平和當前階段的參考日期）：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
之后，我使用函數來獲取貸款的前一級和每筆貸款的上一個參考日期。使用 ，我根據標識符 將數據集劃分為小分區，并按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 查詢，從而實現高效且有洞察力的數據工作。
該文章在 2024/7/24 23:33:41 編輯過
400 186 1886
