Sql 12 เดือน เฉลี่ยเคลื่อนที่

ฉันมีการผลิตตารางที่มีโครงสร้างต่อไปนี้: ฉันมีข้อมูลสำหรับตัวแทนแต่ละรายจาก 112011 ถึง 812013 สิ่งที่ฉันต้องการเพื่อให้สามารถทำคือสร้างค่าเฉลี่ยเคลื่อนที่ 12 เดือนเริ่มต้นที่ 112012 สำหรับตัวแทนแต่ละรายดังนี้ซึ่งแต่ละแถวหมายถึง ค่าเฉลี่ยเคลื่อนที่ 12 เดือนสำหรับตัวแทนดังกล่าวในเวลาที่ระบุ ฉันพบตัวอย่างที่ใกล้เคียงและฉันพยายามให้ไม่มีประโยชน์ ดูเหมือนว่าการเพิ่มกลุ่มโดยองค์ประกอบ rep คือการออกจากตัวอย่างอื่น ๆ ที่สำคัญ นี่เป็นเรื่องเกี่ยวกับเท่าที่ฉันได้รับ: แบบสอบถามนั้นดูเหมือนว่าจะดึงค่าเฉลี่ยโดยรวมหรือผลรวมเนื่องจากไม่มีการจัดกลุ่มในแบบสอบถามย่อยที่มีความสัมพันธ์ เมื่อฉันพยายามจัดกลุ่มตามฉันได้รับข้อผิดพลาดที่สามารถส่งคืนได้ไม่เกินหนึ่งแถว ถาม 10 ต. ค. 13 ที่ 14:47 เฉลี่ย 12 เดือนใน DAX การคำนวณค่าเฉลี่ยเฉลี่ย 12 เดือนของ DAX ดูเหมือนจะเป็นงานง่ายๆ แต่จะซ่อนความซับซ้อนบางอย่าง บทความนี้อธิบายถึงวิธีการเขียนสูตรที่ดีที่สุดเพื่อหลีกเลี่ยงข้อผิดพลาดทั่วไปที่ใช้ฟังก์ชันอัจฉริยะเวลา เราเริ่มต้นด้วยแบบจำลองข้อมูล AdventureWorks ปกติโดยมีตารางผลิตภัณฑ์การขายและปฏิทิน ปฏิทินมีการทำเครื่องหมายเป็นตารางปฏิทิน (จำเป็นต้องทำงานกับฟังก์ชันข่าวกรองในเวลาใด ๆ ) และเราสร้างลำดับชั้นที่เรียบง่ายขึ้นเมื่อเดือนปีเดือน ด้วยการตั้งค่านี้เป็นเรื่องง่ายมากที่จะสร้าง PivotTable แรกที่แสดงยอดขายเมื่อเวลาผ่านไป: เมื่อทำการวิเคราะห์แนวโน้มถ้ายอดขายขึ้นอยู่กับฤดูกาลหรือโดยทั่วไปถ้าคุณต้องการลบผลกระทบของยอดขายและการลดลงของยอดขาย เทคนิคทั่วไปคือการคำนวณค่าในช่วงเวลาที่กำหนดโดยปกติแล้วจะเป็น 12 เดือนและโดยเฉลี่ย ค่าเฉลี่ยของการกลิ้งมากกว่า 12 เดือนแสดงให้เห็นถึงแนวโน้มที่ดีและมีประโยชน์มากในแผนภูมิ เมื่อได้รับวันที่เราสามารถคำนวณค่าเฉลี่ยกลิ้ง 12 เดือนโดยใช้สูตรนี้ซึ่งยังคงมีปัญหาบางอย่างที่เราจะแก้ในภายหลัง: ลักษณะการทำงานของสูตรง่าย: คำนวณมูลค่าการขายหลังจากสร้างตัวกรองในปฏิทินที่ แสดงข้อมูลครบหนึ่งปี หลักของสูตรคือ DATESBETWEEN ซึ่งจะส่งคืนชุดวันที่ระหว่างขอบเขตทั้งสอง ด้านล่างคือ: การอ่านจากด้านในสุด: ถ้าเราแสดงข้อมูลเป็นเดือนหนึ่งพูดว่ากรกฎาคม 2007 เราจะใช้วันที่มองเห็นล่าสุดโดยใช้ LASTDATE ซึ่งจะส่งกลับในวันสุดท้ายในเดือนกรกฎาคม 2550 จากนั้นเราจะใช้ NEXTDAY เพื่อเข้าร่วมการแข่งขันครั้งที่ 1 ของเดือนสิงหาคม 2007 และในที่สุดเราก็ใช้ SAMEPERIODLASTYEAR เพื่อเปลี่ยนกลับมาเป็นเวลาหนึ่งปีโดยให้ผลผลิตตั้งแต่วันที่ 1 สิงหาคม 2549 ขอบเขตด้านบนเป็นเพียงแค่ LASTDATE คือปลายเดือนกรกฎาคม 2007 หากเราใช้สูตรนี้ในตารางสาระสำคัญผลลัพธ์ก็ดูดี แต่เรา มีปัญหาสำหรับวันที่ล่าสุด: ในความเป็นจริงตามที่เห็นในรูปภาพค่าจะถูกคำนวณอย่างถูกต้องจนถึงปี 2008 จากนั้นไม่มีค่าในปี 2009 (ซึ่งเป็นราคาที่ถูกต้องเราไม่มียอดขายในปี 2009) แต่มี มูลค่าที่น่าแปลกใจในเดือนธันวาคม 2010 ซึ่งสูตรของเราแสดงยอดรวมทั้งหมดแทนที่จะเป็นมูลค่าที่ว่างเปล่าตามที่เราคาดไว้ ในความเป็นจริงเมื่อวันที่ธันวาคม LASTDATE จะส่งคืนในวันสุดท้ายของปีและ NEXTDAY จะกลับวันที่ 1 มกราคม 2011 แต่ NEXTDAY เป็นฟังก์ชันข้อมูลอัจฉริยะเวลาและคาดว่าจะส่งคืนชุดวันที่ที่มีอยู่ ความจริงข้อนี้ไม่ค่อยชัดเจนเท่าไรนัก ฟังก์ชันอัจฉริยะเวลาไม่ได้ทำคณิตศาสตร์ในวันที่ ถ้าคุณต้องการใช้วันหลังจากวันที่ที่ระบุคุณสามารถเพิ่ม 1 ลงในคอลัมน์วันใดก็ได้และผลลัพธ์จะเป็นวันถัดไป แต่ฟังก์ชันการทำงานของข้อมูลอัจฉริยะเวลาจะเปลี่ยนชุดวันที่ไปมาตลอดเวลา ดังนั้น NEXTDAY ใช้ข้อมูลของตน (ในกรณีของเราคือตารางแถวเดียวที่มีวันที่ 31 ธันวาคม 2010) และจะเลื่อนออกไปหนึ่งวันในภายหลัง ปัญหาคือผลลัพธ์ควรเป็น 1 มกราคม 2011 แต่เนื่องจากตารางปฏิทินไม่ประกอบด้วยวันที่ดังกล่าวผลลัพธ์คือ BLANK ดังนั้นนิพจน์ของเราคำนวณการขายที่มีขอบเขตต่ำกว่าที่ว่างเปล่าซึ่งหมายถึงจุดเริ่มต้นของเวลาซึ่งส่งผลให้ยอดขายรวมใหญ่ หากต้องการแก้ไขสูตรก็เพียงพอที่จะเปลี่ยนลำดับการประเมินผลของขอบเขตล่าง: ตามที่คุณเห็นตอนนี้เรียกว่า NEXTDAY หลังการเปลี่ยนแปลงหนึ่งปีย้อนหลัง ด้วยวิธีนี้เราใช้เวลา 31 ธันวาคม 2010 ย้ายไปที่ 31 ธันวาคม 2009 และใช้วันถัดไปซึ่งเป็นวันที่ 1 มกราคม 2010: วันที่มีอยู่ในตารางปฏิทิน ผลที่ได้คือตอนนี้เป็นที่คาดหวัง: ในตอนนี้เราต้องแบ่งเฉพาะตัวเลขดังกล่าวเป็น 12 เพื่อให้ได้ค่าเฉลี่ยของการกลิ้ง แต่เท่าที่คุณสามารถนึกภาพได้ง่ายเราไม่สามารถหารด้วย 12 ได้อย่างแท้จริงในตอนต้นของช่วงเวลาไม่มี 12 เดือนนับรวม แต่เป็นตัวเลขที่ต่ำกว่า เราจำเป็นต้องคำนวณจำนวนเดือนที่มียอดขาย ซึ่งสามารถทำได้โดยใช้การกรองข้ามของตารางปฏิทินด้วยตารางการขายหลังจากที่เราใช้บริบทใหม่ 12 เดือน เรากำหนดมาตรการใหม่ที่คำนวณจำนวนเดือนที่มีอยู่ในช่วง 12 เดือน: คุณสามารถดูในรูปถัดไปว่าการวัด Month12M คำนวณค่าที่ถูกต้อง: เป็นมูลค่าที่ควรทราบว่าสูตรนี้ไม่ได้ผลถ้าคุณเลือกช่วงเวลา นานกว่า 12 เดือนเนื่องจาก CalendarMonthName มีเพียง 12 ค่าเท่านั้น หากต้องการระยะเวลานานขึ้นคุณจะต้องใช้คอลัมน์ YYYYMM เพื่อให้สามารถนับได้มากกว่า 12 ส่วนที่น่าสนใจของสูตรนี้ที่ใช้การกรองข้ามคือการคำนวณจำนวนเดือนที่สามารถใช้งานได้แม้ว่าคุณจะใช้ตัวกรองอื่น แอตทริบิวต์ ตัวอย่างเช่นหากคุณเลือกสีฟ้าโดยใช้เครื่องตัดแล้วการขายจะเริ่มขึ้นในเดือนกรกฎาคม 2007 (ไม่ใช่ในปี 2005 เนื่องจากเกิดขึ้นกับสีอื่น ๆ ) การใช้ตัวกรองไขว้ในการขายสูตรนี้คำนวณได้อย่างถูกต้องว่าในเดือนกรกฎาคม 2007 มียอดขายใน Blue เพียงเดือนเดียว: ณ ขณะนี้ค่าเฉลี่ยของการกลิ้งคือ DIVIDE: เมื่อเราใช้ในตาราง Pivot เรายังคงใช้ มีปัญหาเล็กน้อย: ในความเป็นจริงค่าจะคำนวณเป็นเดือนที่ไม่มีการขาย (เช่นเดือนในอนาคต): สามารถแก้ไขได้โดยใช้คำสั่ง IF เพื่อป้องกันไม่ให้สูตรแสดงค่าเมื่อไม่มียอดขาย ฉันไม่มีอะไรต่อต้าน IF แต่สำหรับการติดยาเสพติดในหมู่คุณคุณควรจดจำว่า IF อาจเป็นฆาตกรที่มีประสิทธิภาพเนื่องจากอาจบังคับให้เครื่องยนต์สูตร DAX เตะขึ้นมาในกรณีเฉพาะนี้ความแตกต่างเล็กน้อย แต่ ตามกฎทั่วไปวิธีที่ดีที่สุดในการลบคุณค่าเมื่อไม่มีการขายคือการพึ่งพาสูตรเครื่องมือเก็บข้อมูลแบบสแตนด์อโลนเช่นนี้เปรียบเทียบแผนภูมิโดยใช้ Avg12M กับอีกรุ่นหนึ่งที่แสดงยอดขาย แสดงแนวโน้มในแบบที่สะอาดมาก: แจ้งให้ฉันทราบเกี่ยวกับบทความที่กำลังจะมาถึง (จดหมายข่าว) ยกเลิกการเลือกเพื่อดาวน์โหลดไฟล์ได้อย่างอิสระตอนนี้ฉันต้องการแผนภูมิที่แสดงค่าเฉลี่ยเคลื่อนที่ 12 เดือนนี้ในช่วง 12 เดือนที่ผ่านมานั่นคือในแกนนอนในช่วง 12 เดือนที่ผ่านมาและบนแกนแนวตั้งอัตราร้อยละของโรค ฉันสร้างชุดข้อมูลที่เรียกว่า Last12Months แต่แน่นอนว่าผิดพลาดเช่น สำหรับค่าเฉลี่ยร้อยละที่เจ็บป่วยเคลื่อนไหวในเดือนกรกฎาคม 2010 ค่าเฉลี่ยเคลื่อนที่จะต้องใช้อัตราร้อยละของโรคตั้งแต่สิงหาคม 2009 จนถึงเดือนกรกฎาคม 2010 HOwever เมื่อใช้ชุดข้อมูลที่มีช่วง 12 เดือนที่ผ่านมาอัตราการเจ็บป่วยสำหรับเดือนกรกฎาคม 2010 จะไม่สามารถคำนวณได้ เฉลี่ยเดือนกรกฎาคม 2010 คุณสามารถให้คำอธิบายเพิ่มเติมเกี่ยวกับเรื่องนี้ได้หรือไม่คุณสามารถจัดเตรียมตัวอย่างรายงานตัวอย่างเพื่อล้างข้อกำหนดของคุณได้ในย่อหน้าสุดท้ายดูเหมือนว่าคุณต้องการให้ตัวกรองแก่กลุ่มหมวดหมู่ในแผนภูมิเพื่อแสดงล่าสุด 12 ครั้ง เดือนที่เหมาะสมถ้าเป็นกรณีที่ฉันคิดว่าคุณสามารถใช้ฟังก์ชันวันนี้ในการรายงานการบริการเพื่อให้ได้ปีปัจจุบันและเดือนแล้วใช้ปีนี้เพื่อลบล้าง 1 คุณจะได้รับล่าสุด 12 เดือน หากฉันเข้าใจผิดคุณโปรดแจ้งให้เราทราบ ขอบคุณ, Challen Fu โปรดจำไว้ว่าให้ทำเครื่องหมายคำตอบเป็นคำตอบหากพวกเขาช่วยและยกเลิกการทำเครื่องหมายหากพวกเขาไม่ได้ให้ความช่วยเหลือ ถูกทำเครื่องหมายว่าเป็นคำตอบโดย HennieErgon วันศุกร์ที่ 19 สิงหาคม 2554 เวลา 11:14 น. วันอังคารที่ 16 สิงหาคม 2554 เวลา 5:17 น. ตามเวลาฝั่ง (เดือน (วันนี้)) lt 10, (Year (Today ()) - 2) amp quot0 เดือน amp วันนี้ ()) IIf (เดือน (วันนี้)) lt 10 ปี (วันนี้) แอมป์ quot00 amp (เดือน (วันนี้ ()) (ปี (วันนี้)) - 2) amp quotMquot amp เดือน 1), Year (Today ()) amp quotMquot amp (Month (today ()) - 1)) Marked as answer by Challen Fu วันเสาร์, สิงหาคม 20, 2011 12:50 น. แก้ไขโดย HennieErgon จันทร์สิงหาคม 22 สิงหาคม 2011 6:18 น. สั่งซื้อสูตรที่ถูกต้องวันศุกร์ที่ 19 สิงหาคม 2554 เวลา 11:12 น. ตอนนี้ฉันต้องการกราฟที่แสดงค่าเฉลี่ยเคลื่อนที่ 12 เดือนในช่วง 12 เดือนที่ผ่านมาเช่นในแนวนอนในช่วง 12 เดือนที่ผ่านมา แกนแนวตั้งร้อยละป่วย ฉันสร้างชุดข้อมูลที่เรียกว่า Last12Months แต่แน่นอนว่าผิดพลาดเช่น สำหรับค่าเฉลี่ยร้อยละที่เจ็บป่วยเคลื่อนไหวในเดือนกรกฎาคม 2010 ค่าเฉลี่ยเคลื่อนที่จะต้องใช้อัตราร้อยละของโรคตั้งแต่สิงหาคม 2009 จนถึงเดือนกรกฎาคม 2010 HOwever เมื่อใช้ชุดข้อมูลที่มีช่วง 12 เดือนที่ผ่านมาอัตราการเจ็บป่วยสำหรับเดือนกรกฎาคม 2010 จะไม่สามารถคำนวณได้ เฉลี่ยเดือนกรกฎาคม 2010 คุณสามารถให้คำอธิบายเพิ่มเติมเกี่ยวกับเรื่องนี้ได้หรือไม่คุณสามารถจัดเตรียมตัวอย่างรายงานตัวอย่างเพื่อล้างข้อกำหนดของคุณได้ในย่อหน้าสุดท้ายดูเหมือนว่าคุณต้องการให้ตัวกรองแก่กลุ่มหมวดหมู่ในแผนภูมิเพื่อแสดงล่าสุด 12 ครั้ง เดือนขวาถ้าเป็นกรณีที่ฉันคิดว่าคุณสามารถใช้ฟังก์ชันวันนี้ในการรายงานการบริการเพื่อให้ได้ปีปัจจุบันและเดือนแล้วใช้ปีนี้เพื่อลบล้าง 1 คุณจะได้รับล่าสุด 12 เดือน หากฉันเข้าใจผิดคุณโปรดแจ้งให้เราทราบ ขอบคุณ, Challen Fu โปรดจำไว้ว่าให้ทำเครื่องหมายคำตอบเป็นคำตอบหากพวกเขาช่วยและยกเลิกการทำเครื่องหมายหากพวกเขาไม่ได้ให้ความช่วยเหลือ ถูกทำเครื่องหมายว่าเป็นคำตอบโดย HennieErgon วันศุกร์ที่ 19 สิงหาคม 2554 เวลา 11:14 น. วันอังคารที่ 16 สิงหาคม 2554 เวลา 5:17 น. ขอขอบคุณสำหรับคำตอบ ฉันเหนื่อยกับการกรองวันที่ประเภท แต่แล้วฉันมีปัญหาอื่น ๆ ฉันแนบตัวอย่าง ตามที่คุณเห็นรูปแบบวันที่ของฉันเหมือนกับ 2009M09 แต่เพื่อกรองช่วง 12 (หรือ 24) เดือนที่ผ่านมาฉันต้องแบ่งปีและเดือน HOwever ตามที่คุณเห็นในตัวอย่างตัวอย่างเช่น 2009M10, 2009M11 และ 2009M12 จะถูกละเว้นเพราะคิดว่า 10, 11 en 12 มีค่าต่ำกว่า 9. วันพุธที่ 17 สิงหาคม 2011 เวลา 9:46 น. ฉันทำตัวกรองใน YearMonth Category: IIf (Month (today ()) lt 10, (Year (Today ()) - 2) amp quotM0quot amp เดือน (วันนี้), (Year (Today ()) - 2) แอมป์ amp เดือน (วันนี้)) IIf (เดือน (วันนี้)) lt 10 ปี (วันนี้) amp ampM0quot amp (เดือน (วันนี้)) - 1), ปี (วันนี้ ()) amp quotMquot amp (Month (today ()) - 1)) Marked by answer by Challen Fu วันพฤหัสบดีที่ 20 สิงหาคม 2011 เวลา 12:50 น. แก้ไขโดย HennieErgon จันทร์ 22 สิงหาคม, 2011 6:18 น. 2011 11:12 AMCalculating Values ​​ภายใน Rolling Window ใน Transact SQL Dwain Camps การคำนวณค่าภายใน Rolling Window ใน SQL เวลาที่คุณต้องการรวมค่าข้ามแถวต่างๆใน SQL ปัญหาอาจเป็นสิ่งที่ท้าทายโดยเฉพาะอย่างยิ่งเมื่อกล่าวถึงประสิทธิภาพ เราจะเน้นปัญหารวมทั้งสิ้นสิบสองเดือน แต่วิธีการของเราสามารถนำไปใช้กับหน้าต่างเวลาใด ๆ (เช่น 3 เดือน) หรือไปยังค่าเฉลี่ยและการรวมอื่น ๆ ในช่วงเวลาดังกล่าวได้เช่นกัน ยอดรวมของเดือนรวมเป็นเดือนรวมเดือนก่อนหน้าภายในหน้าต่างเวลาหรือ NULL ถ้าคุณ don8217t มีค่าสำหรับทุกเดือนที่ผ่านมาภายในหน้าต่างเวลา ใน SQL Server รุ่นก่อนหน้านี้คุณต้องข้ามผ่านไม่กี่ห่วงเพื่อหาวิธีที่ทำงานได้ดี แต่ SQL 2012 มีคุณลักษณะใหม่ ๆ ที่ทำให้ง่ายขึ้น ในทั้งสองกรณีมีวิธีแก้ปัญหาที่ถูกต้องหลายประการ ซึ่งเร็วที่สุดและมีประสิทธิภาพมากที่สุด We8217 ลองตอบคำถามนี้ในบทความนี้ เราจะทำงานใน SQL 2012 ถ้าคุณต้องการทำตามคุณสามารถใช้ทรัพยากร Query. sql ตัวอย่างที่คุณเห็นว่ามีการแนบ การตั้งค่าข้อมูลและแถลงการณ์เกี่ยวกับปัญหาทางธุรกิจบ่อยครั้งที่ you8217 พบว่าตัวเองกำลังทำธุรกรรมจำนวนมากภายในหนึ่งเดือน แต่ในกรณีของเรา we8217ll ถือว่า you8217ve จัดกลุ่มการทำธุรกรรมไว้แล้วในแต่ละเดือน We8217 กำหนดคีย์หลักของเราให้เป็นประเภทข้อมูล DATE และรวมค่าบางอย่างที่เราต้องการสะสมรวมเป็นสิบสองเดือน นอกจากนี้ยังก่อให้เกิดแผนการค้นหาที่แตกต่างกันเล็กน้อยดังนั้นเราจึงสนใจที่จะดูว่าผลการปฏิบัติงานเปรียบเทียบกับโซลูชันอื่น ๆ ที่มีอยู่ในปัจจุบันได้อย่างไร มากสำหรับการแก้ปัญหาแบบดั้งเดิมและขอโทษของฉันถ้าฉันเกิดขึ้นที่จะมองข้ามหนึ่งในรายการโปรดของคุณ แต่รู้สึกฟรีเพื่อรหัสขึ้นและเพิ่มไปยังเทรลเลอร์ทดสอบประสิทธิภาพ we8217ll นำเสนอในภายหลังเพื่อดูว่าค่าโดยสาร โซลูชันที่ 5: การใช้ Quirky Update หากคุณไม่เคยได้ยิน Quirky Update (QU) และวิธีที่สามารถใช้กับปัญหาเช่นการเรียกใช้ผลรวมผมขอแนะนำให้คุณอ่านบทความที่โดดเด่นนี้โดย SQL MVP Jeff Moden สิทธิแก้ปัญหาการวิ่งรวมและลำดับอันดับปัญหา ก่อนที่เราจะดำเนินการต่อเราควรทราบว่ามีผู้ที่ยืนยันวิธี QU เป็นพฤติกรรมที่ไม่มีเอกสารของ SQL Server และไม่น่าเชื่อถือ เราสามารถพูดได้ว่าไวยากรณ์ถูกอธิบายไว้อย่างชัดเจนโดยรายการ MS Books On Line สำหรับคำสั่ง UPDATE สำหรับ SQL 2005, 2008 และ 2012 ในความเป็นจริงแล้วมันจะย้อนกลับไปไกลกว่านั้น ฉันได้ใช้มันประสบความสำเร็จใน SQL Server 2000 แต่ได้รับมาจาก Sybase และอยู่ในรุ่น SQL Server แรกที่เคยเปิดตัว I82178 กล่าวว่าพฤติกรรม 8220undocumented8221 มีความสอดคล้องกันอย่างน้อยในทุกเวอร์ชันและอาจมีเหตุผลเล็กน้อยที่จะสงสัยว่าจะมีการเลิกใช้งานหรือเปลี่ยนแปลงใน MS SQL เวอร์ชันต่อไป พิจารณาตัวเองเตือนหากคุณเคยพิจารณาใช้ QU เพื่อแก้ปัญหาใด ๆ คุณต้องจดระวังกฎต่างๆที่ใช้ (รวมอยู่ในบทความที่อ้างอิงโดย Jeff) หลักที่ I8217ve จัดการในแบบสอบถามนี้สามารถสรุปได้ดังนี้: ตารางต้องมีดัชนีแบบคลัสเตอร์ซึ่งระบุลำดับของแถวต้นทางตามช่วงเวลาที่คุณต้องการให้ข้าม ตารางต้องมีคอลัมน์ซึ่งคุณสามารถวางรวมการทำงานทั้งหมดได้ เมื่อคุณทำการปรับปรุงคุณต้องล็อคตารางโดยใช้คำแนะนำแบบสอบถาม TABLOCKX เพื่อให้แน่ใจว่าไม่มีใครได้รับใน INSERT s, DELETE s หรือ UPDATE ก่อนที่คุณจะผ่านไป คุณต้องป้องกันไม่ให้ SQL พยายาม parallelize แบบสอบถามโดยใช้คำแนะนำตัวเลือก (MAXDOP 1) เนื่องจากค่าเฉลี่ยสิบสองเดือนนับเป็นตัวเลขที่มีการปลอมแปลงทั้งหมดเราสามารถเพิ่มคอลัมน์ลงในตารางของเราและใช้แบบสอบถาม QU เพื่อคำนวณของเรา ฉันต้องสารภาพว่าสิ่งนี้ดูไม่เป็นระเบียบและมีตัวแปรทั้งหมดที่คุณต้องใช้เพื่อประกาศ โดยทั่วไปสิ่งที่เรากำลังทำอยู่คือการติดตามค่าสิบสอง (ปกคลุมด้วยวัตถุฉนวน) ล่าสุดเพื่อที่จะลบค่าที่ 12 (ซึ่งมีการกำหนดคอลัมน์ Rolling12Months) จากที่อื่น ๆ ทั้งหมดที่เรียกใช้ QU ดังที่อธิบายไว้ในบทความ Jeff8217s เรามีความหวังสูงเกี่ยวกับความเร็วเนื่องจากเป็นที่ทราบกันดีว่าเป็นวิธีที่เร็วที่สุดในการแก้ปัญหาทั้งหมดที่เกิดขึ้น อีกครั้งคุณควรโน้มน้าวตัวเองว่าผลลัพธ์สอดคล้องกับโซลูชันก่อนหน้าและใช่โซลูชันนี้ยังคงทำงานเหมือนกันใน SQL 2012 หาก you8217re กับฉันจนถึงตอนนี้คุณอาจจะถามตัวเอง 8220 สิ่งที่เกิดขึ้นถ้าฉันต้องการคำนวณการทำงานหลาย สิบสองเดือนรวมในพาร์ทิชันที่แตกต่างกัน 8221 นี้ค่อนข้างง่ายสำหรับทุกโซลูชั่นอื่น ๆ ที่นำเสนอ แต่ไม่เสนอบิตของความท้าทายโดยใช้ QU คำตอบนี้สามารถพบได้ในไฟล์ทรัพยากรที่แนบมา: Quirky Update Partitioned. sql SQL 2012 Solutions จนถึงขณะนี้ทุกสิ่งทุกอย่างที่เราได้ทำจะทำงานใน SQL 2008 สิ่งเดียวที่เราไม่ได้รับการสนับสนุนใน SQL 2005 คือการเริ่มต้นตัวแปรที่เราประกาศ DECL ในวิธี QU ตอนนี้ let8217 ดูว่า SQL 2012 มีคุณลักษณะใหม่ ๆ ที่สามารถใช้กับปัญหานี้ได้อย่างไร โซลูชันที่ 6: การใช้กรอบหน้าต่างโซลูชัน SQL 2012 แรกของเรา (6) แสดงวิธีการใช้กรอบหน้าต่างที่เริ่ม 11 แถวก่อนแถวปัจจุบันผ่านแถวปัจจุบันเพื่อ SUM ผลลัพธ์ที่ต้องการของเรา อีกครั้งผลลัพธ์ที่ได้จะเหมือนกัน แต่แผนแบบสอบถามค่อนข้างแตกต่างจากโซลูชันก่อนหน้าของ SQL 2012 อย่างไรก็ตามเราไม่เห็นด้วยอย่างยิ่งว่าแนวทางนี้จะให้ผลลัพธ์ที่สมเหตุสมผลเนื่องจากจำนวน 8220look-backs8221 ที่จำเป็นสำหรับการทำงาน . การเปรียบเทียบประสิทธิภาพของวิธีทดสอบการทดสอบจริงเพื่อดูว่าการแก้ปัญหาหลายวิธีดำเนินการคือการตรวจสอบเวลาในการดำเนินการจริงในเซิร์ฟเวอร์ที่ไม่มีการหยุดนิ่งโดยใช้เทรลย์ทดสอบหลายแถว ชุดทดสอบของเราจะแสดงพร้อมกับวิธีการแก้ปัญหา 1 และ 2 ที่ได้รับการแก้ไข (อ้างถึงข้อคิดเห็นในโค้ด) เพื่อ: แทรกผลลงในตารางชั่วคราวเพื่อหลีกเลี่ยงผลกระทบต่อเวลาที่ผ่านไปของการส่งคืนแถวข้อมูลไปยังผลลัพธ์ของ Studio8217s ของ SQL Server Management Studio ตะแกรง ลบเลขคณิต DATE เนื่องจากเมื่อสร้างสายรัดทดสอบหลายล้านแถวเป็นการยากที่จะสร้างเดือนที่ไม่ซ้ำหลายเดือนดังนั้นคอลัมน์ของตารางวันที่ได้รับการแก้ไขให้เป็นประเภทข้อมูล BIGINT สำหรับโซลูชันที่เหลือ (2 8211 6) เราได้กราฟผลลัพธ์ CPU และ Elapsed จาก 1M ถึง 4M แถว การตีความผลลัพธ์ที่ผ่านไปและเวลาของ CPU ดูเหมือนจะสอดคล้องกับวิธีการต่างๆที่เกี่ยวกับการสั่งซื้อ ทั้งหมดดูเหมือนจะมีขนาดเป็นเส้นตรง สมมติว่าคุณสามารถเข้าใจได้และกฎที่เกี่ยวข้องทั้งหมดของคุณดูเหมือนว่าจะเป็นทางออกที่เร็วที่สุดในการแก้ปัญหานี้แม้ว่าจะพิจารณาคุณสมบัติใหม่ ๆ ที่มีใน SQL 2012 ใน SQL 2012 วิธีกรอบหน้าต่างก็เป็นระเบียบเรียบร้อย " กะทัดรัดและสง่างาม แต่เล็กน้อยร่องรอยแก้ปัญหาการแก้ปัญหา Quirky ปรับปรุงทั่วแถวที่เราทดสอบ ผลการทดสอบเหล่านี้ดูเหมือนว่าจะสอดคล้องกับการทดสอบก่อนหน้านี้เกี่ยวกับผลรวมทั้งหมดใน SQL 8220Denali8221 CTP3 โดย Microsoft Certified Master Wayne Sheffield ในบล็อกของเขา หาก you8217re ติดอยู่กับ SQL รุ่นก่อนหน้า (2005 หรือ 2008) และด้วยเหตุใดคุณจึงสามารถใช้งาน Quirky Update ได้ (เช่นถ้าคุณ don8217t เชื่อมั่นในพฤติกรรมที่ไม่ได้รับการแจ้งเตือนนี้) โซลูชันที่รวดเร็วที่สุดที่คุณสามารถใช้ได้คือ TOP CROSS APPLY TOP หรือ โดยใช้แบบสอบถามย่อยที่มีความสัมพันธ์เนื่องจากทั้งสองคนดูเหมือนจะอยู่ในกลุ่มที่มีความเกี่ยวข้องกันทั่วกระดาน ดูเหมือนว่า 8220traditional8221 INNER JOIN เป็นสิ่งที่ควรหลีกเลี่ยง อาจจะเลวร้ายยิ่งถ้าคุณต้องคำนวณเลขที่ภายใน JOIN8217s ON clause ในทำนองเดียวกันการใช้ Tally Table หรือหลาย LAG s (SQL 2012) อย่างแน่นอนไม่ใช่ทางที่จะไป เราไม่ได้สำรวจโซลูชันที่ใช้ CURSOR แต่คุณสามารถย้อนกลับไปที่บทความที่อ้างถึงผลรวมทั้งหมดเพื่อให้ทราบว่าแอ็พพลิเคชันเหล่านี้สามารถทำงานได้อย่างไรในกรณีนี้ I8217ve ยังได้เห็นโซลูชันบางอย่างที่ใช้ Recursive Common Table Expression (rCTE) แต่ฉันไม่คิดว่าจะมีประสิทธิภาพมากที่สุดเมื่อเทียบกับโซลูชัน QU หรือกรอบหน้าต่าง มีหลายวิธีในการคำนวณค่าภายในหน้าต่างกลิ้งใน SQL และมีผู้ชนะประสิทธิภาพที่ชัดเจนในหมู่พวกเขา เราหวังว่าคุณจะพบคู่มือนี้เกี่ยวกับวิธีที่มีให้น่าสนใจและให้ข้อมูล สมัครรับข่าวสารเพิ่มเติมจดหมายข่าวรายปักษ์ช่วยพัฒนาทักษะของคุณและช่วยให้คุณก้าวไปข้างหน้าด้วยบทความ ebooks และความคิดเห็นเพื่อแจ้งให้คุณทราบ Dwain Camps เป็นผู้จัดการโครงการมานานหลายปีแล้ว เนื่องจากประสิทธิภาพการทำงานของแอพพลิเคชันสามารถเป็นปัจจัยความสำเร็จที่สำคัญสำหรับโครงการเขาได้รับการประกาศถึงความจำเป็นในการพัฒนา SQL ที่มีประสิทธิภาพสูง โดยการให้คำปรึกษาและเขียนบทความเกี่ยวกับ SQL เขาหวังที่จะฝึกอบรมวิศวกรซอฟต์แวร์ในอนาคตด้วยวิธีที่ถูกและผิดในการส่งมอบโค้ด SQL นอกจากนี้เขายังมีความสนใจเป็นพิเศษในการพัฒนาโซลูชันสำหรับปัญหาที่ซับซ้อนโดยใช้ SQL ที่มีประสิทธิภาพสูงเนื่องจากลักษณะที่เปิดเผยของ SQL ช่วยให้สามารถพัฒนาอัลกอริทึมที่ไม่เหมือนใครซึ่งภาษากระบวนการอาจไม่สามารถใช้งานได้ บทความดีมากฉันรู้สึกประหลาดใจที่ LAG () ทำไม่ดี ฉันเดาการภาวนาแต่ละครั้งจะทำแยกต่างหากแทนที่จะเป็นปัจจัยที่ออกและเพิ่มประสิทธิภาพเช่นหน้าต่าง คำอธิบายที่ยอดเยี่ยมฉันเห็นด้วยนี่เป็นคำอธิบายที่ดีในหลายวิธีในการคำนวณค่าภายในหน้าต่างกลิ้ง ถ้าคุณทดสอบตัวอย่างเหล่านี้ใน SQL 2012 คุณต้องเปลี่ยน MyTable ด้วย RollingTotalsExample ขอบคุณมากนาย Camps Tally วิธี Hi Dwain ผมสังเกตเห็นว่าแบบสอบถามตาราง Tally ของคุณก่อให้เกิดการดำเนินการ Spool ตารางและคิดว่าคุณอาจพิจารณาทำส่วน Tally ของตารางวันที่เช่นนี้ SELECT GroupingDate, ValueMAX (CASE GroupingDate เมื่อวันที่ THEN a. Value END), Rolling12MonthsCASE เมื่อ ROWNUMBER () OVER (ORDER BY GroupingDate) lt 12 THEN NULL อื่น ๆ SUM (ค่า) END INTO ResultsSoln2 จาก RollingTotals ตัวอย่างของ CROSS APPLY (mdash ลบค่าเลขคณิต DATE (Date), (Date1) (วันที่ 2), (วันที่ 2), (วันที่ 3), (วันที่ 4), (วันที่ 5), (วันที่ 6), (วันที่ 7), (วันที่ 8), (วันที่ 9), (วันที่ 10), (วันที่ 11)) กลุ่ม (กลุ่ม) MAX (วันที่) ORDER BY GroupingDate (ขออภัยถ้าการจัดรูปแบบไม่ดี ndash no preview) การเปลี่ยนแปลงนี้ยังคงไม่เกิดขึ้นทำให้เป็น contender แต่จะทำการปรับปรุงที่ใหญ่ขึ้นให้กับ queryhellip ที่ขอบคุณสำหรับความคิดเห็น Joe and Nic Irsquom ดีใจที่คุณพบบทความที่น่าสนใจ Joe: ฉันรู้สึกประหลาดใจเล็กน้อยที่ผล LAG และมันทำให้ฉันสงสัยว่าจุดคุ้มทุนจะเป็นอย่างไร บางที 3 เดือนอาจไม่เลวร้าย แต่ก็ยังยากที่จะเชื่อว่าอาจเร็วกว่า QU ตาราง Tally MM: ด้วยเหตุผลบางประการฉันมีความชอบส่วนบุคคลสำหรับตาราง Tally ในบรรทัด แต่ผลลัพธ์ของคุณน่าสนใจถ้าพิจารณาเฉพาะกรณีอื่น ๆ ความช่วยเหลือเกี่ยวกับการย้ายรวมประจำปีโพสต์ครั้งแรกของฉัน ฉันจำเป็นต้องคำนวณยอดรวมประจำปีที่ย้ายสำหรับค่าดังกล่าวข้างต้นสำหรับ 12 เดือนก่อนหน้านี้โดยเดือนนี้เป็นเดือนที่ 12 จากนั้นฉันต้องได้รับ Moving Annual Annual สำหรับ 12 เดือนก่อนหน้านี้ ด้วยความคิดที่จะเปรียบเทียบ MAT สำหรับเดือนนี้กับเดือนที่สอดคล้องกันของปีที่แล้วและสำหรับแต่ละเดือนก่อนหน้า ความพยายามของฉันให้ฉันนี้: ด้วย cte as (SELECT rNum ROWNUMBER () Over (order by Date) วันที่มูลค่า Rolling12MonthsCASE เมื่อ ROWNUMBER () OVER (ORDER BY Date) gt 11 THEN SUM (Value) OVER (ORDER BY วันที่ ROWS ระหว่าง 11 เลือกและจากปัจจุบัน) เลือกจาก cte (เลือก mRNum max (rNum) จาก cte) deMax โดยที่ rNum ระหว่าง mRNum ndash 23 และ mRNum ด้วยความสามารถในการเปลี่ยนคำสั่ง Were เพื่อสะท้อนว่าฉันต้องการปีนี้หรือไม่ ปีก่อน. ข้อมูลจริงของฉันมีวันที่ใน Integer 201409 ซึ่งฉันคิดว่าจะทำให้ชีวิตง่ายขึ้นสำหรับฉันเพราะฉันสามารถลบ 100 เพื่อให้ได้ปีก่อนหน้า บทความยอดเยี่ยมและความช่วยเหลือใด ๆ จะได้รับการชื่นชม นี่คือโซลูชันการทำงานของฉัน (มีเสียง) mdash Rolling 12 เดือนโดยใช้ SQL 2012 และกรอบหน้าต่าง IF OBJECTID (lsquotempdb ..PreviousYearrsquo) ไม่ใช่ NULL DROP TABLE PreviousYear ด้วย cte เป็น (SELECT rNum ROWNUMBER () Over (เรียงตามวันที่ ) วันที่มูลค่า Rolling12MonthsCASE เมื่อ ROWNUMBER () OVER (ORDER BY Date) gt 11 THEN SUM (ค่า) OVER (ORDER BY วันที่ ROWS ระหว่าง 11 กำหนดไว้ล่วงหน้าและ ROCUS ROW) END จาก RollingTotals ตัวอย่าง) เลือก pyRowNum ROWNUMBER () Over (order by mRNum ) . เริ่มต้นใช้งานครั้งที่ 24 eEnd mRNum ndash 12 ลงใน PreviousYear จาก cte, (เลือก mRNum max (rNum) จาก cte) deMax โดยที่ rNum ระหว่าง mRNum ndash 23 และ mRNum ndash 12 mdash การกลิ้ง 12 เดือนโดยใช้ SQL 2012 และกรอบหน้าต่าง IF OBJECTID (lsquotempdb..Yearrsquo) ไม่ได้เป็น NULL DROP TABLE ปีนี้กับ cte as (SELECT rNum ROWNUMBER () Over (order by Date) วันที่มูลค่า Rolling12MonthsCASE เมื่อ ROWNUMBER () OVER (ORDER BY Date) gt 11 THEN SUM (Value) OVER (ORDER BY Date ROWS ระหว่างแถวที่กำหนดไว้ล่วงหน้าและตอนที่ 11) จากโรลลิ่งสโตนตัวอย่าง) เลือก tyRowNum ROWNUMBER () Over (order by mRNum) . เริ่มต้นใช้งาน mRNum ndash 11. eEnd mRNum เป็น ThisYear จาก cte, (เลือก mRNum max (rNum) จาก cte) deMax โดยที่ rNum ระหว่าง mRNum ndash 11 และ mRNum เลือกจาก ThisYear ty Left Join PreviousYear py บน ty. tyRowNum py. pyRowNum เหล่านี้อาจทำงานได้ Irsquom ไม่อยู่ใกล้กับ comp เข้าถึง sql ตอนนี้ดังนั้นฉัน canrsquot ทดสอบ (อาจมีข้อผิดพลาด typossyntax บาง) YMAValueField จาก DataTable AS T ORDER โดย T. DateKey ASC ในกรณีที่ AVG เป็นหนึ่งในฟังก์ชั่นการรวมไม่ได้รับการสนับสนุนเลือก T. DateKey, AVG (T. ValueField) OVER (ODER BY T. DateKey ASC ระหว่าง 365 ก่อนกำหนดและปัจจุบัน ROW) กับช่วง BETWEEN (ฉันรู้ว่า SUM ได้รับการสนับสนุน) เลือก T. DateKey, SUM (T. ValueField) OVER (ODER BY T. DateKey ASC ระหว่าง 365 ก่อนกำหนดและปัจจุบัน) เมื่อ DATEDIFF (DAY, StartDate, T. DateKey) lt 365 THEN DATEDIFF (DAY, StartDate, T. วันเดือนปีที่ผ่านมา) ELSE 365 END AS YMAValueField จาก DataTable AS T ORDER โดย T. DateKey ASC สมัครรับข่าวสารเพิ่มเติมบทความจดหมายข่าวรายปักษ์ช่วยพัฒนาทักษะของคุณและทำให้คุณก้าวไปข้างหน้าพร้อมกับบทความ ebooks และความคิดเห็นเพื่อแจ้งให้คุณทราบ เยี่ยมชมห้องสมุดของบทความของเราเพื่อดูรูปแบบและแนวทางปฏิบัติที่จำเป็นสำหรับการจัดส่งฐานข้อมูลที่คล่องตัวขึ้น ค้นหาวิธีทำให้กระบวนการสร้างการทดสอบและใช้งานการเปลี่ยนแปลงฐานข้อมูลของคุณโดยอัตโนมัติเพื่อลดความเสี่ยงและเพิ่มความเร็วในการจัดส่ง บทความล่าสุดที่เกี่ยวข้องกับการเขียนโปรแกรมใน T SQL นอกจากนี้ใน SQL ด้วยการเพิ่มขึ้นของฐานข้อมูล NoSQL ที่ใช้ประโยชน์จากด้าน SQL สำหรับการสอบถามและการทำธุรกรรมเต็มรูปแบบจะมีอันตรายต่อรูปแบบเอกสารข้อมูลแบบลำดับชั้นที่ก่อให้เกิดปัญหาพื้นฐาน ความขัดแย้งกับทฤษฎีเชิงสัมพันธ์เราถามผู้เชี่ยวชาญด้านความสัมพันธ์ของเรา Hugh Bin-Haad เพื่ออธิบายถึงจุดที่ยากสำหรับนักทฤษฎีฐานข้อมูล. hellipอ่านเพิ่มเติมนอกจากนี้ใน SQL Server ทุกโปรแกรม SQL Server Database จำเป็นต้องคุ้นเคยกับ System Functions (เช่น Rowcount หรือเอกลักษณ์) เพื่อไร้สาระ (IsNumeric ()) Robert Sheldon ให้ภาพรวมของการใช้กันมากที่สุดของ them. hellip อ่านเพิ่มเติมนอกจากนี้ใน T - SQL Programming เพื่อให้สามารถใช้ประโยชน์จาก แค็ตตาล็อกระบบเพื่อหาข้อมูลเพิ่มเติมเกี่ยวกับฐานข้อมูลคุณต้องทำความคุ้นเคยกับฟังก์ชันเมตาดาต้า ประหยัดเวลาและพิมพ์เมื่อสอบถามเมทาดาทา เมื่อคุณได้รับการแขวนของฟังก์ชันเหล่านี้แคตตาล็อกของระบบก็ดูเหมือนว่าใช้งานง่ายเช่นเดียวกับโรเบิร์ตเชลดอนอธิบายไว้ในบทความนี้อ่านเพิ่มเติมนอกจากนี้ใน T-SQL Programming ข้อคิดเห็นกับนักพัฒนาซอฟต์แวร์ที่ยืนยันว่าการค้นหาคลุมเครือหรือการตรวจสอบตัวสะกด จะทำภายในโปรแกรมประยุกต์แทนที่จะเป็นฐานข้อมูลเชิงสัมพันธ์ที่เป็นแรงบันดาลใจให้ Phil Factor เพื่อแสดงว่ามันทำอย่างไร เมื่อฐานข้อมูลต้องพบเนื้อหาที่เกี่ยวข้องจากคำค้นหาที่ป้อนโดยผู้ใช้ฐานข้อมูลต้องเรียนรู้ที่จะคาดหวังและจัดการกับทั้งคาดการณ์และไม่คาดฝัน hellip อ่านสำเนาเพิ่มเติม 2005 - 2017 Red Gate Software Ltd คุณคิดอย่างไรกับ Simple Talk ใหม่ให้ ความคิดเห็นของคุณ