Beräkna Exponentiell Glidande Medelvärde In Sql
Jag försöker att implementera ett exponentiellt glidande medelvärde (EMA) på postgres, men när jag kontrollerar dokumentationen och tänker på det ju mer jag försöker bli mer förvirrad är jag. Formeln för EMA (x) är: Det verkar vara perfekt för en aggregator. Att hålla resultatet av det senast beräknade elementet är exakt vad som måste göras här. En aggregator producerar emellertid ett enda resultat (som minskning eller vikning) och här behöver vi en lista (en kolumn) resultat (som karta). Jag har kontrollerat hur förfaranden och funktioner fungerar, men AFAIK de producerar en enda utgång, inte en kolumn. Jag har sett massor av procedurer och funktioner, men jag kan inte riktigt räkna ut hur detta interagerar med relationell algebra, särskilt när man gör något så här, en EMA. Jag hade inte lycka till att söka på Internets hittills. Men definitionen för en EMA är ganska enkel, jag hoppas det är möjligt att översätta denna definition till något som fungerar i postgres och är enkelt och effektivt, eftersom flyttning till NoSQL kommer att vara överdriven i mitt sammanhang. Beräknar detta aggregeringen som ger resultatet i varje rad för varje dellista av ingångsdata Eftersom det verkar som om det använder aggregat upp till rad n, returnerar resultatet och går sedan till rad 0 för att beräkna aggregeringen upp till rad n1 igen. Finns det något sätt att använda ackumuleringen eller någon statisk variabel (som i C) så att det måste beräknas en gång Tack. ndash Trylks Jan 20 12 på 11:59 Nej, det använder det ackumulerade värdet. Om du kör queryen med quotraise infoquot kommandot uncommented, kan du se att funktionen bara kallas en gång för varje radutmatning. Postgresql matar ut det statliga värdet på varje rad (om det definierades en finalfunc, skulle det kallas för att omvandla tillståndet till ett utgångsvärde). ndash araqnid Jan 20 12 på 12:04 ErwinBrandstetter: Jag har återställt de flesta förändringarna - när det gäller formatet för den första (ankare) delen av frågan kan EMA (x1) klart representeras med en enda rad - detta corespnds till en rad som definierar den i frågan. När det gäller den rekursiva delen av frågan har jag använt mn-1 i anslutningsvillkoren för att indikera ekvivalensen av förhållandet med EMA (xn-1) i frågan, även om det blir mindre prestanda om prestanda är en problemet kan OP-saken ändra anslutningsvillkoren för att vara som du föreslog. ndash Mark Bannister Jan 16 12 på 9:27 SQL Server T-SQL-kod för att beräkna ett rörligt medelvärde Av: Dallas Snider Läs kommentarer Relaterade tips: Fler funktioner - Användardefinierad UDF Hur kan jag släta data i en kolumn med ett glidande medelvärde i T - SQL Kan du snälla gå igenom ett exempel i SQL Server med T-SQL-kod Hur kan vi validera resultaten Tidsseriedata kan vara iboende bullriga och ett bra sätt att släpa ut data är att beräkna ett glidande medelvärde. Det finns ett antal sätt att beräkna ett glidande medelvärde i T-SQL, men i denna spets ser vi på ett sätt att beräkna ett glidande medelvärde som sätter medelfönstret x antal rader bakom och x antal rader före strömmen datordraden. Fördelen med detta är att det inte finns någon fördröjning i det genomsnittliga värdet som returneras och det glidande medelvärdet ligger i samma rad med dess nuvärde. Låt oss börja med att skapa ett bord och ladda vissa data med T-SQL nedan. Vi har 361 datapunkter som skapar en högljudd sinusvåg. Efter att ha laddat in data, utför vi följande T-SQL-kod för att välja alla kolumner tillsammans med det glidande medelvärdet. I koden nedan är den glidande medelfönsterstorleken 15 (7 rader före den aktuella raden plus den aktuella raden plus de 7 följande raderna). Det glidande genomsnittet för DataValue-kolumnen returneras som kolumnen MovingAverageWindowSize15. ORDER BY-klausulen är extremt viktigt för att hålla uppgifterna i rätt sorterad ordning. Vi kan kopiera och klistra in resultaten i Excel för att validera beräkningen är korrekt. I bilden nedan börjar fönstret i cell C3 och slutar vid C17. Det rörliga genomsnittet som beräknats av T-SQL i denna tip visas i cell D10. Medelvärdet som beräknat av Excel ligger längst ner och det är lika med värdet i D10. I figuren nedan kan vi se de ursprungliga datavärdena plottade i blått med det glidande medelvärdet ritat i rött. Nästa steg Justera storleken på det glidande medelfönstret för att se hur diagrammet ändras. Också, se till att kolla in de här andra tipsen om T-SQL från mssqltips: Senaste uppdatering: 382016Möter genomsnittet i T-SQL En vanlig beräkning i trendanalys är det rörliga (eller rullande) genomsnittet. Ett glidande medelvärde är medelvärdet av t ex de senaste 10 raderna. Det rörliga genomsnittet visar en mer jämn kurva än de faktiska värdena, mer med en längre period för glidande medelvärde, vilket gör det till ett bra verktyg för trendanalys. Detta blogginlägg visar hur man beräknar glidande medelvärde i T-SQL. Olika metoder kommer att användas beroende på vilken version av SQL Server. Diagrammet nedan visar utjämningseffekten (röd linje) med ett 200 dagars glidande medelvärde. Aktiekurserna är den blå linjen. Den långsiktiga trenden är tydligt synlig. T-SQL Moving Avergage 200 dagar Demonstrationen nedan kräver TAdb-databasen som kan skapas med manuset som finns här. I det kommande exemplet kommer vi att beräkna ett glidande medelvärde under de senaste 20 dagarna. Beroende på versionen av SQL Server kommer det att finnas en annan metod för att göra beräkningen. Och som vi ser senare har de nyare versionerna av SQL Server funktioner som möjliggör en mycket effektivare beräkning. SQL Server 2012 och senare Flyttande medelvärde Den här versionen använder sig av en samlad fönsterfunktion. Vad som är nytt i SQL 2012 är möjligheten att begränsa storleken på fönstret genom att ange hur många rader som föregår fönstret ska innehålla: Rader som är föregående är 19, eftersom vi också kommer att inkludera den aktuella raden i beräkningen. Som du kan se är beräkningen av glidande medelvärde i SQL Server 2012 ganska enkelt. Figuren nedan visar fönsterprincipen. Nuvarande rad är markerad med gul. Fönstret är markerat med en blå bakgrund. Det rörliga genomsnittet är helt enkelt genomsnittet av QuoteClose i de blå linjerna: T-SQL Flyttande medelfönster. Resultaten av beräkningarna i äldre versioner av SQL Server är desamma, så de kommer inte att visas igen. SQL Server 2005 8211 2008R2 Flyttande medelvärde Denna version använder sig av ett gemensamt tabelluttryck. CTE är självhänvisad för att få de senaste 20 raderna för varje rad: Flyttande medelvärde före SQL Server 2005 Pre 2005-versionen använder en vänster yttre anslutning till samma tabell för att få de senaste 20 raderna. Det yttre bordet kan sägas innehålla det fönster som vi vill beräkna ett genomsnitt på: Prestationsjämförelse Om vi kör de tre olika metoderna samtidigt och kontrollera den resulterande exekveringsplanen, är det en dramatisk skillnad i prestanda mellan metoderna: Jämförelse av tre olika metoder för att beräkna glidande medelvärde Som du kan se, förbättrar fönsterfunktionsförbättringarna i SQL 2012 en stor skillnad i prestanda. Som nämnts i början av detta inlägg används glidande medelvärden som ett verktyg för att illustrera trender. Ett gemensamt tillvägagångssätt är att kombinera rörliga medelvärden av olika längder, för att kunna upptäcka förändringar på respektive kort, medellång och lång sikt. Av särskilt intresse är korsningen av trendlinjer. Till exempel när den korta trenden rör sig över den långa eller medellånga trenden kan detta tolkas som en köpsignal i teknisk analys. Och när den korta trenden rör sig under en längre trendlinje kan detta tolkas som en försäljningssignal. Diagrammet nedan visar Citat, Ma20, Ma50 och Ma200. T-SQL Ma20, Ma50, Ma200 köp och sälj signaler. Detta blogginlägg är en del av en serie om teknisk analys, TA, i SQL Server. Se andra inlägg här. Upplagt av Tomas LindExponentiellt glidande medelvärde i T-SQL Exponentiella glidande medelvärden liknar vägda glidmedelvärden genom att de tilldelar mindre vikt till förändringar för länge sedan och större vikt vid senaste förändringar. Viktiga glidmedel är linjära, men exponentiella glidmedel är exponentiella. Det vill säga vikten kan uttryckas som en kurva: Det finns ett bra sätt att beräkna exponentiella glidande medelvärden i T-SQL med en obodifierad funktion om variabler och körtotaler i SQL Server. I det här blogginlägget ska jag visa hur man använder den metoden för att beräkna exponentiell glidande medelvärde i T-SQL, men jag kommer också presentera en metod som använder standardfunktioner i SQL Server. Tyvärr betyder det att du använder en slinga. I exemplen kommer jag att beräkna ett 9 dagars exponentiellt rörligt medelvärde. Exemplen använder databasen TAdb. Ett skript för att skapa TAdb finns här. Exponential Moving Average (EMA): Running Totals Method Teorin bakom de totala funktionerna i uppdateringar beskrivs i detalj av Jeff Moden i sin artikel Lösning av Running Total och Ordinal Rank Problem. Andra resurser som beskriver att använda denna metod för att beräkna EMA är bloggposten Beräkning av rörliga medelvärden med T-SQL av Gabriel Priester och forumet efter Exponentiell Moving Average Challenge. både på SQL Server Central. I grund och botten kan du i T-SQL uppdatera variabler såväl som kolumner i en uppdatering. Uppdateringarna görs rad för rad internt av SQL Server. Denna rad i rad beteende är det som gör att man kan beräkna en löpande total. Detta exempel visar hur det fungerar: Observera att 8220ColumnRunningTotal8221 är en löpande total av 8220ColumnToSum8221. Med hjälp av denna metod kan vi beräkna EMA9 med denna T-SQL: Beräkningen av EMA är ganska enkel. Vi använder nuvarande rad och föregående men med större vikt till nuvarande rad. Vikten beräknas med formeln 2 (19), där 822098221 är parametern för längden på EMA. För att beräkna EMA9 för rad 10 ovan är beräkningen: I det här fallet får den nuvarande raden 20 av vikten (2 (19) 0.2) och föregående rad får 80 av vikten (1-2 (19) 0,8). Du hittar denna beräkning i ovanstående uttalande i CASE-satsen: Exponentiell rörlig genomsnitts (EMA): Looping Method Såvitt jag vet, med undantag för den löpande totalsmetoden som skisseras ovan, finns det ingen möjlighet att beräkna EMA med hjälp av en uppsättning baserad SQL-stämning . Därför använder T-SQL nedan en stundslinga för att beräkna EMA9: Resultaten är desamma som i det totala löpande summan ovan. Prestanda Som förväntat är den uppsättning baserade löpande totalversionen mycket snabbare än loopversionen. På min maskin var den uppsatta baserade lösningen ungefär 300 ms, jämfört med omkring 1200 med slingversionen. Slinga-versionen överensstämmer dock med SQL-standarder. Valet mellan metoderna beror därför på what8217s viktigaste för dig, prestanda eller standarder. Det exponentiella glidande medlet kan användas i trendanalys, som med de andra typerna av glidande medelvärden, Simple Moving Average (SMA) och Weighted moving average (WMA). Det finns också andra beräkningar i teknisk analys som exempelvis använder EMA, MACD. Detta blogginlägg är en del av en serie om teknisk analys, TA, i SQL Server. Se andra inlägg här. Upplagt av Tomas Lind Tomas Lind - Konsulttjänster som SQL Server DBA och Database Developer på High Coast Database Solutions AB.
Comments
Post a Comment