Flytte Gjennomsnittet Query Sql

Jeg jobber med SQL Server 2008 R2, og prøver å beregne et glidende gjennomsnitt. For hver post som jeg ser, vil jeg gjerne samle verdiene til de 250 tidligere postene, og deretter beregne gjennomsnittet for dette valget. Visningskolonnene mine er som følger: TransactionID er unikt. For hvert TransaksjonsID. Jeg vil gjerne beregne gjennomsnittet for kolonneverdi, over tidligere 250 poster. Så for TransactionID 300, samle alle verdier fra tidligere 250 rader (visningen sorteres synkende av TransactionID) og deretter i kolonne MovAvg skrive resultatet av gjennomsnittet av disse verdiene. Jeg ønsker å samle inn data innenfor en rekke poster. spurte 28. oktober klokken 20:58 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. Det saken betyr det hvor mye datapoints som er på hverandre (som 6:12 og 11:48) er fjernt fra hverandre. En brukstilstand jeg kan tenke på, ville være et forsøk på å glatte histogrammet hvor datapunkter 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 på 0:33 Ditt svar 2017 Stack Exchange, IncSQL Server T-SQL-kode for å beregne et flytende gjennomsnitt av: Dallas Snider Les kommentarer Relaterte tips: Flere funksjoner - Brukerdefinert UDF Hvordan kan jeg glatte data i en kolonne med et bevegelige gjennomsnitt i T-SQL Kan du gå gjennom et eksempel i SQL Server med T-SQL-kode Hvordan kan vi validere resultatene Tidsseriedata kan iboende være støyende og en god måte å jevne ut dataene er å beregne et glidende gjennomsnitt. Det finnes en rekke måter å beregne et bevegelig gjennomsnitt på i T-SQL, men i dette tipset vil vi se på en måte å beregne et glidende gjennomsnitt som setter gjennomsnittsvinduet x antall rader bak og x antall rader foran gjeldende datarute. Fordelen med dette er at det ikke er noe lag i gjennomsnittsverdien returnert og den bevegelige gjennomsnittsverdien er i samme rad med gjeldende verdi. La oss starte med å lage et bord og laste inn noen data ved hjelp av T-SQL nedenfor. Vi har 361 datapunkter som lager en støyende sinusbølge. Etter at du har lastet inn dataene, utfører vi følgende T-SQL-kode for å velge alle kolonnene sammen med den bevegelige gjennomsnittsverdien. I koden nedenfor er glidende gjennomsnittsvinduestørrelse 15 (7 rader som går foran den nåværende raden, pluss den nåværende raden, pluss de 7 følgende radene). Det bevegelige gjennomsnittet for DataValue-kolonnen returneres som MovingAverageWindowSize15-kolonnen. ORDER BY-klausulen er ekstremt viktig for å holde dataene i riktig sortert rekkefølge. Vi kan kopiere og lime inn resultatene i Excel for å bekrefte at beregningen er riktig. I bildet under starter vinduet i celle C3 og slutter ved C17. Det bevegelige gjennomsnittet som beregnet av T-SQL i dette tipset, vises i celle D10. Gjennomsnittet som beregnet av Excel er nederst og det er lik verdien i D10. I figuren nedenfor kan vi se de opprinnelige dataverdiene plottet i blått med det bevegelige gjennomsnittet plottet i rødt. Neste trinn Juster størrelsen på det bevegelige gjennomsnittsvinduet for å se hvordan plottet endres. Vær også sikker på å sjekke ut disse andre tipsene på T-SQL fra mssqltips: Siste oppdatering: 382016 Beregning av verdier i et rullende vindu i Transact SQL Dwain Camps Beregning av verdier i et rullende vindu i SQL Hver gang du må kombinere verdier på flere rader i SQL kan problemet være utfordrende, spesielt når det gjelder ytelse. Vi vil fokusere på det rullende tolvmåneders totalsproblemet, men våre metoder kan brukes til et hvilket som helst tidsvindu (f. eks. 3 måneder) eller til gjennomsnitt og andre aggregeringer over disse tidsvinduene også. En rullende sum i en måned er summen for den måneden pluss de forrige månedene i tidsvinduet, eller NULL hvis du ikke har verdiene for alle de foregående månedene i tidsvinduet. I tidligere versjoner av SQL Server måtte du hoppe gjennom et par hoops for å komme frem til en metode som fungerer bra, men SQL 2012 tilbyr noen nye funksjoner som gjør det enklere. I begge tilfeller er det flere gyldige løsninger. Hvilken er raskeste og mest effektive We8217ll prøver å svare på dette spørsmålet i denne artikkelen. Vi vil jobbe i SQL 2012. Hvis du vil følge med, kan du bruke Sample Queries. sql ressursen you8217ll finner vedlagt. Dataoppsett og erklæring om virksomhetsproblem Ofte vil du finne deg selv med mange transaksjoner innen en måned, men i vårt tilfelle antar vi at du allerede har gruppert transaksjonene dine for hver måned. We8217ll tilordne vår primære nøkkel til en datatype for datatype, og ta med noen verdier over hvilke vi ønsker å samle rullende tolv måneders totals. Dette gir også en liten forskjellig spørringsplan, slik at vi er interessert i å se hvordan resultatene sammenligner med andre foreslåtte løsninger hittil. Så mye for tradisjonelle løsninger, og jeg beklager hvis jeg skjedde med å overse en av favorittene dine, men vær så snill å kode den opp og legg den til ytelsestestselen we8217ll presenterer senere for å se hvordan det går. Løsning 5: Bruke en quirky oppdatering Hvis you8217ve aldri har hørt om Quirky Update (QU) og hvordan den kan brukes på problemer som kjører totals, anbefaler jeg sterkt at du har en lesning av denne fremragende artikkelen av SQL MVP Jeff Moden. berettiget Løse løpende og ordinære rangproblemer. Før vi fortsetter, bør vi være oppmerksom på at det er de som insisterer på QU-metoden, representerer en ulovlig oppførsel av SQL Server, og det er derfor ikke å stole på. Vi kan si at syntaksen er tydelig beskrevet av MS Books On Line-oppføringen for UPDATE-setningen for SQL-versjoner 2005, 2008 og 2012. Faktisk går det videre enn det. Jeg har brukt det i SQL Server 2000, men det ble arvet fra Sybase og ble i den første SQL Server-versjonen som ble utgitt. Til naysayers I8217ll sier at 8220undocumented8221 oppførselen er minst konsekvent i alle versjoner, og det er sannsynligvis liten grunn til å mistenke at den vil bli avskrevet eller endret i fremtidige versjoner av MS SQL. Overvei deg selv advart Hvis du noen gang vurderer å bruke en QU for å løse et problem, må du ta nøye merke til de mange reglene som gjelder (også inkludert i den refererte artikkelen av Jeff). De viktigste, som I8217ve håndterte i dette spørsmålet, kan oppsummeres som: Tabellen må ha en klynget indeks som angir bestilling av kildene i perioden ettersom du ønsker at den skal krysse. Tabellen må ha en kolonne som du kan plassere den samlede summen av. Når du utfører oppdateringen, må du låse bordet ved hjelp av TABLOCKX-spørringshenvisningen for å forsikre deg om at ingen andre kommer i noen INSERT s, DELETE eller UPDATE s før du går gjennom. Du må hindre SQL i å forsøke å parallellere spørringen ved hjelp av OPTION (MAXDOP 1) hint. Siden et rullende tolvmåneders gjennomsnitt bare er en løpende sum i forkledning, kan vi legge til en kolonne i vårt bord og bruke en QU-spørring for å gjøre beregningen vår. Jeg må bekjenne at dette ser litt rotete ut, med alle variablene du trenger for å avklare. I utgangspunktet hva vi gjør er å holde oversikt over de siste tolv (lagging) verdiene, for å fjerne den 12. (hvor Rolling12Months-kolonnen er tildelt) fra hva som ellers er en QU-løpende sum som beskrevet i Jeff8217s artikkel. Vi har høye forhåpninger for sin fart gitt at det er kjent å være den raskeste metoden for å løse løpende totalsproblemet. Igjen bør du overbevise deg selv om at resultatene stemmer overens med tidligere løsninger, og ja, denne løsningen oppfører seg fortsatt i SQL 2012. Hvis du er med meg så langt, kan du også spørre deg selv 8220 hva skjer hvis jeg må beregne flere løp tolv måneders totals over ulike partisjoner8221 Dette er relativt enkelt for alle de andre løsningene som presenteres, men foreslår litt utfordring ved hjelp av QU. Svaret på dette finner du i vedlagte ressursfil: Quirky Update Partitioned. sql. SQL 2012-løsninger Hittil har alt vi har gjort, fungert i SQL 2008. Det eneste vi har gjort som ikke støttes i SQL 2005, er initialiseringene av variablene vi DECLARE d i QU-tilnærmingen. Nå let8217s se hvilke nye funksjoner SQL 2012 har som kan brukes på dette problemet. Løsning 6: Bruke en vindusramme Vår første SQL 2012-løsning (6) viser hvordan du bruker en vindusramme som starter 11 rader før den nåværende raden, opp gjennom den nåværende raden til SUM våre ønskede resultater. Nok en gang er de returnerte resultatene det samme, men spørringsplanen er ganske annerledes enn for den tidligere SQL 2012-løsningen, men vi er ikke særlig optimistiske at denne tilnærmingen vil gi et rimelig alternativ på grunn av antall 8220look-backs8221 som trengs for å få det til å fungere . Prestasjonsjämförelse av metodene Den virkelige testen for å se hvordan flere løsninger utfører, er å kontrollere faktiske utførelsestider i en hvilende server ved hjelp av en testsele med mange rader. Vår test sele er vist, sammen med hvordan løsning 1 og 2 er blitt modifisert (se kommentarer i koden) til: Sett inn resultatene i et tempeltabell for å unngå den forløpte tidsvirkningen av å returnere radene til SQL Server Management Studio8217s resultater Nett. Fjern DATE-aritmetikken, fordi det er vanskelig å generere flere unike måneder, når du genererer flere millioner testrør, slik at kolonnen Dato-tabell har blitt revidert for å være en BIGINT-datatype. For de resterende løsningene (2 8211 6), har vi gradert CPU og Forløpt tidsresultat fra 1M om 4M rader. Tolking av resultatene Forløpt og CPU-tider synes å være konsistente over de forskjellige metodene med hensyn til deres bestilling. Alle ser ut til å skalere på en lineær måte. Den quirky oppdateringen, forutsatt at du kan forstå det og alle tilhørende regler, synes å være den raskeste tilgjengelige løsningen for å løse dette problemet, selv om du vurderer de nye funksjonene som er tilgjengelige i SQL 2012. I SQL 2012 er vindurammens tilnærming helt sikkert ryddig, kompakt og elegant, men løyper lett Quirky Update-løsningen på tvers av rader vi testet. Disse testresultatene ser ut til å være i overensstemmelse med en tidligere test på løpende tall i SQL 8220Denali8221 CTP3 av Microsoft Certified Master Wayne Sheffield i sin blogg. Hvis du setter deg fast i en tidligere versjon av SQL (2005 eller 2008), og av en eller annen grunn kan du ikke fortsette å bruke en quirky oppdatering (f. eks. Hvis du ikke stoler på denne uokumenterte oppførselen), er de raskeste løsningene som er tilgjengelige for deg enten CROSS APPLY TOP eller ved hjelp av en korrelert underforespørsel, da begge syntes å være i nært hold over hele linja. Det ser ut til at 8220traditional8221 INNER JOIN er noe som må unngås. Det vil trolig bare bli verre hvis du trenger å gjøre dataritikk innenfor JOIN8217s ON-klausulen. På samme måte var det ikke sikkert å bruke en Tally-tabell eller flere LAG-s (SQL 2012). Vi undersøkte ikke CURSOR-baserte løsninger, men du kan tilbake følge artikkelen referert til løpende totaler for å få en ide om hvordan de kan utføre i dette tilfellet. I8217ve har også sett noen løsninger som benytter en rekursiv Common Table Expression (rCTE), men jeg vil absolutt ikke satse på deres ytelse sammenlignet med QU - eller vindusrammene. Det er mange måter å beregne verdier i et rullende vindu i SQL, og det er noen klare ytelsesvinnere blant dem. Vi håper du fant denne veiledningen til tilgjengelige metoder interessant og informativ. Abonner på flere artikler Fortjennende nyhetsbrev bidrar til å skjerpe dine ferdigheter og holde deg foran, med artikler, bøker og meninger for å holde deg informert. Ønsker mer Abonner på vårt nyhetsbrev på to uker Totalt: 33 Gjennomsnitt: 4.65 Dwain Camps har vært prosjektleder i mange år. Fordi ytelse av applikasjoner kan være en kritisk suksessfaktor for prosjekter, har han vært evangeliserende på behovet for å utvikle høyt utført SQL. Ved å veilede og forfattere artikler om SQL, håper han å trene en fremtidig generasjon av programvareingeniører på riktig og feil måte å levere SQL-kode. Han har også en spesiell interesse i å utvikle løsninger for komplekse, datakrevende problemer ved å bruke høy ytelse SQL fordi SQL-deklarative naturen tillater utvikling av algoritmisk unike løsninger som prosessoriske språk ikke kan klare. Følg Dwain på Twitter Veldig bra Flott artikkel Jeg ble overrasket over at LAG () gjorde det så dårlig. Jeg antar at hvert påkalling er gjort separat heller enn utregnet og optimalisert som et vindu. Stor forklaring Jeg er enig, dette er en god forklaring på ulike måter å beregne verdier i et rullende vindu. Hvis du tester disse eksemplene på SQL 2012, må du endre MyTable med RollingTotalsExample. Tusen takk, Mr. Camps Tally-metoden Hi Dwain, la jeg merke til at Tally-tabellforespørselen forårsaket en tabellspooloperatør, og tenkte at du kanskje vurderer å gjøre Tally-delen av et datatabell slik: SELECT GroupingDate, ValueMAX (CASE GroupingDate WHEN Date DER A. Value END), Rolling12MonthsCASE NÅR ROWNUMBER () OVER (ORDER AV GroupingDate) lt 12 NULL ELSE SUM (Verdi) END IN ResultsSoln2 Fra RollingTotalsExempel en CROSS APPLY (mdash Fjern DATE aritmetiske verdier (Dato), (Dato1) (Date2), (Date3), (Date4), (Date5), (Date6), (Date7), (Date8), (Date9), (Date10), (Date11)) c (GroupingDate) GROUP BY GroupingDate HAVING GroupingDate lt MAX (Dato) BESTILL BY GroupingDate (Unnskyld hvis formatering er dårlig, ikke forhåndsvisning) Denne endringen vil fortsatt gjøre det til en konkurrent, men gjør en enorm forbedring til den spørringen. Takk for kommentarene Takk Joe og Nic. Irsquom glad du fant artikkelen interessant. Joe: Jeg ble også litt overrasket over LAG-resultatene, og det får meg til å lure på hva break-even punktet ville være. Kanskje 3 måneder kanskje ikke er så ille, men det er fortsatt vanskelig å tro at det kan være raskere enn QU. Tally Tables MM: Av en eller annen grunn har jeg en personlig preferanse for in-line Tally-tabeller, men resultatene dine er interessante hvis bare å vurdere for andre tilfeller. Hjelp med å flytte årlig totalt mitt første innlegg. Jeg må beregne den flyttende årlige summen for verdien ovenfor for de foregående 12 månedene, med denne måneden som måned 12. Jeg må da få det flyttende årlige totale i de tolv månedene før dette. Med ideen er å sammenligne MAT for denne måneden med tilsvarende måned i fjor, og for hver foregående måned. Mitt forsøk ga meg dette: Med cte as (SELECT rNum ROWNUMBER () Over (rekkefølge etter dato). Dato. Verdi Rolling12MonthsCASE NÅR ROWNUMBER () OVER (BESTILL BY DATO) gt 11 SOM (Verdi) OVER (BESTILL BYRET Dato RAKER MELLOM 11 PRØVENDE OG HØYRE RÅD) Slutt fra RollingTotalsExample) Velg Fra cte, (Velg mRNum max (rNum) fra cte) deMax Hvor rNum mellom mRNum ndash 23 og mRNum Med evnen til å endre Were-setningen for å reflektere om jeg vil ha dette året eller forrige år. Mine virkelige data har datoen som i Integer 201409 som jeg tror vil gjøre livet enklere for meg, da jeg kan trekke 100 for å komme i fjor. Utmerket artikkel og hvilken som helst hjelp ville bli verdsatt. Dette er min arbeidsløsning (med litt støy) mdash Rolling 12 måneders totals ved hjelp av SQL 2012 og et vinduesramme IF OBJECTID (lsquotempdb..PreviousYearrsquo) ER IKKE NULL DROP TABLE Forrige år med cte as (velg rNum ROWNUMBER () Over (ordre etter dato ). Dato. Verdien Rolling12MonthsCASE NÅR ROWNUMBER () OVER (BESTILL BY DATO) gt 11 SEN (Verdi) OVER (BESTILL BY DATO RADER MELLOM 11 FORHOLD OG HØYRE RETT) SLUT FRA RollingTotalsExample) Velg pyRowNum ROWNUMBER () Over (rekkefølge ved mRNum ). . sStart mRNum ndash 24. eEnd mRNum ndash 12 i PreviousYear Fra cte, (Velg mRNum max (rNum) fra cte) deMax Hvor rNum mellom mRNum ndash 23 og mRNum ndash 12 mdash Rullende 12 måneders totals ved hjelp av SQL 2012 og en vindu ramme IF OBJECTID (lsquotempdb..ThisYearrsquo) ER IKKE NULL DROP TABLE ThisYear Med cte as (SELECT rNum ROWNUMBER () Over (rekkefølge etter dato). Dato. Verdi. Rolling12MonthsCASE Når ROWNUMBER () OVER (BESTILL BY DATO) gt 11 SUM (Verdi) OVER (BESTILL BY DATUM ROWS MELLOM 11 PRØVENDE OG HØYRE RÅD) Slutt fra RollingTotalsExample) Velg tyRowNum ROWNUMBER () Over (rekkefølge av mRNum). . sStart mRNum ndash 11. eEnd mRNum i ThisYear Fra cte, (Velg mRNum max (rNum) fra cte) deMax Hvor rNum mellom mRNum ndash 11 og mRNum Velg fra ThisYear ty Venstre Bli med TidligereYear py på ty. tyRowNum py. pyRowNum Disse kan fungere Irsquom ikke i nærheten av en komp sql tilgang akkurat nå, så jeg kan sjekke det (det kan være noen typossyntax feil). VELG T. DateKey, AVG (T. ValueField) OVER (ODER BY T. DateKey ASC MELLOM 365 PRECEDING OG OG HØYRE RÅD) Som YMAValueField FRA DataTable AS T ORDER AV T. DateKey ASC Hvis AVG er en av de samlede funksjonene, støttes ikke med MELLOM rekkevidde (jeg vet at SUM støttes). VELG T. DateKey, SUM (T. ValueField) OVER (ODER BY T. DateKey ASC MELLOM 365 PRECEDING OG OG HØYRE RÅD) SOM DATERIFF (DAG, StartDate, T. DateKey) lt 365 THEN DATEDIFF (DAY, StartDate, T. DateKey) ELSE 365 END AS YMAValueField FRA DataTable AS T ORDER AV T. DateKey ASC Abonner på flere artikler Fortynnende nyhetsbrev bidrar til å skarpere dine ferdigheter og holde deg foran med artikler, bøker og meninger for å holde deg informert. Ønsker mer Abonner på vårt nyhetsbrev for to uker Besøk vårt artiklerbibliotek for å finne ut hvilke mønstre og praksiser du trenger for å bevege deg mot mer fleksible metoder for databaselevering. Finn ut hvordan du automatiserer prosessen med å bygge, teste og distribuere databaseendringer for å redusere risikoen og øke hastigheten på leveringssyklusen. Høyest vurderte artikler i T-SQL Programmering Relaterte artikler Også i SQL Med oppstart av NoSQL-databaser som utnytter aspekter av SQL for å spørre, og omfavner full transactionality, er det fare for at datadokumentmodellene hierarkisk natur forårsaker en fundamental konflikt med relasjonsteori Vi spurte vår relasjonsekspert, Hugh Bin-Haad, for å forklare et vanskelig område for database theorists. hellip Les mer Også i SQL Server Hver SQL Server Database programmer må være kjent med systemfunksjonene. Disse spenner fra det sublime (som radnummer eller identitet) til det latterlige (IsNumeric ()) Robert Sheldon gir en oversikt over de mest brukte av dem. Hellip Les mer Også i T-SQL Programmering For å kunne få full nytte av systemkatalogen for å finne ut mer om en database, må du være kjent med metadatafunksjonene. De sparer mye tid og skriver når de spør om metadataene. Når du får tak i disse funksjonene, virker systemkatalogen plutselig enkel å bruke, som Robert Sheldon demonstrerer i denne artikkelen. Hellip Les mer Også i T-SQL Programmering En rekke argumenter med utviklere som insisterer på at fuzzy søk eller stavekontroll gjøres innen søknaden, snarere da en relasjonsdatabase inspirerte Phil Factor for å vise hvordan det er gjort. Når databasen må finne relevant materiale fra søkeord angitt av brukere, må databasen lære seg å forvente og håndtere både forventet og uventet hellip Les mer kopi 2005 - 2017 Red Gate Software Ltd Hva synes du om den nye Simple Talk Give oss din tilbakemelding