Pl sql glidande medelvärde

Det fanns en fin fråga om OTN idag om det finns en vanlig Oracle-funktion för att beräkna exponentiell glidande medelvärdet. Svaret är att det inte finns någon sådan funktion, men med modellklausulen kan du beräkna det väldigt enkelt. Och det är ett bra exempel på Vad jag menar med varierande antal beräkningar baserade på beräknade värden, skrivna i min tredje del av modellklausulens handledning. Innan idag visste jag inte ens vad ett exponentiellt rörligt medelvärde var exakt. Du kan läsa mer om det här på Wikipedia eller här Med ett bra exempel Från den första länken. En exponentiell glidande medelvärde EMA tillämpar viktningsfaktorer som minskar exponentiellt Vägningen för varje äldre datapunkt minskar exponentiellt, vilket ger mycket större betydelse för de senaste observationerna medan de fortfarande inte slänger bort äldre observationer helt. Från den andra länken . Formeln för att beräkna en exponentiell rörlig genomsnitts EMA är. X Nuvarande EMA dvs EMA att beräknas. C Nuvarande ursprungliga datavärde. K Utjämning C Onstant. P Tidigare EMA. Den första EMA i det räckvidd som ska beräknas är godtyckligt och kan vara motsvarande ursprungliga data värde eller ofta ett enkelt rörligt medelvärde. K utjämning konstant 2 1 n. Och denna formel följs av ett exempel som jag förlängde lite, med hjälp av denna tabell. Rekord från produkt En matcha exemplet i länken Jag gjorde upp siffrorna från produkt B Här är modellklausulfrågan som implementerar formeln Observera hur formeln direkt översätter till den enda regeln i modellklausulen The utjämningskonstant K är satt till 5 baserat på ett fönstervärde n som motsvarar 3.Challenge försök detta utan modellklausulen och se om du kan komma med något mer omfattande.11 2 funktioner i bruk. med det som välj ett produktdatum 2009-01-01 månad, 10 belopp från dubbel union alla välj A, datum 2009-02-01, 15 från dubbel union alla välj A, datum 2009-03-01, 17 från dubbel union alla välj A, datum 2009-04 -01, 20 från dubbel union alla välj A, datum 2009-05-01, 22 från dubbel union alla välj A, datum 2009-06-01, 20 från dubbel union alla välj A, datum 2009-07-01, 25 från dubbel union alla välj A, datum 2009-08-01, 27 från dubbel union alla välj A, datum 2009- 09-01, 30 från dubbel union alla välj A, datum 2009-10-01, 35 från dubbel union alla välj A, datum 2009-11-01, 37 från dubbel union alla välj A, datum 2009-12-01, 40 från dubbel union alla välj B, datum 2009-01-01, 0 från dubbel union alla välj B, datum 2009-02-01, 50 från dubbel union alla välj B, datum 2009-03-01, 10 från dubbel union alla välj B, datum 2009-04-01, 40 från dubbel union alla välj B, datum 2009-05-01, 15 från dubbel union alla välj B, datum 2009-06-01, 35 från dubbel union alla välj B, datum 2009- 07-01, 30 från dubbel union alla välj B, datum 2009-08-01, 30 från dubbel union alla välj B, datum 2009-09-01, 20 från dubbel union alla välj B, datum 2009-10-01, 20 Från dubbel union alla välj B, datum 2009-11-01, 20 från dubbel union alla välj B, datum 2009-12-01, 20 från dual, rns som välj dat rownumber över delning efter produkt order efter månad rn - 2 1 räkna över partition med produkt k 0 5 k från dat, res produkt, månad, mängd, rn, x som välj x från rns r där rn 1 union alla väljer xx från rns ns, res es där 1 och välj produkt, månad, mängd, rn, runda x, 3 EMA från resorderna per produkt, month. after beräkning av den slutna blanketten kom jag upp med följande kod att om mer som en obfuscation än vad som är heltäckande Tanken är att skapa körmultiplar med hjälp av en strängkonatatisering och xml-eval-funktionaliteten De slutna formerna av de speciella fallen behöver bara springa summor. Det finns allmänt fall och två speciella fall som är mycket enklare. Med t1 som välj produkt, månad, mängd, mängd ci, rownumber över partition efter produkt order efter månad rn, --2 1 rownumber över partition efter produkt order efter månad ki 0 5 ki från försäljning, t2 som valda produkt, månad, mängd, fall då rn 1 sedan 1 annan ki slutet ci ai, fall då rn 1 då 1 annars 1 - ki slutet bi från t1, t3 som SELECT produ ct, MÅNAD, mängd, ai, xmlquery REPLERA wmconcat bi över PARTITION BY-produkt ORDER PER MÅNAD rader MELLAN obegränsad föregående OCH LÖPANDE RÅD,, RETURNING mi FRÅN t2, t4 som vald produkt, månad, mängd, mi, ai mi xi från t3 VÄLJ produkt, MÅNAD, mängd, rund mi SUM xi över PARTITION BY produkt ORDER PER MÅNAD rader MELLAN obegränsad föregående OCH LÖPANDE RÅD, 3 eMA FRÅN T4.Specialfall K 0 5.med t1 som välj produkt, månad, mängd, rownumber över partition per produkt order efter månad rn, mängd effekt 2, nvl nullif rownumber över partition efter produkt order efter månad - 1, 0, 1 ci från försäljnings välj produkt, månad, mängd, runda summa ci över partition efter produkt order efter månad rader mellan obegränsad föregående och nuvarande radkraft 2, rn, 3 ema från t1.Special case K 2 1 i. with t1 som välj produkt, månad, mängd, rownumber över partition efter produkt order efter månad rn, mängd rownumber över partition per produkt order per månad ci från försäljning välj produkt, månad, belopp, round sum ci over partit jon för produkt order efter månad rader mellan obegränsad föregående och nuvarande rad 2 rn rn 1, 3 ema från t1.I ll posta beviset på den slutna formen om någon är intresserad av det. Detta är ett bra exempel på kul med SQL. A kombination av XMLQuery, den ofokumenterade wmconcat och analytiska funktioner med fönsterklausulen Jag tycker om den Även om den inte är lika omfattande som modellklausulvarianen och Rafu s rekursiv med en, som du sa själv. Och jag skulle vilja se beviset på den slutna formen. Jag tackade en annan fråga hur man optimerar utjämningskonstanten. SELECT k - utjämning konstant mse - genomsnittlig kvadratfel FRÅN VÄLJ FRÅN försäljningsmodell DIMENSION PER PRODUKT RÖDDOM ÖVER DELITION AV PRODUKT ORDER PER MÅNAD ASC RN ÅTGÄRDER mängd - försäljningsbelopp månad - månad 0 AS C 0 AS P 0 AS X 0 AS SE - kvadrerat fel - - arbetsrad och attribut - en arbetsrad är produkt X, rn 1 - b arbetsattribut är som följer 0 AS SSE - summa SE för alla produkter månader 0 AS MSE - medel SSE för alla pr oducts months 0 AS k - för alla produkter månader 0 AS PreMSE - tidigare ks MSE för alla produkter månader 0 AS diff - mellan nuvarande MSE och tidigare 0 1 AS delta - initial inkrement 0 AS prioritet - startpunkt - - REGLER ITERERA 99 UNTIL MAG DIFFERA, 1 0 00010 C Något, Mängden cv, cv KA, 1 A A, 1 A A, 1 X ANVÄND, RN BESTÄLL AV PRODUKT, RN ASC COALESCE KA, 1 C cv, cv 1 - KA, 1 X cv, cv-1, C cv, cv P-produkt, rn X cv, cv-1-produkt, rn POWER C cv, cv - X cv, cv-1, 2 SSE A, 1 SUM SE , Vilken som helst MSE A, 1 SUM SE, vilken som helst 24 diff A, 1 CASE iterationsnummer när 0, då NULL ELSE preMSE A, 1 - MSE A, 1 END preMSE A, 1 MSE A, 1 delta A, 1 CASE WHEN diff A, 1 0 THEN - abs delta A, 1 2 ELSE abs delta A, 1 END prioriterad A, 1 KA, 1 där produkt A och rn 1 K MSE ---------- -------- - 599999237 174 016094.Your grupp av är vad som aggregerar ditt medelvärde och det grupperar vid hela bordet Jag antar att du gjorde detta för att tillåta valet för allt Flytta bara din avg till en annan underfråga, ta bort Den övergripande gruppen av och som borde lösa den. När du kör det grundläggande SELECT AVG-kostnadsredovisningen är det naturligt att gruppera med den angivna kolumnen i det här fallet, eftersom det är vad du ber om. Jag föreslår att du läser mer på GROUP BY och aggregerar till få bättre förståelse för konceptet Det bör hjälpa dig mer än bara en enkel lösning. Svaret nedan är faktiskt från Davids svar. Det använder analytiska funktioner. I grund och botten är det som händer att på varje AVG-samtal säger du motorn Vad man ska använda för funktionen i det här fallet, ingenting En anständig skrivning på analytiska funktioner finns här och här och mer med en google i frågan. Men om din SQL-motor tillåter variabler kan du lika enkelt göra det nedan svarar jag faktiskt föredrar detta för framtida underhållbarhet. Orsaken är att en variabel med ett bra namn kan vara väldigt beskrivande för framtida läsare av koden, mot en analytisk funktion som kräver lite mor e fungerar för att läsa speciellt om du inte förstår överfunktionen. Också denna lösning duplicerar samma fråga två gånger så det kan vara värt att lagra ditt medelvärde i en SQL-variabel. Sedan kan du ändra ditt uttalande för att helt enkelt använda det globala genomsnittet. är variabler i SQL-Server måste du anpassa det för din egen instans av SQL. Den här lösningen kommer att läsa mycket renare till framtida läsare av din SQL också. Jag är ganska säker på att David s fråga är minst lika effektiv och inte Kräver användning av ett PL SQL-block som kräver att du sätter resultatet av SELECT i variabler - det kommer inte att visas på samma sätt som du skrev det. Jag tror att det inte ens skulle köras. Och förutom det dubbla frågan kommer endast att köras en gång av Oracle Jag är ganska säker på att optimeringsenheten är smart nog att upptäcka det ahorsewithnoname 10 mar kl 12 på 16 21. Jag uppskattar din förklaring, men PL SQL-blocket är helt enkelt fel Det här tillvägagångssättet kommer aldrig att fungera i Oracle Om du inte vill upprepa avg Fråga, använd tillvägagångssättet med t Han analytiska funktion eller cross join-lösningen ahorsewithnoname mar 10 12 på 16 29.Ut ett enkelt glidande medelvärde för att jämna ut data är en ganska populär teknik det är så illa det primära exemplet i SQL Anywhere Help är långt ifrån enkelt. Vad gör det exemplet så komplicerat Förutom problemformuleringen beräknas det rörliga genomsnittet av all produktförsäljning, per månad, år 2000. Här är det som gör det komplext. två referenser till AVG-funktionen. En GRUPP AV vilken allt i sig handlar om Någon SELECT en huvudskrapa. en snygg WINDOW clause. a WINDOW-klausul som inte ens använder WINDOW-sökordet så att de oinitierade personerna som behöver exempel mer än någon annan är det inte uppenbart att en WINDOW är involverad alls. Inte bara någon Windows-klausul, tänker dig, men en som innehåller varje enskild komponent som du kan koda i en WINDOW. a PARTITION BY. a RANGE-klausul, inte en enkel ROWS-klausul, men fullblåst RANGE-klausul, en som har ett intimt förhållande med ORDER BY Jag vet vad en rad är, men vad redigeras är en RANGE. Men vänta, det är mer Valet av RANGE över ROWS i det här exemplet är avgörande för korrekt sökning av frågan för en mer fullständig diskussion av det här exemplet, se exempel 23 - Beräkning av ett rörligt medelvärde i Glenn Paulleys utmärkta OLAP-vitt papper. Låt oss nu komma tillbaka på spår. A Verkligen riktigt enkelt rörande medelvärde. Följande exempel visar 10 dagars värde av data tillsammans med det glidande medlet av dagens värde och igår s WINDOW-klausulen på linjer 21 till 23 definierar ett rörligt fönstret som innehåller två rader idag s raden CURRENT ROW och igår s row 1 PRECEDING. theWINDOW ORDER BY-klausulen bestämmer vad PRECEDING betyder föregående rad av and. the ROWS-klausulen bestämmer storleken på fönstret alltid två rader. Uttrycket AVG OVER twodays på rad 19 hänvisar till WINDOW-klausulen med namnet, och det berättar att SQL Anywhere ska beräkna genomsnittet av de två värdena som finns i 2-rader skjutfönstret, för varje rad i resultatuppsättningen. Så för 2012 -02-02 medeltalet 10 och 20 är 15 000000.för 2012-02-03 är genomsnittet 20 och 10 15 000000.för 2012-02-04 är genomsnittet 10 och 30 20 000000.för 2012-02 -10 genomsnittet 10 och 60 är 35 000000.Oops, vad sägs om den första roden. 2012-02-01 raden har inte en föregående rad, så vad är genomsnittet över det rörliga fönstret. Enligt Glenn Paulley s vita Papper vid ett rörligt fönster antas att rader med Null-värden existerar före första raden och efter sista raden i inpu T. Det innebär att när det rörliga fönstret har 2012-02-01 som CURRENT ROW innehåller raden 1 PRECEDING NULL-värden och när SQL Anywhere beräknar en AVG som innehåller ett NULL-värde, räknar det inte med att NULL alls inte är i Täljare eller i nämnaren vid beräkning av genomsnittet Här är beviset Det är därför twodayaverage 10 000000 för första raden 2012-02-01. Upplagt av Breck Carter klockan 3 47 PM.