แผนคิวรีสําหรับ Power Query (ตัวอย่าง)
บทความ 23/03/2566
6 ผู้สนับสนุน
คำติชม
ในบทความนี้
1. ตรวจสอบตัวบ่งชี้การพับคิวรี
2. เลือกขั้นตอนคิวรีเพื่อตรวจสอบแผนคิวรี
3. นําการเปลี่ยนแปลงไปใช้กับคิวรีของคุณ
แผนคิวรีสําหรับ Power Query เป็นคุณลักษณะที่ให้มุมมองการประเมินของคิวรีของคุณได้ดีขึ้น ซึ่งมีประโยชน์เพื่อช่วยในการกําหนดสาเหตุที่คิวรีเฉพาะอาจไม่พับในขั้นตอนใดขั้นตอนหนึ่ง
ในตัวอย่างที่ใช้ได้จริง บทความนี้จะสาธิตกรณีการใช้งานหลักและประโยชน์ที่อาจเกิดขึ้นจากการใช้คุณลักษณะแผนคิวรีเพื่อตรวจสอบขั้นตอนคิวรีของคุณ ตัวอย่างที่ใช้ในบทความนี้ถูกสร้างขึ้นโดยใช้ฐานข้อมูลตัวอย่าง AdventureWorksLT สําหรับ Azure SQL Server ซึ่งคุณสามารถดาวน์โหลดได้จาก ฐานข้อมูล ตัวอย่าง AdventureWorks
หมายเหตุ
คุณลักษณะแผนคิวรีสําหรับ Power Query จะพร้อมใช้งานใน Power Query ออนไลน์เท่านั้น
บทความนี้ได้รับการแบ่งออกเป็นชุดของขั้นตอนที่แนะนําเพื่อแปลแผนคิวรี ขั้นตอนเหล่านี้คือ:
ตรวจสอบตัวบ่งชี้ การพับคิวรี
เลือกขั้นตอนคิวรีเพื่อตรวจสอบแผน คิวรี
ทําการเปลี่ยนแปลงไปยังคิวรี ของคุณ
ใช้ขั้นตอนต่อไปนี้เพื่อสร้างคิวรีในสภาพแวดล้อม Power Query Online ของคุณเอง
จาก Power Query - เลือกแหล่งข้อมูล เลือก คิวรี ว่าง
แทนที่สคริปต์ของคิวรีที่ว่างเปล่าด้วยคิวรีต่อไปนี้
let
Source = Sql.Database("servername", "database"),
Navigation = Source{[Schema = "Sales", Item = "SalesOrderHeader"]}[Data],
#"Removed other columns" = Table.SelectColumns(Navigation, {"SalesOrderID", "OrderDate", "SalesOrderNumber", "PurchaseOrderNumber", "AccountNumber", "CustomerID", "TotalDue"}),
#"Filtered rows" = Table.SelectRows(#"Removed other columns", each [TotalDue] > 1000),
#"Kept bottom rows" = Table.LastN(#"Filtered rows", 5)
in
#"Kept bottom rows"
เปลี่ยน servername
และ database
ด้วยชื่อที่ถูกต้องสําหรับสภาพแวดล้อมของคุณเอง
(ไม่บังคับ) ถ้าคุณกําลังพยายามเชื่อมต่อกับเซิร์ฟเวอร์และฐานข้อมูลสําหรับสภาพแวดล้อมภายในองค์กร ตรวจสอบให้แน่ใจว่าได้กําหนดค่าเกตเวย์สําหรับสภาพแวดล้อมนั้นแล้ว
เลือก ถัดไป
ในตัวแก้ไข Power Query ให้เลือก กําหนดค่าการเชื่อมต่อ และใส่ข้อมูลประจําตัวไปยังแหล่งข้อมูลของคุณ
หมายเหตุ
สําหรับข้อมูลเพิ่มเติมเกี่ยวกับการเชื่อมต่อกับ SQL Server ให้ไปที่ ฐานข้อมูล SQL Server
หลังจากทําตามขั้นตอนเหล่านี้ คิวรีของคุณจะมีลักษณะเหมือนกับในรูปต่อไปนี้
คิวรีนี้เชื่อมต่อกับตาราง SalesOrderHeader และเลือกสองสามคอลัมน์จากคําสั่งซื้อห้ารายการล่าสุดที่มี ค่า TotalDue มากกว่า 1000
หมายเหตุ
บทความนี้ใช้ตัวอย่างแบบง่ายเพื่อแสดงคุณลักษณะนี้ แต่แนวคิดที่อธิบายไว้ในบทความนี้ใช้กับคิวรีทั้งหมด เราขอแนะนําให้คุณมีความรู้ที่ดีเกี่ยวกับการพับคิวรีก่อนที่จะอ่านแผนคิวรี เมื่อต้องการเรียนรู้เพิ่มเติมเกี่ยวกับ Query Folding ไปที่ พื้นฐาน การพับคิวรี
1. ตรวจสอบตัวบ่งชี้การพับคิวรี
หมายเหตุ
ก่อนอ่านส่วนนี้ เราขอแนะนําให้คุณตรวจทานบทความเกี่ยวกับ ตัวบ่งชี้ Query Folding
ขั้นตอนแรกของคุณในกระบวนการนี้คือการตรวจสอบคิวรีของคุณ และให้ความสนใจกับตัวบ่งชี้ Query Folding อย่างใกล้ชิด เป้าหมายคือการตรวจสอบขั้นตอนที่มีการทําเครื่องหมายว่าไม่ได้พับ จากนั้นคุณสามารถดูได้ว่าการเปลี่ยนแปลงคิวรีโดยรวมอาจทําให้การแปลงเหล่านั้นพับอย่างสมบูรณ์หรือไม่
สําหรับตัวอย่างนี้ขั้นตอนเดียวที่ไม่สามารถพับได้คือ Kept แถว ด้านล่างซึ่งง่ายต่อการระบุผ่าน ตัวบ่งชี้ขั้นตอนที่ไม่สามารถพับ ได้ ขั้นตอนนี้เป็นขั้นตอนสุดท้ายของคิวรี
เป้าหมายตอนนี้คือการตรวจสอบขั้นตอนนี้และทําความเข้าใจสิ่งที่ถูกพับกลับไปยังแหล่งข้อมูลและสิ่งที่ไม่สามารถพับได้
2. เลือกขั้นตอนคิวรีเพื่อตรวจสอบแผนคิวรี
คุณได้ระบุขั้นตอนแถวล่างที่ เก็บไว้เป็นขั้นตอนที่น่าสนใจเนื่องจากไม่ได้พับกลับไปยังแหล่งข้อมูล คลิกขวาที่ขั้นตอนและเลือกตัวเลือก ดูแผน คิวรี การดําเนินการนี้แสดงกล่องโต้ตอบใหม่ที่มีไดอะแกรมสําหรับแผนการคิวรีของขั้นตอนที่เลือก
Power Query พยายามปรับคิวรีของคุณให้เหมาะสมโดยการใช้ประโยชน์จากการประเมินผลที่ขี้เกียจและการพับคิวรี ตามที่กล่าวไว้ใน พื้นฐาน การพับคิวรี แผนคิวรีนี้แสดงคําแปลที่เหมาะสมที่สุดของคิวรี M ของคุณลงในคิวรีดั้งเดิมที่ถูกส่งไปยังแหล่งข้อมูล นอกจากนี้ยังรวมถึงการแปลงใดๆ ที่ดําเนินการโดยกลไกจัดการ Power Query ลําดับที่โหนดปรากฏตามลําดับคิวรีของคุณโดยเริ่มต้นจากขั้นตอนสุดท้ายหรือผลลัพธ์ของคิวรีของคุณ ซึ่งแสดงอยู่ทางด้านซ้ายสุดของไดอะแกรม และในกรณีนี้คือโหนด Table.LastN ที่แสดงขั้นตอนแถวล่างสุดที่ Kept
ที่ด้านล่างของกล่องโต้ตอบ จะมีแถบที่มีไอคอนที่ช่วยให้คุณขยายหรือย่อมุมมองแผนคิวรี และปุ่มอื่นๆ เพื่อช่วยให้คุณจัดการมุมมองได้ สําหรับรูปภาพ ก่อนหน้า ตัวเลือกพอดีกับมุมมอง จากแถบนี้ถูกใช้เพื่อชื่นชมโหนดได้ดียิ่งขึ้น
หมายเหตุ
แผนคิวรีแสดงแผนการที่ปรับให้เหมาะสมแล้ว เมื่อกลไกจัดการกําลังประเมินคิวรี จะพยายามพับตัวดําเนินการทั้งหมดลงในแหล่งข้อมูล ในบางกรณี อาจยังสามารถจัดลําดับขั้นตอนภายในใหม่เพื่อเพิ่มการพับได้สูงสุด โดยคํานึงถึงสิ่งนี้ โหนด/ตัวดําเนินการที่ยังเหลืออยู่ในแผนคิวรีที่ปรับให้เหมาะสมนี้มักประกอบด้วยคิวรีแหล่งข้อมูล "folded" และตัวดําเนินการใด ๆ ที่ไม่สามารถพับได้และได้รับการประเมินภายในเครื่อง
คุณสามารถระบุโหนดในไดอะแกรมนี้เป็นสองกลุ่ม:
โหนด แบบพับได้: โหนดนี้สามารถเป็นValue.NativeQuery
โหนด "แหล่งข้อมูล" เช่นSql.Database
นอกจากนี้ยังสามารถระบุข้อมูลเหล่านี้ได้ด้วยระยะไกลสําหรับป้ายชื่อ ภายใต้ชื่อฟังก์ชัน
โหนด ที่ไม่มีการพับ: ตัวดําเนินการตารางอื่น ๆ เช่น Table.SelectRows
, Table.SelectColumns
และฟังก์ชันอื่น ๆ ที่ไม่สามารถพับได้ สิ่งเหล่านี้ยังสามารถระบุได้ด้วยป้ายชื่อการสแกน แบบเต็มรูปแบบและการสตรีมได้อีกด้วย
รูปต่อไปนี้แสดงโหนดที่ถูกพับภายในสี่เหลี่ยมผืนผ้าสีแดง ส่วนที่เหลือของโหนดไม่สามารถพับกลับไปยังแหล่งข้อมูลได้ คุณจะต้องตรวจสอบส่วนที่เหลือของโหนดเนื่องจากเป้าหมายคือการพยายามให้โหนดเหล่านั้นพับกลับไปยังแหล่งข้อมูล
คุณสามารถเลือก ดูรายละเอียด ที่ด้านล่างของโหนดบางโหนดเพื่อแสดงข้อมูลที่ขยายออกได้ ตัวอย่างเช่น รายละเอียดของ Value.NativeQuery
โหนดแสดงคิวรีดั้งเดิม (ใน SQL) ที่จะถูกส่งไปยังแหล่งข้อมูล
คิวรีที่แสดงที่นี่อาจไม่ใช่คิวรีเดียวกับที่ส่งไปยังแหล่งข้อมูล แต่เป็นการประมาณการที่ดี สําหรับกรณีนี้ ซึ่งจะบอกคุณว่าคอลัมน์ใดที่จะถูกคิวรีจากตาราง SalesOrderHeader และวิธีการกรองตารางนั้นโดยใช้เขตข้อมูล TotalDue เพื่อรับเฉพาะแถวที่ค่าของเขตข้อมูลนั้นมีขนาดใหญ่กว่า 1000 แถว โหนดที่อยู่ถัดจาก Table.LastN จะถูกคํานวณภายในเครื่องโดยกลไกจัดการ Power Query เนื่องจากไม่สามารถพับได้
หมายเหตุ
ตัวดําเนินการอาจไม่ตรงกับฟังก์ชันที่ใช้ในสคริปต์ของคิวรี
คุณได้พิจารณาแล้วว่าโหนดใดที่ไม่สามารถพับได้และจะถูกประเมินภายในเครื่อง กรณีนี้มีโหนดเท่านั้น Table.LastN
แต่ในสถานการณ์อื่น ๆ อาจมีอีกมากมาย
เป้าหมายคือการใช้การเปลี่ยนแปลงกับคิวรีของคุณเพื่อให้สามารถพับขั้นตอนได้ การเปลี่ยนแปลงบางอย่างที่คุณอาจใช้อาจมีตั้งแต่การจัดเรียงขั้นตอนของคุณใหม่เพื่อใช้ตรรกะทางเลือกกับคิวรีของคุณที่ชัดเจนมากขึ้นกับแหล่งข้อมูล ซึ่งไม่ได้หมายความว่าคิวรีทั้งหมดและการดําเนินการทั้งหมดสามารถพับได้โดยใช้การเปลี่ยนแปลงบางอย่าง แต่เป็นแนวทางปฏิบัติที่ดีในการกําหนดผ่านการทดลองใช้และข้อผิดพลาดหากสามารถพับคิวรีของคุณกลับได้
เนื่องจากแหล่งข้อมูลเป็นฐานข้อมูล SQL Server ถ้าเป้าหมายคือการดึงข้อมูลคําสั่งซื้อห้ารายการสุดท้ายจากตาราง ดังนั้นทางเลือกที่ดีคือใช้ประโยชน์จาก ส่วนคําสั่ง TOP และ ORDER BY ใน SQL เนื่องจากไม่มีส่วนคําสั่ง BOTTOM ใน SQL การแปลงใน Table.LastN
PowerQuery จึงไม่สามารถแปลเป็น SQL ได้ คุณสามารถลบขั้นตอน Table.LastN
ออกและแทนที่ด้วย:
ขั้นตอน การเรียงลําดับจากมากไปหาน้อยตามคอลัมน์ SalesOrderID ในตาราง เนื่องจากคอลัมน์นี้จะกําหนดลําดับก่อนและลําดับที่ถูกป้อนล่าสุด
เลือกห้าแถว บนสุดนับตั้งแต่มีการเรียงลําดับตาราง การแปลงนี้จะสามารถทําได้เหมือนกับว่าเป็น แถว ล่างสุดที่เก็บไว้ (Table.LastN
)
ตัวเลือกนี้เทียบเท่ากับคิวรีเดิม ในขณะที่ดูเหมือนว่าทางเลือกนี้ในทฤษฎีนี้ดี คุณต้องทําการเปลี่ยนแปลงเพื่อดูว่าตัวเลือกนี้จะทําให้โหนดนี้พับกลับไปยังแหล่งข้อมูลอย่างสมบูรณ์หรือไม่
3. นําการเปลี่ยนแปลงไปใช้กับคิวรีของคุณ
ใช้ตัวเลือกที่อธิบายไว้ในส่วนก่อนหน้า:
ปิดกล่องโต้ตอบแผนคิวรีและย้อนกลับไปยังตัวแก้ไข Power Query
ลบขั้นตอน แถวล่าง ที่เก็บไว้
จัดเรียงคอลัมน์ SalesOrderID จากมากไปหาน้อย
เลือกไอคอนตารางที่มุมบนซ้ายของมุมมองการแสดงตัวอย่างข้อมูล และเลือกตัวเลือกที่อ่านเก็บแถวบนสุด ในกล่องโต้ตอบ ส่งตัวเลขห้าเป็นอาร์กิวเมนต์ และกดตกลง
หลังจากใช้การเปลี่ยนแปลงแล้ว ให้ตรวจสอบตัวบ่งชี้การพับคิวรีอีกครั้งและดูว่าจะให้ตัวบ่งชี้แบบพับแก่คุณหรือไม่
ในตอนนี้ถึงเวลาตรวจทานแผนคิวรีของขั้นตอนสุดท้าย ซึ่งตอนนี้ คือเก็บแถว บนสุด ตอนนี้มีเพียงโหนดพับเท่านั้น เลือก ดูรายละเอียด ภายใต้ Value.NativeQuery
เพื่อตรวจสอบว่าการสอบถามใดที่กําลังถูกส่งไปยังฐานข้อมูล
ในขณะที่บทความนี้กําลังแนะนําทางเลือกที่จะนําไปใช้ แต่เป้าหมายหลักคือให้คุณเรียนรู้วิธีการใช้แผนคิวรีเพื่อตรวจสอบการพับคิวรี บทความนี้ยังให้การมองเห็นของสิ่งที่จะถูกส่งไปยังแหล่งข้อมูลของคุณ และสิ่งที่จะทําการแปลงภายในเครื่อง
คุณสามารถปรับเปลี่ยนโค้ดของคุณเพื่อดูผลกระทบที่มีในคิวรีของคุณได้ เมื่อใช้ตัวบ่งชี้ Query Folding คุณจะมีความคิดที่ดีกว่าว่าขั้นตอนใดที่ทําให้คิวรีของคุณไม่สามารถพับได้