SQL Group By และ Partition By Scenarios: เมื่อใดและอย่างไรจึงจะรวมข้อมูลใน Data Science – KDnuggets

ภาพโดย Freepik

SQL (Structured Query Language) เป็นภาษาโปรแกรมที่ใช้สำหรับจัดการและจัดการข้อมูล นั่นคือสาเหตุที่คำสั่ง SQL มีความสำคัญมากสำหรับการโต้ตอบกับฐานข้อมูลในลักษณะที่มีโครงสร้างและมีประสิทธิภาพ

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

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

GROUP BY clause ใน SQL ใช้สำหรับ

1. การจัดกลุ่มข้อมูลในบางคอลัมน์
2. ลดกลุ่มให้เหลือแถวเดียว
3. ดำเนินการรวมกลุ่มในคอลัมน์อื่นของกลุ่ม

คอลัมน์การจัดกลุ่ม = ค่าในคอลัมน์การจัดกลุ่มควรเหมือนกันสำหรับแถวทั้งหมดในกลุ่ม

คอลัมน์การรวม = โดยทั่วไปค่าในคอลัมน์การรวมจะแตกต่างกับฟังก์ชันที่ใช้ เช่น ผลรวม สูงสุด ฯลฯ

คอลัมน์การรวมไม่ควรเป็นคอลัมน์การจัดกลุ่ม

สถานการณ์ที่ 1: การจัดกลุ่มเพื่อค้นหาผลรวมของผลรวม

สมมติว่าเราต้องการคำนวณยอดขายรวมของทุกประเภทในตารางการขาย

ดังนั้นเราจะจัดกลุ่มตามหมวดหมู่และรวมยอดขายแต่ละรายการในทุกหมวดหมู่

select category, 
sum(amount) as sales
from sales
group by category;

คอลัมน์การจัดกลุ่ม = หมวดหมู่

คอลัมน์การรวม = จำนวนเงิน

ฟังก์ชันการรวม = ผลรวม()

สถานการณ์ที่ 2: การจัดกลุ่มเพื่อค้นหาการนับ

สมมติว่าเราต้องการคำนวณจำนวนพนักงานในแต่ละแผนก

ในกรณีนี้เราจะจัดกลุ่มตามแผนกและคำนวณจำนวนพนักงานในทุกแผนก

select department, 
count(empid) as emp_count
from employees
group by department;

คอลัมน์การจัดกลุ่ม = แผนก

คอลัมน์การรวม = empid

ฟังก์ชันการรวม = การนับ

สถานการณ์ที่ 3: การจัดกลุ่มเพื่อค้นหาค่าเฉลี่ย

สมมติว่าเราต้องการคำนวณเงินเดือนเฉลี่ยของพนักงานในแต่ละแผนก

ในทำนองเดียวกัน เราจะจัดกลุ่มตามแผนกอีกครั้ง และคำนวณเงินเดือนเฉลี่ยของพนักงานในทุกแผนกแยกกัน

select department, 
avg(salary) as avg_salary
from employees
group by department;

คอลัมน์การจัดกลุ่ม = แผนก

คอลัมน์รวม = เงินเดือน

ฟังก์ชันการรวม = เฉลี่ย

สถานการณ์ที่ 4: การจัดกลุ่มเพื่อค้นหาสูงสุด / ต่ำสุด

สมมติว่าเราต้องการคำนวณเงินเดือนสูงสุดของพนักงานในแต่ละแผนก

เราจะจัดกลุ่มแผนกและคำนวณเงินเดือนสูงสุดในทุกแผนก

select department, 
max(salary) as max_salary
from employees
group by department;

คอลัมน์การจัดกลุ่ม = แผนก

คอลัมน์รวม = เงินเดือน

ฟังก์ชันการรวม = สูงสุด

สถานการณ์ที่ 5: การจัดกลุ่มเพื่อค้นหารายการที่ซ้ำกัน

สมมติว่าเราต้องการค้นหาชื่อลูกค้าที่ซ้ำกันหรือชื่อเดียวกันในฐานข้อมูลของเรา

เราจะจัดกลุ่มตามชื่อลูกค้าและใช้การนับเป็นฟังก์ชันการรวมกลุ่ม นอกจากนี้ เราจะใช้การมีส่วนคำสั่งเหนือฟังก์ชันการรวมเพื่อกรองเฉพาะจำนวนที่มากกว่าหนึ่งเท่านั้น

select name, 
count(*) AS duplicate_count
from customers
group by name
having count(*) > 1;

คอลัมน์การจัดกลุ่ม = ชื่อ

คอลัมน์การรวม = *

ฟังก์ชันการรวม = การนับ

