ซึ่งจะมีประโยชน์อย่างยิ่งเมื่อคุณต้องการคำนวณการรวมหรือการจัดอันดับสำหรับกลุ่มของแถวโดยไม่ต้องยุบชุดผลลัพธ์ทั้งหมด
เข้าร่วมกับเราในบทช่วยสอนนี้ในขณะที่เราเรียนรู้ทุกสิ่งที่ควรรู้เพื่อให้คุณเริ่มต้นทำงานกับส่วนคำสั่ง OVER
ความต้องการ:
ก่อนที่เราจะเจาะลึกฟังก์ชันและการทำงานของส่วนคำสั่ง OVER ตรวจสอบให้แน่ใจว่าคุณมีพื้นฐานของ SQL อยู่แล้ว นอกจากนี้เรายังถือว่าคุณมีสิทธิ์เข้าถึงฐานข้อมูลที่คุณสามารถใช้เพื่อทดสอบความรู้ของคุณ
ในกรณีของเรา เราจะใช้ฐานข้อมูล MySQL กับฐานข้อมูลตัวอย่าง Sakila เพียงตรวจสอบให้แน่ใจว่าคุณมีสิทธิ์เพียงพอและกลไกฐานข้อมูลของคุณรองรับฟังก์ชันหน้าต่าง
ไวยากรณ์:
ดังที่เราได้กล่าวไว้ก่อนหน้านี้ ในกรณีส่วนใหญ่ เราใช้ส่วนคำสั่ง OVER เป็นหลักร่วมกับฟังก์ชันหน้าต่าง
ดังนั้นเราจึงสามารถแสดงไวยากรณ์ของประโยคได้ดังนี้:
<ฟังก์ชั่นหน้าต่าง>(การแสดงออก) OVER ([แบ่งตาม partition_expression, ...]
[เรียงลำดับตาม sort_expression [ASC | ดีเอสซี], ...]
[frame_special]
)
ในรูปแบบที่กำหนด เราสามารถแบ่งแต่ละองค์ประกอบได้ดังนี้:
-
– หมายถึงฟังก์ชันหน้าต่างที่เราต้องการใช้กับหน้าต่างเฉพาะของแถว เช่น SUM(), AVG(), ROW_NUMBER(), RANK เป็นต้น - นิพจน์ – ระบุคอลัมน์หรือนิพจน์ที่ใช้ฟังก์ชันหน้าต่าง
- การแบ่งส่วนตาม - นี่เป็นส่วนคำสั่งเสริมที่แบ่งชุดผลลัพธ์ออกเป็นพาร์ติชัน โดยแต่ละพาร์ติชันเป็นเหมือนหน่วยแยกต่างหากที่ใช้ฟังก์ชัน แถวภายในพาร์ติชันเดียวกันใช้ค่าเดียวกันในคอลัมน์ที่ระบุ
- ORDER BY – ระบุลำดับการประมวลผลแถวในแต่ละพาร์ติชัน
- frame_specialation – นี่คือส่วนคำสั่งทางเลือกที่กำหนดเฟรมของแถวภายในพาร์ติชัน ข้อมูลจำเพาะของเฟรมทั่วไป ได้แก่ ROWS BETWEEN
AND หรือ RANGE BETWEEN AND
เพื่อไม่ให้เป็นการรบกวน ให้เราสำรวจตัวอย่างที่เป็นประโยชน์เกี่ยวกับวิธีการใช้งาน
ตัวอย่าง:
ให้เราสาธิตวิธีการใช้อนุประโยคโดยใช้ฐานข้อมูลตัวอย่าง Sakila พิจารณาตัวอย่างที่เราจำเป็นต้องกำหนดรายได้รวมสำหรับภาพยนตร์แต่ละหมวด
เราสามารถใช้ฟังก์ชัน sum window กับ OVER clause และคำสั่ง join ดังแสดงในตัวอย่างต่อไปนี้:
เลือกCategory.name AS Category_name,
film.title AS film_title,
film.rental_rate,
SUM(Payment.amount) สูงกว่า (แบ่งตาม Category.name) AS Total_revenue
จาก
ฟิล์ม
เข้าร่วม
film_category เปิดอยู่
film.film_id = film_category.film_id
เข้าร่วม
หมวดหมู่เปิด
film_category.category_id = category.category_id
เข้าร่วม
เปิดสินค้าคงคลัง
film.film_id = สินค้าคงคลัง.film_id
เข้าร่วม
เปิดการเช่า
inventory.inventory_id = เช่า.inventory_id
เข้าร่วม
ชำระเงินบน
rent.rental_id = การชำระเงินrental_id
สั่งโดย
ชื่อหมวดหมู่,
film.title;
ในแบบสอบถามที่กำหนด เราเริ่มต้นด้วยการเลือกชื่อภาพยนตร์ อัตราค่าเช่า และใช้นิพจน์ผลรวม (จำนวนเงินที่ชำระเหนือพาร์ติชันตามหมวดหมู่) เพื่อกำหนดผลรวมของพาร์ติชันแต่ละหมวดหมู่ตามชื่อหมวดหมู่
เราต้องใช้ส่วนคำสั่ง PARTITION BY เพื่อให้แน่ใจว่าการคำนวณผลรวมจะเริ่มต้นใหม่ในแต่ละหมวดหมู่ที่ไม่ซ้ำกัน
ผลลัพธ์ที่ได้จะเป็นดังนี้:
ได้แล้ว!
บทสรุป
ในตัวอย่างนี้ เราได้สำรวจพื้นฐานของการทำงานกับส่วนคำสั่ง OVER ใน SQL นี่ไม่ใช่คำสั่งพื้นฐานและจำเป็นต้องมีความคุ้นเคยกับคุณลักษณะ SQL อื่นๆ มาก่อน