บทนำสู่ฟังก์ชันหน้าต่าง SQL

คุณสมบัติที่ทรงพลังมากที่คุณชอบเกลียด (แต่จำเป็นต้องรู้)

ฟังก์ชันหน้าต่าง SQL มีคุณสมบัติที่ทรงพลังและมีประโยชน์อย่างยิ่ง แต่สำหรับหลาย ๆ คน เนื่องจากพวกมันต่างไปจาก SQL มาตรฐาน พวกเขาจึงเรียนรู้และเข้าใจได้ยาก มีไวยากรณ์แปลก ๆ และมักหลีกเลี่ยง

ฟังก์ชันหน้าต่างสามารถอธิบายได้ง่ายๆ เป็นฟังก์ชันการคำนวณที่คล้ายกับการรวม แต่เมื่อการรวมปกติผ่านส่วนคำสั่ง GROUP BY จะซ่อนแถวแต่ละแถวที่ถูกรวมเข้าด้วยกัน ฟังก์ชันหน้าต่างจะสามารถเข้าถึงแต่ละแถวและสามารถเพิ่มแอตทริบิวต์บางส่วนจากแถวเหล่านั้นลงใน ชุดผลลัพธ์

ในบทช่วยสอนเกี่ยวกับฟังก์ชันหน้าต่าง SQL นี้ ฉันจะให้คุณเริ่มต้นใช้งานฟังก์ชันหน้าต่าง อธิบายประโยชน์และเวลาที่คุณจะใช้งาน และให้ตัวอย่างจริงแก่คุณเพื่อช่วยในแนวคิด

หน้าต่างสู่ข้อมูลของคุณ

หนึ่งในคุณลักษณะที่ใช้มากที่สุดและมีความสำคัญใน SQL คือความสามารถในการรวมหรือจัดกลุ่มแถวของข้อมูลในลักษณะเฉพาะ อย่างไรก็ตาม ในบางกรณี การจัดกลุ่มอาจซับซ้อนมาก ขึ้นอยู่กับความจำเป็น

คุณเคยต้องการที่จะวนซ้ำผลลัพธ์ของการสืบค้นของคุณเพื่อให้ได้อันดับ รายการ x สูงสุดหรือคล้ายกันหรือไม่? คุณมีโครงการวิเคราะห์ใดบ้างที่คุณต้องการเตรียมข้อมูลให้เหมาะสมกับเครื่องมือสร้างภาพ แต่พบว่าแทบจะเป็นไปไม่ได้หรือซับซ้อนจนไม่คุ้มค่าหรือไม่

ฟังก์ชันของหน้าต่างช่วยให้สิ่งต่างๆ ง่ายขึ้น หลังจากที่คุณได้รับผลลัพธ์ของการสืบค้นของคุณ—เช่น หลังจาก WHERE clause และการรวมมาตรฐานใดๆ ฟังก์ชันหน้าต่างจะทำหน้าที่ในแถวที่เหลือ ( หน้าต่าง ของข้อมูล) และได้สิ่งที่คุณต้องการ

ฟังก์ชันหน้าต่างบางอย่างที่เราจะพิจารณา ได้แก่:

• OVER
• COUNT()
• SUM()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD()
• LAG()

ง่ายเกินไป

ส่วนคำสั่ง OVER คือสิ่งที่ระบุฟังก์ชันหน้าต่าง และต้องรวมอยู่ในคำสั่งเสมอ ค่าเริ่มต้นในส่วนคำสั่ง OVER คือชุดแถวทั้งหมด ตัวอย่างเช่น ให้ดูตารางพนักงานในฐานข้อมูลของบริษัทและแสดงจำนวนพนักงานทั้งหมดในแต่ละแถว พร้อมด้วยข้อมูลของพนักงานแต่ละคน รวมถึงเวลาที่พวกเขาเริ่มต้นกับบริษัท

 SELECT COUNT(*) OVER() As NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

เช่นเดียวกับฟังก์ชันของหน้าต่างหลายๆ ฟังก์ชันข้างต้น สามารถเขียนด้วยวิธีที่ไม่ใช่หน้าต่างที่คุ้นเคย ซึ่งในตัวอย่างง่ายๆ นี้ ก็ไม่ได้แย่เกินไป:

 SELECT (SELECT COUNT(*) FROM Employee) as NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

