การฝึกอบรม
โมดูล
Use built-in functions and GROUP BY in Transact-SQL - Training
Use built-in functions and GROUP BY in Transact-SQL
เบราว์เซอร์นี้ไม่ได้รับการสนับสนุนอีกต่อไป
อัปเกรดเป็น Microsoft Edge เพื่อใช้ประโยชน์จากคุณลักษณะล่าสุด เช่น การอัปเดตความปลอดภัยและการสนับสนุนด้านเทคนิค
ใน Power Query คุณสามารถจัดกลุ่มค่าในแถวต่าง ๆ ให้เป็นค่าเดียวได้โดยการจัดกลุ่มแถวตามค่าในหนึ่งคอลัมน์หรือมากกว่า คุณสามารถเลือกการดําเนินการจัดกลุ่มได้สองชนิด:
การจัดกลุ่มคอลัมน์
การจัดกลุ่มแถว
สําหรับบทช่วยสอนนี้ คุณกําลังใช้ตารางตัวอย่างต่อไปนี้
ภาพหน้าจอของตารางที่มีคอลัมน์ที่แสดงปี (2020) ประเทศ (สหรัฐอเมริกา ปานามา หรือแคนาดา), ผลิตภัณฑ์ (เสื้อหรือกางเกงขาสั้น), ช่องทางการขาย (ออนไลน์หรือผู้จําหน่าย) และหน่วย (ค่าต่างๆ ตั้งแต่ 55 ถึง 7500)
คุณสามารถค้นหาปุ่ม จัดกลุ่มตาม ในสามตําแหน่ง:
บนแท็บ หน้าแรก ในกลุ่ม แปลง
บนแท็บ แปลง ในกลุ่ม ตาราง
บนเมนูทางลัด เมื่อคุณคลิกขวาเพื่อเลือกคอลัมน์
ในตัวอย่างนี้ เป้าหมายของคุณคือสรุปจํานวนหน่วยทั้งหมดที่ขายได้ในระดับประเทศและช่องทางการขาย คุณใช้คอลัมน์ ประเทศ และ ช่องทาง การขายเพื่อดําเนินการจัดกลุ่มตามการดําเนินการ
การดําเนินการนี้จะให้ตารางต่อไปนี้แก่คุณ
ด้วยคุณลักษณะ จัดกลุ่มตาม การดําเนินการที่พร้อมใช้งานสามารถจัดประเภทได้สองวิธี:
ตารางต่อไปนี้อธิบายการดําเนินการเหล่านี้แต่ละรายการ
ชื่อการดําเนินการ | หมวดหมู่ | คำอธิบาย |
---|---|---|
Sum | การดําเนินการคอลัมน์ | รวมค่าทั้งหมดจากคอลัมน์ |
Average | การดําเนินการคอลัมน์ | คํานวณค่าเฉลี่ยจากคอลัมน์ |
มัธยฐาน | การดําเนินการคอลัมน์ | คํานวณค่ามัธยฐานจากคอลัมน์ |
Min | การดําเนินการคอลัมน์ | คํานวณค่าต่ําสุดจากคอลัมน์ |
Max | การดําเนินการคอลัมน์ | คํานวณค่าสูงสุดจากคอลัมน์ |
เปอร์เซ็นไทล์ | การดําเนินการคอลัมน์ | คํานวณเปอร์เซ็นไทล์โดยใช้ค่าอินพุตจาก 0 ถึง 100 จากคอลัมน์ |
นับจํานวนค่าที่แตกต่างกัน | การดําเนินการคอลัมน์ | คํานวณจํานวนค่าที่แตกต่างกันจากคอลัมน์ |
นับจํานวนแถว | การดําเนินการของแถว | คํานวณจํานวนแถวทั้งหมดจากกลุ่มที่ระบุ |
นับจํานวนแถวที่แตกต่างกัน | การดําเนินการของแถว | คํานวณจํานวนแถวที่แตกต่างกันจากกลุ่มที่ระบุ |
แถวทั้งหมด | การดําเนินการของแถว | แสดงแถวที่จัดกลุ่มทั้งหมดค่าตารางที่ไม่มีการรวม |
หมายเหตุ
ค่านับจํานวนที่แตกต่างกันและการดําเนินการเปอร์เซ็นต์ไทล์พร้อมใช้งานใน Power Query Online เท่านั้น
เริ่มต้นจากตัวอย่างเดิม ในตัวอย่างนี้ คุณสร้างคอลัมน์ที่มีหน่วยรวมและสองคอลัมน์อื่นๆ ที่ให้ชื่อและหน่วยที่ขายให้กับผลิตภัณฑ์ที่มีประสิทธิภาพสูงสุด โดยสรุปไว้ในระดับประเทศและช่องทางการขาย
ใช้คอลัมน์ต่อไปนี้เป็น จัดกลุ่มตาม คอลัมน์:
สร้างสองคอลัมน์ใหม่โดยทําตามขั้นตอนเหล่านี้:
หลังจากการดําเนินการดังกล่าวเสร็จสมบูรณ์ ให้สังเกตว่าคอลัมน์ ผลิตภัณฑ์ มีค่า [ตาราง] ภายในแต่ละเซลล์อย่างไร แต่ละค่า [ตาราง] ประกอบด้วยแถวทั้งหมดที่ถูกจัดกลุ่มโดย คอลัมน์ประเทศ และ ช่องทาง การขายจากตารางต้นฉบับของคุณ คุณสามารถเลือกช่องว่างภายในเซลล์เพื่อดูตัวอย่างของเนื้อหาของตารางที่ด้านล่างของกล่องโต้ตอบ
หมายเหตุ
บานหน้าต่างแสดงตัวอย่างรายละเอียดอาจไม่แสดงแถวทั้งหมดที่ใช้สําหรับการดําเนินการ group-by คุณสามารถเลือกค่า [ตาราง] เพื่อดูแถวทั้งหมดที่เกี่ยวข้องกับการดําเนินการ group-by ที่สอดคล้องกัน
ถัดไป คุณจําเป็นต้องแยกแถวที่มีค่าสูงสุดในคอลัมน์ Units ของตารางภายในคอลัมน์ Products ใหม่ และเรียกใช้คอลัมน์ใหม่นั้น ผลิตภัณฑ์ที่มีประสิทธิภาพสูงสุด
ด้วยคอลัมน์ผลิตภัณฑ์ใหม่ที่มีค่า [ตาราง] คุณสามารถสร้างคอลัมน์แบบกําหนดเองใหม่ได้โดยไปที่แท็บ เพิ่มคอลัมน์ บนริบบอน และเลือก คอลัมน์แบบกําหนดเอง จากกลุ่ม ทั่วไป
ตั้งชื่อคอลัมน์ ใหม่ของคุณว่า ผลิตภัณฑ์ที่มีประสิทธิภาพสูงสุด ใส่สูตร Table.Max([Products], "Units" )
ภายใต้ สูตรคอลัมน์แบบกําหนดเอง
ผลลัพธ์ของสูตรนั้นสร้างคอลัมน์ใหม่ด้วยค่า [Record] ค่าเรกคอร์ดเหล่านี้เป็นตารางที่มีแถวเดียวเป็นหลัก ระเบียนเหล่านี้ประกอบด้วยแถวที่มีค่าสูงสุดสําหรับคอลัมน์ Units ของแต่ละค่า [Table] ในคอลัมน์ Products
ด้วยคอลัมน์ผลิตภัณฑ์ผู้ทํางานยอดเยี่ยมที่ประกอบด้วยค่า [ระเบียน] ใหม่ คุณสามารถเลือกไอคอนขยาย เลือกเขตข้อมูลผลิตภัณฑ์และหน่วย จากนั้นเลือกตกลงได้
หลังจากที่คุณลบคอลัมน์ ผลิตภัณฑ์ และตั้งค่าชนิดข้อมูลสําหรับคอลัมน์ที่เพิ่งขยายใหม่ ผลลัพธ์ของคุณจะคล้ายกับรูปภาพต่อไปนี้
หมายเหตุ
คุณลักษณะต่อไปนี้พร้อมใช้งานใน Power Query Online เท่านั้น
หากต้องการสาธิตวิธีการ "การจัดกลุ่มแบบไม่เป็นทางการ" ให้พิจารณาตารางตัวอย่างที่แสดงในรูปต่อไปนี้
เป้าหมายของการจัดกลุ่มแบบไม่ลับคือการดําเนินจัดกลุ่มตามที่ใช้อัลกอริทึมการจับคู่โดยประมาณสําหรับสตริงข้อความ Power Query ใช้อัลกอริทึมความคล้ายคลึงกันของ Jaccard เพื่อวัดความคล้ายคลึงกันระหว่างคู่ของอินสแตนซ์ จากนั้นใช้การคลัสเตอร์แบบลําดับชั้นเชิงรวมกับอินสแตนซ์กลุ่มเข้าด้วยกัน รูปภาพต่อไปนี้แสดงผลลัพธ์ที่คุณคาดหวังซึ่งตารางจะถูกจัดกลุ่มตามคอลัมน์ Person
ในการจัดกลุ่มแบบไม่เป็นทางการ คุณต้องทําตามขั้นตอนเดียวกันที่อธิบายไว้ในบทความนี้ ความแตกต่างเดียวคือ ในเวลา นี้ในกล่องโต้ตอบ จัดกลุ่มตาม คุณเลือกกล่องกา เครื่องหมาย ใช้การ จัดกลุ่มแบบไม่เต็มตา
สําหรับแต่ละกลุ่มแถว Power Query จะเลือกอินสแตนซ์ที่ใช้บ่อยที่สุดเป็นอินสแตนซ์ "มาตรฐาน" ถ้าหลายอินสแตนซ์เกิดขึ้นด้วยความถี่เดียวกัน Power Query จะเลือกอินสแตนซ์แรก หลังจากที่คุณเลือก ตกลง ในกล่องโต้ตอบ จัดกลุ่มตาม คุณจะได้รับผลลัพธ์ที่คุณคาดหวังไว้
อย่างไรก็ตาม คุณสามารถควบคุมการดําเนินการจัดกลุ่มแบบไม่น่าสนใจได้มากขึ้นโดยการขยาย ตัวเลือกกลุ่ม Fuzzy
ตัวเลือกต่อไปนี้จะพร้อมใช้งานสําหรับการจัดกลุ่มแบบไม่เป็นทางการ:
สําหรับตัวอย่างนี้ ตารางการแปลงถูกใช้เพื่อแสดงให้เห็นว่าสามารถแมปค่าได้อย่างไร ตารางการแปลงมีสองคอลัมน์:
รูปภาพต่อไปนี้แสดงตารางการแปลงที่ใช้ในตัวอย่างนี้
ข้อสำคัญ
สิ่งสําคัญคือตารางการแปลงมีคอลัมน์และชื่อคอลัมน์เดียวกันดังที่แสดงในรูปภาพก่อนหน้า (ต้องมีป้ายชื่อ "From" และ "To") มิฉะนั้น Power Query ไม่รู้จักตารางเป็นตารางการแปลง
กลับไปยัง กล่องโต้ตอบ จัดกลุ่มตาม ขยาย ตัวเลือกกลุ่มที่ไม่ชัดเจน เปลี่ยนการดําเนินการจาก นับแถว เป็น แถวทั้งหมด เปิดใช้งาน ตัวเลือกแสดงคะแนน ความคล้ายคลึงกัน จากนั้นเลือก เมนูดรอปดาวน์ ตาราง การแปลง
หลังจากที่คุณเลือกตารางการแปลงแล้ว ให้เลือก ตกลง ผลลัพธ์ของการดําเนินการดังกล่าวให้ข้อมูลต่อไปนี้แก่คุณ:
ในตัวอย่าง นี้ ตัวเลือกกรณี ละเว้นถูกเปิดใช้งาน ดังนั้นค่าใน คอลัมน์ จาก ของ ตาราง การแปลง จะถูกใช้เพื่อค้นหาสตริงข้อความโดยไม่ต้องพิจารณากรณีของสตริง การดําเนินการแปลงนี้เกิดขึ้นก่อน จากนั้นจะดําเนินการจัดกลุ่มแบบไม่เป็นทางการ
คะแนนความคล้ายคลึงกันจะแสดงในค่าตารางถัดจากคอลัมน์บุคคล ซึ่งสะท้อนให้เห็นว่ามีการจัดกลุ่มค่าและคะแนนความคล้ายคลึงกันอย่างไร คุณสามารถขยายคอลัมน์นี้ได้ถ้าต้องการหรือใช้ค่าจากคอลัมน์ความถี่ใหม่สําหรับการแปลงประเภทอื่น ๆ
หมายเหตุ
เมื่อจัดกลุ่มตามหลายคอลัมน์ ตารางการแปลงจะดําเนินการแทนที่ในคอลัมน์ทั้งหมดถ้าการแทนที่ค่าจะเพิ่มคะแนนความคล้ายคลึงกัน
สําหรับข้อมูลเพิ่มเติมเกี่ยวกับวิธีการทํางานของตารางการแปลง ให้ไปที่ ค่าตารางการแปลง
การฝึกอบรม
โมดูล
Use built-in functions and GROUP BY in Transact-SQL - Training
Use built-in functions and GROUP BY in Transact-SQL