SQL視窗函式進階應用

視窗函式是 SQL 中強大的工具，允許在不分組的情況下對相關資料進行計算。不同於聚合函式，視窗函式能保留每一列資料，同時計算出根據視窗的聚合值或排名等資訊。這使得視窗函式在處理排名、移動平均、資料變化追蹤等場景時非常有效。理解視窗函式的 OVER() 子句、PARTITION BY、ORDER BY 以及 RANGE 或 ROWS 等關鍵字，對於活用視窗函式至關重要。

探討視窗函式在 SQL 中的應用

在現代 SQL 與分析中，視窗函式扮演著至關重要的角色。與常規的聚合函式不同，視窗函式允許我們在不將多行資料合併為單一行輸出的情況下，檢視其他行的資料。這種特性使得視窗函式非常適合用於排名、計算獨立聚合、計算執行總計以及存取前後行資料等場景。

視窗函式的基本概念

視窗函式透過 OVER() 子句來定義資料視窗，並將函式應用於該視窗內的資料。視窗的大小和分割槽可以獨立定義，從而提供了靈活的資料分析能力。

使用視窗函式的好處

排名: 可以根據特定的條件對資料進行排名。
計算獨立聚合: 在不同的分割槽內進行聚合運算。
計算執行總計: 對資料進行累計計算。
存取前後行資料: 透過 lag 或 lead 函式存取前一行或後一行的資料。

例項解析：使用 `dense_rank()` 進行排名

假設我們需要檢索每個系統中電力生產量最高的 top 3 時間點。一個簡單的方法是使用 ORDER BY 和 LIMIT，但這種方法可能無法滿足需求，因為它只傳回電力生產量最高的值，而不一定是 top 3 的不同值。

WITH ranked_readings AS (
  SELECT *,
         dense_rank() OVER (PARTITION BY system_id ORDER BY power DESC) AS rnk
  FROM readings
)
SELECT *
FROM ranked_readings
WHERE rnk <= 3;

程式碼解析：

CTE（公用表表達式）: 使用 WITH 子句定義了一個名為 ranked_readings 的臨時結果集。
dense_rank() 函式: 對每個 system_id 分割槽內的資料根據 power 進行降序排名，且排名是連續的，不會因為相同值而跳過排名。
OVER() 子句: 定義了視窗函式的操作範圍，包括分割槽和排序。
外層查詢: 從 ranked_readings 中篩選出 rnk 小於或等於 3 的記錄，即每個系統的 top 3 電力生產量記錄。

圖表解析

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title SQL視窗函式進階應用

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

圖表說明：

此圖示呈現了使用 dense_rank() 進行排名的流程。首先讀取資料，然後根據 system_id 分割槽並對 power 進行降序排序，接著使用 dense_rank() 進行排名，最後篩選出排名前 3 的記錄。

進階資料彙總與分析

在處理資料時，我們經常需要對資料進行彙總和分析。SQL 中的視窗函式（Window Function）提供了一種強大的工具，可以在不改變原始資料列數的情況下，對資料進行計算和分析。

視窗函式的基本概念

視窗函式可以對一組相關的資料列進行計算，並傳回一個值。與聚合函式不同，視窗函式不會將多行資料聚合成一行。視窗函式的基本語法如下：

SELECT *,
       dense_rank() OVER (
           PARTITION BY system_id
           ORDER BY power DESC
       ) AS rnk
FROM readings;

在這個例子中，dense_rank() 是一個視窗函式，用於計算每個 system_id 分組內的排名。

資料分割槽

當我們使用視窗函式時，可以透過 PARTITION BY 子句將資料分成多個分割槽。在每個分割槽內，視窗函式會獨立計算。

WITH ranked_readings AS (
    SELECT *,
           dense_rank() OVER (
               PARTITION BY system_id
               ORDER BY power DESC
           ) AS rnk
    FROM readings
)
SELECT * FROM ranked_readings WHERE rnk <= 2
ORDER BY system_id, rnk ASC;

