Thursday, 23 November 2017

Glidande medelvärde beräkning in sql


I det här inlägget visar jag ett knep för att flytta genomsnittlig beräkning (kan utvidgas till andra operationer som kräver fönsterfunktioner) som är super snabbt. Ofta behöver SAS-analytiker genomföra glidande medelberäkningar och det finns flera alternativ i preferensordningen: 1. PROC EXPAND 2. DATABLAD 3. PROC SQL Men många webbplatser får inte licensierade SASETS att använda PROC EXPAND och flytta genomsnittet i DATA STEP kräver viss kodning och är felaktig. PROC SQL är ett naturligt val för juniorprogrammerare och i många affärssaker är den enda lösningen, men SAS39s PROC SQL saknar fönsterfunktioner som finns tillgängliga i många DB: er för att underlätta glidande medelberäkning. En teknik som vanligtvis används är CROSS JOIN, vilket är väldigt dyrt och inte en lönsam lösning för även medelstor dataset. I det här inlägget visar jag ett knep för att flytta genomsnittlig beräkning (kan utvidgas till andra operationer som kräver fönsterfunktioner) som är super snabbt. Tänk på den enklaste glidande genomsnittliga beräkningen där de efterföljande K-observationerna ingår i beräkningen, nämligen MA (K), här ställer vi in ​​K5. Vi skapar först en 20 obs provdata, där variabel ID ska användas för fönstret och variabeln X ska användas vid MA-beräkning, och sedan tillämpar vi standard CROSS JOIN för att först undersöka de resulterande data, Ej grupperade, bara för att förstå hur man utnyttjar datastrukturen. Från den resulterande datasatsen är det svårt att hitta en ledtråd, nu let39s sortera enligt quotbidquot-kolumnen i den här datasatsen: Från den här sorterade data är det uppenbart att vi faktiskt inte behöver kryssa ihop hela den ursprungliga datasatsen, utan istället, vi kan generera en quotationationquot dataset som innehåller skillnadsvärdet och låt den ursprungliga datamängden CROSS JOIN med denna mycket mindre quotoperationquot dataset och alla data vi behöver använda för MA-beräkning kommer att finnas där. Låt oss nu göra det: KROSS JOIN original data med quotoperationquot data, sortera efter (a. idops), vilket är faktiskt quotbid39 i sorterade dataset. Observera att i ovanstående kod är det nödvändigt att ha ax multiplicera med b. weight så att data kan vara mellanliggande, annars kommer samma X-värde från originalbordet att matas ut och MA-beräkningen misslyckas. Den uttryckliga viktvariabeln lägger faktiskt i mer flexibilitet till hela MA-beräkningen. Medan man bestämmer att det är 1 för alla, resulterar det i en enkel MA-beräkning. Tilldela olika vikter hjälper till att lösa mer komplicerad MA-databehandling, till exempel att ge ytterligare observationer mindre vikt för en förfallna MA. Om olika K-parametrar i MA (K) beräkningar är nödvändiga behöver endast operationsdatasatsen uppdateras vilket är trivialt jobb. Nu är den faktiska kodmallen för MA (K) beräkning: Med den här nya metoden är det intressant att jämföra det med det dyra själv CROSS JOIN såväl som PROC EXPAND. På min arbetsstation (Intel i5 3.8Ghz, 32GB minne, 1TB 72K HDD) är självkrokslängden förbjuden länge under körtiden (om data är stor) medan den nya metoden endast använder 2X så mycket tid som PROC EXPAND, är båda tidsförbrukningarna trivial jämförelse med sig själv CROSS JOIN. Tidsförbrukningen som visas nedan är i kvotskvot. Nedan är kodläsarna kan springa och jämföra er själva. Postat 10 maj 2015 av Liang Xie SAS Programmering för datavinnning Jag vill infoga två genomsnittliga beräkningar för ett gäng värdekolumner i mitt valda uttalande. se den här länken för min förenklade tabellstruktur, inklusive önskad utgångsberäkning: Pastebin Month1 värde av värdet1-kolumnen för den månaden, månad2 om summan 0 skriv 0, annars avg (månad1 och månad2) och så vidare. Så för varje produkt vill jag ha det glidande genomsnittet för varje månad inom ett år. Jag har denna inställd i min Excel men jag kan inte överföra uttrycket till sql. för varje produkt, beräkna medelvärdet över alla år och duplicera det beräknade värdet till alla rader för den produkten. Jag hoppas att du kan hjälpa mig med det här. Det verkar som om jag behöver ett förfarande, men det är kanske bara ett enkelt uttalande. Jag jobbar med SQL Server 2008 R2 och 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å den 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 dagarna. 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)

No comments:

Post a Comment