วิธีเขียน Query เพื่อ Select Top N ของ Group

สมมติว่าเรามีข้อมูลเป็นตาราง Customer ซึ่งมีฟิลด์ credit เป็นข้อมูลเครดิตลูกค้า และฟิลด์ province ซึ่งบอกจังหวัดของลูกค้า
เราอาจจะรู้วิธีเขียน query เพื่อดึงคนที่มีเครดิตสูงสุด N ลำดับแรกออกมา (Top N)
เราอาจจะรู้วิธีทำ aggregation บางอย่าง เช่นหาค่า Min, Max หรือ Average ของแต่ละกลุ่ม(ในที่นี้คือจังหวัด) ออกมา
แต่ถ้าโจทย์ของเราคือ หา Top N ของแต่ละกลุ่มออกมาล่ะ?

เพื่อให้เห็นภาพ
ขอยกตัวอย่างข้อมูลในตาราง Customer ดังนี้
Sample Data

ลองดูโจทย์แบบง่ายก่อน
ถ้าต้องการหาลูกค้าที่เครดิตสูงสุด 3 ลำดับแรก เราจะเขียนด้วย query ลักษณะนี้

select top 3 * from Customer order by credit desc

และผลที่ได้ก็คือ
Select Top N Result

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

select max(credit), province from Customer group by province

และผลที่ได้ก็คือ
Select Max Group By
ว่าแต่ max ของแต่ละจังหวัด มันคือใครกันล่ะเนี่ย
อันนี้ก็เป็นเรื่องวุ่นๆ ที่ต้องย้อนกลับไป join หาอีก
แต่เราจะไม่จำเป็นต้องพูดถึงมันแล้ว เพราะ query ที่เราจะได้เห็นต่อไปนี้

เฉลยโจทย์ของเรา
ถ้าต้องการหาลูกค้าที่เครดิตสูงสุด N ลำดับแรกของแต่ละจังหวัด ให้เริ่มต้นด้วย query แบบนี้ก่อนครับ

select *,
row_number() over (partition by province order by credit desc) rowno
from Customer

จุดสำคัญของ query ข้างต้นก็คือฟังค์ชั่น ROW_NUMBER ซึ่งเป็นฟังค์ชั่นที่จะให้เลขบรรทัดของผลลัพธ์ออกมา
ตามแต่การจัดกลุ่มข้อมูลหรือการเรียงลำดับที่เรากำหนดไว้ด้วยคำสั่ง OVER, PARTITION BY และ ORDER BY ที่ตามมา
จากตัวอย่างข้างต้นหมายความว่า เรากำลัง Select * จากตาราง Customer พร้อมกับดึงเลข ROW_NUMBER ออกมา
โดยเป็นเลขบรรทัดที่ให้แบ่งกลุ่มด้วยจังหวัด และให้เรียงลำดับด้วยเครดิตจากมากไปน้อย ซึ่งผลที่ได้จะเป็นดังนี้
Select RowNumber Partition By

สังเกตที่ rowno ดีๆ นะครับ จะเห็นว่ามันแสดงตามอันดับของเครดิตและถูกแบ่งตามจังหวัดอย่างเรียบร้อย
แค่นี้เราก็สามารถที่จะ select เอา Top ที่เท่าไหร่ของแต่ละกลุ่มก็ได้แล้ว โดยเลือกเอา rowno ที่ต้องการ
ทำได้โดยเขียน select…where ซ้อน query ข้างต้นเข้าไปอีกทีดังนี้ (สมมติเอา Top แค่ 2 พอ เพราะตัวอย่างมีน้อย)

select * from
  (select *,
   row_number() over (partition by province order by credit desc) rowno
   from Customer) q
where rowno <= 2

และผลสุดท้ายก็คือ
Select Top 2 of Group
ด้วยวิธีนี้ นอกจากเราจะได้ Top N ตามต้องการแล้ว เรายังจะได้ชื่อของ Customer รายนั้นๆ ออกมาด้วย

