Microsoft SQL Server 2022 T-SQL Windows子句加強功能 ~ UCOM 職能補給站

黃家瑞 Jerry Huang

恆逸教育訓練中心-資深講師
技術分類：資料庫

Microsoft SQL Server 2022的T-SQL新增不少功能，本專題中針對WINDOW子句，以下以Northwind做為範例資料庫，做範例說明。

WINDOW子句是ISO/IEC SQL標準，在視窗函數中，可以命名視窗定義的一部分（或整個視窗），然後在查詢視窗函數的 OVER 子句中使用視窗名稱。以避免重複定義視窗相同的部分，縮短程式碼。

WINDOW子句位置在T-SQL中，SELECT語法的GROUP BY和HAVING子句之間：

SELECT
FROM
JOIN
WHERE
GROUP BY
WINDOW
HAVING
ORDER BY

WINDOW子句語法如下：

WINDOW window_name AS ( [reference_window_name]
[ <window partition clause> ]
[ <window order clause> ]
[ <window frame clause> ] )

以下範例未使用WINDOW子句

SELECT c.CategoryID, c.CategoryName, 
    p.ProductID, p.ProductName, p.UnitPrice,
    SUM(p.UnitPrice) OVER (PARTITION BY c.CategoryID 
    ORDER BY p.UnitPrice
    ROWS BETWEEN UNBOUNDED PRECEDING  AND CURRENT ROW) AS RunningTotal
FROM dbo.Products p
INNER JOIN dbo.Categories c
    ON p.CategoryID = c.CategoryID

執行後的結果：

可以將語法改為

SELECT c.CategoryID, c.CategoryName, 
    p.ProductID, p.ProductName, p.UnitPrice,
    SUM(p.UnitPrice) OVER POR AS RunningTotal
FROM dbo.Products p
INNER JOIN dbo.Categories c
    ON p.CategoryID = c.CategoryID
WINDOW  P AS(PARTITION BY c.CategoryID),
        PO AS(P ORDER BY p.UnitPrice),
        POR AS(PO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

OVER子句中的內容，無法重複利用，常常在撰寫視窗函數時會讓整個T-SQL語法變得紊亂，利用WINDOW子句可以讓這個問題獲得改善，在下一個例子中示範重複使用定義好的WINDOW子句。

SELECT c.CategoryID, c.CategoryName, 
    p.ProductID, p.ProductName, p.UnitPrice,
    SUM(p.UnitPrice) OVER POR AS RunningTotal,
    AVG(p.UnitPrice) OVER (PO ROWS BETWEEN 2 PRECEDING AND  CURRENT ROW)       
        AS MovingAverage
FROM dbo.Products p
INNER JOIN dbo.Categories c
    ON p.CategoryID = c.CategoryID
WINDOW P AS(PARTITION BY c.CategoryID),
       PO AS(P ORDER BY p.UnitPrice),
       POR AS(PO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

可以重複利用先前已定義好的語法，不必再重新將冗長的語法再重複一次，只需要使用定義在WINDOW子句中簡稱P, PO, POR讓整個T-SQL語法可以更簡潔易懂。

執行後的結果：

下面這個例子中：

SELECT e.EmployeeID, e.FirstName, 
    MONTH(o.OrderDate) AS OrderMonth,
    SUM(od.Quantity*od.UnitPrice) AS Total,
    SUM(SUM(od.Quantity*od.UnitPrice)) OVER () AS TotalAll,    --總合計    
    SUM(SUM(od.Quantity*od.UnitPrice)) OVER P AS TotalEmployee,--該月合計
    SUM(SUM(od.Quantity*od.UnitPrice)) OVER PO AS RunningTotal,--累計至當月
    AVG(SUM(od.Quantity*od.UnitPrice)) OVER POR AS MovingAvg --近三個月平均
FROM dbo.Employees e
INNER JOIN dbo.Orders o
    ON e.EmployeeID = o.EmployeeID
INNER JOIN dbo.[Order Details] od
    ON o.OrderID = od.OrderID
WHERE YEAR(o.OrderDate) = 1997 
GROUP BY e.EmployeeID, e.FirstName, MONTH(o.OrderDate)
WINDOW P AS (PARTITION BY e.EmployeeID),
       PO AS (P ORDER BY MONTH(o.OrderDate)),
       POR AS (PO ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);

執行後的結果：

相較於過去的方式，可以有較為簡潔的語法，方便管理維護T-SQL語法。

UCOM 職能補給站

提供您專業技能的最佳管道，IT企業主管唯一推薦

2024年11月1日星期五