มี = เงื่อนไขตัวกรองที่จะใช้กับฟังก์ชันการรวม

PARTITION BY clause ใน SQL ใช้สำหรับ

1. การจัดกลุ่ม/การแบ่งพาร์ติชันข้อมูลในบางคอลัมน์
2. แต่ละแถวจะยังคงอยู่และ ไม่ รวมกันเป็นหนึ่งเดียว
3. ดำเนินการจัดอันดับและการรวมกลุ่มในคอลัมน์อื่นของกลุ่ม/พาร์ติชัน

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

Ordering column = โดยแต่ละกลุ่มสร้างขึ้นตาม Partitioning Column เราจะเรียงลำดับ/จัดเรียงแถวในกลุ่ม

ฟังก์ชันการจัดอันดับ = ฟังก์ชันการจัดอันดับหรือฟังก์ชันการรวมจะถูกนำไปใช้กับแถวในพาร์ติชัน

สถานการณ์ที่ 6: การแบ่งพาร์ติชันเพื่อค้นหาสถิติสูงสุดในกลุ่ม

สมมติว่าเราต้องการคำนวณว่าหนังสือเล่มใดในแต่ละหมวดหมู่มียอดขายสูงสุด พร้อมด้วยจำนวนหนังสือที่ขายดีที่สุด

ในกรณีนี้ เราไม่สามารถใช้กลุ่มตามข้อได้ เนื่องจากการจัดกลุ่มจะลดบันทึกในทุกหมวดหมู่ให้เหลือแถวเดียว

อย่างไรก็ตาม เราต้องการรายละเอียดบันทึก เช่น ชื่อหนังสือ จำนวน ฯลฯ พร้อมด้วยหมวดหมู่เพื่อดูว่าหนังสือเล่มใดมียอดขายสูงสุดในแต่ละหมวด

select book_name, amount
row_number() over (partition by category order by amount) as sales_rank
from book_sales;

การแบ่งคอลัมน์ = หมวดหมู่

คอลัมน์การสั่งซื้อ = จำนวน

ฟังก์ชันการจัดอันดับ = row_number()

แบบสอบถามนี้แสดงแถวทั้งหมดในตาราง book_sales และแถวต่างๆ จะถูกเรียงลำดับในหนังสือทุกประเภท โดยหนังสือที่ขายดีที่สุดจะเป็นแถวที่ 1

ตอนนี้เราต้องกรองเฉพาะแถวหมายเลข 1 เพื่อให้ได้หนังสือขายดีในแต่ละหมวด

select category, book_name, amount from (
select category, book_name, amount
row_number() over (partition by category order by amount) as sales_rank
from book_sales
) as book_ranked_sales
where sales_rank = 1;

ตัวกรองด้านบนจะแสดงเฉพาะหนังสือขายดีในแต่ละหมวดหมู่ พร้อมด้วยยอดขายหนังสือขายดีแต่ละเล่ม

สถานการณ์ที่ 7: การแบ่งพาร์ติชันเพื่อค้นหาผลรวมสะสมในกลุ่ม

สมมติว่าเราต้องการคำนวณยอดรวมสะสม (ยอดรวมสะสม) ของการขายเมื่อมีการขาย เราต้องการยอดรวมสะสมแยกต่างหากสำหรับทุกผลิตภัณฑ์

เราจะแบ่งพาร์ติชันตาม product_id และจัดเรียงพาร์ติชันตามวันที่

select product_id, date, amount,
sum(amount) over (partition by product_id order by date desc) as running_total
from sales_data;

คอลัมน์การแบ่งพาร์ติชัน = product_id

คอลัมน์การเรียงลำดับ = วันที่

ฟังก์ชันการจัดอันดับ = sum()

สถานการณ์ที่ 8: การแบ่งพาร์ติชันเพื่อเปรียบเทียบค่าภายในกลุ่ม

สมมติว่าเราต้องการเปรียบเทียบเงินเดือนของพนักงานทุกคนกับเงินเดือนเฉลี่ยของแผนกของเขา

ดังนั้นเราจะแบ่งพนักงานตามแผนกและหาเงินเดือนเฉลี่ยของแต่ละแผนก

คุณสามารถลบค่าเฉลี่ยออกจากเงินเดือนของพนักงานแต่ละคนได้อย่างง่ายดาย เพื่อคำนวณว่าเงินเดือนของพนักงานสูงกว่าหรือต่ำกว่าค่าเฉลี่ย

select employee_id, salary, department,
avg(salary) over (partition by department) as avg_dept_sal
from employees;

คอลัมน์การแบ่งพาร์ติชัน = แผนก

