Microsoft SQL Server 2022 T-SQL Windows子句加強功能

黃家瑞 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語法。




您可在下列課程中了解更多技巧喔!