ในบทช่วยสอนนี้ เราจะเรียนรู้เกี่ยวกับการทำงานของส่วนคำสั่ง PARTITION BY ใน SQL และค้นพบวิธีที่เราสามารถใช้เพื่อแบ่งพาร์ติชันข้อมูลสำหรับชุดย่อยที่ละเอียดยิ่งขึ้น
ไวยากรณ์:
ให้เราเริ่มต้นด้วยไวยากรณ์ของ PARTITION BY clause ไวยากรณ์อาจขึ้นอยู่กับบริบทที่คุณใช้ แต่นี่คือไวยากรณ์ทั่วไป:
เลือกคอลัมน์ 1, คอลัมน์ 2, ...
OVER (แบ่งตาม partition_column1, partition_column2, ...)
จาก table_name
ไวยากรณ์ที่กำหนดแสดงถึงองค์ประกอบต่อไปนี้:
- column1, column2 – หมายถึงคอลัมน์ที่เราต้องการรวมไว้ในชุดผลลัพธ์
- แบ่งพาร์ติชันตามคอลัมน์ – ส่วนนี้จะกำหนดวิธีที่เราต้องการแบ่งพาร์ติชันหรือจัดกลุ่มข้อมูล
ข้อมูลตัวอย่าง
ให้เราสร้างตารางพื้นฐานพร้อมข้อมูลตัวอย่างเพื่อสาธิตวิธีการใช้ส่วนคำสั่ง PARTITION BY สำหรับตัวอย่างนี้ เรามาสร้างตารางพื้นฐานที่เก็บข้อมูลผลิตภัณฑ์กันดีกว่า
สร้างผลิตภัณฑ์ตาราง (
product_id INT คีย์หลัก AUTO_INCREMENT,
product_name วาร์ชาร์( 255 -
หมวดหมู่ วาร์ชาร์( 255 -
ราคาทศนิยม( 10 - 2 -
ปริมาณ INT,
expiration_date วันที่,
บาร์โค้ด BIGINT
-
แทรก
เข้าไปข้างใน
ผลิตภัณฑ์ (ชื่อผลิตภัณฑ์,
หมวดหมู่,
ราคา,
ปริมาณ,
วันหมดอายุ,
บาร์โค้ด)
ค่า ( 'หมวกเชฟ 25ซม.' -
'เบเกอรี่' -
24.67 -
57 -
'2023-09-09' -
2854509564204 -
แทรก
เข้าไปข้างใน
ผลิตภัณฑ์ (ชื่อผลิตภัณฑ์,
หมวดหมู่,
ราคา,
ปริมาณ,
วันหมดอายุ,
บาร์โค้ด)
ค่า ( 'ไข่นกกระทา - กระป๋อง' -
'ตู้กับข้าว' -
17.99 -
67 -
'2023-09-29' -
1708039594250 -
แทรก
เข้าไปข้างใน
ผลิตภัณฑ์ (ชื่อผลิตภัณฑ์,
หมวดหมู่,
ราคา,
ปริมาณ,
วันหมดอายุ,
บาร์โค้ด)
ค่า ( 'กาแฟ - Egg Nog Capuccino' -
'เบเกอรี่' -
92.53 -
10 -
'2023-09-22' -
8704051853058 -
แทรก
เข้าไปข้างใน
ผลิตภัณฑ์ (ชื่อผลิตภัณฑ์,
หมวดหมู่,
ราคา,
ปริมาณ,
วันหมดอายุ,
บาร์โค้ด)
ค่า ( 'ลูกแพร์ - เต็มไปด้วยหนาม' -
'เบเกอรี่' -
65.29 -
48 -
'23-08-2023' -
5174927442238 -
แทรก
เข้าไปข้างใน
ผลิตภัณฑ์ (ชื่อผลิตภัณฑ์,
หมวดหมู่,
ราคา,
ปริมาณ,
วันหมดอายุ,
บาร์โค้ด)
ค่า ( 'พาสต้า - แองเจิ้ลแฮร์' -
'ตู้กับข้าว' -
48.38 -
59 -
'2023-08-05' -
8008123704782 -
แทรก
เข้าไปข้างใน
ผลิตภัณฑ์ (ชื่อผลิตภัณฑ์,
หมวดหมู่,
ราคา,
ปริมาณ,
วันหมดอายุ,
บาร์โค้ด)
ค่า ( 'ไวน์ - Prosecco Valdobiaddene' -
'ผลิต' -
44.18 -
3 -
'2023-03-13' -
6470981735653 -
แทรก
เข้าไปข้างใน
ผลิตภัณฑ์ (ชื่อผลิตภัณฑ์,
หมวดหมู่,
ราคา,
ปริมาณ,
วันหมดอายุ,
บาร์โค้ด)
ค่า ( 'เพสตรี้ - เฟรนช์มินิคละ' -
'ตู้กับข้าว' -
36.73 -
52 -
'2023-05-29' -
5963886298051 -
แทรก
เข้าไปข้างใน
ผลิตภัณฑ์ (ชื่อผลิตภัณฑ์,
หมวดหมู่,
ราคา,
ปริมาณ,
วันหมดอายุ,
บาร์โค้ด)
ค่า ( 'ส้ม - กระป๋อง, แมนดาริน' -
'ผลิต' -
65.0 -
1 -
'2023-04-20' -
6131761721332 -
แทรก
เข้าไปข้างใน
ผลิตภัณฑ์ (ชื่อผลิตภัณฑ์,
หมวดหมู่,
ราคา,
ปริมาณ,
วันหมดอายุ,
บาร์โค้ด)
ค่า ( 'ไหล่หมู' -
'ผลิต' -
55.55 -
73 -
'2023-05-01' -
9343592107125 -
แทรก
เข้าไปข้างใน
ผลิตภัณฑ์ (ชื่อผลิตภัณฑ์,
หมวดหมู่,
ราคา,
ปริมาณ,
วันหมดอายุ,
บาร์โค้ด)
ค่า ( 'ดีซี ฮิคิอาเกะ ฮิระ ฮูบะ' -
'ผลิต' -
56.29 -
53 -
'2023-04-14' -
3354910667072 -
เมื่อเราตั้งค่าข้อมูลตัวอย่างแล้ว เราก็สามารถดำเนินการต่อและใช้คำสั่งย่อย PARTITION BY ได้
การใช้งานขั้นพื้นฐาน
สมมติว่าเราต้องการคำนวณรายการรวมสำหรับแต่ละหมวดหมู่ผลิตภัณฑ์ในตารางก่อนหน้า เราสามารถใช้ PARTITION BY เพื่อแบ่งรายการออกเป็นหมวดหมู่ที่ไม่ซ้ำกัน จากนั้นจึงกำหนดจำนวนรวมในแต่ละหมวดหมู่
ตัวอย่างมีดังนี้:
เลือก
ชื่อผลิตภัณฑ์,
หมวดหมู่,
ปริมาณ,
SUM(ปริมาณ) OVER (แบ่งตามหมวดหมู่) AS Total_items
จาก
สินค้า;
โปรดสังเกตว่าในตัวอย่างที่กำหนด เราแบ่งพาร์ติชันข้อมูลโดยใช้คอลัมน์ 'หมวดหมู่' จากนั้นเราจะใช้ฟังก์ชันการรวม SUM() เพื่อกำหนดรายการรวมในแต่ละหมวดหมู่แยกกัน ผลลัพธ์จะแสดงรายการทั้งหมดในแต่ละหมวดหมู่
การใช้พาร์ติชั่นตามข้อ
โดยสรุป กรณีการใช้งานที่พบบ่อยที่สุดของส่วนคำสั่ง PARTITION BY คือการเชื่อมต่อกับฟังก์ชันหน้าต่าง ฟังก์ชั่นหน้าต่างถูกนำไปใช้กับแต่ละพาร์ติชั่นแยกกัน
ฟังก์ชันหน้าต่างทั่วไปบางอย่างที่จะใช้กับ PARTITION BY มีดังต่อไปนี้:
- SUM() – คำนวณผลรวมของคอลัมน์ภายในแต่ละพาร์ติชัน
- AVG() – คำนวณค่าเฉลี่ยของคอลัมน์ภายในแต่ละพาร์ติชัน
- COUNT() – นับจำนวนแถวภายในแต่ละพาร์ติชัน
- ROW_NUMBER() – กำหนดหมายเลขแถวที่ไม่ซ้ำกันให้กับแต่ละแถวภายในแต่ละพาร์ติชัน
- RANK() – กำหนดอันดับให้กับแต่ละแถวภายในแต่ละพาร์ติชัน
- DENSE_RANK() – กำหนดอันดับหนาแน่นให้กับแต่ละแถวภายในแต่ละพาร์ติชัน
- NTILE() – แบ่งข้อมูลออกเป็นควอนไทล์ภายในแต่ละพาร์ติชัน
แค่นั้นแหละ!
บทสรุป
ในบทช่วยสอนนี้ เราได้เรียนรู้วิธีทำงานกับส่วนคำสั่ง PARTITION BY ใน SQL เพื่อแบ่งพาร์ติชันข้อมูลออกเป็นเซ็กเมนต์ต่างๆ จากนั้นใช้การดำเนินการเฉพาะกับแต่ละพาร์ติชันผลลัพธ์แยกกัน