คำนวณ เคลื่อนไหว ค่าเฉลี่ย ใน Sql เซิร์ฟเวอร์

ฉันกำลังทำงานกับ SQL Server 2008 R2 พยายามคำนวณค่าเฉลี่ยเคลื่อนที่ สำหรับแต่ละเร็กคอร์ดในมุมมองของฉันฉันต้องการรวบรวมข้อมูลจาก 250 ระเบียนก่อนหน้าและคำนวณค่าเฉลี่ยสำหรับการเลือกนี้ คอลัมน์มุมมองของฉันมีดังนี้: TransactionID ไม่ซ้ำกัน สำหรับแต่ละ TransactionID ฉันต้องการคำนวณค่าเฉลี่ยสำหรับค่าของคอลัมน์มากกว่า 250 ระเบียนก่อนหน้า ดังนั้นสำหรับ TransactionID 300 ให้เก็บค่าทั้งหมดจากแถวก่อนหน้า 250 แถว (มุมมองจะเรียงลำดับจากมากไปน้อยโดย TransactionID) จากนั้นในคอลัมน์ MovAvg จะเขียนค่าเฉลี่ยของค่าเหล่านี้ ฉันต้องการรวบรวมข้อมูลภายในช่วงระเบียน ถาม 28 ต. ค. ที่เวลา 20: 58 ก่อนหน้านี้เราได้พูดถึงวิธีการเขียนค่าเฉลี่ยของม้วนใน Postgres ตามความต้องการที่เป็นที่นิยมได้แสดงวิธีการทำเช่นเดียวกันใน MySQL และ SQL Server รวมทั้งวิธีการอธิบายแผนภูมิที่มีเสียงดังเช่นนี้ด้วยเส้นเฉลี่ย 7 วันดังนี้: ความคิดที่ยิ่งใหญ่แผนภูมิแรกของเราด้านบนมีความดังและมีข้อมูลที่เป็นประโยชน์ เราสามารถทำให้เป็นไปอย่างราบรื่นโดยการคำนวณค่าเฉลี่ย 7 วันที่ด้านบนของข้อมูลพื้นฐาน ซึ่งสามารถทำได้ด้วยฟังก์ชันหน้าต่างการรวมตัวหรือคำจำกัดความย่อยที่มีความสัมพันธ์ - รวมทั้งสองอย่างแรก เริ่มต้นด้วยค่าเฉลี่ยก่อนหน้าซึ่งหมายความว่าค่าเฉลี่ยจุดในวันที่ 7 ของเดือนมีค่าเฉลี่ย 7 วันแรก การมองเห็นจะเป็นการเลื่อนตำแหน่งของกราฟไปทางขวาเนื่องจากมีการเพิ่มขึ้นเป็นเวลานานในเจ็ดวันต่อไปนี้ ขั้นแรกสร้างตารางจำนวนขั้นกลางเราต้องการคำนวณค่าเฉลี่ยสำหรับจำนวนการลงชื่อสมัครใช้ทั้งหมดในแต่ละวัน สมมติว่าเรามีตารางผู้ใช้ทั่วไปที่มีแถวต่อผู้ใช้รายใหม่และสร้างไทม์สแต้มเราสามารถสร้างตารางการลงชื่อสมัครใช้ของเราได้ดังนี้: ใน Postgres และ SQL Server คุณสามารถใช้งานนี้เป็น CTE ใน MySQL คุณสามารถบันทึกเป็นตารางชั่วคราวได้ Postgres Rolling Average โชคดีที่ Postgres มีฟังก์ชันของหน้าต่างซึ่งเป็นวิธีที่ง่ายที่สุดในการคำนวณค่าเฉลี่ยในการทำงาน แบบสอบถามนี้อนุมานว่าวันที่ไม่มีช่องว่าง แบบสอบถามมีค่าเฉลี่ยในช่วงเจ็ดแถวที่ผ่านมาไม่ใช่วันที่เจ็ดที่ผ่านมา หากข้อมูลของคุณมีช่องว่างให้เติมข้อมูลเหล่านี้ด้วยคำสั่ง generateseries หรือเข้าร่วมกับตารางที่มีแถววันที่หนาแน่น MySQL Rolling Average MySQL ขาดฟังก์ชันหน้าต่าง แต่เราสามารถคำนวณได้โดยใช้ Self-Join สำหรับแต่ละแถวในตารางการนับของเราเราเข้าร่วมทุกแถวที่อยู่ในช่วงเจ็ดวันที่ผ่านมาและใช้ค่าเฉลี่ย แบบสอบถามนี้จะจัดการกับช่องว่างของวันที่โดยอัตโนมัติขณะที่เรากำลังดูแถวภายในช่วงวันที่แทนที่จะเป็นแถว N ก่อนหน้า SQL Server Rolling Average SQL Server มีฟังก์ชันของหน้าต่างดังนั้นการคำนวณค่าเฉลี่ยโดยรวมสามารถทำได้ทั้งแบบ Postgres หรือแบบ MySQL สำหรับความเรียบง่ายใช้ MySQL เวอร์ชันที่มีการเข้าร่วมด้วยตนเอง นี่คือ conceptually เช่นเดียวกับใน MySQL การแปลเพียงอย่างเดียวคือฟังก์ชัน dateadd และระบุชื่อกลุ่มตามคอลัมน์อย่างชัดเจน ค่าเฉลี่ยอื่น ๆ เราเน้นที่ค่าเฉลี่ยถ่วงน้ำหนัก 7 วันในโพสต์นี้ ถ้าเราต้องการดูค่าเฉลี่ยชั้นนำ 7 วันการจัดเรียงวันในทิศทางอื่นทำได้ง่ายเพียงใด ถ้าเราต้องการดูค่าเฉลี่ยที่ตรงกลางให้ใช้ wed: Postgres: แถวระหว่าง 3 ก่อนหน้าและ 3 ตาม MySql: ระหว่าง signups. date - 3 และ signups. date 3 ใน MySQL SQL Server: ระหว่าง dateadd (day, -3, signups) SQL Server T-SQL รหัสการคำนวณค่าเฉลี่ยเคลื่อนที่โดย: Dallas Snider อ่านความคิดเห็นเคล็ดลับที่เกี่ยวข้อง: ฟังก์ชันเพิ่มเติม - UDF ที่กำหนดโดยผู้ใช้ฉันจะทำให้ข้อมูลในคอลัมน์มีความคลาดเคลื่อนได้อย่างไร ค่าเฉลี่ยเคลื่อนที่ใน T-SQL คุณสามารถเดินผ่านตัวอย่างใน SQL Server ด้วยรหัส T-SQL ได้อย่างไรเราสามารถตรวจสอบความถูกต้องได้ผลลัพธ์ข้อมูลชุดข้อมูลเวลามีความเสียงดังและมีวิธีที่ดีในการทำให้ข้อมูลที่ราบรื่นขึ้นคือการคำนวณค่าเฉลี่ยเคลื่อนที่ . มีหลายวิธีในการคำนวณค่าเฉลี่ยเคลื่อนที่ใน T-SQL แต่ในเคล็ดลับนี้เราจะดูวิธีคำนวณค่าเฉลี่ยเคลื่อนที่ที่กำหนดค่าเฉลี่ยของหน้าต่าง x จำนวนแถวหลังและ x จำนวนแถวถัดจากปัจจุบัน แถวข้อมูล ข้อได้เปรียบของข้อนี้คือไม่มีค่าเฉลี่ยของความล่าช้าและค่าเฉลี่ยเคลื่อนที่จะอยู่ในแถวเดียวกันกับค่าปัจจุบัน ให้เริ่มด้วยการสร้างตารางและโหลดข้อมูลบางส่วนโดยใช้ T-SQL ด้านล่าง เรามีจุดข้อมูล 361 จุดที่สร้างคลื่นไซน์ที่มีเสียงดัง หลังจากโหลดข้อมูลเราจะเรียกใช้โค้ด T-SQL ต่อไปนี้เพื่อเลือกคอลัมน์ทั้งหมดพร้อมกับค่าเฉลี่ยที่เคลื่อนที่ ในโค้ดด้านล่างขนาดหน้าต่างเฉลี่ยเคลื่อนไหวคือ 15 (7 แถวก่อนแถวปัจจุบันและแถวปัจจุบันบวก 7 แถวต่อไปนี้) ค่าเฉลี่ยเคลื่อนที่ของคอลัมน์ DataValue ถูกส่งกลับเป็นคอลัมน์ MovingAverageWindowSize15 คำสั่ง ORDER BY เป็นสิ่งสำคัญมากในการเก็บข้อมูลตามลำดับที่ถูกต้อง เราสามารถคัดลอกและวางผลลัพธ์ลงใน Excel เพื่อตรวจสอบการคำนวณได้อย่างถูกต้อง ในภาพด้านล่างหน้าต่างจะเริ่มทำงานในเซลล์ C3 และสิ้นสุดที่ C17 ค่าเฉลี่ยเคลื่อนที่ที่คำนวณโดย T-SQL ในส่วนปลายนี้จะปรากฏในเซลล์ D10 ค่าเฉลี่ยที่คำนวณโดย Excel อยู่ที่ด้านล่างและเท่ากับค่าใน D10 ในภาพด้านล่างเราจะเห็นค่าข้อมูลเดิมที่วางแผนไว้เป็นสีน้ำเงินโดยมีค่าเฉลี่ยเคลื่อนที่เป็นสีแดง ขั้นตอนถัดไปปรับขนาดของหน้าต่างเฉลี่ยที่เคลื่อนที่เพื่อดูว่าพล็อตเปลี่ยนแปลงไปอย่างไร นอกจากนี้โปรดตรวจสอบเคล็ดลับอื่น ๆ เกี่ยวกับ T-SQL จาก mssqltips: อัปเดตล่าสุด: 382016