คอลัมน์การสั่งซื้อ = ไม่มีการสั่งซื้อ

ฟังก์ชันการจัดอันดับ = avg()

สถานการณ์ที่ 9: การแบ่งพาร์ติชันเพื่อแบ่งผลลัพธ์ออกเป็นกลุ่มเท่าๆ กัน

สมมติว่าเราต้องการแบ่งพนักงานออกเป็น 4 กลุ่มเท่าๆ กัน (หรือเกือบเท่ากัน) ตามเงินเดือนของพวกเขา

ดังนั้นเราจะได้คอลัมน์ลอจิคัลอีกคอลัมน์หนึ่งคือtile_id ซึ่งจะมีรหัสตัวเลขของพนักงานแต่ละกลุ่ม

กลุ่มจะถูกสร้างขึ้นตามเงินเดือน - กลุ่มไทล์แรกจะมีเงินเดือนสูงสุด และอื่นๆ

select employee_id, salary,
ntile(4) over (order by salary desc) as tile_id
from employees;

คอลัมน์การแบ่งพาร์ติชัน = ไม่มีพาร์ติชัน - ตารางทั้งหมดอยู่ในพาร์ติชันเดียวกัน

คอลัมน์การเรียงลำดับ = เงินเดือน

ฟังก์ชันการจัดอันดับ = ntile()

สถานการณ์ที่ 10: การแบ่งพาร์ติชันเพื่อระบุเกาะหรือช่องว่างในข้อมูล

สมมติว่าเรามีคอลัมน์ product_id ตามลำดับ และเราต้องการระบุช่องว่างในคอลัมน์นี้

ดังนั้นเราจะได้คอลัมน์ตรรกะอีกคอลัมน์หนึ่งคือ island_id ซึ่งจะมีตัวเลขเดียวกันหาก product_id เป็นแบบเรียงลำดับ เมื่อมีการระบุตัวแบ่งใน product_id แล้ว island_id จะเพิ่มขึ้น

select product_id,
row_number() over (order by product_id) as row_num,
product_id - row_number() over (order by product_id) as island_id,
from products;

คอลัมน์การแบ่งพาร์ติชัน = ไม่มีพาร์ติชัน - ตารางทั้งหมดอยู่ในพาร์ติชันเดียวกัน

คอลัมน์การสั่งซื้อ = product_id

ฟังก์ชันการจัดอันดับ = row_number()

Group By และ Partition By ใช้เพื่อแก้ไขปัญหาต่างๆ เช่น:

ข้อมูลโดยสรุป: การจัดกลุ่มช่วยให้คุณสามารถรวบรวมข้อมูลและสรุปข้อมูลในทุกกลุ่ม

การวิเคราะห์รูปแบบ: ช่วยในการระบุรูปแบบหรือแนวโน้มภายในชุดย่อยของข้อมูล โดยให้ข้อมูลเชิงลึกในด้านต่างๆ ของชุดข้อมูล

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

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

การวิเคราะห์กลุ่ม: มีประโยชน์ในการวิเคราะห์ตามรุ่น การติดตามและการเปรียบเทียบกลุ่มของเอนทิตีในช่วงเวลาหนึ่ง ฯลฯ
 
 

Hanu ทำงาน บล็อก HelperCodes ซึ่งส่วนใหญ่เกี่ยวข้องกับ แผ่นโกง SQL. ฉันเป็นนักพัฒนาซอฟต์แวร์เต็มรูปแบบและสนใจที่จะสร้างเนื้อหาที่นำมาใช้ซ้ำได้

• เนื้อหาที่ขับเคลื่อนด้วย SEO และการเผยแพร่ประชาสัมพันธ์ รับการขยายวันนี้
• PlatoData.Network Vertical Generative Ai เพิ่มพลังให้กับตัวเอง เข้าถึงได้ที่นี่.
• เพลโตไอสตรีม. Web3 อัจฉริยะ ขยายความรู้ เข้าถึงได้ที่นี่.
• เพลโตESG. คาร์บอน, คลีนเทค, พลังงาน, สิ่งแวดล้อม แสงอาทิตย์, การจัดการของเสีย. เข้าถึงได้ที่นี่.
• เพลโตสุขภาพ เทคโนโลยีชีวภาพและข่าวกรองการทดลองทางคลินิก เข้าถึงได้ที่นี่.
• ที่มา: https://www.kdnuggets.com/sql-group-by-and-partition-by-scenarios-when-and-how-to-combine-data-in-data-science?utm_source=rss&utm_medium=rss&utm_campaign=sql-group-by-and-partition-by-scenarios-when-and-how-to-combine-data-in-data-science