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

การใช้คิวรีอื่นเป็นดาต้าซอร์ส

ในภาษาเอ็มเราสามารถเขียนคิวรีที่อ้างถึงคิวรีอื่นเป็นซอร์สได้ ยกตัวอย่างเช่น สมมติว่าเรามีเวิร์กชีตในโปรเกรมเอ็กซ์เซลซึ่งมีตารางอยู่สองอัน ตารางชื่อ Sales และ ตารางชื่อ Product

เราสามารถเขียนคิวรีโดยใช้ตารางขวาเป็นซอร์สได้อย่างที่เห็นในรูปที่ 1 บรรทัดที่ 3-7 ซึ่งเป็นคิวรีชื่อ ProductQuery และเราสามารถเขียนโค้ดโดยใช้คิวรีนี้เป็นซอร์สได้ตามบรรทัดที่ 9-12 ผลลัพธ์คือคิวรีบรรทัดที่ 9-12 จะมีซอร์สเป็นข้อมูลจากสองตาราง

คิวรีแบบมีพารามิเตอร์

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

คิวรีบรรทัดที่ 14-20 คัดกรองเอาเฉพาะสินค้าที่เป็น Apples เมื่อรันแล้วจะได้ผลลัพธ์ดังนี้

ส่วนที่เราควรจะเปลี่ยนให้กลายเป็นพารามิเตอร์คือคำว่า 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 จะได้ผลลัพธ์เป็นตารางอันล่างสุด

จะเห็นว่าตาราง 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

บรรทัด 5-6 โหลดข้อมูลมาจากตารางในโปรแกรมไมโครซอฟท์เอ็กซ์เซล
บรรทัด 9-10 หายอดรวมของการขายนำไปใส่เป็นตาราง GrandTotal
บรรทัด 11 นำค่าผลรวมจากตาราง GrandTotal มากำหนดให้ตัวแปร GrandTotal
บรรทัด 16 กำหนดให้ตัวแปร Custom1 อ้างไปยังดาต้าซอร์ส (ตาราง PercentageShareOfTotalInput)
บรรทัด 19 แทรกคอลัมน์ชื่อ Share เข้าไปในตาราง GrandTotal ใส่ค่าในแต่ละแถวเป็นผลคำนวณยอดขายหารด้วยยอดรวม

 

หาการโตของยอดขาย

หากเรามีตารางแสดงยอดขายของสินค้าในแต่ละวัน ชื่อตาราง 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: เรียงวันที่จากน้อยไปมาก

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