內容解密：

PARTITION BY system_id 將資料分成多個分割槽，每個分割槽對應一個 system_id。
dense_rank() 計算每個分割槽內的排名，按照 power 降序排列。
外層查詢篩選出排名前 2 的資料列。

計算聚合值

除了排名函式外，我們還可以使用聚合函式，如 avg、sum、max 和 min，在視窗函式中計算聚合值。

SELECT *,
       avg(kWh) OVER (
           PARTITION BY system_id
       ) AS average_per_system
FROM v_power_per_day;

內容解密：

avg(kWh) 計算每個 system_id 分組內的平均 kWh 值。
PARTITION BY system_id 將資料分成多個分割槽，每個分割槽對應一個 system_id。
結果中每個資料列都會包含對應 system_id 的平均 kWh 值。

框架（Framing）

框架指定了一組相對於當前資料列的資料範圍，可以用於計算移動平均值等。

SELECT system_id,
       day,
       kWh,
       avg(kWh) OVER (
           PARTITION BY system_id
           ORDER BY day ASC
           RANGE BETWEEN INTERVAL '3 days' PRECEDING
           AND INTERVAL '3 days' FOLLOWING
       ) AS "kWh 7-day moving average"
FROM v_power_per_day
ORDER BY system_id, day;

內容解密：

PARTITION BY system_id 將資料分成多個分割槽，每個分割槽對應一個 system_id。
ORDER BY day ASC 指定了資料列的排序順序。
RANGE BETWEEN INTERVAL '3 days' PRECEDING AND INTERVAL '3 days' FOLLOWING 定義了一個 7 天的框架，用於計算移動平均值。
avg(kWh) 計算框架內的平均 kWh 值。

視窗函式的高階應用：量化分析與資料變化追蹤

在處理時間序列資料或需要進行複雜分析的場景中，視窗函式（Window Functions）提供了強大的工具。本篇文章將探討視窗函式的命名視窗（Named Windows）功能，以及如何利用 lag() 和 lead() 函式來追蹤資料的變化。

命名視窗的定義與應用

在進行複雜的資料分析時，我們常常需要在多個聚合函式中使用相同的視窗定義。重複定義視窗不僅繁瑣，也容易導致錯誤。DuckDB 的命名視窗功能允許我們在 WINDOW 子句中定義一個視窗，並在多個聚合函式中參照它。

使用命名視窗進行量化分析

以下是一個實際的例子，展示如何利用命名視窗來計算某個時間範圍內的最小值、最大值和分位數：

SELECT 
    system_id,
    day,
    min(kWh) OVER seven_days AS "7-day min",
    quantile(kWh, [0.25, 0.5, 0.75]) OVER seven_days AS "kWh 7-day quartile",
    max(kWh) OVER seven_days AS "7-day max"
FROM 
    v_power_per_day
WINDOW 
    seven_days AS (
        PARTITION BY system_id, month(day)
        ORDER BY day ASC
        RANGE BETWEEN INTERVAL 3 Days PRECEDING AND INTERVAL 3 Days FOLLOWING
    )
ORDER BY 
    system_id, day;

內容解密：

WINDOW 子句：定義了一個名為 seven_days 的視窗，該視窗按照 system_id 和 day 的月份進行分割槽，並按 day 升序排序。視窗範圍是當前日期前後3天。
min、max 和 quantile 函式：分別計算了7天內的最小值、最大值和分位數。這些函式都參照了 seven_days 這個命名視窗，避免了重複定義視窗的麻煩。
quantile 函式：計算了 kWh 的四分位數，能夠更好地捕捉資料的分佈情況。

使用 `lag()` 和 `lead()` 追蹤資料變化

在某些場景下，我們需要比較同一分割槽內不同行之間的資料變化，例如計算價格的變化量。這時，lag() 和 lead() 函式就派上用場了。

使用 `lag()` 計算價格變化

以下是一個例子，展示如何利用 lag() 函式計算價格的變化量：