แต่ตอนนี้ สมมติว่าเราต้องการแสดงจำนวนพนักงานที่เริ่มต้นในเดือนเดียวกับพนักงานในแถว เราจะต้องจำกัดหรือจำกัดการนับให้เหลือเพียงเดือนนั้นสำหรับแต่ละแถว มันทำอย่างไร? เราใช้ส่วนคำสั่ง window PARTITION ดังนี้:

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started),YEAR(date_started)) As NumPerMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname FROM Employee ORDER BY date_started;

พาร์ติชั่นช่วยให้คุณกรองหน้าต่างออกเป็นส่วนๆ ตามค่าหรือค่าที่กำหนด แต่ละส่วนมักเรียกว่า กรอบ หน้าต่าง

ในการกล่าวเพิ่มเติม สมมติว่าเราไม่เพียงแต่ต้องการทราบจำนวนพนักงานที่เริ่มต้นในเดือนเดียวกัน แต่เราต้องการแสดงลำดับที่พวกเขาเริ่มต้นในเดือนนั้น สำหรับสิ่งนั้น เราสามารถใช้คำ ORDER BY ที่คุ้นเคยได้ อย่างไรก็ตาม ภายในฟังก์ชันหน้าต่าง ORDER BY ทำงานแตกต่างไปจากที่ทำที่ส่วนท้ายของแบบสอบถามเล็กน้อย

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started) ORDER BY date_started) As NumThisMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

ในกรณีนี้ ORDER BY จะแก้ไขหน้าต่างให้เปลี่ยนจากจุดเริ่มต้นของพาร์ติชัน (ในกรณีนี้คือเดือนและปีที่พนักงานเริ่มทำงาน) เป็นแถวปัจจุบัน ดังนั้น การนับจะเริ่มต้นใหม่ในแต่ละพาร์ติชั่น

อันดับมัน

ฟังก์ชันหน้าต่างมีประโยชน์มากสำหรับจุดประสงค์ในการจัดอันดับ ก่อนหน้านี้เราพบว่าการใช้ฟังก์ชันการรวม COUNT ทำให้เราเห็นลำดับที่พนักงานเข้าร่วมบริษัท นอกจากนี้เรายังสามารถใช้ฟังก์ชันการจัดอันดับหน้าต่าง เช่น ROW_NUMBER() , RANK() และ DENSE_RANK()

สามารถเห็นความแตกต่างได้หลังจากที่เราเพิ่มพนักงานใหม่ในเดือนถัดไป และลบพาร์ติชั่น:

 SELECT ROW_NUMBER() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As StartingRank, RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As EmployeeRank, DENSE_RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As DenseRank, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

คุณสามารถเห็นความแตกต่าง ROW_NUMBER() ให้การนับตามลำดับภายในพาร์ติชั่นที่กำหนด (แต่หากไม่มีพาร์ติชั่น มันจะผ่านทุกแถว) RANK() ให้อันดับของแต่ละแถวตามคำสั่งย่อย ORDER BY มันแสดงความเสมอกัน แล้วข้ามอันดับถัดไป DENSE_RANK ยังแสดงความเสมอกัน แต่จากนั้นจะดำเนินต่อไปด้วยค่าที่ต่อเนื่องกันถัดไปราวกับว่าไม่มีการเสมอกัน

ฟังก์ชันการจัดอันดับอื่นๆ ได้แก่:

• CUME_DIST – คำนวณอันดับสัมพัทธ์ของแถวปัจจุบันภายในพาร์ติชัน
• NTILE – แบ่งแถวสำหรับพาร์ติชั่นหน้าต่างแต่ละพาร์ติชั่นเท่าๆ กัน
• PERCENT_RANK – เปอร์เซ็นต์อันดับของแถวปัจจุบัน

สังเกตในตัวอย่างนี้ด้วยว่าคุณสามารถมีฟังก์ชัน Window ได้หลายฟังก์ชันในแบบสอบถามเดียว และทั้งพาร์ติชันและลำดับอาจแตกต่างกันในแต่ละส่วน!

แถวและช่วงและเฟรม Oh My

หากต้องการกำหนดหรือจำกัดกรอบหน้าต่างของคุณเพิ่มเติมภายในอนุประโยค OVER() คุณสามารถใช้ ROWS และ RANGE ด้วยคำสั่งย่อย ROWS คุณสามารถระบุแถวที่รวมอยู่ในพาร์ติชันของคุณเป็นแถวก่อนหน้าหรือหลังแถวปัจจุบัน

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

ในตัวอย่างนี้ กรอบหน้าต่างเปลี่ยนจากแถวแรกเป็นแถวปัจจุบันลบ 1 และขนาดหน้าต่างยังคงเพิ่มขึ้นสำหรับแต่ละแถว

