Pl Sql Moving Average

Det var et fint spørsmål om OTN i dag om det finnes en standard Oracle-funksjon for å beregne eksponentielt glidende gjennomsnitt. Svaret er at det ikke finnes en slik funksjon, men med modellklausulen kan du beregne det veldig enkelt. Og det er et godt eksempel på hva jeg mener med variabelt antall beregninger basert på beregnede verdier, skrevet i min tredje del av modellklausulen tutorial. Før i dag skjønte jeg ikke engang hva et eksponentielt glidende gjennomsnitt var akkurat. Du kan lese mer om det her på Wikipedia eller her med et fint eksempel Fra den første linken. En eksponentiell glidende gjennomsnittlig EMA, bruker vektningsfaktorer som reduseres eksponentielt. Vektingen for hvert eldre datapunkt faller eksponentielt, noe som gir mye større betydning for de siste observasjonene, mens det fortsatt ikke fjernes eldre observasjoner helt. Fra den andre lenken . Formelen for å beregne en eksponentiell flytende gjennomsnittlig EMA er. X Nåværende EMA dvs. EMA skal beregnes. C Nåværende opprinnelige dataverdi. K Glatting C onstant. P Tidligere EMA. Den første EMA i området som skal beregnes, er vilkårlig og kan være den tilsvarende opprinnelige dataværdi eller ofte en enkel flytende gjennomsnittsverdi. K utjevning konstant 2 1 n. Og denne formelen følges av et eksempel som jeg utvidet litt, ved hjelp av denne tabellen. Oppføringene fra produktet En samsvar med eksemplet i lenken Jeg lagde tallene fra produktet B Her er spørsmålet om modellklausul som implementerer formelen Merk hvordan formelen direkte oversetter til den eneste regelen av modellklausulen The utjevning konstant K er satt til 5, basert på et vindu av verdier n som tilsvarer 3.Challenge prøve dette uten modellklausulen og se om du kan komme opp med noe mer omfattende.11 2 funksjoner i bruk. med det som velg en produktdato 2009-01-01 måned, 10 beløp fra dobbelt union alle velg A, dato 2009-02-01, 15 fra dobbelt union alle velg A, dato 2009-03-01, 17 fra dual union alle velg A, dato 2009-04 -01, 20 fra dual union alle velg A, dato 2009-05-01, 22 fra dual union all select A, dato 2009-06-01, 20 fra dual union alle velg A, dato 2009-07-01, 25 fra dual union alle velg A, dato 2009-08-01, 27 fra dual union alle velg A, dato 2009- 09-01, 30 fra dual union alle velg A, dato 2009-10-01, 35 fra dual union alle velg A, dato 2009-11-01, 37 fra dual union alle velg A, dato 2009-12-01, 40 fra toforeningen alle velger B, dato 2009-01-01, 0 fra tounion alle velger B, dato 2009-02-01, 50 fra dobbeltforening alle velger B, dato 2009-03-01, 10 fra dobbeltforening alle velger B, dato 2009-04-01, 40 fra dual union alle velg B, dato 2009-05-01, 15 fra dual union alle velg B, dato 2009-06-01, 35 fra dual union alle velg B, dato 2009- 07-01, 30 fra dual union alle velger B, dato 2009-08-01, 30 fra dual union alle velg B, dato 2009-09-01, 20 fra dual union alle velg B, dato 2009-10-01, 20 fra dual union alle velger B, dato 2009-11-01, 20 fra dual union alle velger B, dato 2009-12-01, 20 fra dual, rns som velg dat rownumber over partisjon etter produkt rekkefølge etter måned rn - 2 1 telle over partisjon etter produkt k 0 5 k fra dat, res produkt, måned, beløp, rn, x som velg x fra rns r hvor rn 1 union alle velger - es x es xx fra rns ns, res er der 1 og velg produkt, måned, beløp, rn, runde x, 3 EMA fra res rekkefølge etter produkt, month. after beregne lukkede skjema Jeg kom opp med følgende kode som at hvis det er mer som en forvirring enn noe omfattende Ideen er å lage løpende multipler ved hjelp av en strengkonsentasjon og xml-eval-funksjonaliteten. De lukkede formene av de spesielle tilfellene trenger bare løpende summer. Det er generelt og to spesielle tilfeller som er mye enklere. Med t1 som valgt produkt, måned, beløp, mengde ci, rownumber over partisjon etter produkt rekkefølge etter måned rn, --2 1 rownumber over partisjon etter produkt rekkefølge etter måned ki 0 5 ki fra salg, t2 som velg produkt, måned, beløp, tilfelle når rn 1 deretter 1 annet ki ende ci ai, tilfelle når rn 1 deretter 1 annet 1 - ki ende bi fra t1, t3 som SELECT produ ct, Måned, beløp, ai, xmlquery SKIFT Wmconcat bi over DELISJON AV produkt BESTILL PER MÅNED ROR MELLOM ubundet foregående OG HØYRE RÅD,, RETURNING mi FRA t2, t4 som velg produkt, måned, beløp, mi, ai mi xi fra t3 VELG produkt, MÅNED, mengde, runde mi SUM xi over DELISJON AV produkt ORDER PER MÅNEDRER MELLOM ubundet foregående OG HØYRE RÅD, 3 eMA FRA t4.Spesielt tilfelle K 0 5.Velg t1 som velg produkt, måned, beløp, rownummer over partisjon etter produkt rekkefølge etter måned rn, mengde strøm 2, nvl nullif rownumber over partisjon etter produkt rekkefølge etter måned - 1, 0, 1 ci fra salg velg produkt, måned, beløp, runde sum ci over partisjon etter produkt rekkefølge etter måned rader mellom ubundet forrige og nåværende radmakt 2, rn, 3 ema fra t1. Spesielt tilfelle K 2 1 i. med t1 som velg produkt, måned, beløp, rownumber over partisjon etter produkt rekkefølge etter måned rn, beløp rownummer over partisjon etter produkt rekkefølge etter måned ci fra salg velg produkt, måned, beløp, runde sum ci over partit ion etter produkt rekkefølge etter måned rader mellom ubundet foregående og nåværende rad 2 rn rn 1, 3 ema fra t1.Jeg legger inn beviset på lukket form hvis noen er interessert i den. Dette er et godt eksempel på moro med SQL. A. kombinasjon av XMLQuery, den uokumenterte wmconcat og analytiske funksjoner med windowing-klausulen Jeg liker det Selv om det ikke er så omfattende som modellklausulvarianten og Rafu s rekursiv med en, som du sa selv. Og jeg vil gjerne se Beviset på den lukkede formen. Jeg taklet et annet spørsmål om hvordan du optimaliserer utjevningskonstanten. SELECT k - utjevning konstant mse - gjennomsnittlig kvadratfeil FRA VELG FRA SALG MODELL DIMENSJON AV PRODUKT ROWNUMBER OVER DELISJON AV PRODUKT ORDER AV MÅNED ASC RN MÅL mengder - salgsmengde måned - måned 0 AS C 0 AS P 0 AS X 0 AS SE - kvadratfeil - - arbeidsrad og attributter - en arbeidsrad er produkt X, rn 1 - b arbeidsattributter er som følger 0 AS SSE - sum SE for alle produkter måneder 0 AS MSE - betyr SSE for alle pr oducts måneder 0 AS k - for alle produkter måneder 0 AS PreMSE - tidligere MS MSE for alle produkter måneder 0 AS diff - mellom nåværende MSE og tidligere 0 1 AS delta - innledende inkrement 0 AS prioritet - startpunkt - - REGLER ITERATE 99 TIL MIS DIFFERT A, 1 0 00010 C noen, RN-beløp cv, cv KA, 1 prioriterte A, 1 delta A, 1 X noen, rn BESTILL BY produkt, ASC COALESCE KA, 1 C cv, cv 1 - KA, 1 X cv, cv -1, C cv, cv P produkt, rn X cv, cv -1 SE produkt, RN POWER C cv, cv - X cv, cv -1, 2 SSE A, 1 SUM SE noen , noen MSE A, 1 SUM SE, noen 24 diff A, 1 CASE iterasjonsnummer når 0 da 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 prioriterte A, 1 KA, 1 hvor produkt A og rn 1 K MSE ---------- -------- - 599999237 174 016094.Den gruppe av er hva som aggregerer gjennomsnittet ditt, og det er gruppering av hele bordet. Jeg antar at du gjorde dette for å tillate valget for alt. Bare flytt din avg til en annen underforespørsel, fjern den overordnede gruppen av og som bør løse den. Når du kjører den grunnleggende SELECT AVG-kostnadsoversikten, grupperer den naturlig etter kolonnen angitt pris i dette tilfellet, da det er det du ber om. Jeg foreslår at du leser mer på GROUP BY og aggregater til få bedre forståelse av konseptet. Det burde hjelpe deg mer enn bare en enkel løsning. Svaret nedenfor er faktisk fra Davids svar. Det bruker analytiske funksjoner. I utgangspunktet er det som skjer i hvert AVG-anrop, du forteller motoren hva du skal bruke for funksjonen i dette tilfellet, ingenting En anstendig oppskrivning på analytiske funksjoner finner du her og her og mer med en google på saken. Men hvis din SQL-motor tillater variabler, kan du like gjerne gjøre det under svar Jeg foretrekker dette for fremtidig vedlikeholdbarhet. Årsaken er at en variabel med et godt navn kan være veldig beskrivende for fremtidige lesere av koden, i motsetning til en analytisk funksjon som krever litt mor e arbeider for å lese spesielt hvis du ikke forstår overfunksjonen. Også denne løsningen dupliserer det samme spørsmålet to ganger, så det kan være verdt å lagre gjennomsnittet i en SQL-variabel. Derefter kan du endre setningen din for å bare bruke det globale gjennomsnittet. Dette er variabler i SQL-Server må du tilpasse det til din egen forekomst av SQL. Denne løsningen vil lese mye renere til fremtidige lesere av SQL-en din også. Jeg er ganske sikker på at David s spørring er minst like effektiv og ikke krever bruk av en PL SQL-blokk som krever at du legger resultatet av SELECT i variabler - det vil ikke bli vist slik du skrev det faktisk, jeg tror det ikke ville kjøre, og i tillegg vil det dupliserte spørsmålet bare bli kjørt en gang etter Oracle Jeg er ganske sikker på at optimalisereren er smart nok til å oppdage at ahorsewithnoname 10. mars kl. 16 på 21. 21. Jeg setter pris på din forklaring, men PL SQL-blokken er rett og slett feil. Denne tilnærmingen vil aldri fungere i Oracle. Hvis du ikke vil gjenta avg spørre, bruk tilnærmingen med t han analytisk funksjon eller krysset sammenløpsløsning ahorsewithnoname Mar 10 12 på 16 29. Bruk et enkelt glidende gjennomsnitt for å jevne ut data er en ganske populær teknikk, det er så ille, at det primære eksemplet i SQL Anywhere Help er langt fra enkelt. Hva gjør det eksempelet så komplisert I tillegg til problemstillingen, beregner det det bevegelige gjennomsnittet av alt produktsalg, per måned, i år 2000. Her er det som gjør det komplekst. Two referanser til AVG-funksjonen. a GRUPPE AV som det i seg selv gjør omtrent noen SELECT en head-scratcher. en skjult WINDOW clause. a WINDOW-klausul som ikke bruker WINDOW-søkeordet, slik at de uinitierte folkene som trenger eksempler mer enn noen andre, er det ikke åpenbart at en WINDOW er involvert i det hele tatt. Ikke bare noe WINDOW-klausul, husk deg, men en som inneholder hver enkelt komponent du kan kode i en WINDOW. a PARTITION BY. a RANGE-klausul ikke en enkel ROWS-klausul, men fullblåst RANGE-klausul, en som har et intimt forhold til ORDEREN BY Jeg vet hva en rad er, men hva redigeres er en RANGE. Men vent, det er mer. Valget av RANGE over ROWS i dette eksemplet er avgjørende for riktig bruk av spørringen for en mer fullstendig diskusjon av dette bestemte eksempelet, se eksempel 23 - Computing a Moving Average i Glenn Paulleys utmerkede OLAP-papir. La oss nå komme tilbake på sporet. En virkelig virkelig enkel flytende gjennomsnitt. Følgende eksempel viser 10 dagers verdier sammen med det bevegelige gjennomsnittet av dagens verdi og i går s WINDOW-klausulen på linjer 21 til 23 definerer et bevegelsesvindu som inneholder to rader i dag s rad CURRENT ROW og i går s rad 1 PRECEDING. the WINDOW ORDER BY-setningen bestemmer hva PRECEDING betyr forrige rad av and. the ROWS-klausulen bestemmer størrelsen på vinduet alltid to rader. Uttrykket AVG OVER twodays på linje 19 refererer til WINDOW-klausulen etter navn, og det forteller SQL Anywhere å beregne gjennomsnittet av de to verdiene som eksisterer i 2-rads glidende vindu, for hver rad i resultatsettet. Så for 2012 -02-02 gjennomsnittet 10 og 20 er 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 på 10 og 60 er 35 000000.Oops, hva med den første row. The 2012-02-01-raden har ikke en PRØVENDE rad, så hva er gjennomsnittet over det bevegelige vinduet. Ifølge Glenn Paulleys s hvite papir i tilfelle et bevegelig vindu antas det at rader med null-verdier eksisterer før første rad, og etter siste rad i inpu t. Det betyr at når vinduet som beveger seg 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 at NULL i det hele tatt ikke er i teller eller i nevneren når du beregner gjennomsnittet Her er bevis Det er derfor twodayaverage 10 000000 for første rad 2012-02-01.Postet av Breck Carter klokka 3 47 PM.