เก่งโค้ดงาน 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

การคิวรีข้อมูลจากฐานข้อมูล SQL ที่มีผลต่อความปลอดภัย

ต่อไปมาดูการเขียนโค้ดภาษาเอ็มเพื่อคิวรีข้อมูลจากฐานข้อมูล การคิวรีข้อมูลจากฐานข้อมูลคล้ายการคิวรีข้อมูลจากตารางในโปรเกรมเอ็กซ์เซลที่เห็นในตัวอย่างโค้ดในหัวข้อก่อนหน้านี้ ในรูปที่ 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 มีการอ้างถึงดาต้าซอร์สภายนอกภายในบรรทัดคำสั่งเดียวกัน ซึ่งอาจมีผลต่อความปลอดภัย คิวรีนี้จึงไม่สามารถทำงานได้

การคิวรีข้อมูลจากฐานข้อมูล SQL ที่ไม่มีผลต่อความปลอดภัย

วิธีแก้ไขปัญหานี้ทำได้โดยแบ่งการทำงานออกเป็นสองบรรทัดคำสั่งอย่างที่เห็นในรูปที่ 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: เรียงวันที่จากน้อยไปมาก

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