ช่วงทำงานแตกต่างกันเล็กน้อยและเราอาจได้ผลลัพธ์ที่แตกต่างกัน

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

ช่วงจะรวมแถวเหล่านั้นในกรอบหน้าต่างซึ่งมีค่า ORDER BY เดียวกันกับแถวปัจจุบัน ดังนั้น เป็นไปได้ที่คุณจะได้รับรายการซ้ำด้วย RANGE หาก ORDER BY ไม่ซ้ำกัน

บางคนอธิบาย ROWS เป็นตัวดำเนินการจริงในขณะที่ RANGE เป็นตัวดำเนินการเชิงตรรกะ ไม่ว่าในกรณีใด ค่าเริ่มต้นสำหรับ ROWS และ RANGE จะเป็น UNBOUNDED PRECEDING AND CURRENT ROW เสมอ

อะไรอีก?

ฟังก์ชันการรวมมาตรฐานส่วนใหญ่ทำงานกับฟังก์ชัน Window เราได้เห็น COUNT ในตัวอย่างแล้ว อื่นๆ ได้แก่ SUM , AVG , MIN , MAX เป็นต้น

ด้วยฟังก์ชันหน้าต่าง คุณยังสามารถเข้าถึงทั้งระเบียนก่อนหน้าและระเบียนที่ตามมาโดยใช้ LAG และ LEAD และ FIRST_VALUE และ LAST_VALUE ตัวอย่างเช่น สมมติว่าคุณต้องการแสดงตัวเลขยอดขายสำหรับเดือนปัจจุบันในแต่ละแถว และความแตกต่างระหว่างตัวเลขยอดขายของเดือนที่แล้ว คุณอาจทำสิ่งนี้:

 SELECT id, OrderMonth, OrderYear, product, sales, sales - LAG(sales,1) OVER (PARTITION BY product ORDER BY OrderYear, OrderMonth) As sales_change FROM sales_products WHERE sale_year = 2019;

โดยทั่วไป ฟังก์ชันของหน้าต่าง SQL นั้นทรงพลังมาก

แม้ว่านี่จะเป็นการแนะนำสั้นๆ เกี่ยวกับฟังก์ชันหน้าต่าง SQL แต่หวังว่าคุณจะสนใจที่จะเห็นสิ่งที่พวกเขาสามารถทำได้ เราได้เรียนรู้ว่าฟังก์ชันหน้าต่างทำการคำนวณคล้ายกับฟังก์ชันการรวม แต่ด้วยประโยชน์เพิ่มเติมที่ฟังก์ชันเหล่านี้สามารถเข้าถึงข้อมูลภายในแต่ละแถวได้ ซึ่งทำให้ฟังก์ชันเหล่านี้มีประสิทธิภาพมาก พวกมันประกอบด้วย OVER เสมอ และอาจมี PARTITION BY , ORDER BY และโฮสต์ของการรวม ( SUM , COUNT ฯลฯ ) และฟังก์ชันตำแหน่งอื่นๆ ( LEAD , LAG ) นอกจากนี้เรายังได้เรียนรู้เกี่ยวกับกรอบหน้าต่างและวิธีห่อหุ้มส่วนต่างๆ ของข้อมูล

โปรดทราบว่า SQL รสชาติต่างๆ อาจใช้ฟังก์ชันหน้าต่างต่างกัน และบางส่วนอาจไม่ได้ใช้งานฟังก์ชันหน้าต่างหรือส่วนคำสั่งทั้งหมด อย่าลืมตรวจสอบเอกสารประกอบสำหรับแพลตฟอร์มที่คุณใช้อยู่

ในฐานะนักพัฒนา SQL หากคุณสนใจที่จะปรับแต่งประสิทธิภาพฐานข้อมูล SQL ของคุณ ลองดูที่ การปรับแต่งประสิทธิภาพของฐานข้อมูล SQL สำหรับนักพัฒนา

หน้าต่างมีความสุข!

สำหรับข้อมูลเพิ่มเติมเกี่ยวกับการใช้งานเฉพาะ โปรดดู:

• เอกสารประกอบ Window Functions ของ PostgreSQL สำหรับการใช้งาน PostgreSQL
• เอกสาร SELECT - OVER Clause (Transact-SQL) โดย Microsoft
• ฟังก์ชั่นหน้าต่างใน SQL Server สำหรับภาพรวมที่ดีในการใช้งาน SQL Server และส่วนที่ 2