SELECT 
    valid_from,
    value,
    lag(value) OVER validity AS "Previous value",
    value - lag(value, 1, value) OVER validity AS Change
FROM 
    prices
WINDOW 
    validity AS (ORDER BY valid_from);

內容解密：

lag(value)：取得當前行之前的行的 value 值。如果當前行是分割槽的第一行，則傳回 NULL。
value - lag(value, 1, value)：計算當前行的 value 與前一行的 value 之差。如果前一行不存在，則使用當前行的 value 作為預設值，避免結果為 NULL。
WINDOW 子句：定義了一個名為 validity 的視窗，按照 valid_from 欄位排序。

進階資料彙總與分析

在處理資料時，彙總和分析是至關重要的步驟。彙總可以幫助我們瞭解資料的整體趨勢，而分析則能夠提供更深入的見解。本章節將介紹如何使用SQL進行進階的資料彙總與分析。

使用視窗函式進行資料分析

視窗函式是一種強大的工具，可以用來計算資料的匯總值，同時保留原始資料的詳細資訊。例如，我們可以使用lag函式來計算價格的變化。

SELECT 
  valid_from, 
  value, 
  lag(value, 1) OVER (ORDER BY valid_from) AS previous_value, 
  value - lag(value, 1) OVER (ORDER BY valid_from) AS change
FROM prices
WHERE date_part('year', valid_from) = 2019
ORDER BY valid_from;

內容解密：

lag(value, 1) OVER (ORDER BY valid_from)用於取得前一筆記錄的value值，並根據valid_from欄位排序。
value - lag(value, 1) OVER (ORDER BY valid_from)計算當前記錄與前一筆記錄的value差值，即價格變化。

計算總變化量

如果我們想要計算2019年的總價格變化量，需要使用CTE（Common Table Expression）。

WITH changes AS (
  SELECT value - lag(value, 1, value) OVER (ORDER BY valid_from) AS v
  FROM prices
  WHERE date_part('year', valid_from) = 2019
)
SELECT sum(changes.v) AS total_change
FROM changes;

內容解密：

CTE changes用於計算每筆記錄的價格變化量。
sum(changes.v)計算所有價格變化量的總和，即總變化量。

在WHERE子句之外進行條件篩選

有時候，我們需要根據彙總值或視窗函式的結果進行篩選。這時候就需要使用HAVING或QUALIFY子句。

使用HAVING子句

HAVING子句用於對彙總值進行篩選。例如，我們想要找出所有生產量超過900 kWh的日子。

SELECT 
  system_id, 
  date_trunc('day', read_on) AS day, 
  round(sum(power) / 4 / 1000, 2) AS kWh
FROM readings
GROUP BY ALL
HAVING kWh >= 900
ORDER BY kWh DESC;

內容解密：

GROUP BY ALL表示根據所有欄位進行分組。
HAVING kWh >= 900用於篩選出生產量超過900 kWh的日子。

使用QUALIFY子句

QUALIFY子句用於對視窗函式的結果進行篩選。例如，我們想要找出七天移動平均生產量高於875 kWh的日子。

SELECT 
  dense_rank() OVER (ORDER BY power DESC) AS rnk, 
  *
FROM readings
QUALIFY rnk <= 3;

內容解密：

dense_rank() OVER (ORDER BY power DESC)用於計算每筆記錄的排名。
QUALIFY rnk <= 3用於篩選出排名前三的記錄。

4.6 在WHERE子句外進行條件篩選與過濾

SELECT 
    system_id,
    day,
    avg(kWh) OVER (
        PARTITION BY system_id
        ORDER BY day ASC
        RANGE BETWEEN INTERVAL 3 Days PRECEDING
        AND INTERVAL 3 Days FOLLOWING
    ) AS "kWh 7-day moving average"
FROM 
    v_power_per_day
QUALIFY 
    "kWh 7-day moving average" > 875
ORDER BY 
    system_id, day;

內容解密：