เกี่ยวกับ Window Function
ROW_NUMBER จัดเป็น Window Function หรือ Set Function ที่จะดำเนินการภายใต้การแบ่งกลุ่มและเรียงลำดับอย่างใดอย่างหนึ่ง
ซึ่งฟังค์ชั่นประเภทนี้ยังมีอีกหลายตัวเช่น RANK, DENSE_RANK, NTILE หรือแม้แต่ Aggregate function อย่าง COUNT, MIN, MAX, AVG
เราลองมาดูฟังค์ชั่นอีกตัวที่น่าสนใจอื่นอีก อย่างเช่น RANK ซึ่งทำงานคล้ายกับ ROW_NUMBER
แต่แทนที่จะให้เลขบรรทัดออกมา มันจะให้ค่า rank ออกมา ค่า rank คืออันดับตามมูลค่าของมัน ไม่ใช่แค่เลขบรรทัด
อย่างเช่น ถ้าสอง record มีมูลค่าเท่ากัน มันจะมีลำดับ rank ที่เท่ากัน แม้มันจะมี row number ที่ไม่เหมือนกัน
อย่างไรก็ตามฟังค์ชั่น RANK อาจไม่ได้ให้เลขอันดับที่ต่อเนื่อง เพราะจะมีการเว้นว่างลำดับไว้สำหรับ record ที่มูลค่าเท่ากันนั้น
หากเราต้องการเลขอันดับที่ต่อเนื่องให้ใช้ฟังค์ชั่น DENSE_RANK แทน
อ่านถึงตรงนี้ถ้านึกภาพไม่ออก ก็ไม่ต้องนึกครับ ดูภาพประกอบด้านล่างดีกว่า
จะแสดงให้เห็นความต่างของฟังค์ชั่น row_number, rank และ dense_rank จากตัวอย่างข้อมูลข้างต้น
ROW_NUMBER, RANK, DENSE_RANK

สรุปครับ
จากโจทย์ที่ตั้งไว้ เราสามารถแก้ได้โดยใช้ Window Function ซึ่งมันสามารถหาเลขลำดับบางอย่างภายในกลุ่มข้อมูลออกมาให้ได้
รูปแบบทั่วไปของ query คือ

select *,
WFUNCTION over (partition by GROUP_FIELDS order by ORDER_FIELDS) n
from SOURCE

โดยที่ SOURCE คือตัวข้อมูล
WFUNCTION หมายถึง Window Function เช่น ROW_NUMBER(), RANK() ฯลฯ
GROUP_FIELDS คือรายการฟิลด์ที่จะแบ่งกลุ่ม เขียนเหมือนตอนที่เราจะ group by นั่นแหละ, สามารถแบ่งด้วยหลายฟิลด์ก็ได้
ORDER_FIELDS คือรายการฟิลด์ที่ใช้เรียงลำดับ, สามารถเรียงด้วยหลายฟิลด์ก็ได้, ใส่ ASC หรือ DESC ได้เหมือนคำสั่ง order by ปกติ
เราไม่จำเป็นต้องใส่ทั้ง partition by และ order by อาจจะใส่แค่อันใดอันหนึ่ง แล้วแต่ลักษณะโจทย์ว่าต้องการแบ่งกลุ่มหรือเรียงลำดับหรือไม่
n เป็นชื่อ alias ของผลลัพธ์ จะตั้งชื่ออย่างไรก็ได้
เมื่อเราได้เลขลำดับ n ของแต่ละกลุ่มออกมาแล้ว ทีนี้จะเอาไปหา Top N หรือพลิกแพลงยังไง ก็แล้วแต่จะ query ออกมาครับ

Note ส่งท้าย
– Window Function เป็น feature ตามมาตรฐาน SQL:2003
– MS SQL Server version 2005 ขึ้นไปจึงจะ support feature นี้
– Database ยี่ห้ออื่น ผมยังไม่เคยลอง แต่ถ้ายี่ห้อนั้นๆ สนับสนุน SQL:2003 ก็น่าจะใช้ได้เหมือนกัน

One Response to “วิธีเขียน Query เพื่อ Select Top N ของ Group”

  1. Mag Says:

    ขอบคุณครับ ได้ความรู้มากๆ


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: