เก่งโค้ดงาน Business Intelligence ตอนที่ 8
บทความตอนที่ผ่านมาผู้เขียนได้พูดถึงการประยุกต์ใช้งานคิวรีภาษาเอ็มกับตารางในเวิร์กชีทของเอ็กซ์เซล เรื่องการใช้คิวรีอื่นเป็นดาต้าซอร์ส การทำคิวรีแบบมีพารามิเตอร์ การเชื่อมต่อข้อมูล การจอยตาราง การหาร้อยละของผลรวม และวิธีหาการโตของยอดขาย ส่วนในตอนนี้จะเป็นเรื่องการประยุกต์ใช้คิวรีภาษาเอ็มเพื่อการประมวลผลที่มีประโยชน์ต่องานบิซิเนสอินเทลลิเจนซ์และเทคนิคที่น่าสนใจอื่น ๆ
หาเลขอันดับของยอดขาย
ในการวิเคราะห์ข้อมูลบ่อยครั้งที่เรามีตัวเลขที่เป็นรายการของค่า โดยที่ค่าต่าง ๆ เหล่านี้เป็นชุดเดียวกัน หากเราสามารถจัดลำดับหรือหาค่าอันดับของรายการค่าเหล่านี้ได้ก็จะเป็นประโยชน์ต่อการวิเคราะห์ ยกตัวอย่างเช่น การจัดลำดับรายการสินต้าตามยอดขาย
| Product | Sales | Product | Sales | Rank | |
| Apples | 10 | Apples | 10 | 5 | |
| Oranges | 30 | Oranges | 30 | 2 | |
| Pears | 30 | Pears | 30 | 2 | |
| Grapes | 20 | Grapes | 20 | 4 | |
| Pineapples | 10 | Pineapples | 10 | 5 | |
| Bananas | 50 | Bananas | 50 | 1 |
ตารางซ้ายชื่อ TiedRanksInput ทำหน้าที่เก็บยอดขายสินค้าประเภทต่าง ๆ คอลัมน์ Product คือรายการสินค้า คอลัมน์ Sales คือยอดขายของสินค้านั้น ๆ สิ่งที่เราต้องการหรือโจทย์คือ “หาเลขอันดับ” (Rank) ของยอดขายของสินค้าแต่ละตัว แล้วนำมาทำเป็นคอลัมน์ใหม่ชื่อ Rank มีผลลัพธ์เป็นอย่างตารางทางขวา สินค้าที่มียอดขายมากที่สุดคือ Bananas มีมูลค่าขาย 50 ถือเป็นอันดับที่ 1 สินค้าที่มียอดขายอันดับรองลงมาคือ Oranges และ Pears เป็นอันดับที่สอง และสินค้าที่มียอดขายต่ำที่สุดคือ Apples และ Pineapples รั้งอันดับที่ห้า
วิธีทำมีโค้ดอย่างในรูป 1 ซึ่งเป็นรายการคำสั่งในภาษาเอ็มเพื่อทำหน้าที่หาเลขอันดับ
- บรรทัด 5-7 ทำหน้าที่โหลดตาราง TiedRanksInput แล้วกำหนดให้อ้างถึงด้วยชื่อ Source
- บรรทัด 10-13 ประกาศฟังก์ชันซึ่งทำหน้าที่คำนวณเลขอันดับ
- บรรทัด 16 ทำหน้าที่กำหนดให้ตัวแปรหรือค่า Custom1 อ้างอิงไปยังแหล่งข้อมูลต้นทางหรือตาราง TiedRanksInput
- บรรทัด 19-22 นำคอลัมน์ชื่อ Rank ที่จะเป็นค่าอันดับใส่เข้าไปในตาราง ค่าในคอลัมน์นี้เกิดจากการนำค่าในคอลัมน์ Sales ส่งไปยังฟังก์ชัน Rank เพื่อคำนวณคือค่าอันดับ
นับจำนวนลูกค้าที่ไม่ซ้ำกัน
บางครั้งเรามีชุดข้อมูลที่มีข้อมูลบางคอลัมน์ซ้ำกัน หากเราสามารถนับจำนวนที่ไม่ซ้ำกันได้ก็จะมีประโยชน์ต่อการนำไปประมวลผลในขั้นตอนอื่น ๆ ยกตัวอย่างเช่นในตาราง DistinctCustomersInput เรามีข้อมูลสามคอลัมน์ คือ Date เก็บวันที่ขาย Customer เก็บชื่อลูกค้า และ Sales เก็บยอดขาย
ในวันที่ 1-Jan-2019 เรามีรายการขายสี่รายการ แต่เป็นรายการขายที่เกิดกับ Chris สองครั้ง นั่นคือมีรายชื่อลูกค้าที่ซ้ำกัน แม้ว่าจะมีรายการขายสี่รายการ แต่ก็มีลูกค้าเพียงสามคน โจทย์ของเราคือต้องการสร้างตารางอย่างทางขวามือ เพื่อแสดงว่าในแต่ละวันมีลูกค้าที่ไม่ซ้ำกันอยู่กี่คน
| Date | Customer | Sales | Date | Distinct Customers | |
| 1-Jan-2019 | Chris | 1 | 1/1/2019 | 3 | |
| 1-Jan-2019 | Helen | 2 | 1/2/2019 | 4 | |
| 1-Jan-2019 | Chris | 2 | 1/3/2019 | 4 | |
| 1-Jan-2019 | Mimi | 4 | |||
| 2-Jan-2019 | Robert | 7 | |||
| 2-Jan-2019 | Chris | 3 | |||
| 2-Jan-2019 | Mimi | 4 | |||
| 2-Jan-2019 | Natasha | 2 | |||
| 3-Jan-2019 | Mimi | 2 | |||
| 3-Jan-2019 | Helen | 6 | |||
| 3-Jan-2019 | Natasha | 4 | |||
| 3-Jan-2019 | Natasha | 3 | |||
| 3-Jan-2019 | Helen | 2 | |||
| 3-Jan-2019 | Rebert | 4 |
วิธีทำมีโค้ดอย่างที่เห็นในรูป 2 ซึ่งเป็นรายการคำสั่งในภาษาเอ็มที่จะนับจำนวนลูกค้าที่ไม่ซ้ำกัน
- บรรทัด 7-9 ทำหน้าที่โหลดตาราง DistinctCustomersInput แล้วกำหนดให้อ้างถึงด้วยชื่อ Source
- บรรทัด 12-17 ทำหน้าที่แปลงชนิดข้อมูลของคอลัมน์ Date ให้เป็นไทป์ date เพื่อจะได้สามารถนำไปใช้ในการคำนวณในขั้นตอนต่อไปโดยใช้ฟังก์ชัน Table.TransformColumnTypes
- บรรทัด 21-26 เรียกหาฟังก์ชัน Table.Distinct เพื่อลบข้อมูลทั้งหมดที่ซ้ำกันในคอลัมน์ Customer ผลลัพธ์ที่ได้คือค่า DuplicatesRemoved ที่เป็นรายชื่อคนโดยไม่ซ้ำ
- บรรทัด 29-35 หาจำนวนนับของแถวในแต่ละวันด้วยการใช้ฟังก์ชัน Table.Group ที่จะจัดข้อมูลออกเป็นกลุ่ม ๆ ตามค่า DuplicatesRemoved
การแปลงคอลัมน์เดียวให้กลายเป็นหลาย ๆ คอลัมน์
ถ้าเรามีตารางอย่างตาราง ListData ซึ่งเป็นตารางที่มีคอลัมน์เดียว คือคอลัมน์ Data ข้อมูลในคอลัมน์นี้ ทุก ๆ สามแถวเป็นข้อมูลกลุ่มเดียวกัน คือข้อมูลของคนหนึ่งคน ประกอบด้วยชื่อ เพศ และสัญชาติ ถ้าเราสามารถแปลงข้อมูลคอลัมน์เดียวนี้ให้กลายเป็นสี่คอลัมน์อย่างตารางทางขวาก็คงจะมีประโยชน์
| Data | CustomerID | Name | Gender | Country | |
| Chris | 0 | Chris | Male | UK | |
| Male | 1 | Bill | Male | USA | |
| UK | 2 | Helen | Female | Australia | |
| Bill | 3 | Mimi | Female | UK | |
| Male | |||||
| USA | |||||
| Helen | |||||
| Female | |||||
| Australia | |||||
| Mimi | |||||
| Female | |||||
| UK |
โค้ดที่เห็นในรูป 3 แสดงวิธีแปลงคอลัมน์เดียวให้กลายเป็นหลาย ๆ คอลัมน์
- บรรทัด 7-8 ทำหน้าที่โหลดตาราง ListData แล้วกำหนดให้อ้างถึงด้วยชื่อ Source
- บรรทัด 11-13 ใส่คอลัมน์ใหม่ชื่อ Index เอาไว้ทำหน้าเก็บเลขดรรชนีโดยใช้ฟังก์ชัน Table.AddIndexColumn
- บรรทัด 15-19 ใส่คอลัมน์ใหม่ชื่อ RowType เอาไว้ทำหน้าที่เก็บตัวเลข 1, 2, 3 เพื่อไว้ใช้ประมวลผลในขั้นตอนถัดไป
- บรรทัด 22-27 ใส่คอลัมน์ใหม่ชื่อ RowTypeText โดยใช้ฟังก์ชัน Table.AddColumn ทำหน้าที่เก็บข้อมูลที่ระบุว่าแถวข้อมูลนั้นเป็น Name, Gender หรือ Country หากข้อมูลในคอลัมน์ RowType เป็น 0 จะใส่คำว่า Name ไว้ แต่ถ้าเป็น 1 จะใส่คำว่า Gender และถ้าเป็น 2 ให้เป็น Country
- บรรทัด 30-33 ใส่คอลัมน์ใหม่ชื่อ CustomerID โดยใช้ฟังก์ชัน Table.AddColumn เอาไว้ทำหน้าเก็บเลขประจำตัวของลูกค้า เป็นเลขที่เราสร้างขึ้นเดี๋ยวนี้ เพื่อใช้ช่วยจัดกลุ่มข้อมูลของแต่ละคน
- บรรทัด 36-41 ลบคอลัมน์ Index และ RowType ที่ไม่ต้องการใช้ในขั้นตอนต่อไป
- บรรทัด 44-52 ทำ Pivot เพื่อให้ข้อมูล Name, Gender และ Country กลายมาเป็นคอลัมน์ แล้วลบคอลัมน์ RowTypeText และ Data ที่ไม่จำเป็นต้องใช้อีกแล้ว
การหาลูกค้าใหม่ ลูกค้าที่หายไป และลูกค้าที่กลับคืนมา
ตารางซ้ายชื่อ ThisYearCustomers ทำหน้าที่เก็บรายชื่อลูกค้าที่ซื้อสินค้าในปีนี้ ตารางกลางชื่อ LastYearCustomers ทำหน้าที่เก็บรายชื่อลูกค้าที่ซื้อสินค้าในปีก่อนหน้านี้ ตารางขวาสุดคือสิ่งที่เราอยากได้ เพราะเป็นผลลัพธ์ของโจทย์การหาลูกค้าที่ใหม่ ที่หายไป และที่กลับคืนมา
โจทย์คือการแบ่งลูกค้าออกเป็นสามกลุ่มได้แก่
- New: พวกที่ซื้อในปีนี้แต่ไม่เคยซื้อเมื่อปีก่อน
- Lost: พวกที่ซื้อในปีที่แล้วแต่ไม่ได้ซื้อในปีนี้
- Return: พวกที่ซื้อทั้งในปีนี้และปีที่ผ่านมา
| Customers This Year | Customers Last Year | Customer | Classification | ||
| Chris | Chris | Chris | Returning | ||
| Natasha | Helen | Natasha | New | ||
| Helen | Mimi | Helen | Returning | ||
| Louisa | Robert | Louisa | New | ||
| Victoria | Barbara | Robert | Returning | ||
| Robert | Victoria | New | |||
| Mimi | Lost | ||||
| Barbara | Lost |
โค้ดที่เห็นในรูป 4 และ 5 แสดงคิวรีสร้างตารางเพื่อแบ่งกลุ่มลูกค้าใหม่ ลูกค้าที่หายไป และลูกค้าที่กลับคืนมา
- บรรทัดที่ 7-11: นำตารางของทั้งสองปีมาผนวกกันโดยใช้ฟังก์ชัน Table.Combine
- บรรทัดที่ 14-23: ผสานคอลัมน์ Customers This Year เข้ากับคอลัมน์ Customers Last Year โดยใช้ฟังก์ชัน Table.CombineColumns
- บรรทัดที่ 26-34: ลบชื่อลูกค้าที่ซ้ำออกด้วยฟังก์ชัน Table.Distinct
- บรรทัดที่ 31-34: เปลี่ยนชื่อคอลัมน์ที่ตอนนี้มีเพียงอันเดียวจาก Merged ไปเป็น Customer โดยใช้ฟังก์ชัน Table.RenameColumns
- บรรทัดที่ 37-42: จอยคิวรีนี้กับตาราง ThisYearCustomers โดยใช้ฟังก์ชัน Table.NestedJoin
- บรรทัดที่ 46-51: หาจำนวนรวมของลูกค้าปีนี้ เครื่องหมาย # และ ” ” ถูกนำมาใช้เพื่อให้สามารถตั้งชื่อค่า (Aggregate NewColumn) ที่มีเคาะวรรคภายในชื่อได้
- บรรทัดที่ 54-57: เปลี่ยนชื่อคอลัมน์จาก Count (Not Blank) of NewColumn.Customers This Year ไปเป็น ThisYear
- บรรทัดที่ 60-65: จอยคิวรีนี้กับตาราง LastYearCustomers
- บรรทัดที่ 69-74: หาจำนวนรวมของลูกค้าปีก่อน
- บรรทัดที่ 77-80: เปลี่ยนชื่อคอลัมน์จาก Count (Not Blank) of NewColumn.Customers Last Year ไปเป็น LastYear
- บรรทัดที่ 84-90: จำแนกกลุ่มของลูกค้าโดยใช้คอลัมน์ ThisYear และ LastYear
- บรรทัดที่ 93-96: ลบคอลัมน์ที่ไม่ต้องการ
บทความตอนนี้พูดถึงการหาเลขอันดับของยอดขาย การนับจำนวนลูกค้าที่ไม่ซ้ำกัน การแปลงคอลัมน์เดียวให้กลายเป็นหลาย ๆ คอลัมน์ การหาลูกค้าใหม่ ลูกค้าที่หายไป และลูกค้าที่กลับคืนมา ในตอนต่อไปจะเป็นเรื่องการประยุกต์ใช้คิวรีภาษาเอ็มเพื่อการประมวลผลที่มีประโยชน์ต่องานบิซิเนสอินเทลลิเจนซ์และเทคนิคต่าง ๆ ที่น่าสนใจ
