ชี้แจง ถัว เฉลี่ยเคลื่อนที่ Sql

ฉันอ่านบทสนทนาที่คุณพูดถึงแล้ว สามารถใช้ได้กับ PostgreSQL เนื่องจากได้รับอนุญาตให้สร้างฟังก์ชันการรวมที่ผู้ใช้กำหนดโดยใช้ SQL ใน PostgreSQL แต่ไม่ได้รับอนุญาตให้ใช้ใน SQL Server การใช้ recursive CTE เป็นวิธีการที่เป็นไปได้ใน SQL Server แต่ฉันสังเกตเห็นว่าวิธี CTE อาจทำให้เกิดการสแกนตารางมากกว่าฟังก์ชันหน้าต่าง ดังนั้นฉันจะโพสต์นี้เพื่อถามว่าเป็นไปได้ในการคำนวณค่าเฉลี่ยเคลื่อนที่แบบเสวนาโดยใช้ฟังก์ชันหน้าต่าง SQL Server 2012 เช่นเดียวกับการคำนวณค่าเฉลี่ยเคลื่อนที่ที่เรียบง่าย ndash xiagao1982 Apr 14 13 at 2:53 อันดับแรกคุณคำนวณ EMA (SMA (x)) แทน EMA (x) ประการที่สองค่าคงที่ quotsmoothing ของคุณเป็นค่า Beta ในสูตรของฉันไม่ใช่ค่า alpha ด้วยการเปลี่ยนแปลงทั้งสองแบบนี้ SQLFiddle มีลักษณะดังนี้: sqlfiddle6191921 อย่างไรก็ตามยังมีความแตกต่างเล็กน้อยระหว่างผลลัพธ์ที่เกิดขึ้นจริงกับผลลัพธ์ที่คาดไว้ ฉันจะกลับไปดูว่าคำจำกัดความ EMA ตรงกับที่ฉันรู้จักหรือไม่ ndash: Sebastian Meine 7 พฤษภาคม 13 เวลา 13:46 ฉันเพียงแค่ดูที่สูตรในสเปรดชีตที่คุณแนบมาและเป็นวิธีปิดคำจำกัดความมาตรฐาน EMA สูตรของฉันคำนวณค่าเฉลี่ยของการเคลื่อนที่แบบเสวนาของแถวสุดท้าย 10 แถว สเปรดชีตจะคำนวณค่าเฉลี่ยมาตรฐานแรกในแถวสุดท้าย 10 แถวและค่าเฉลี่ยเคลื่อนที่ที่ถ่วงน้ำหนักแบบทวีคูณที่ไม่ จำกัด สำหรับค่าเฉลี่ยทั้งหมด ค่าเฉลี่ยเคลื่อนที่ที่ถ่วงน้ำหนักใน T-SQL ค่าเฉลี่ยเคลื่อนที่เชิงเส้นมีค่าใกล้เคียงกับค่าเฉลี่ยเคลื่อนที่ที่ถ่วงน้ำหนักโดยให้น้ำหนักน้อยกว่าการเปลี่ยนแปลงที่ผ่านมานานและมีน้ำหนักมากขึ้น การเปลี่ยนแปลงล่าสุด ค่าเฉลี่ยเคลื่อนที่ที่ถ่วงน้ำหนักเป็นเส้นตรง แต่ค่าเฉลี่ยเคลื่อนที่แบบเสวนาคือเลขยกกำลัง นั่นคือน้ำหนักที่สามารถแสดงเป็นเส้นโค้ง: มีวิธีที่ยอดเยี่ยมในการคำนวณค่าเฉลี่ยเคลื่อนที่ที่อธิบายใน T-SQL โดยใช้คุณลักษณะที่ไม่มีเอกสารเกี่ยวกับตัวแปรและจำนวนทั้งหมดที่รันใน SQL Server ในโพสต์บล็อกนี้ฉันจะแสดงวิธีการใช้วิธีการคำนวณค่าเฉลี่ยเคลื่อนที่ที่อธิบายใน T-SQL แต่ฉันยังจะนำเสนอวิธีการที่ใช้คุณลักษณะมาตรฐานใน SQL Server แต่น่าเสียดายที่นั่นหมายถึงการใช้ลูป ในตัวอย่างฉันจะคำนวณค่าเฉลี่ยเคลื่อนที่แบบเสวนา 9 วัน ตัวอย่างใช้ฐานข้อมูล TAdb คุณสามารถดูสคริปต์เพื่อสร้าง TAdb ได้ที่นี่ ค่าเฉลี่ยการเคลื่อนที่แบบ Exponential (EMA): การคำนวณ Total Totals Method ทฤษฎีที่อยู่เบื้องหลังการทำงานของคุณลักษณะทั้งหมดในการอัพเดตจะอธิบายโดยละเอียดโดย Jeff Moden ในบทความของเขาในการแก้ปัญหา Total Overall and Ordinal Rank แหล่งข้อมูลอื่น ๆ ที่อธิบายโดยใช้วิธีนี้ในการคำนวณ EMA คือการโพสต์บล็อกการคำนวณค่าเฉลี่ยเคลื่อนที่โดย T-SQL โดย Gabriel Priester และฟอรัมโพสต์ความท้าทายเฉลี่ยการเคลื่อนย้ายเลขประจำ ทั้งใน SQL Server Central โดยทั่วไปใน T-SQL คุณสามารถปรับปรุงตัวแปรเช่นเดียวกับคอลัมน์ในคำสั่ง update การปรับปรุงจะทำแถวโดยแถวภายในโดย SQL Server พฤติกรรมแถวละบรรทัดนี้คือสิ่งที่ทำให้การคำนวณรวมการทำงานเป็นไปได้ ตัวอย่างนี้แสดงวิธีการทำงาน: โปรดทราบว่า 8220ColumnRunningTotal8221 เป็นผลรวม 8220ColumnToSum8221 ที่ทำงานอยู่ ด้วยวิธีนี้เราสามารถคำนวณ EMA9 ด้วย T-SQL นี้ได้การคำนวณ EMA ค่อนข้างง่าย เราใช้แถวปัจจุบันและแถวก่อน แต่มีน้ำหนักมากขึ้นในแถวปัจจุบัน น้ำหนักคำนวณจากสูตร 2 (19) โดยที่ 822098221 เป็นพารามิเตอร์สำหรับความยาวของ EMA ในการคำนวณ EMA9 สำหรับแถว 10 ด้านบนการคำนวณคือ: ในกรณีนี้แถวปัจจุบันมีน้ำหนัก 20 (2 (19) 0.2) และแถวก่อนหน้าจะมีน้ำหนัก 80 (1-2 (19) 0.8) คุณพบการคำนวณนี้ในคำแถลงข้างต้นในคำสั่ง CASE: ค่าเฉลี่ยเคลื่อนที่แบบเสวนา (EMA): วิธีวนรอบเท่าที่ฉันรู้ยกเว้นวิธีการทั้งหมดที่ระบุไว้ด้านบนไม่มีวิธีใดในการคำนวณ EMA โดยใช้คำสั่ง SQL ที่กำหนดไว้ . ดังนั้น T-SQL ด้านล่างใช้ห่วงขณะที่คำนวณ EMA9: ผลลัพธ์จะเหมือนกับในตัวอย่างผลรวมด้านบน ประสิทธิภาพตามที่คาดไว้ชุดทำงานตามผลรวมเป็นรุ่นที่เร็วกว่ารุ่นของวง ในเครื่องของฉันชุดโซลูชั่นตามประมาณ 300 ms เทียบกับประมาณ 1200 กับรุ่นห่วง รุ่นของลูปมีความสอดคล้องกับมาตรฐาน SQL มากขึ้น ดังนั้นทางเลือกระหว่างวิธีการขึ้นอยู่กับ what8217s ที่สำคัญที่สุดสำหรับคุณประสิทธิภาพหรือมาตรฐาน ค่าเฉลี่ยเคลื่อนที่แบบทึบที่สามารถใช้ในการวิเคราะห์แนวโน้มเช่นเดียวกับค่าเฉลี่ยเคลื่อนที่อื่น ๆ Simple Moving Average (SMA) และ Weighted moving average (WMA) นอกจากนี้ยังมีการคำนวณอื่น ๆ ในการวิเคราะห์ทางเทคนิคที่ใช้ EMA เช่น MACD เป็นต้น โพสต์บล็อกนี้เป็นส่วนหนึ่งของซีรี่ส์เกี่ยวกับการวิเคราะห์ทางเทคนิค TA ใน SQL Server ดูโพสต์อื่นที่นี่ โพสต์โดย Tomas Lind Tomas Lind - บริการให้คำปรึกษาเป็น SQL Server DBA และนักพัฒนาฐานข้อมูลที่ High Coast Solutions ฐานข้อมูล AB การสำรวจความผันผวนเฉลี่ยถ่วงน้ำหนักที่ชี้แจงเป็นค่าเฉลี่ยของการวัดความเสี่ยงที่พบบ่อยที่สุด แต่มาในหลายรสชาติ ในบทความก่อนหน้านี้เราได้แสดงวิธีการคำนวณความผันผวนทางประวัติศาสตร์ที่เรียบง่าย เราใช้ข้อมูลราคาหุ้นที่เกิดขึ้นจริงของ Google เพื่อคำนวณความผันผวนรายวันตามข้อมูลหุ้นภายใน 30 วัน ในบทความนี้เราจะปรับปรุงความผันผวนที่เรียบง่ายและหารือเกี่ยวกับค่าเฉลี่ยเคลื่อนที่แบบถ่วงน้ำหนักแบบทวีคูณ (EWMA) Historical Vs ความผันแปรเบื้องต้นก่อนอื่นให้วางเมตริกนี้ไว้ในมุมมองเล็กน้อย มีสองแนวทางที่กว้าง: ความผันผวนในอดีตและโดยนัย (หรือโดยนัย) วิธีการทางประวัติศาสตร์สมมติว่าอดีตเป็นคำนำที่เราวัดประวัติศาสตร์ด้วยความหวังว่าจะเป็นการคาดการณ์ ในทางตรงกันข้ามความผันผวนโดยนัยจะละเลยประวัติความเป็นมาซึ่งจะช่วยแก้ปัญหาความผันผวนโดยนัยตามราคาตลาด หวังว่าตลาดจะรู้ได้ดีที่สุดและราคาในตลาดมีแม้กระทั่งโดยนัยประมาณการความผันผวน ถ้าเรามุ่งเน้นไปที่สามวิธีทางประวัติศาสตร์ (ด้านซ้ายด้านบน) พวกเขามีสองขั้นตอนที่เหมือนกัน: คำนวณชุดของผลตอบแทนเป็นระยะ ๆ ใช้สูตรการถ่วงน้ำหนักก่อนอื่นเรา คำนวณผลตอบแทนเป็นระยะ ๆ โดยทั่วไปแล้วผลตอบแทนรายวันจะได้รับผลตอบแทนแต่ละรายการในแง่บวก สำหรับแต่ละวันเราจะบันทึกล็อกอัตราส่วนราคาหุ้น (เช่นราคาในปัจจุบันหารด้วยราคาเมื่อวานนี้เป็นต้น) นี่เป็นการสร้างผลตอบแทนรายวันจาก u i to u i-m ขึ้นอยู่กับจำนวนวัน (m วัน) ที่เราวัด ที่ทำให้เราก้าวไปสู่ขั้นตอนที่สอง: นี่คือแนวทางที่แตกต่างกันสามวิธี ในบทความก่อนหน้า (ใช้ความผันผวนเพื่อวัดความเสี่ยงในอนาคต) เราพบว่าภายใต้สอง simplifications ยอมรับความแปรปรวนง่ายคือค่าเฉลี่ยของผลตอบแทนที่เป็นกำลังสอง: ขอให้สังเกตว่าผลรวมนี้แต่ละผลตอบแทนเป็นระยะจากนั้นแบ่งทั้งหมดโดย จำนวนวันหรือสังเกตการณ์ (ม.) ดังนั้นจริงๆมันเป็นเพียงเฉลี่ยของผลตอบแทนเป็นระยะ ๆ squared ใส่อีกวิธีหนึ่งแต่ละยกกำลังสองจะได้รับน้ำหนักเท่ากัน ดังนั้นถ้า alpha (a) เป็นปัจจัยการถ่วงน้ำหนัก (โดยเฉพาะ 1m) ความแปรปรวนแบบง่ายๆมีลักษณะดังนี้: EWMA ช่วยเพิ่มความแปรปรวนอย่างง่ายจุดอ่อนของวิธีนี้คือผลตอบแทนทั้งหมดจะมีน้ำหนักเท่ากัน การกลับมาเมื่อวาน (ล่าสุด) ไม่มีอิทธิพลต่อความแปรปรวนมากกว่าผลตอบแทนของเดือนที่ผ่านมา ปัญหานี้ได้รับการแก้ไขโดยใช้ค่าเฉลี่ยเคลื่อนที่แบบถ่วงน้ำหนักแบบทวีคูณ (EWMA) ซึ่งผลตอบแทนที่ได้รับเมื่อเร็ว ๆ นี้มีน้ำหนักมากขึ้นกับความแปรปรวน ค่าเฉลี่ยเคลื่อนที่แบบถ่วงน้ำหนักแบบเลขยกกำลัง (EWMA) แนะนำ lambda ซึ่งเรียกว่าพารามิเตอร์การให้ราบเรียบ แลมบ์ดาต้องมีค่าน้อยกว่าหนึ่ง ภายใต้เงื่อนไขดังกล่าวแทนที่จะใช้น้ำหนักที่เท่ากันผลตอบแทนที่ได้รับจะเพิ่มขึ้นตามตัวคูณดังนี้ตัวอย่างเช่น RiskMetrics TM ซึ่งเป็น บริษัท บริหารความเสี่ยงทางการเงินมีแนวโน้มที่จะใช้แลมบ์ดาเท่ากับ 0.94 หรือ 94 ในกรณีนี้เป็นครั้งแรก (1-0.94) (. 94) 0 6. ผลตอบแทนที่ได้จะเป็นตัวเลข lambda-multiple ของน้ำหนักก่อนหน้าในกรณีนี้ 6 คูณด้วย 94 5.64 และสามวันก่อนหน้ามีน้ำหนักเท่ากับ (1-0.94) (0.94) 2 5.30 นั่นคือความหมายของเลขยกกำลังใน EWMA: แต่ละน้ำหนักเป็นตัวคูณคงที่ (เช่น lambda ซึ่งต้องน้อยกว่าหนึ่ง) ของน้ำหนักก่อนหน้า เพื่อให้แน่ใจว่ามีความแปรปรวนที่ถ่วงน้ำหนักหรือลำเอียงไปยังข้อมูลล่าสุด (หากต้องการเรียนรู้เพิ่มเติมโปรดดูที่แผ่นงาน Excel สำหรับความผันผวนของ Google) ความแตกต่างระหว่างความผันผวนเพียงอย่างเดียวกับ EWMA สำหรับ Google จะแสดงไว้ด้านล่าง ความผันผวนอย่างง่ายมีผลต่อการกลับคืนเป็นระยะ ๆ ทุกๆ 0.196 ตามที่แสดงไว้ในคอลัมน์ O (เรามีข้อมูลราคาหุ้นย้อนหลังเป็นเวลา 2 ปีนั่นคือผลตอบแทน 509 วันและ 1509 0.196) แต่สังเกตว่าคอลัมน์ P กำหนดน้ำหนัก 6, 5.64 แล้ว 5.3 และอื่น ๆ Thats ความแตกต่างระหว่างความแปรปรวนง่ายและ EWMA โปรดจำไว้ว่า: หลังจากที่เราสรุปชุดข้อมูลทั้งหมด (ในคอลัมน์ Q) เรามีความแปรปรวนซึ่งเป็นค่าสแควร์ของส่วนเบี่ยงเบนมาตรฐาน ถ้าเราต้องการความผันผวนเราต้องจำไว้ว่าให้ใช้รากที่สองของความแปรปรวนนั้น ความแตกต่างของความแปรปรวนรายวันระหว่างค่าความแปรปรวนและ EWMA ในกรณีของ Google มีความหมาย: ความแปรปรวนง่ายทำให้เรามีความผันผวนรายวันอยู่ที่ 2.4 แต่ EWMA มีความผันผวนรายวันเพียง 1.4 (ดูสเปรดชีตเพื่อดูรายละเอียด) เห็นได้ชัดว่าความผันผวนของ Googles ตกลงไปเมื่อไม่นานมานี้ดังนั้นความแปรปรวนที่เรียบง่ายอาจเป็นจำนวนเทียมสูง ความแปรปรวนวันนี้เป็นฟังก์ชันของความแตกต่างของวัน Pior คุณจะสังเกตเห็นว่าเราจำเป็นต้องคำนวณชุดน้ำหนักลดลงอย่างมาก เราจะไม่ใช้คณิตศาสตร์ที่นี่ แต่คุณลักษณะที่ดีที่สุดของ EWMA คือชุดผลิตภัณฑ์ทั้งหมดสามารถลดสูตร recursive ได้อย่างง่ายดาย: Recursive หมายถึงการอ้างอิงความแปรปรวนในปัจจุบัน (คือฟังก์ชันของความแปรปรวนในวันก่อนหน้า) คุณสามารถหาสูตรนี้ในสเปรดชีตได้ด้วยและจะให้ผลเหมือนกันกับการคำนวณแบบ longhand กล่าวว่าค่าความแปรปรวนวันนี้ (ต่ำกว่า EWMA) เท่ากับความแปรปรวนของ yesterdays (weighted by lambda) บวกกับค่า yesterdays squared return (ชั่งน้ำหนักโดยลบหนึ่งแลมบ์ดา) แจ้งให้เราทราบว่าเรากำลังเพิ่มคำสองคำลงท้ายด้วยกันอย่างไร: ความแปรปรวนที่ถ่วงน้ำหนักในวันอังคารและเมื่อวานถ่วงน้ำหนัก แม้กระนั้นแลมบ์ดาก็คือพารามิเตอร์ที่ราบเรียบของเรา แลมบ์ดาที่สูงขึ้น (เช่น RiskMetrics 94) บ่งชี้การสลายตัวช้าลงในซีรีย์ - ในแง่สัมพัทธ์เราจะมีจุดข้อมูลมากขึ้นในซีรีส์และพวกเขาจะลดลงอย่างช้าๆ ในทางกลับกันถ้าเราลดแลมบ์ดาเราจะบ่งชี้ว่าการสลายตัวที่สูงขึ้น: น้ำหนักจะลดลงอย่างรวดเร็วและเป็นผลโดยตรงจากการผุกร่อนที่รวดเร็วใช้จุดข้อมูลน้อยลง (ในสเปรดชีตแลมบ์ดาเป็นอินพุตเพื่อให้คุณสามารถทดลองกับความไว) ความผันผวนโดยสรุปคือส่วนเบี่ยงเบนมาตรฐานของหุ้นและความเสี่ยงที่พบมากที่สุด นอกจากนี้ยังเป็นรากที่สองของความแปรปรวน เราสามารถวัดความแปรปรวนในอดีตหรือโดยนัย (ความผันผวนโดยนัย) เมื่อวัดในอดีตวิธีที่ง่ายที่สุดคือความแปรปรวนที่เรียบง่าย แต่ความอ่อนแอกับความแปรปรวนที่เรียบง่ายคือผลตอบแทนทั้งหมดจะมีน้ำหนักเท่ากัน ดังนั้นเราจึงต้องเผชิญกับข้อเสียแบบคลาสสิก: เราต้องการข้อมูลเพิ่มเติม แต่ข้อมูลที่เรามีมากขึ้นการคำนวณของเราจะถูกเจือจางด้วยข้อมูลที่อยู่ไกล (ไม่เกี่ยวข้อง) ค่าเฉลี่ยเคลื่อนที่แบบถ่วงน้ำหนักที่ถ่วงน้ำหนัก (EWMA) ช่วยเพิ่มความแปรปรวนอย่างง่ายโดยกำหนดน้ำหนักให้กับผลตอบแทนเป็นงวด เมื่อทำเช่นนี้เราสามารถใช้ตัวอย่างขนาดใหญ่ แต่ยังให้น้ำหนักมากขึ้นกับผลตอบแทนล่าสุด (หากต้องการดูบทแนะนำเกี่ยวกับภาพยนตร์เกี่ยวกับหัวข้อนี้โปรดไปที่ Bionic Turtle)