SQL Server Window Function窗體函數之SQL Windowing

SQL Server 窗體函數主要用來處理由 OVER 子句定義的行集, 主要用來分析和處理

Running totals

Moving averages

Gaps and islands

先看一個簡單的應用 - 按照訂單額從高到低對訂單表信息做一個排名

USE TSQL2012
GO
    
SELECT orderid,
       orderdate,
       val,
       RANK() OVER(ORDER BY val DESC) AS rnk
FROM Sales.OrderValues
ORDER BY rnk

查詢結果 -

OrderID OrderDate　　　　　　       Val　　　　Rnk

10865    2008-02-02 00:00:00.000    16387.50    1

10981    2008-03-27 00:00:00.000    15810.00    2

11030    2008-04-17 00:00:00.000    12615.05    3

10889    2008-02-16 00:00:00.000    11380.00    4

10417    2007-01-16 00:00:00.000    11188.40    5

10817    2008-01-06 00:00:00.000    10952.85    6

10897    2008-02-19 00:00:00.000    10835.24    7

10479    2007-03-19 00:00:00.000    10495.60    8

OVER 的作用

OVER 的作用就是定義了行集窗體，這個窗體的集合為當前行提供了一個上下文環境. RANK 函數根 據指定的集合以及行集的排序順序計算出當前行的排名, 以 Rnk = 5 為例, 排序後這條數據的前面有 4條數據，所以它的排名就是 4 + 1 = 5

再總結簡單一點就是, OVER 定義了一個行的集合, 它是一個函數, 每次向當前行返回一個唯一的 值, 如何返回? 在這個例子中就使用 RANK 函數返回了當前行的一個排名.

與OVER搭配使用的其它函數

聚合的函數 - SUM, COUNT, MIN, MAX

排名的函數 - RANK， DENSE_RANK, ROW_NUMBER, NTILE

Distribution 函數 - PERCENT_RANK, CUME_DIST, PERCENTILE_CONT, PERCENTILE_DISC

Offset 函數  - LAG, LEAD, FIRST_VALUE, LAST_VALUE

SQL Server Window Function 的應用

窗體函數的應用非常廣泛 - 像分頁，去重，分組的基礎上返回 Top N 的行, 計算 Running Totals，Gaps and islands, 百分率, Hierarchy 排序, Pivoting 等等

使用 Windows 窗體函數的原因一方面是因為 SQL Server 的優化器不夠完美, 盡管足夠強大, 但 是並不會涵蓋所有的優化規則.

第二, 在執行計劃的選擇上，SQL Server 並不會真正執行所有有可能的計劃來獲取一個最優的選 擇，對於 SQL 本身這種指令性語言的解析和優化優化器只能說是在最短時間裡盡量做到足夠好，選擇 一個好的執行計劃. 而 Window 窗體函數本身就經歷過了很好的調優處理, 所以性能會更加好一 些.