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 Detta är en Evergreen Joe Celko fråga. Jag ignorerar vilken DBMS-plattform som används. Men i alla fall kunde Joe svara mer än 10 år sedan med standard SQL. Joe Celko SQL Pussel och svar citation: Det senaste uppdateringsförsöket antyder att vi skulle kunna använda predikatet för att konstruera en fråga som skulle ge oss ett glidande medelvärde: Är den extra kolumnen eller frågeställningen bättre Frågan är tekniskt bättre eftersom UPDATE-metoden kommer att denormalisera databasen. Om de historiska data som registreras inte kommer att förändras och beräkningen är det rörliga genomsnittet är dyrt, kan du överväga att använda kolumninriktningen. SQL Pusselfråga: för all del enhetlig. Du kastar bara till lämplig vikthink beroende på avståndet från aktuell tidpunkt. Till exempel kvittot vikt1 för datapoäng inom 24 timmar från nuvarande datapoint weight0.5 för datapoäng inom 48hrsquot. I det fallet spelar det roll hur mycket efterföljande datapoäng (som 6:12 och 11:48) är avlägsen från varandra. Ett användningsfall som jag kan tänka på skulle vara ett försök att släta histogramet där datapunkter inte är täta nog. Ndash msciwoj 27 maj 15 på 22:22 Jag är inte säker på att ditt förväntade resultat (output) visar klassiskt enkelt rörligt (rullande) medelvärde i 3 dagar. Eftersom exempelvis den första trippeln av siffror per definition ger: men du förväntar dig 4,360 och det är förvirrande. Ändå föreslår jag följande lösning, som använder fönsterfunktion AVG. Detta tillvägagångssätt är mycket effektivare (tydligare och mindre resursintensivt) än SELF-JOIN infört i andra svar (och jag är förvånad över att ingen har givit en bättre lösning). Du ser att AVG är förpackad med fall då rownum gt pays sedan för att tvinga NULL s i första raden, där 3 dagars rörande medelvärde är meningslöst. svarade 23 februari 16 kl 13:12 Vi kan använda Joe Celkos smutsiga vänster yttre anslutningsmetod (som citerad ovan av Diego Scaravaggi) för att svara på frågan som den ställdes. Genererar den begärda utgåvan: svarade jan 9 16 på 0:33 Ditt svar 2017 Stack Exchange, IncSQL 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 detta tips kommer vi att se på ett sätt att beräkna ett glidande medelvärde som anger medelvärdet fö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 nuvarande raden plus de 7 följande raderna). Det glidande medelvärdet 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 du kolla in de här andra tipsen om T-SQL från mssqltips: Senaste uppdatering: 382016Räkning av värden i ett rullande fönster i Transact SQL Dwain Camps Beräkning av värden i ett rullande fönster i SQL Varje gång du behöver kombinera värden i flera rader I SQL kan problemet vara utmanande, särskilt när det gäller prestanda. Vi kommer att fokusera på det rullande tolvmånaders totalproblemet, men våra metoder kan tillämpas på vilket som helst tidsfönster (t ex 3 månader) eller till medelvärden och andra aggregeringar över dessa tidsfönster. En rullande summa för en månad är summan för den månaden plus de föregående månaderna inom tidsfönstret eller NULL om du inte har värdena för alla föregående månader i tidsfönstret. I tidigare versioner av SQL Server måste du hoppa genom några hoops för att komma fram till en metod som fungerar bra, men SQL 2012 erbjuder några nya funktioner som gör det enklare. I båda fallen finns det flera giltiga lösningar. Vilket är snabbast och mest effektivt We8217ll försöker svara på denna fråga i den här artikeln. Vi kommer att arbeta i SQL 2012. Om du vill följa med kan du använda Sample Queries. sql resursen you8217ll hitta bifogad. Uppdatering av data och redogörelse för affärsproblemet Ofta befinner sig you8217ll många transaktioner inom en månad, men i vårt fall antar vi att du8217ve redan har grupperat dina transaktioner för varje månad. We8217ll tilldela vår PRIMARY KEY till en datatyp för DATE, och inkludera några värden som vi vill ackumulera rullande tolv månaders totala. Detta ger också en litet annorlunda fråga, så vi är intresserade av att se hur dess prestationsresultat jämförs med andra lösningar som hittills föreslagits. Så mycket för traditionella lösningar, och jag ber om ursäkta om jag råkade förbise en av dina favoriter, men känner mig fri att koda upp den och lägga till den i prestanda test sele vi8217ll presentera senare för att se hur det kostar. Lösning 5: Använda en quirky uppdatering Om you8217ve aldrig hört talas om Quirky Update (QU) och hur det kan tillämpas på problem som löpande totals rekommenderar jag starkt att du läser denna utestående artikel av SQL MVP Jeff Moden. med titeln Lösning av löpande totala och ordinära rankproblem. Innan vi fortsätter bör vi notera att det finns de som insisterar på QU-metoden representerar ett obodifierat beteende hos SQL Server och det är inte att lita på. Vi kan säga att syntaxen tydligt beskrivs av MS Books On Line-posten för UPDATE-förklaringen för SQL-versioner 2005, 2008 och 2012. Faktum är att det går längre än det. Jag har framgångsrikt använt den i SQL Server 2000 men det ärvde från Sybase och var i den första SQL Server-versionen som någonsin släpptes. Till naysayersna I8217ll säger att 8220undocumented8221-beteendet är åtminstone konsekvent i alla versioner och det finns förmodligen liten anledning att misstänka att den kommer att avlägsnas eller ändras i framtida versioner av MS SQL. Tänk dig varnad Om du någonsin överväga att använda en QU för att lösa ett problem, måste du noggrant beakta de många regler som gäller (ingår också i Jeffers referensartikel). De viktigaste, som I8217ve hanterade i den här frågan kan sammanfattas som: Tabellen måste ha ett grupperat index som anger beställningen av källraderna under perioden som du önskar att den ska kryssas. Bordet måste ha en kolumn där du kan placera den sammanlagda totala summan. När du utför uppdateringen måste du låsa bordet med hjälp av TABLOCKX-frågan för att se till att ingen annan kommer i någon INSERT s, DELETE s eller UPDATE s innan du går igenom. Du måste förhindra att SQL försöker parallellera frågan med hjälp av alternativet OPTION (MAXDOP 1). Eftersom ett rullande tolvmånadsmedelvärde helt enkelt är en löpande summa i förklädnad, kan vi lägga till en kolumn i vårt bord och tillämpa en QU-fråga för att göra vår beräkning. Jag måste bekänna att det här ser lite rotigt ut, med alla de variabler du behöver för att förklara. I grund och botten vad vi gör är att hålla reda på de sista tolv (kvarvarande) värdena, för att ta bort den 12: e (där Rolling12Months-kolumnen är tilldelad) från vad som annars är en QU-löpande summa som beskrivs i Jeff8217s artikel. Vi har stora förhoppningar om sin hastighet med tanke på att det är känt att vara den snabbaste metoden för att lösa löpande totalproblemet. Återigen bör du övertyga dig om att resultaten överensstämmer med tidigare lösningar och ja, den här lösningen fungerar fortfarande i SQL 2012. Om du har varit med mig hittills kan du också fråga dig själv 8220 Vad händer om jag behöver beräkna flera körningar tolv månaders totals över olika partitioner8221 Detta är relativt enkelt för alla andra lösningar som presenteras men föreslår lite utmaning med hjälp av QU. Svaret på detta finns i bifogade resursfilen: Quirky Update Partitioned. sql. SQL 2012-lösningar Hittills har allt vi gjort gjort för att fungera i SQL 2008. Det enda vi8217ve gjort som inte stöds i SQL 2005 är initialiseringen av variablerna vi DECLARE d i QU-metoden. Nu let8217s se vilka nya funktioner SQL 2012 har som kan tillämpas på detta problem. Lösning 6: Använda en fönsterram Vår första SQL 2012-lösning (6) visar hur man använder en fönsterram som börjar 11 rader före den aktuella raden, upp genom den aktuella raden till SUM våra önskade resultat. Återigen är de returnerade resultaten samma, men frågeplanen är helt annorlunda än för den tidigare SQL 2012-lösningen, men vi är inte särskilt optimistiska att detta tillvägagångssätt kommer att ge ett rimligt alternativ eftersom antalet 8220look-backs8221 behövs för att få det att fungera . Prestationsjämförelse av metoderna Det verkliga testet för att se hur flera lösningar fungerar är att kontrollera faktiska körningstider i en vilande server med hjälp av en testledning med många rader. Vårt test sele visas tillsammans med hur lösningen 1 och 2 har modifierats (se kommentarer i koden) till: Lägg in resultaten i en temp tabell för att undvika den förflutna tiden påverkan av att returnera raderna till SQL Server Management Studio8217s resultat rutnät. Ta bort DATE-aritmetiken, eftersom det är svårt att generera flera miljoner testtestar med flera miljoner unika månader, så kolumnen Datum har ändrats för att vara en BIGINT-datatyp. För de återstående lösningarna (2 8211 6) har vi grafat CPU och förflutna tid resultat från 1M men 4M rader. Tolkning av resultaten Förlindade och CPU-tiderna verkar vara konsekventa över de olika metoderna med avseende på deras beställning. Alla verkar skala på ett linjärt sätt. Den Quirky Update, förutsatt att du kan förstå det och alla dess associerade regler, verkar vara den snabbaste tillgängliga lösningen för att lösa detta problem, även med tanke på de nya funktionerna som finns tillgängliga i SQL 2012. I SQL 2012 är fönsterramens tillvägagångssätt säkerligen snyggt, kompakt och elegant, men spårar lätt Quirky Update-lösningen över de rader vi testat. Dessa testresultat verkar överensstämma med ett tidigare test på Running Totals i SQL 8220Denali8221 CTP3 av Microsoft Certified Master Wayne Sheffield i sin blogg. Om you8217re fastnar med en tidigare version av SQL (2005 eller 2008), och av någon anledning kan du fortsätta använda en quirky uppdatering (t. ex. om du inte lita på detta ookumenterade beteende), är de snabbaste lösningarna du har tillgång till antingen CROSS APPLY TOP eller med hjälp av en korrelerad underfråga, eftersom båda dessa verkade vara nära varandra över hela linjen. Det verkar som att 8220traditional8221 INNER JOIN är något som ska undvikas. Det kommer sannolikt bara att bli värre om du behöver göra datumräkning inom JOIN8217s ON-klausulen. På samma sätt var det inte säkert att använda en Tally-tabell eller flera LAG-s (SQL 2012). Vi utforskade inte CURSOR-baserade lösningar, men du kan spåra tillbaka till artikeln som hänvisas till löpande totaler för att få en uppfattning om hur de kan utföra i det här fallet. I8217ve har också sett några lösningar som använder en rekursiv Common Table Expression (rCTE), men jag skulle absolut inte satsa på deras prestanda jämfört med QU - eller fönsterramslösningarna. Det finns många sätt att beräkna värden i ett rullande fönster i SQL och det finns några tydliga prestationsvinster bland dem. Vi hoppas att du hittade den här guiden till de tillgängliga metoderna intressanta och informativa. Prenumerera på fler artiklar Snabba nyhetsbrev hjälper dig att skärpa dina kunskaper och hålla dig framåt, med artiklar, böcker och åsikter för att hålla dig informerad. Vill ha mer Prenumerera på vårt nyhetsbrev varje vecka Totalt: 33 Genomsnitt: 4.65 Dwain Camps har varit projektledare i många år. Eftersom applikationsprestanda kan vara en kritisk framgångsfaktor för projekt har han blivit evangeliserad på behovet av att utveckla högpresterande SQL. Med hjälp av mentorskap och författande artiklar om SQL hoppas han kunna träna en framtida generation av programvaruingenjörer på rätt och fel sätt att leverera SQL-kod. Han har också ett särskilt intresse för att utveckla lösningar för komplexa, datakrävande problem med hjälp av högpresterande SQL, eftersom SQL-deklarativa naturen möjliggör utveckling av algoritmiskt unika lösningar som procedurspråk kanske inte kan klara av. Följ Dwain på Twitter Mycket bra Bra artikel Jag blev förvånad över att LAG () gjorde så illa. Jag antar att varje påkallelse görs separat istället för faktureras och optimeras som ett fönster. Stor förklaring Jag håller med om, det här är en bra förklaring av olika sätt att beräkna värden inom ett rullande fönster. Om du testa dessa exempel på SQL 2012 måste du ändra MyTable med RollingTotalsExample. Tack så mycket, Mr. Camps Tally-metoden Hi Dwain, jag märkte att din Tally-tabellfråga orsakade en tabellspooloperatör och trodde att du kunde överväga att göra Tally-delen av ett datatabell så här: SELECT GroupingDate, ValueMAX (CASE GroupingDate WHEN Date TILL A. Value END), Rolling12MonthsCASE När ROWNUMBER () ÖVER (BESTÄLL AV GROUPINGDATE) lt 12 NULL ELSE SUM (Värde) SLUT IN I ResultsSoln2 FRÅN RollingTotalsExempel en CROSS APPLY (mdash Ta bort DATE aritmetiska värden (Datum), (Datum1) (Datum2), (Datum3), (Datum4), (Datum5), (Datum6), (Datum7), (Datum8), (Datum9), (Datum10), (Datum11)) c (GroupingDate) GROUP BY GroupingDate HAVING GroupingDate lt MAX (Datum) BESTÄLL AV GROUPINGDATE (ursäkta om formatering är dålig ndash ingen förhandsgranskning) Den här ändringen fortfarande skulle göra det en contender, men gör en enorm förbättring till den frågan. Tack för kommentarerna Tack Joe och Nic. Irsquom glad att du hittade artikeln intressant. Joe: Jag blev också något överraskad av LAG-resultaten och det får mig att undra vad break-even punkten skulle vara. Kanske 3 månader kanske inte är lika dåliga, men det är fortfarande svårt att tro att det kan vara snabbare än QU. Tally Tables MM: Av någon anledning har jag en personlig preferens för inline-Tally-tabeller, men dina resultat är intressanta att bara överväga för andra fall. Hjälp med att flytta årligt Totalt mitt första inlägg. Jag behöver beräkna det rörliga årliga summan för värdet ovan under de senaste 12 månaderna, med den här månaden som månad 12. Jag behöver då få det rörliga årliga summan för de tolv månaderna före detta. Med tanken att jämföra MAT för denna månad med motsvarande månad förra året och för varje föregående månad. Mitt försök gav mig det här: Med cte as (VÄLJ rNUM ROWNUMBER () Över (ordning efter datum). Datum. Värde Rolling12MonthsCASE När ROWNUMBER () ÖVER (BESTÄLL AV DATUM) gt 11 SUM (Värde) ÖVER (BESTÄLLNING AV DATUM RÄDD MELLAN 11 RÄDDNING OCH RÄTTSLIG RÅDE) SLUT FRÅN RollingTotalsExample) Välj Från cte, (Välj mRNum max (rNum) från cte) deMax Där rNum mellan mRNum ndash 23 och mRNum Med förmågan att ändra Were-satsen för att avspegla om jag vill ha det här året eller förra året. Min verkliga data har datumet som i heltal 201409 som jag tror kommer att göra livet enklare för mig, eftersom jag kan subtrahera 100 för att komma det föregående året. Utmärkt artikel och eventuell hjälp skulle uppskattas. Detta är min arbetslösning (med lite ljud) mdash Rolling 12 månader totalt med hjälp av SQL 2012 och en fönsterram IF OBJECTID (lsquotempdb..PreviousYearrsquo) ÄR INTE NULL DROP TABLE FöregåendeYear Med cte as (VÄLJ rNUM ROWNUMBER () Över (order efter datum ). Datum. Värde Rolling12MonthsCASE NÄR ROWNUMBER () ÖVER (ORDER AV DATUM) gt 11 SUM (Värde) ÖVER (BESTÄLLNING AV DAG RÖD MELLAN 11 RÄDDNING OCH RÖD RÅD) SLUT FRÅN RollingTotalsExample) Välj pyRowNum ROWNUMBER () Över (order av mRNum ). . sStart mRNum ndash 24. eEnd mRNum ndash 12 till PreviousYear Från cte, (Välj mRNum max (rNum) från cte) deMax Där rNum mellan mRNum ndash 23 och mRNum ndash 12 mdash Rollande 12 månaders totals med SQL 2012 och en fönsterram IF OBJECTID (lsquotempdb..ThisYearrsquo) ÄR INTE NULL DROP TABLE ThisYear Med cte as (VÄLJ rNUM ROWNUMBER () Över (ordning efter datum). Datum. Värde. Rolling12MonthsCASE När ROWNUMBER () ÖVER (BESTÄLL AV DATUM) gt 11 SUM (Värde) ÖVER MELLAN 11 RÄDDNING OCH LÖPANDE RÅD) SLUT FRÅN RollingTotalsExample) Välj tyRowNum ROWNUMBER () Över (order av mRNum). . sStart mRNum ndash 11. eEnd mRNum in ThisYear Från cte, (Välj mRNum max (rNum) från cte) deMax Där rNum mellan mRNum ndash 11 och mRNum Välj från ThisYear ty Vänster Bli medlem FöregåendeYear py på ty. tyRowNum py. pyRowNum Dessa kan fungera Irsquom inte nära en komp sql-åtkomst just nu så jag kan testa det (det kan finnas några typossyntaxfel). VÄLJ T. DateKey, AVG (T. ValueField) ÖVER (ÄR AV T. DateKey ASC MELLAN 365 PRECEDING OCH OCH RÄDD RÅD) AS YMAValueField FROM DataTable AS T ORDER AV T. DateKey ASC Om AVG är en av de sammanlagda funktionerna inte stöds med MELLAN intervall (jag vet att SUM stöds). VÄLJ T. DateKey, SUM (T. ValueField) ÖVER (ÄNDRA AV T. DateKey ASC MELLAN 365 PRECEDING OCH OCH HUVUD RÅD) CASE DATERIFF (DAG, StartDate, T. DateKey) lt 365 THEN DATEDIFF (DAG, StartDate, T. DateKey) ELSE 365 ENDA AS YMAValueField FRÅN DATABLAD AS T ORDER BY T. DateKey ASC Prenumerera på fler artiklar Fortnightly nyhetsbrev hjälper dig att skärpa dina kunskaper och hålla dig framåt, med artiklar, böcker och åsikter för att hålla dig informerad. Vill ha mer Prenumerera på vårt nyhetsbrev om två veckor Besök vårt artiklarbibliotek för att upptäcka de mönster och praxis du behöver för att flytta till mer smidiga metoder för databasleverans. Ta reda på hur du automatiserar processen att bygga, testa och distribuera dina databasändringar för att minska risken och påskynda leveranscykeln. Topprankade artiklar i T-SQL Programmering Relaterade artiklar Även i SQL Med uppkomsten av NoSQL-databaser som utnyttjar aspekter av SQL för att fråga, och omfattar full transaktionalitet, finns det en risk för att datadokumentmodellerna hierarkisk karaktär orsakar en grundläggande konflikt med relationsteorin Vi frågade vår relationella expert, Hugh Bin-Haad, för att förklara ett svårt område för databassteoretiker. hellip Läs mer Även i SQL Server Varje SQL Server Database-programmerare behöver känna till systemfunktionerna. Dessa sträcker sig från det sublima (som raden eller identiteten) till det löjliga (IsNumeric ()) Robert Sheldon ger en översikt över de vanligaste användningarna av dem. HELP Läs mer Även i T-SQL-programmering För att kunna utnyttja systemkatalogen för att få veta mer om en databas, måste du vara bekant med metadatafunktionerna. De sparar mycket tid och skriver när de frågar metadata. När du väl har hängt på dessa funktioner ser systemkatalogen plötsligt ut att vara enkel att använda, som Robert Sheldon demonstrerar i den här artikeln. Hellip Läs mer Även i T-SQL-programmering En rad argument med utvecklare som insisterar på att fuzzy sökningar eller stavningskontroll görs inom applikationen snarare än en relationsdatabas inspirerade Phil Factor för att visa hur det görs. När databasen måste hitta relevant material från söktermer inskrivna av användarna måste databasen lära sig att förvänta sig och hantera både förväntat och oväntat hellip Läs mer kopia 2005 - 2017 Red Gate Software Ltd Vad tycker du om den nya Simple Talk Give oss din feedback
No comments:
Post a Comment