เก่งโค้ดงาน Business Intelligence ตอนที่ 6
ทักษะ (ระบุได้หลายทักษะ)
เก่งโค้ดงาน Business Intelligence ตอนที่ 6
พารามิเตอร์ตัวที่สี่
บทความตอนที่ผ่านมาเราดูพารามิเตอร์สามตัวแรกของฟังก์ชัน Table.Group() ไปแล้ว ในหัวข้อนี้มาดูหน้าที่ของพารามิเตอร์ตัวที่สี่กันบ้าง พารามิเตอร์ตัวที่สี่ของฟังก์ชัน Table.Group() ใช้ทำหน้าที่กำหนดวิธีเรียกข้อมูล ถ้าเราไม่กำหนดอะไรจะมีค่าโดยปริยายเป็น GroupKind.Global ซึ่งมีความหมายว่า เมื่อเราจัดกลุ่มคอลัมน์ ลำดับการเรียงข้อมูลจะไม่มีผล ทุกแถวที่สัมพันธ์กับค่าในคอลัมน์จะถูกรวมเข้าด้วยกัน
หากเรากำหนดให้พารามิเตอร์ตัวที่สี่ของฟังก์ชัน Table.Group() เป็นแบบ GroupKind.Local ซึ่งมีความหมายว่า เมื่อเราจัดกลุ่มคอลัมน์ ลำดับการเรียงข้อมูลจะมีผล โดยจะจัดกลุ่มเพียงเฉพาะย่านที่มีค่าต่อเนื่องกันเท่านั้น รูปที่ 1 เป็นตัวอย่างโค้ดแสดงการใช้ Table.Group() เพื่อหาจำนวนแถวข้อมูลที่จัดกลุ่มโดยคอลัมน์ WeekdayOrWeekend บรรทัดที่ 10 เราใส่พารามิเตอร์ตัวที่สี่เป็น GroupKind.Global ที่แม้ไม่ใส่ก็ได้ผลอย่างเดียวกัน เพราะเป็นค่าปริยาย แต่ก็ใส่ไว้เพื่อความชัดเจนได้เหมือนกัน
นี่คือตารางแสดงยอดขายเรียงตามวันที่ มีคอลัมน์ WeekdayOrWeekend เก็บค่าที่บอกว่าวันไหนเป็นวันทำงาน วันไหนเป็นวันหยุด
| Date | WeekdayOrWeekend | Sales | Units |
|---|---|---|---|
| 1/1/2019 | Weekday | 5 | 2 |
| 1/2/2019 | Weekday | 4 | 1 |
| 1/3/2019 | Weekday | 2 | 1 |
| 1/4/2014 | Weekend | 6 | 1 |
| 1/5/2019 | Weekend | 8 | 5 |
| 1/6/2019 | Weekday | 6 | 3 |
| 1/7/2019 | Weekday | 4 | 2 |
| 1/8/2019 | Weekday | 5 | 1 |
| 1/9/2019 | Weekday | 5 | 2 |
| 1/10/2019 | Weekday | 2 | 3 |
| 1/11/2019 | Weekend | 1 | 1 |
| 1/12/2019 | Weekend | 3 | 1 |
เมื่อรันนิจน์จะได้ผลลัพธ์เป็นตารางที่มีสองคอลัมน์ สองแถว มีค่า 8 และ 4 ตามลำดับดังนี้
| WeekdayOrWeekend | Count of Rows |
|---|---|
| Weekday | 8 |
| Weekend | 4 |
ในกรณีที่เราไม่เอา GroupKind.Global แต่จะเอาแบบ GroupKind.Local โค้ดจะเป็นอย่างบรรทัดที่ 13-21 จะเห็นว่าโค้ดเหมือนเดิมทุกอย่าง ยกเว้นพารามิเตอร์ตัวที่สี่ ที่เปลี่ยนจาก GroupKind.Global ไปเป็น GroupKind.Local (ดูบรรทัด 28) ผลลัพธ์การทำงานจะเป็นอย่างที่เห็นในตารางข้างล่าง
| WeekdayOrWeekend | Count of Rows |
|---|---|
| Weekday | 3 |
| Weekend | 2 |
| Weekday | 5 |
| Weekend | 2 |
สาเหตุที่ตารางนี้มีสี่แถวแทนที่จะเป็นสองแถวเหมือนอย่างตารางก่อนหน้านี่ เนื่องจากการทำงานของฟังก์ชัน Table.Group() จะทำให้ค่ารวมลำดับแถวที่มีค่าเดียวกันในคอลัมน์ WeekdayOrWeekend ซึ่งแบ่งออกได้สี่กลุ่ม คือ กลุ่มแรก สามแถวแรกของตารางต้นทางเป็นวันทำงาน กลุ่มที่สอง สองแถวถัดไปเป็นวันหยุด กลุ่มที่สาม ห้าแถวถัดไปเป็นวันทำงาน และกลุ่มสุดท้าย คือสองแถวสุดท้ายที่เป็นวันหยุด
การเรียงข้อมูลในตาราง
แถวข้อมูลในตารางก็เหมือนลิสต์ที่สืบคุณสมบัติการเรียงข้อมูลแม้ว่าลำดับของแถวต่าง ๆ จะเป็นลำดับที่มาจากแหล่งข้อมูลหรือดาต้าซอร์สก็ตาม ตัวเลือกในการเรียงข้อมูลในตารางคล้ายการเรียงข้อมูลในลิสต์มาก ดังนั้นฟังก์ชัน Table.ReverseRows() จะเรียงข้อมูลกลับหลังและ Table.Sort() จะเรียงข้อมูลแถวในตารางตามลำดับจากมากไปน้อยหรือน้อยไปมาก ตามแต่ข้อมูลในคอลัมน์ใดคอลัมน์หนึ่ง
จากข้อมูลใน “ตารางแสดงยอดขายเรียงตามวันที่” อันเดิม เราสามารถใช้นิพจน์เรียงข้อมูลอย่างที่เห็นในบรรทัดที่ 23-29 โดยเราจะเรียงคอลัมน์ WeekdayOrWeekend ตามลำดับจากมากไปน้อย (Descending) และจะเรียงคอลัมน์ Sales ตามลำดับน้อยไปมาก (Ascending) โปรดสังเกตพารามิเตอร์ตัวที่สอง ที่เป็น “ลิสต์ของลิสต์” ใช้เพื่อส่งค่าร่วมกันของคอลัมน์และวิธีเรียงข้อมูล ผลลัพธ์ของการทำงานเป็นดังนี้
| Date | WeekdayOrWeekend | Sales | Units |
|---|---|---|---|
| 01/11/19 | Weekend | 1 | 1 |
| 01/12/19 | Weekend | 3 | 1 |
| 01/04/14 | Weekend | 6 | 1 |
| 01/05/19 | Weekend | 8 | 5 |
| 01/10/19 | Weekday | 2 | 3 |
| 01/03/19 | Weekday | 2 | 1 |
| 01/02/19 | Weekday | 4 | 1 |
| 01/07/19 | Weekday | 4 | 2 |
| 01/09/19 | Weekday | 5 | 2 |
| 01/01/19 | Weekday | 5 | 2 |
| 01/08/19 | Weekday | 5 | 1 |
| 01/06/19 | Weekday | 6 | 3 |
การคัดกรองข้อมูลในตาราง
ฟังก์ชันซึ่งทำหน้าที่คัดกรองข้อมูลในตาราง คล้ายฟังก์ชันเพื่อคัดกรองข้อมูลในลิสต์ ฟังก์ชัน Table.First() และฟังก์ชัน Table.Last() ให้ค่าแถวแรกและแถวสุดท้ายของตาราง ขณะที่ฟังก์ชัน Table.SelectRows() ทำงานคล้ายฟังก์ชัน List.Select() แม้ว่าเราจะสามารถอ้างถึงค่าในคอลัมน์โดยใช้พารามิเตอร์ตัวที่สองได้โดยไม่ต้องใช้คำสั่ง _notation
ยกตัวอย่างเช่น จากข้อมูลใน “ตารางแสดงยอดขายเรียงตามวันที่” อันเดิม เราสามารถใช้นิพจน์ในภาพที่ 2 บรรทัด 3-7 เพื่อเอาแถวข้อมูลทั้งหมดที่มีค่าในคอลัมน์ Sales น้อยกว่า 6 ออกโดยในกรณีนี้ เราจะใช้ [Sales] ทำหน้าที่อ้างถึงค่าของแถวในคอลัมน์ Sales แล้วให้ฟังก์ชันวนค่าตั้งแต่แถวแรกไปจนครบ ผลลัพธ์การทำงานเป็นดังนี้
| Date | WeekdayOrWeekend | Sales | Units |
|---|---|---|---|
| 1/4/2014 | Weekend | 6 | 1 |
| 1/5/2019 | Weekend | 8 | 5 |
| 1/6/2019 | Weekday | 6 | 3 |
การทำ Pivot และ Unpivot ตาราง
เราสามารถ unpivot ตารางได้โดยใช้ฟังก์ชัน Table.UnPivot() ซึ่งทำหน้าที่ unpivot ตามรายการคอลัมน์ที่กำหนด และมีฟังก์ชัน Table.UnpivotOtherColumns() ซึ่งจะ unpivot ทุกคอลัมน์ ยกเว้นคอลัมน์ที่ระบุ ซิกเนเจอร์ของฟังก์ชัน Table.Unpivot เป็นอย่างที่เห็นในรูปที่ 2 บรรทัดที่ 9-15 ส่วนซิกเนเจอร์ของฟังก์ชัน Table.UnpivotOtherColumns เป็นอย่างที่เห็นในรูปที่ 2 บรรทัดที่ 17-23
หากเราต้องการทำ UnpivotOtherColumns กับ “ตารางแสดงยอดขายเรียงตามวันที่” อันเดิมโดยใช้นิพจน์ในบรรทัด 25-30 จะได้ผลลัพธ์ดังนี้
| Date | WeekdayOrWeekend | Attribute | Value |
|---|---|---|---|
| 01/01/19 | Weekday | Sales | 5 |
| 01/01/19 | Weekday | Units | 2 |
| 01/02/19 | Weekday | Sales | 4 |
| 01/02/19 | Weekday | Units | 1 |
| 01/03/19 | Weekday | Sales | 2 |
| 01/03/19 | Weekday | Units | 1 |
| 01/04/14 | Weekend | Sales | 6 |
| 01/04/14 | Weekend | Units | 1 |
| 01/05/19 | Weekend | Sales | 8 |
| 01/05/19 | Weekend | Units | 5 |
| 01/06/19 | Weekday | Sales | 6 |
| 01/06/19 | Weekday | Units | 3 |
| 01/07/19 | Weekday | Sales | 4 |
| 01/07/19 | Weekday | Units | 2 |
| 01/08/19 | Weekday | Sales | 5 |
| 01/08/19 | Weekday | Units | 1 |
| 01/09/19 | Weekday | Sales | 5 |
| 01/09/19 | Weekday | Units | 2 |
| 01/10/19 | Weekday | Sales | 2 |
| 01/10/19 | Weekday | Units | 3 |
| 01/11/19 | Weekend | Sales | 1 |
| 01/11/19 | Weekend | Units | 1 |
| 01/12/19 | Weekend | Sales | 3 |
| 01/12/19 | Weekend | Units | 1 |
สิ่งที่เกิดขึ้นคือแทนที่จะมีคอลัมน์ Sales และ Units ตารางใหม่กลายเป็นมีคอลัมน์ Attribute ซึ่งมีข้อมูลภายในเป็นข้อมูลจาก คอลัมน์ Sales และ Units เรียงสลับกันจนครบทุกแถว
การทำ Pivot โดยทั่วไปแล้วสามารถทำได้โดยใช้ตัวเลือกคำสั่งต่าง ๆ ในโปรแกรม Excel แต่การการทำ Pivot บางอย่างทำได้ด้วยการเขียนโค้ดเท่านั้น ยกตัวอย่างเช่น การทำที่ตรงกันข้ามกับตัวอย่างที่ผ่านมาคือ Pivot ให้แถวกลายเป็นคอลัมน์ สามารถทำได้โดยใช้ฟังก์ชัน Table.Pivot() ที่มีซิกเนอร์เจอร์อย่างที่เห็นในรูปที่ 3 บรรทัด 3-10
โดยใช้ “ตารางแสดงยอดขายเรียงตามวันที่” อันเดิม เราสามารถ Pivot ตารางนี้ให้คอลัมน์ Sales ที่เดิมมีอยู่อันเดียว แยกเป็นสองคอลัมน์ โดยใช้นิพจน์ในบรรทัด 12-19 จะได้ผลลัพธ์ดังนี้
| Date | Units | Weekday | Weekend |
|---|---|---|---|
| 01/04/14 | 1 | 6 | |
| 01/01/19 | 2 | 5 | |
| 01/02/19 | 1 | 4 | |
| 01/03/19 | 1 | 2 | |
| 01/05/19 | 5 | 8 | |
| 01/06/19 | 3 | 6 | |
| 01/07/19 | 2 | 4 | |
| 01/08/19 | 1 | 5 | |
| 01/09/19 | 2 | 5 | |
| 01/10/19 | 3 | 2 | |
| 01/11/19 | 1 | 1 | |
| 01/12/19 | 1 | 3 |
โปรดสังเกตว่าคอลัมน์ Units ไม่ได้รับผลกระทบจากการทำ Pivot และเราจำเป็นต้องนำค่ารายการในลิสต์ของคอลัมน์ WeekdayOrWeekend มาป้อนให้แทนที่จะเขียนเป็นฮาร์ดโค้ด หรือจะเขียนโดยใช้ร่วมกับฟังก์ชัน Table.Column และฟังก์ชัน List.Distinct อย่างที่เห็นในบรรทัดที่ 21-28 ก็ได้เช่นกัน
พารามิเตอร์ตัวสุดท้ายของฟังก์ชัน Table.Pivot() ที่เห็นในตัวอย่างนี้คือ List.Sum (ดูบรรทัดที่ 18,27) จะทำงานเมื่อการ Pivot ต้องใช้การหาผลรวมใด ๆ แต่ในตัวอย่างนี้ไม่มีการหาผลรวมจึงไม่จำเป็น และจะไม่ใส่ก็ได้ แต่ในกรณีที่คอลัมน์ WeekdayOrWeekend กับคอลัมน์ Date มีข้อมูลหลายแถวผสมกัน จำเป็นต้องรวมค่าของแถว เพราะตารางผลลัพธ์มีข้อมูลแค่แถวเดียวต่อหนึ่งวัน
บทความตอนนี้พูดถึงการประยุกต์ใช้งานคิวรีภาษาเอ็มกับตารางในเวิร์กชีทของเอ็กซ์เซล เรื่องการจัดกลุ่มข้อมูลในตาราง ใช้งานฟังก์ชัน Table.Group() ต่อจากตอนที่แล้ว และมีเรื่องการเรียงข้อมูลโดยใช้ ฟังก์ชัน Table.Sort() และฟังก์ชัน Table.ReverseRows() ที่เรียงข้อมูลกลับหลัง การทำ Pivot และ Unpivot ในตอนต่อไปจะเป็นเรื่องการประยุกต์ใช้คิวรีภาษาเอ็มเพื่อการประมวลผลที่มีประโยชน์ต่องานบิซิเนสอินเทลลิเจนซ์ในระดับเข้มข้นขึ้น