此查詢計算每個system_id的每日電力生產（kWh）的7天移動平均值。
使用OVER子句對每個system_id進行分割槽，並按日期排序。
RANGE BETWEEN INTERVAL 3 Days PRECEDING AND INTERVAL 3 Days FOLLOWING確保計算的是前後3天的資料，即7天視窗。
QUALIFY子句用於過濾結果，只保留7天移動平均值大於875的記錄。
最終結果按system_id和day排序。

利用範例資料，我們找到了三個日期，它們代表了西半球光伏發電的典型“好日子”： ┌───────────┬────────────┬──────────────────────────┐ │ system_id │ day │ kWh 7-day moving average │ │ int32 │ date │ double │ ├───────────┼────────────┼──────────────────────────┤ │ 34 │ 2020-05-21 │ 887.4628571428572 │ │ 34 │ 2020-05-22 │ 884.7342857142858 │ │ 34 │ 2020-06-09 │ 882.4628571428572 │ └───────────┴────────────┴──────────────────────────┘

4.6.3 使用FILTER子句

有時，您需要計算聚合值、平均值或計數，但又希望排除某些行。您可能會考慮在WHERE子句中新增條件，但對於複雜查詢，您可能需要保留那些行來計算其他欄位。例如，假設您有時會獲得不良讀數，這些讀數顯示為負值。您希望計算感測器的總讀數和平均讀數。如果在WHERE子句中過濾掉不良讀數，您就無法計算總讀數。但如果只是簡單地平均所有讀數，您就會包含一些不良的負值。為瞭解決這類別問題，您可以使用FILTER表示式作為聚合的一部分。

回到4.1節，我們曾經處理過不一致的感測器讀數，實際上遇到了將NULL值拉入平均值的問題，這很可能不是我們想要的結果。與其將NULL值限制在零，不如直接將它們從平均值中過濾掉，如下所示：

INSERT INTO readings(system_id, read_on, power)
SELECT 
    any_value(SiteId),
    time_bucket(
        INTERVAL '15 Minutes',
        CAST("Date-Time" AS timestamp)
    ) AS read_on,
    coalesce(avg(ac_power)
        FILTER (
            ac_power IS NOT NULL AND
            ac_power >= 0
        ), 0)
FROM 
    read_csv_auto(
        'https://developer.nrel.gov/api/pvdaq/v3/' ||
        'data_file?api_key=DEMO_KEY&system_id=10&year=2019'
    )
GROUP BY 
    read_on
ORDER BY 
    read_on
ON CONFLICT DO NOTHING;

內容解密：

使用FILTER子句過濾掉ac_power為NULL或小於0的值，以確保平均值的準確性。
coalesce函式用於處理當所有資料被過濾掉時，聚合結果為NULL的情況，將其替換為0。
這種方法確保了在計算平均值的同時，能夠正確處理不良資料。

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。

SQL視窗函式進階應用

探討視窗函式在 SQL 中的應用

視窗函式的基本概念

使用視窗函式的好處

例項解析：使用 dense_rank() 進行排名

程式碼解析：

圖表解析

圖表說明：

進階資料彙總與分析

視窗函式的基本概念

資料分割槽

內容解密：

計算聚合值

內容解密：

框架（Framing）

內容解密：

視窗函式的高階應用：量化分析與資料變化追蹤

命名視窗的定義與應用

使用命名視窗進行量化分析

內容解密：

使用 lag() 和 lead() 追蹤資料變化

使用 lag() 計算價格變化

內容解密：

進階資料彙總與分析

使用視窗函式進行資料分析

內容解密：

計算總變化量

內容解密：

在WHERE子句之外進行條件篩選

使用HAVING子句

內容解密：

使用QUALIFY子句

內容解密：

4.6 在WHERE子句外進行條件篩選與過濾

內容解密：

4.6.3 使用FILTER子句

內容解密：

玄貓 BlackCat

例項解析：使用 `dense_rank()` 進行排名

使用 `lag()` 和 `lead()` 追蹤資料變化

使用 `lag()` 計算價格變化