Flytte gjennomsnitt i T-SQL En vanlig beregning i trendanalyse er det bevegelige (eller rullende) gjennomsnittet. Et glidende gjennomsnitt er gjennomsnittet av for eksempel de siste 10 radene. Det bevegelige gjennomsnittet viser en mer jevn kurve enn de faktiske verdiene, mer med en lengre periode for glidende gjennomsnitt, noe som gjør det til et godt verktøy for trendanalyse. Dette blogginnlegget viser hvordan du beregner glidende gjennomsnitt i T-SQL. Ulike metoder vil bli brukt avhengig av versjonen av SQL Server. Diagrammet nedenfor viser utjevningseffekten (rød linje) med et 200 dagers glidende gjennomsnitt. Aksjekursene er den blå linjen. Den langsiktige trenden er tydelig synlig. T-SQL Moving Avergage 200 dager Demonstrasjonen nedenfor krever TAdb-databasen som kan opprettes med skriptet som er plassert her. I det kommende eksemplet beregner vi et glidende gjennomsnitt for de siste 20 dagene. Avhengig av versjonen av SQL Server, vil det være en annen metode for å gjøre beregningen. Og som vi vil se senere, har nyere versjoner av SQL Server funksjoner som muliggjør mye mer effektiv beregning. SQL Server 2012 og senere Flytende gjennomsnitt Denne versjonen benytter en samlet vindusfunksjon. Hva er nytt i SQL 2012, er muligheten til å begrense størrelsen på vinduet ved å spesifisere hvor mange rader som ligger foran vinduet, inneholde: Rader som er tidligere er 19, fordi vi også vil inkludere den nåværende raden i beregningen. Som du kan se, er beregningen av glidende gjennomsnitt i SQL Server 2012 ganske enkelt. Figuren under viser vindusprinsippet. Nåværende rad er merket med gul. Vinduet er merket med en blå bakgrunn. Det bevegelige gjennomsnittet er ganske enkelt gjennomsnittet av QuoteClose i de blå linjene: T-SQL Moving gjennomsnittlig vindu. Resultatene av beregningene i eldre versjoner av SQL Server er de samme, så de vil ikke bli vist igjen. SQL Server 2005 8211 2008R2 Flyttende gjennomsnitt Denne versjonen benytter et vanlig tabelluttrykk. CTE er selv referert til å få de siste 20 radene for hver rad: Flytende gjennomsnitt før SQL Server 2005 Pre 2005-versjonen bruker en venstre ytre sammenføyning til samme tabell for å få de siste 20 radene. Ytre bordet kan sies å inneholde vinduet som vi vil beregne et gjennomsnitt på: Ytelsessammensetning Hvis vi kjører de tre forskjellige metodene samtidig og kontrollerer den resulterende utførelsesplanen, er det en dramatisk forskjell i ytelse mellom metodene: Sammenligning av tre forskjellige metoder for å beregne glidende gjennomsnitt Som du kan se, forbedrer vinduerfunksjonen i SQL 2012 en stor forskjell i ytelse. Som nevnt i begynnelsen av dette innlegget, blir glidende gjennomsnitt brukt som et verktøy for å illustrere trender. En felles tilnærming er å kombinere bevegelige gjennomsnitt av forskjellige lengder, for å kunne oppdage endringer i henholdsvis kort, mellomlang og lang sikt. Av spesiell interesse er krysset av trendlinjer. For eksempel, når den korte trenden beveger seg over den lange eller middels trenden, kan dette tolkes som et kjøpssignal i teknisk analyse. Og når den korte trenden beveger seg under en lengre trendlinje, kan dette tolkes som et salgssignal. Tabellen under viser Quotes, Ma20, Ma50 og Ma200. T-SQL Ma20, Ma50, Ma200 kjøp og salg signaler. Dette blogginnlegget er en del av en serie om teknisk analyse, TA, i SQL Server. Se de andre innleggene her. Skrevet av Tomas Lind Dette er et Evergreen Joe Celko spørsmål. Jeg ignorerer hvilken DBMS-plattform som brukes. Men i hvert fall kunne Joe svare på mer enn 10 år siden med standard SQL. Joe Celko SQL Puslespill og svar citation: Det siste oppdateringsforsøket antyder at vi kunne bruke predikatet til å konstruere en spørring som ville gi oss et bevegelige gjennomsnitt: Er den ekstra kolonnen eller spørringsmetoden bedre Spørringen er teknisk bedre fordi UPDATE-tilnærmingen vil deformalisere databasen. Men hvis de historiske dataene som blir registrert ikke kommer til å endres og beregning er det bevegelige gjennomsnittet dyrt, kan du vurdere å bruke kolonne tilnærming. SQL Puslespørsmål: for all del uniform. Du kaster bare til riktig vektbøtte, avhengig av avstanden fra det nåværende tidspunktet. For eksempel kvittve vekt1 for datapoints innen 24 timer fra nåværende datapoint weight0.5 for datapoints innen 48hrsquot. Den saken er det viktig hvor mye datapoints som følger (som 6:12 og 11:48) er fjernt fra hverandre. En brukstilstand jeg kan tenke på, ville være et forsøk på å jevne histogrammet hvor datapoints ikke er tette nok ndash msciwoj 27. mai kl 15 22:22 Jeg er ikke sikker på at ditt forventede resultat (utgang) viser klassisk enkelt bevegelige (rullende) gjennomsnitt i 3 dager. Fordi, for eksempel, gir den første trippen av tall per definisjon: men du forventer 4,360 og det er forvirrende. Likevel foreslår jeg følgende løsning, som bruker vindufunksjon AVG. Denne tilnærmingen er mye mer effektiv (klar og mindre ressursintensiv) enn SELF-JOIN introdusert i andre svar (og jeg er overrasket over at ingen har gitt en bedre løsning). Du ser at AVG er innpakket med et tilfelle når rownum gt p. days deretter tvinge NULL s i første rader, hvor 3 dagers Moving Average er meningsløs. besvart 23 februar 16 kl 13:12 Vi kan bruke Joe Celkos skitne venstre ytre tilkoblingsmetode (som nevnt ovenfor av Diego Scaravaggi) for å svare på spørsmålet som det ble spurt. Genererer den forespurte utdataen: Besvart jan 9 16 kl 0:33 Svaret 2017 Stack Exchange, IncBruk av et enkelt glidende gjennomsnitt for å utjevne data er en ganske populær teknikk. det er synd det primære eksemplet i SQL Anywhere Help er langt fra enkelt: Hva gjør dette eksemplet så komplisert I tillegg til problemstillingen er det: Beregn det bevegelige gjennomsnittet av alt produktsalg, per måned i år 2000. Heres hva som gjør det komplekse: to referanser til AVG () - funksjonen, en GROUP BY (som i seg selv gjør omtrent en hvilken som helst SELECT en scratcher),. en skjult WINDOW-setning en WINDOW-klausul som ikke engang bruker WINDOW-søkeordet. så til de uinitierte (folkene som trenger eksempler mer enn noen andre) er det ikke åpenbart at et vindu er involvert i det hele tatt. Ikke bare noe WINDOW-klausul, husk deg, men en som inneholder hver enkelt komponent du kan kode i et Windows: en PARTITION BY, en RANGE-klausul. ikke en enkel ROWS-klausul, men fullblåst RANGE-klausul, en som har et intimt forhold til ORDER BY. Jeg vet hva en rad er, men hva redigeres er en rekkevidde, men vent, det er mer: Valget av RANGE over ROWS i dette eksemplet er avgjørende for riktig drift av spørringen. (for en mer fullstendig diskusjon av dette spesielle eksempelet, se eksempel 23 - Beregning av et flytende gjennomsnitt i Glenn Paulleys utmerkede OLAP-hvitt papir.) Nå kan vi komme tilbake på sporet: En virkelig virkelig enkel flytende gjennomsnitt Følgende eksempel viser 10 dagers verdi av data sammen med det bevegelige gjennomsnittet av dagens verdi og gjerdag: WINDOW-klausulen på linjene 21 til 23 definerer et bevegelig vindu som inneholder to rader: rad for dagens rad (CURRENT ROW) og yesterdays row (1 PRECEDING): WINDOW ORDER BY-klausulen bestemmer hva PRECEDING betyr (forrige rad ved t. entrydate) og ROWS-klausulen bestemmer størrelsen på vinduet (alltid to rader). Uttrykket AVG (t. value) OVER twodays på linje 19 refererer til WINDOW-klausulen etter navn, og det forteller SQL Anywhere å beregne gjennomsnittet av de to verdiene av t. value som eksisterer i 2-rads glidende vindu, for hver rad i resultatsettet. Så for 2012-02-02 er gjennomsnittet av 10 og 20 15,000000, for 2012-02-03 gjennomsnittet av 20 og 10 er 15,000000, for 2012-02-04 gjennomsnittet av 10 og 30 er 20,000000, for 2012- 02-10 gjennomsnittet av 10 og 60 er 35,000000. Ups, hva med den første raden Retten 2012-02-01 har ikke en PRØVENDE rad, så hva er gjennomsnittet over det bevegelige vinduet Ifølge Glenn Paulleys hvitt papir i tilfelle et bevegelig vindu antas det at rader som inneholder Null Verdiene eksisterer før første rad, og etter siste rad, i inngangen. Det betyr at når det bevegelige vinduet har 2012-02-01 som CURRENT ROW, inneholder 1 PRECEDING-raden NULL-verdier. og når SQL Anywhere beregner en AVG () som inneholder en NULL-verdi, teller det ikke NULL i det hele tatt. ikke i telleren eller i nevneren når du beregner gjennomsnittet. Heres bevis: Thats hvorfor twodayaverage 10,000000 for første rad 2012-02-01. Skrevet av Breck Carter klokka 15:47
Comments
Post a Comment