ใช้ฟังก์ชันหน้าต่างสําหรับการวิเคราะห์
เคล็ดลับ
ดูแท็บ ข้อความและรูปภาพ สําหรับรายละเอียดเพิ่มเติม!
คิวรีการวิเคราะห์มักต้องการการคํานวณที่ครอบคลุมหลายแถวในขณะที่ยังคงส่งคืนรายละเอียดแต่ละแถว ฟังก์ชันการรวมแบบดั้งเดิมจะยุบแถวเป็นกลุ่ม ทําให้ข้อมูลระดับแถวหายไป ฟังก์ชันหน้าต่างแก้ปัญหานี้โดยทําการคํานวณในชุดของแถวที่เกี่ยวข้องกับแถวปัจจุบันโดยไม่ยุบชุดผลลัพธ์
ทําความเข้าใจไวยากรณ์ของฟังก์ชันหน้าต่าง
ฟังก์ชันหน้าต่างคํานวณค่าทั่วทั้ง "หน้าต่าง" ของแถวที่กําหนดโดย OVER ส่วนคําสั่ง ฟังก์ชันหน้าต่างไม่จัดกลุ่มแถวเป็นแถวเอาต์พุตเดียว แต่จะคํานวณค่าในแถวที่เกี่ยวข้องในขณะที่รักษาแถวเดิมทั้งหมดในผลลัพธ์
ไวยากรณ์ทั่วไปสําหรับฟังก์ชันหน้าต่างคือ:
function_name(arguments) OVER (
[PARTITION BY partition_expression]
[ORDER BY order_expression [ASC | DESC]]
[ROWS | RANGE frame_specification]
)
OVERคอมโพเนนต์ส่วนคําสั่งจะควบคุมวิธีการกําหนดหน้าต่าง:
- PARTITION BY: แบ่งแถวออกเป็นกลุ่ม (พาร์ติชัน) สําหรับการคํานวณ
- ORDER BY: กําหนดลําดับตรรกะของแถวภายในแต่ละพาร์ติชัน
- ROWS/RANGE: กําหนดขอบเขตเฟรมที่สัมพันธ์กับแถวปัจจุบัน
แบบสอบถามต่อไปนี้สาธิตฟังก์ชันหน้าต่างอย่างง่ายที่คํานวณยอดรวมของใบสั่งต่อลูกค้า:
SELECT
CustomerID,
SalesOrderID,
OrderDate,
TotalDue,
SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
FROM SalesLT.SalesOrderHeader
ORDER BY CustomerID, OrderDate;
Note
เมื่อคุณระบุ ORDER BY ในส่วน OVER คําสั่งโดยไม่มีข้อมูลจําเพาะของเฟรมเฟรมดีฟอมป์เริ่มต้น RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW สําหรับฟังก์ชันการรวม สิ่งนี้จะสร้างการคํานวณสะสม
ใช้ฟังก์ชันการจัดอันดับ
ฟังก์ชันการจัดอันดับจะกําหนดหมายเลขตามลําดับให้กับแถวตามตําแหน่งภายในพาร์ติชัน SQL Server มีฟังก์ชันการจัดอันดับสี่ฟังก์ชัน แต่ละฟังก์ชันจะจัดการความสัมพันธ์ที่แตกต่างกัน:
ROW_NUMBER() กําหนดหมายเลขลําดับที่ไม่ซ้ํากันให้กับแต่ละแถว โดยไม่มีรายการที่ซ้ํากันแม้กระทั่งสําหรับค่าที่ผูกไว้:
SELECT
ProductID,
Name,
ListPrice,
ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
ชุดผลลัพธ์มีลักษณะดังนี้:
ProductID Name ListPrice PriceRank
--------- ------------------------- --------- ---------
749 Road-150 Red, 62 3578.27 1
750 Road-150 Red, 44 3578.27 2
751 Road-150 Red, 48 3578.27 3
771 Mountain-100 Silver, 38 3399.99 4
แบบสอบถามนี้จะจัดอันดับผลิตภัณฑ์ทั้งหมดตามราคาจากสูงสุดไปต่ําสุด สินค้าแต่ละรายการจะได้รับหมายเลขที่ไม่ซ้ํากันไม่ว่าสินค้าหลายรายการจะมีราคาเดียวกันหรือไม่
RANK() กําหนดอันดับเดียวกันให้กับค่าที่เสมอกัน จากนั้นข้ามตัวเลขเพื่อพิจารณาการเสมอกัน:
SELECT
ProductID,
Name,
ListPrice,
RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
ชุดผลลัพธ์มีลักษณะดังนี้:
ProductID Name ListPrice PriceRank
--------- ------------------------- --------- ---------
749 Road-150 Red, 62 3578.27 1
750 Road-150 Red, 44 3578.27 1
751 Road-150 Red, 48 3578.27 1
771 Mountain-100 Silver, 38 3399.99 4
เมื่อสินค้าสองรายการมีราคาเท่ากัน ทั้งคู่จะได้รับอันดับเดียวกัน อันดับของผลิตภัณฑ์ถัดไปสะท้อนถึงจํานวนผลิตภัณฑ์ทั้งหมดที่อยู่ในอันดับที่สูงขึ้น
DENSE_RANK() กําหนดอันดับเดียวกันให้กับค่าที่เสมอกัน แต่ไม่ข้ามตัวเลข:
SELECT
ProductID,
Name,
ListPrice,
DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
ชุดผลลัพธ์มีลักษณะดังนี้:
ProductID Name ListPrice PriceRank
--------- ------------------------- --------- ---------
749 Road-150 Red, 62 3578.27 1
750 Road-150 Red, 44 3578.27 1
751 Road-150 Red, 48 3578.27 1
771 Mountain-100 Silver, 38 3399.99 2
ชอบ RANK()ค่าที่ผูกไว้มีอันดับเดียวกัน อย่างไรก็ตาม DENSE_RANK() ให้ดําเนินการต่อด้วยตัวเลขที่ต่อเนื่องกันถัดไป คุณจึงสามารถใช้เพื่อนับระดับราคาที่แตกต่างกันได้
NTILE(n) แจกจ่ายแถวออกเป็นกลุ่มที่เท่ากันโดยประมาณตามจํานวนที่กําหนด:
SELECT
ProductID,
Name,
ListPrice,
NTILE(4) OVER (ORDER BY ListPrice DESC) AS PriceQuartile
FROM SalesLT.Product
WHERE ListPrice > 0;
ชุดผลลัพธ์มีลักษณะดังนี้:
ProductID Name ListPrice PriceQuartile
--------- --------------------------- --------- -------------
749 Road-150 Red, 62 3578.27 1
771 Mountain-100 Silver, 38 3399.99 1
722 LL Road Frame - Black, 58 337.22 2
859 Half-Finger Gloves, S 24.49 4
แบบสอบถามนี้แบ่งผลิตภัณฑ์ออกเป็นสี่กลุ่มตามราคา ผลิตภัณฑ์ที่มีราคาสูงสุดอยู่ในควอร์ไทล์ 1 และราคาต่ําสุดอยู่ในควอร์ไทล์ 4 ใช้สําหรับ NTILE() การวิเคราะห์เปอร์เซ็นไทล์หรือกระจายงานอย่างสม่ําเสมอ
การ PARTITION BY รวมกับฟังก์ชันการจัดอันดับจะช่วยให้สามารถจัดอันดับต่อกลุ่มได้:
SELECT
pc.Name AS Category,
p.Name AS Product,
p.ListPrice,
ROW_NUMBER() OVER (
PARTITION BY p.ProductCategoryID
ORDER BY p.ListPrice DESC
) AS CategoryPriceRank
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 0;
ชุดผลลัพธ์มีลักษณะดังนี้:
Category Product ListPrice CategoryPriceRank
--------------- ------------------------- --------- -----------------
Road Bikes Road-150 Red, 62 3578.27 1
Road Bikes Road-150 Red, 44 3578.27 2
Mountain Bikes Mountain-100 Silver, 38 3399.99 1
Mountain Bikes Mountain-100 Black, 38 3374.99 2
คําค้นหานี้จะจัดอันดับผลิตภัณฑ์ภายในแต่ละหมวดหมู่แยกกัน การจัดอันดับจะเริ่มต้นที่ 1 สําหรับแต่ละหมวดหมู่ ดังนั้นคุณจึงสามารถระบุผลิตภัณฑ์ที่แพงที่สุดในแต่ละหมวดหมู่ได้โดยการ CategoryPriceRank = 1กรอง
เคล็ดลับ
ใช้ ROW_NUMBER() เมื่อคุณต้องการหนึ่งแถวต่ออันดับ (เช่น ค้นหา N อันดับแรกต่อกลุ่ม) ใช้ RANK() หรือ DENSE_RANK() เมื่อคุณต้องการเก็บรักษาข้อมูลเนคไทเพื่อวัตถุประสงค์ในการรายงาน
ใช้ฟังก์ชันหน้าต่างรวม
ฟังก์ชันการรวมมาตรฐาน เช่น SUM, AVG, , COUNTและMINMAXสามารถใช้เป็นฟังก์ชันหน้าต่างได้โดยการเพิ่มOVERส่วนคําสั่ง วิธีนี้ช่วยให้คุณสามารถคํานวณการรวมในขณะที่ยังคงรักษารายละเอียดแต่ละแถวไว้
แบบสอบถามต่อไปนี้สาธิตวิธีการคํานวณผลรวมสะสมและการรวมสะสม:
SELECT
SalesOrderID,
OrderDate,
TotalDue,
SUM(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningTotal,
AVG(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningAverage,
COUNT(*) OVER (ORDER BY OrderDate, SalesOrderID) AS OrderNumber
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID;
ชุดผลลัพธ์มีลักษณะดังนี้:
SalesOrderID OrderDate TotalDue RunningTotal RunningAverage OrderNumber
------------ ---------- --------- ------------ -------------- -----------
71774 2008-06-01 972.785 972.785 972.785 1
71776 2008-06-01 87.083 1059.868 529.934 2
71780 2008-06-01 42452.65 43512.518 14504.172 3
71782 2008-06-01 43962.79 87475.308 21868.827 4
สำคัญ
เมื่อใช้ฟังก์ชันหน้าต่างรวมที่ไม่มี ORDER BY ในส่วน OVER คําสั่งฟังก์ชันจะคํานวณทั่วทั้งพาร์ติชัน การเพิ่ม ORDER BY จะสร้างการคํานวณที่ทํางานอยู่จากจุดเริ่มต้นของพาร์ติชันไปยังแถวปัจจุบัน
กําหนดกรอบหน้าต่างด้วย ROWS และ RANGE
กรอบหน้าต่างช่วยให้คุณระบุได้อย่างชัดเจนว่าแถวใดที่สัมพันธ์กับแถวปัจจุบันควรรวมอยู่ในการคํานวณ ส่วนคําสั่งจะ ROWS นับแถวทางกายภาพ ในขณะที่ RANGE จัดกลุ่มแถวที่มีค่าเท่ากัน
สามารถระบุขอบเขตเฟรมได้โดยใช้:
-
UNBOUNDED PRECEDING: จากการเริ่มต้นพาร์ติชัน -
n PRECEDING:nแถวก่อนแถวปัจจุบัน -
CURRENT ROW: แถวปัจจุบัน -
n FOLLOWING:nแถวหลังแถวปัจจุบัน -
UNBOUNDED FOLLOWING: ไปที่ปลายพาร์ติชัน
คิวรีต่อไปนี้คํานวณค่าเฉลี่ยเคลื่อนที่ในสามคําสั่งล่าสุด:
SELECT
SalesOrderID,
OrderDate,
TotalDue,
AVG(TotalDue) OVER (
ORDER BY OrderDate
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS MovingAvg3Orders
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;
ชุดผลลัพธ์มีลักษณะดังนี้:
SalesOrderID OrderDate TotalDue MovingAvg3Orders
------------ ---------- --------- ----------------
71774 2008-06-01 972.785 972.785
71776 2008-06-01 87.083 529.934
71780 2008-06-01 42452.65 14504.172
71782 2008-06-01 43962.79 28834.174
แบบสอบถามนี้จะคํานวณค่าเฉลี่ยเคลื่อนที่ 3 ลําดับโดยรวมแถวปัจจุบันและสองแถวก่อนหน้า สําหรับแถวแรก มีค่าเพียงค่าเดียว ดังนั้นค่าเฉลี่ยจึงเท่ากับTotalDue ในแถวที่สามหน้าต่างจะมีทั้งสามแถว
ใช้ฟังก์ชันการวิเคราะห์
ฟังก์ชันการวิเคราะห์ช่วยให้คุณเข้าถึงข้อมูลจากแถวอื่นได้โดยไม่ต้องใช้การรวมด้วยตนเองหรือคิวรีย่อย ฟังก์ชันเหล่านี้มีประโยชน์สําหรับการวิเคราะห์อนุกรมเวลา การตรวจจับแนวโน้ม และการเปรียบเทียบค่าปัจจุบันกับค่าในอดีตหรือในอนาคต ซึ่งแตกต่างจากฟังก์ชันหน้าต่างรวมที่คํานวณสรุป ฟังก์ชันการวิเคราะห์จะดึงค่าเฉพาะจากแถวเฉพาะในหน้าต่าง
LAG() และ LEAD() เข้าถึงค่าจากแถวก่อนหน้าหรือแถวถัดไป เช่น
SELECT
SalesOrderID,
OrderDate,
TotalDue,
LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS PreviousOrderTotal,
LEAD(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS NextOrderTotal,
TotalDue - LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS ChangeFromPrevious
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;
ชุดผลลัพธ์มีลักษณะดังนี้:
SalesOrderID OrderDate TotalDue PreviousOrderTotal NextOrderTotal ChangeFromPrevious
------------ ---------- --------- ------------------ -------------- ------------------
71774 2008-06-01 972.785 0 87.083 972.785
71776 2008-06-01 87.083 972.785 42452.65 -885.702
71780 2008-06-01 42452.65 87.083 43962.79 42365.567
71782 2008-06-01 43962.79 42452.65 0 1510.14
LAG() ดึงค่าจากแถวก่อนหน้า ในขณะที่ LEAD() ดึงข้อมูลจากแถวต่อไปนี้ พารามิเตอร์ที่สองระบุจํานวนแถวที่จะมองย้อนกลับหรือไปข้างหน้า (ค่าเริ่มต้นคือ 1) และพารามิเตอร์ที่สามจะให้ค่าเริ่มต้นเมื่อไม่มีแถว (เช่น สําหรับแถวแรกที่มี LAG()) ใช้ฟังก์ชันเหล่านี้เพื่อคํานวณการเปลี่ยนแปลงแบบรอบระยะเวลา ระบุแนวโน้ม หรือตรวจหาความผิดปกติในข้อมูลตามลําดับ
FIRST_VALUE() และ LAST_VALUE() ส่งคืนค่าจากแถวแรกหรือแถวสุดท้ายในเฟรม:
SELECT
ProductID,
Name,
ListPrice,
ProductCategoryID,
FIRST_VALUE(Name) OVER (
PARTITION BY ProductCategoryID
ORDER BY ListPrice DESC
) AS MostExpensiveInCategory,
LAST_VALUE(Name) OVER (
PARTITION BY ProductCategoryID
ORDER BY ListPrice DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS LeastExpensiveInCategory
FROM SalesLT.Product
WHERE ListPrice > 0;
ชุดผลลัพธ์มีลักษณะดังนี้:
ProductID Name ListPrice ProductCategoryID MostExpensiveInCategory LeastExpensiveInCategory
--------- ------------------------- --------- ----------------- ------------------------ ------------------------
749 Road-150 Red, 62 3578.27 5 Road-150 Red, 62 LL Road Frame - Red, 58
750 Road-150 Red, 44 3578.27 5 Road-150 Red, 62 LL Road Frame - Red, 58
722 LL Road Frame - Red, 58 337.22 5 Road-150 Red, 62 LL Road Frame - Red, 58
771 Mountain-100 Silver, 38 3399.99 6 Mountain-100 Silver, 38 Mountain-500 Black, 52
FIRST_VALUE() ส่งกลับค่าจากแถวแรกในหน้าต่างที่สั่งซื้อ ซึ่งในกรณีนี้คือผลิตภัณฑ์ที่แพงที่สุดต่อหมวดหมู่
LAST_VALUE() ส่งกลับราคาแพงที่สุด แต่ต้องมีเฟรมที่ชัดเจนเพื่อรวมแถวทั้งหมด ฟังก์ชันเหล่านี้ช่วยให้คุณเปรียบเทียบแต่ละแถวกับค่าเกณฑ์มาตรฐาน เช่น ค่าสูงสุด ต่ําสุด หรือค่าพื้นฐานในกลุ่ม
Note
LAST_VALUE() ต้องมีข้อกําหนดเฟรมที่ชัดเจนเพื่อรวมแถวหลังแถวปัจจุบัน หากไม่มีเฟรมเริ่มต้นจะรวมเฉพาะแถวจนถึงแถว LAST_VALUE() ปัจจุบันทําให้ส่งคืนค่าของแถวปัจจุบัน
PERCENT_RANK() และ CUME_DIST() คํานวณตําแหน่งสัมพัทธ์ภายในพาร์ติชัน:
SELECT
Name,
ListPrice,
PERCENT_RANK() OVER (ORDER BY ListPrice) AS PercentRank,
CUME_DIST() OVER (ORDER BY ListPrice) AS CumulativeDistribution
FROM SalesLT.Product
WHERE ListPrice > 0
ORDER BY ListPrice;
ชุดผลลัพธ์มีลักษณะดังนี้:
Name ListPrice PercentRank CumulativeDistribution
------------------------- --------- ----------- ----------------------
Patch Kit/8 Patches 2.29 0.0 0.0081
Road Tire Tube 3.99 0.0081 0.0162
Touring Tire Tube 4.99 0.0162 0.0243
Road-150 Red, 62 3578.27 0.9919 1.0
PERCENT_RANK() ส่งกลับค่าระหว่าง 0 ถึง 1 ซึ่งระบุว่าแถวมีค่าต่ํากว่ากี่เปอร์เซ็นต์ (0 หมายถึงต่ําสุด หนึ่งหมายถึงสูงสุด)
CUME_DIST() แสดงการแจกแจงสะสม โดยระบุว่าแถวมีค่าน้อยกว่าหรือเท่ากับแถวปัจจุบันกี่เปอร์เซ็นต์ ใช้ฟังก์ชันเหล่านี้สําหรับการวิเคราะห์เปอร์เซ็นไทล์ การระบุค่าผิดปกติ หรือการสร้างรายงานการแจกจ่าย
สําหรับข้อมูลเพิ่มเติมเกี่ยวกับฟังก์ชันหน้าต่าง โปรดดู ฟังก์ชันหน้าต่าง (Transact-SQL) และ ฟังก์ชันการจัดอันดับ