Beräkna glidande medelvärde sql server

Jag jobbar med SQL Server 2008 R2, försöker beräkna ett glidande medelvärde. För varje post enligt min uppfattning vill jag samla värdena för de 250 tidigare posterna och beräkna sedan genomsnittet för det här urvalet. Mina synkolumner är följande: TransaktionsID är unik. För varje TransaktionsID. Jag skulle vilja beräkna medelvärdet för kolumnvärde, över tidigare 250 poster. Så för TransactionID 300, samla alla värden från tidigare 250 rader (vy sorteras nedåt med TransactionID) och sedan i kolumnen MovAvg skriv resultatet av genomsnittet av dessa värden. Jag letar efter att samla in data inom en rad poster. frågade 28 okt 14 kl 20:58. Tidigare diskuterade vi hur man skriver rullande medelvärden i Postgres. Med populära efterfrågan visade du hur du gör detsamma i MySQL och SQL Server. Väl täcka hur du kommenterar bullriga kartor så här: Med en 7-dagars föregående medellinje så här: The Big Idea Vår första graf ovan är ganska bullriga och svår att få användbar information från. Vi kan släta ut det genom att planera ett 7-dagars genomsnitt ovanpå de underliggande data. Detta kan göras med fönsterfunktioner, självförbindelser eller korrelerade undersökningar - väl täcka de första två. Bra start med föregående medelvärde, vilket innebär att medelvärdet på den 7: e månaden är genomsnittet för de första sju dagarna. Visuellt växlar detta spikarna i diagrammet till höger, eftersom en stor spik är medeltal under de följande sju dagarna. Först Skapa ett mellantalstabell Vi vill beräkna ett genomsnitt över de totala registreringarna för varje dag. Om vi ​​antar att vi har ett typiskt användartabell med en rad per ny användare och en tidsstämpel skapad, kan vi skapa vårt aggregat vårt anmälningsbord enligt följande: I Postgres och SQL Server kan du använda detta som en CTE. I MySQL kan du spara det som en tillfällig tabell. Postgres Rolling Average Fortunately Postgres har fönsterfunktioner som är det enklaste sättet att beräkna ett löpande medelvärde. Denna fråga förutsätter att datumen inte har några luckor. Frågan är genomsnittlig under de senaste sju raderna, inte de senaste sju datumen. Om dina data har luckor, fyll i dem med generateseries eller gå med i ett bord med täta datumrader. MySQL Rolling Average MySQL saknar fönsterfunktioner, men vi kan göra en liknande beräkning med hjälp av självförbindelser. För varje rad i vår räknatabell deltar vi i varje rad som var de senaste sju dagarna och tar medeltalet. Denna fråga hanterar automatiskt datumluckor, eftersom vi tittar på rader inom ett datumintervall snarare än föregående N-rader. SQL Server Rolling Average SQL Server har fönsterfunktioner, så beräkning av rullande medel kan göras i antingen Postgres-stilen eller MySQL-stilen. För enkelheten användes MySQL-versionen med en självförening. Detta är konceptuellt detsamma som i MySQL. De enda översättningarna är datumaddfunktionen och uttryckligen namngiven grupp efter kolumner. Andra medelvärden Vi fokuserade på 7-dagars efterföljande genomsnitt i detta inlägg. Om vi ​​ville titta på det 7-dagars ledande genomsnittet är det så enkelt att sortera datumen i andra riktningen. Om vi ​​ville titta på ett centrerat medelvärde, bruka vi: Postgres: rader mellan 3 föregående och 3 efter MySql: mellan signups. date - 3 och signups. date 3 i MySQL SQL Server: mellan dateadd (dag, -3, registreringar. datum) och dateadd (dag, 3, signups. date) Flyttande medelvärde 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å versionen 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 Denna version 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änvisat 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 rörliga 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 se 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 Lind