為什么以及何時我們應(yīng)該使用窗口函數(shù)？

讓我們從WHEN開始。我們什么時候使用窗口函數(shù)？好吧，每當(dāng)我們需要時：

• 根據(jù)特定條件計算數(shù)據(jù)子集的運行總計、排名、平均值或其他計算

• 比較當(dāng)前行和上一行/下一行值

不要把 “為什么”留在外面。 當(dāng)情況需要時，我們?yōu)槭裁匆褂么翱诤瘮?shù)？

因為窗口功能：

• 維護行級別詳細信息 — 允許我們在不折疊數(shù)據(jù)的情況下執(zhí)行計算，使您能夠在保持原始數(shù)據(jù)完好無損的情況下跨多行執(zhí)行計算。

• 簡化復(fù)雜查詢— 該工具幫助我們簡化最復(fù)雜的查詢，使它們更易于閱讀、編寫，最重要的是，易于維護。

• 提高性能 — 通常可以帶來更好的性能，尤其是對于大型數(shù)據(jù)集，因為它們由 SQL 引擎優(yōu)化。

• 啟用高級分析 — 允許我們運行高級分析操作，例如運行總計、移動平均線等。

• 對數(shù)據(jù)進行分區(qū)以進行詳細分析 — 根據(jù)特定條件對數(shù)據(jù)進行分區(qū)，從而在組內(nèi)進行詳細分析，而無需聚合整個數(shù)據(jù)集。

• 支持時間序列和更改檢測 — 為訪問上一行或下一行值提供內(nèi)置支持，這對于時間序列數(shù)據(jù)和更改檢測非常有用。

真實世界的用例

作為一名在銀行業(yè)工作的數(shù)據(jù)工程師，我收到了一個請求，要求識別合同“階段”發(fā)生變化的記錄，并捕獲此變化的日期。

說起來容易做起來難，對吧？不完全是，因為窗口函數(shù)幫助我完成了請求并快速交付了結(jié)果。

假設(shè)我們有兩個表：

source.data_records

以及temp.data_records：

我們需要創(chuàng)建一個表，在其中保存以下信息：

• 標識符

• 標識符的當(dāng)前級別

• 當(dāng)前階段的基準日期

• 標識符的上一級

• 上一個參考日期

• 標識符更改其級別的日期

該表是根據(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)在，讓我們深入了解一下解釋：

1. 首先，我創(chuàng)建了主語句，在該語句中我獲取了標識符（貸款標識符）、水平和date_ref等信息（這 2 個是貸款的實際水平和當(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ù)來獲取貸款的前一級和每筆貸款的上一個參考日期。使用 ，我根據(jù)標識符 將數(shù)據(jù)集劃分為小分區(qū)，并按date_ref對每個分區(qū)內(nèi)的記錄進行排序.

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ū)中的每條記錄分配一個等級：DENSE_RANK()

dense_rank() over (partition by fct.identifier order by fct.date_ref desc) as ranks

此代碼將返回以下結(jié)果：

更進一步，我們編寫另一條語句，以便能夠?qū)ι弦粋€結(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

并且僅獲取每個標識符的最新記錄（ranks=1 對應(yīng)于說明前面的最新記錄），即當(dāng)前級別與上一級別（級別 <> previous_level）不同的記錄，并確保更改日期有效且與上一個參考日期不同。根據(jù)這些過濾器，我們將結(jié)果插入到新的表tmp_change_level_date中（我們使用著名的 CREATE TABLE table_name AS 語法創(chuàng)建的表）：

從這些結(jié)果中，我們看到：

• 對于標識符 2：2023 年 3 月 15 日，級別從 A 更改為 C。

• 對于標識符 3：2023 年 2 月 20 日，級別從 B 更改為 A。

結(jié)論

SQL 窗口函數(shù)簡化了復(fù)雜的數(shù)據(jù)分析并增強了性能。本文介紹了它們的基礎(chǔ)知識、語法、常見用法（如排名和時間序列分析）以及一個真實示例。掌握這些功能有助于簡化 SQL 查詢，從而實現(xiàn)高效且有洞察力的數(shù)據(jù)工作。