เก่งโค้ดงาน Business Intelligence ตอนที่ 7
ทักษะ (ระบุได้หลายทักษะ)
เก่งโค้ดงาน Business Intelligence ตอนที่ 7
การใช้คิวรีอื่นเป็นดาต้าซอร์ส
ในภาษาเอ็มเราสามารถเขียนคิวรีที่อ้างถึงคิวรีอื่นเป็นซอร์สได้ ยกตัวอย่างเช่น สมมติว่าเรามีเวิร์กชีตในโปรเกรมเอ็กซ์เซลซึ่งมีตารางอยู่สองอัน ตารางชื่อ Sales และ ตารางชื่อ Product
| Month | Product | Sales |
|---|---|---|
| January | Apples | 1 |
| February | Apples | 2 |
| March | Apples | 3 |
| January | Oranges | 10 |
| February | Oranges | 12 |
| March | Pears | 14 |
| January | Pears | 20 |
| February | Pears | 23 |
| March | Pears | 26 |
| Product |
|---|
| apples |
เราสามารถเขียนคิวรีโดยใช้ตารางขวาเป็นซอร์สได้อย่างที่เห็นในรูปที่ 1 บรรทัดที่ 3-7 ซึ่งเป็นคิวรีชื่อ ProductQuery และเราสามารถเขียนโค้ดโดยใช้คิวรีนี้เป็นซอร์สได้ตามบรรทัดที่ 9-12 ผลลัพธ์คือคิวรีบรรทัดที่ 9-12 จะมีซอร์สเป็นข้อมูลจากสองตาราง
คิวรีแบบมีพารามิเตอร์
ในกรณีที่เรามีดาต้าซอร์สเป็นข้อมูลจำนวนมากเราควรคัดกรองเอาเฉพาะข้อมูลที่ต้องการก่อนที่จะโหลดมาใส่ในดาต้าโมเดลในโปรเกรมเอ็กซ์เซล ยกตัวอย่างเช่นถ้าเรามีข้อมูลอย่างในตาราง Sales แต่เป็นข้อมูลจำนวนมาก เราอาจเขียนคิวรีคัดกรองให้โหลดเฉพาะสินค้าบางประเภท เช่นโหลดเฉพาะ Apples แต่ถ้าภายหลังเราต้องการโหลดสินค้าอื่น ๆ จะต้องเขียนคิวรีใหม่ หรือนำคิวรีเดิมมาแก้ไข ซึ่งจะไม่สะดวก ทางที่ดีควรเขียนคิวรีให้ระบุได้ว่าต้องการสินค้าแบบใด นั่นคือคิวรีแบบมีพารามิเตอร์
คิวรีบรรทัดที่ 14-20 คัดกรองเอาเฉพาะสินค้าที่เป็น Apples เมื่อรันแล้วจะได้ผลลัพธ์ดังนี้
| Month | Product | Sales |
|---|---|---|
| January | Apples | 1 |
| February | Apples | 2 |
| March | Apples | 3 |
ส่วนที่เราควรจะเปลี่ยนให้กลายเป็นพารามิเตอร์คือคำว่า Apples ในบรรทัดที่ 18 คิวรีเป็นอย่างที่เห็นในบรรทัด 22-28 ผู้ใช้สามารถป้อนอินพุตพารามิเตอร์ได้โดยใส่ในเซลศูนย์ของตาราง Product
ต่อไปมาดูการเขียนโค้ดภาษาเอ็มเพื่อคิวรีข้อมูลจากฐานข้อมูล การคิวรีข้อมูลจากฐานข้อมูลคล้ายการคิวรีข้อมูลจากตารางในโปรเกรมเอ็กซ์เซลที่เห็นในตัวอย่างโค้ดในหัวข้อก่อนหน้านี้ ในรูปที่ 2 โค้ดบรรทัดที่ 6-10 คือคิวรีชื่อ WeekDay ทำหน้าที่คิวรีข้อมูลมาจากตาราง WeekDay ซึ่งเป็นตารางในโปรแกรมเอ็กซ์เซล ตารางนี้เหมือนตาราง Product ในหัวข้อก่อนหน้านี้ คือมีหนึ่งแถวหนึ่งคอลัมน์ ภายในเซลข้อมูลที่มีอยู่เพียงช่องเดียวของมันเราจะใส่ชื่อวันในสัปดาห์ เราจะใช้คิวรี WeekDay ทำหน้าที่เป็นพารามิเตอร์ให้แก่คิวรีถัดไป
ส่วนโค้ดบรรทัดที่ 14-25 ทำหน้าที่คิวรีข้อมูลจากตาราง DimDate ที่อยู่ในฐานข้อมูลตัวอย่าง Adventure Works DW ของโปรแกรม Microsoft SQL Server บรรทัดที่ 16 ทำหน้าที่เชื่อมต่อกับดาต้าเบสเซอฟเวอร์และฐานข้อมูล Adventure Works DW บรรทัด 19 แสดงวิธีชื่อมต่อกับตาราง DimDate บรรทัดที่ 23-24 ทำหน้าที่คัดกรองแถวข้อมูลในตาราง DimDate โดยใช้ WeekDay เป็นตัวคัดกรอง ผลลัพธ์ที่ได้คือเราจะได้แถวข้อมูลทั้งหมดในตาราง DimDate
ที่มีค่าในคอลัมน์ EnglishDayNameOfWeek ตรงกับค่าในคิวรี WeekDay
โค้ดบรรทัดที่ 30-37 คือตัวอย่างการเขียนคิวรีภาษาเอ็มเพื่อเชื่อมต่อกับซีค่วลเซอฟเวอร์แล้วส่งคิวรีภาษา เอส-คิว-แอล ไปยังเซอฟเวอร์เพื่อทำงานเหมือนโค้ดตัวอย่างก่อนหน้านี้ แตกต่างกันที่ในตัวอย่างก่อนหน้านี้เราคิวรีด้วยภาษาเอ็ม แต่ในโค้ดนี้เราคิวรีข้อมูลด้วยภาษา เอส-คิว-แอล และใส่โค้ดภาษา เอส-คิว-แอล ฝังไว้ภายในคิวรีภาษาเอ็มโดยตรง เมื่อรันคิวรีนี้จะมีผลให้ข้อความว่า “Permission is required to run this native database query” และมีปุ่ม Edit Permission ปรากฏขึ้น สาเหตุที่มีข้อความนี้ปรากฏขึ้นเพราะคิวรีภาษา เอส-คิว-แอล บรรทัดที่ 33-34 มีการอ้างถึงดาต้าซอร์สภายนอกภายในบรรทัดคำสั่งเดียวกัน ซึ่งอาจมีผลต่อความปลอดภัย คิวรีนี้จึงไม่สามารถทำงานได้
วิธีแก้ไขปัญหานี้ทำได้โดยแบ่งการทำงานออกเป็นสองบรรทัดคำสั่งอย่างที่เห็นในรูปที่ 3 บรรทัดที่ 6 เป็นคำสั่งบรรทัดแรก ทำหน้าที่อ่านค่า WeekDay จากเซลที่กำหนด บรรทัดที่ 7-11 เป็นคำสั่งบรรทัดที่สอง เมื่อแยกออกเป็นสองคิวรีเช่นนี้จะมีผลให้เมื่อรันแล้วไม่แสดงข้อความว่า “Permission is required to run this native database query” และคิวรีสามารถทำงานได้ โดยมีข้อแม้ว่าเวิร์กบุ๊คและซีค่วลเซอฟเวอร์จะต้องถูกกำหนดค่าความปลอดภัยไว้ในระดับพับลิกหรือระดับองค์กรตรงกันทั้งคู่
การเชื่อมต่อข้อมูล
การเขียนโค้ดภาษาเอ็มเพื่อนำข้อมูลในดาต้าเซตหลาย ๆ อันมาเชื่อมต่อกัน มีโค้ดที่เรียบง่ายตรงไปตรงมา ในบทความตอนที่ผ่านมาทั้งหมด ผู้เขียนใช้วิธีเรียกฟังก์ชัน Table.Combine() ฟังก์ชันนี้รับพารามิเตอร์เพียงตัวเดียว เป็นลิสต์ของตารางต่าง ๆ ที่ต้องการนำข้อมูลมาต่อกัน ยกตัวอย่างเช่นรูปที่ 4 บรรทัดที่ 3 แสดงการนำตาราง Apple, Oranges และ Pares มาต่อกัน โปรสังเกตุว่ารายชื่อเหล่านี้มีภาวะเป็นลิสต์ คืออยู่ภายในวงเล็บปีกกา
ฟังก์ชัน Table.Combine() จะไม่แจ้งแออเรอร์หากตารางสองอันที่นำมาต่อกันมีรายการคอลัมน์ไม่ตรงกัน แต่มันจะนำสองตารางมาผสมกันโดยเพิ่มคอลัมน์และแถวที่ต่างกันเข้าด้วยกันเป็นตารางใหม่ ยกตัวอย่างเช่นเรามีตารางชื่อ Apple (ตารางบนสุด) และตารางชื่อ AppleProfit (ตารางกลาง) เมื่อรันโค้ดบรรทัดที่ 5 จะได้ผลลัพธ์เป็นตารางอันล่างสุด
| Month | Product | Sales |
|---|---|---|
| January | Apples | 1 |
| February | Apples | 2 |
| March | Apples | 3 |
| Month | Product | Profit |
|---|---|---|
| January | Apples | 100 |
| February | Apples | 200 |
| March | Apples | 300 |
| Month | Product | Sales | Profit |
|---|---|---|---|
| January | Apples | 1 | null |
| February | Apples | 2 | null |
| March | Apples | 3 | null |
| January | Apples | null | 100 |
| February | Apples | null | 200 |
| March | Apples | null | 300 |
จะเห็นว่าตาราง Apple และตาราง AppleProfit มีคอลัมน์ Month และ Product เหมือนกันทั้งคู่ แต่ตราง Apple มีคอลัมน์ Sales ขณะที่ตาราง AppleProfit ไม่มีคอลัมน์นี้ และในทางกลับกัน ตาราง AppleProfit มีคอลัมน์ Profit ขณะที่ตาราง Apple ไม่มีคอลัมน์นี้
การจอยตาราง
ภาษาเอ็มมีฟังก์ชันที่ใช้สำหรับการเชื่อมตารางอยู่หลายฟังก์ชัน เช่นฟังก์ชัน Table.NestedJoin() ซึ่งมีฟังก์ชันซิกเนเจอร์อย่างที่เห็นในบรรทัดที่ 7-12 และฟังก์ชัน Table.Join() ซึ่งมีฟังก์ชันซิกเนเจอร์อย่างที่เห็นในบรรทัดที่ 20-25 บรรทัดที่ 14-17 คือตัวอย่างคิวรีการทำอินเนอร์จอยตาราง Apples และ ApplesProjft
พารามิเตอร์ตัวสุดท้าย (ขวาสุด) ของฟังก์ชัน Table.NestedJoin() คือ JoinKind ทำหน้าที่กำหนดลักษณะของการจอยซึ่งมีตัวเลือกดังต่อไปนี้
Inner: เอาแถวที่ตรงเงื่อนไขทั้งสองฝั่ง
LeftOuter: เอาแถวที่ตรงเงื่อนไขที่ระบุโดยพารามิเตอร์ตัวที่สาม
RightOuter: เอาแถวจากตารางที่ระบุโดยพารามิเตอร์ตัวที่สาม ที่ตรงเงื่อนไขที่ระบุโดยพารามิเตอร์ตัวแรก
FullOuter: เอาทุกแถวจากทั้งสองตาราง
LeftAni: เอาเฉพาะแถวที่ไม่ซ้ำกับในตารางซ้าย
RightAnit: เอาเฉพาะแถวที่ไม่ซ้ำกับในตารางขวา
การหาร้อยละของผลรวม
หากเรามีตารางแสดงยอดขายของสินค้าแต่ละประเภทอย่างที่เห็นข้างล่าง (ชื่อตาราง PercentageShareOfTotalInput) และเราต้องการเพิ่มคอลัมน์ Share ซึ่งทำหน้าที่แสดงผลรวม (Grand Total) เป็นอัตราร้อยละ เราสามารถเขียนเป็นคิวรีภาษาเอ็มได้อย่างที่เห็นในรูปที่ 5
| Product | Sales |
| Apples | 5 |
| Oranges | 7 |
| Pears | 8 |
| Grapes | 3 |
| Product | Sales | Share |
| Apples | 5 | 0.217391304 |
| Oranges | 7 | 0.304347826 |
| Pears | 8 | 0.347826087 |
| Grapes | 3 | 0.130434783 |
บรรทัด 5-6 โหลดข้อมูลมาจากตารางในโปรแกรมไมโครซอฟท์เอ็กซ์เซล
บรรทัด 9-10 หายอดรวมของการขายนำไปใส่เป็นตาราง GrandTotal
บรรทัด 11 นำค่าผลรวมจากตาราง GrandTotal มากำหนดให้ตัวแปร GrandTotal
บรรทัด 16 กำหนดให้ตัวแปร Custom1 อ้างไปยังดาต้าซอร์ส (ตาราง PercentageShareOfTotalInput)
บรรทัด 19 แทรกคอลัมน์ชื่อ Share เข้าไปในตาราง GrandTotal ใส่ค่าในแต่ละแถวเป็นผลคำนวณยอดขายหารด้วยยอดรวม
| Date | Sales |
|---|---|
| 1-Jan-2019 | 1 |
| 2-Jan-2019 | 2 |
| 3-Jan-2019 | 2 |
| 4-Jan-2019 | 3 |
| 5-Jan-2019 | 5 |
| 6-Jan-2019 | 7 |
| Date | Sales | PercentageSalesGrowth |
|---|---|---|
| 1/1/2019 | 1 | |
| 1/2/2019 | 2 | 1 |
| 1/3/2019 | 2 | 0 |
| 1/4/2019 | 3 | 0.5 |
| 1/5/2019 | 5 | 0.666666667 |
| 1/6/2019 | 7 | 0.4 |
หาการโตของยอดขาย
หากเรามีตารางแสดงยอดขายของสินค้าในแต่ละวัน ชื่อตาราง PercentageGrowthPrevDayInput และเราต้องการเพิ่มคอลัมน์ PercentageSalesGrowth ซึ่งทำหน้าที่แสดงความเติบโตของยอดขายเป็นอัตราร้อยละ เราสามารถเขียนเป็นคิวรีภาษาเอ็มได้อย่างที่เห็นในรูปที่ 6
โค้ดบรรทัดที่ 5-6: โหลดข้อมูลจากตาราง PercentageGrowthPrevDayInput
โค้ดบรรทัดที่ 9-10: ทำให้คอลัมน์ Date มีชนิดข้อมูลเป็น date
โค้ดบรรทัดที่ 13-15: หาวันที่ของวันก่อนหน้า
โค้ดบรรทัดที่ 19-21: เอาตารางมาจอยตัวเองเพื่อหาค่าของวันก่อนหน้า
โค้ดบรรทัดที่ 24-25: ใส่คอลัมน์ Sales
โค้ดบรรทัดที่ 28-29: เปลี่ยนชื่อคอลัมน์เป็น PreviousDateSales
โค้ดบรรทัดที่ 32-35: คำนวณร้อยละของความเติบโต
โค้ดบรรทัดที่ 38-41: ลบคอลัมน์ PreviousDate และ PreviousDateSales
โค้ดบรรทัดที่ 44-45: เรียงวันที่จากน้อยไปมาก
บทความตอนนี้พูดถึงการประยุกต์ใช้งานคิวรีภาษาเอ็มกับตารางในเวิร์กชีทของเอ็กซ์เซล เรื่องการใช้คิวรีอื่นเป็นดาต้าซอร์ส การทำคิวรีแบบมีพารามิเตอร์ การเชื่อมต่อข้อมูล การจอยตาราง การหาร้อยละของผลรวม และหาการโตของยอดขาย ในตอนต่อไปจะเป็นเรื่องการประยุกต์ใช้คิวรีภาษาเอ็มเพื่อการประมวลผลที่มีประโยชน์ต่องานบิซิเนสอินเทลลิเจนซ์และเทคนิคต่าง ๆ ที่น่าสนใจ