เก่งโค้ดงาน Business Intelligence ตอนที่ 8

เก่งโค้ดงาน Business Intelligence ตอนที่ 8

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

หาเลขอันดับของยอดขาย

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

 

ตารางซ้ายชื่อ 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 สองครั้ง นั่นคือมีรายชื่อลูกค้าที่ซ้ำกัน แม้ว่าจะมีรายการขายสี่รายการ แต่ก็มีลูกค้าเพียงสามคน โจทย์ของเราคือต้องการสร้างตารางอย่างทางขวามือ เพื่อแสดงว่าในแต่ละวันมีลูกค้าที่ไม่ซ้ำกันอยู่กี่คน

วิธีทำมีโค้ดอย่างที่เห็นในรูป 2 ซึ่งเป็นรายการคำสั่งในภาษาเอ็มที่จะนับจำนวนลูกค้าที่ไม่ซ้ำกัน

• บรรทัด 7-9 ทำหน้าที่โหลดตาราง DistinctCustomersInput แล้วกำหนดให้อ้างถึงด้วยชื่อ Source

• บรรทัด 12-17 ทำหน้าที่แปลงชนิดข้อมูลของคอลัมน์ Date ให้เป็นไทป์ date เพื่อจะได้สามารถนำไปใช้ในการคำนวณในขั้นตอนต่อไปโดยใช้ฟังก์ชัน Table.TransformColumnTypes

• บรรทัด 21-26 เรียกหาฟังก์ชัน Table.Distinct เพื่อลบข้อมูลทั้งหมดที่ซ้ำกันในคอลัมน์ Customer ผลลัพธ์ที่ได้คือค่า DuplicatesRemoved ที่เป็นรายชื่อคนโดยไม่ซ้ำ

• บรรทัด 29-35 หาจำนวนนับของแถวในแต่ละวันด้วยการใช้ฟังก์ชัน Table.Group ที่จะจัดข้อมูลออกเป็นกลุ่ม  ๆ ตามค่า DuplicatesRemoved

การแปลงคอลัมน์เดียวให้กลายเป็นหลาย ๆ คอลัมน์

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

โค้ดที่เห็นในรูป 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: พวกที่ซื้อทั้งในปีนี้และปีที่ผ่านมา

โค้ดที่เห็นในรูป 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:  ลบคอลัมน์ที่ไม่ต้องการ

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