N day exponentiell glidande medelvärde

Exponentiell rörlig medelkalkylator Med en ordnad lista över datapunkter kan du konstruera det exponentiellt viktade glidande medlet för alla punkter upp till den aktuella punkten. I ett exponentiellt rörligt medelvärde (EMA eller EWMA för korta) minskar vikterna med en konstant faktor 945 då villkoren blir äldre. Denna typ av kumulativt glidande medel används ofta vid kartläggning av aktiekurserna. Den rekursiva formeln för EMA är där x idag är dagens prispunkt och 945 är konstant mellan 0 och 1. Ofta är 945 en funktion av ett visst antal dagar N. Den vanligaste funktionen är 945 2 (N1). Exempelvis har 9-dagars EMA för en sekvens 945 0,2, medan en 30-dagars EMA har 945 231 0,06452. För värden på 945 närmare 1 kan EMA-sekvensen initialiseras vid EMA8321 x8321. Om 945 är väldigt liten kan emellertid de tidigaste termerna i sekvensen få otillbörlig vikt vid en sådan initialisering. För att korrigera detta problem i en N-dag EMA, är EMA-sekvensens första termin fastställd som det enkla genomsnittet för de första 8968 (N-1) 28969 termerna. EMA börjar därför på dag nummer 8968 (N-1 ) 28969. Till exempel i ett 9-dagars exponentiellt rörligt medelvärde, EMA8324 (x8321x8322x8323x8324) 4. Då EMA8325 0.2x8325 0.8EMA8324 och EMA8326 0.2x8326 0.8EMA8325 etc. Med hjälp av exponentiell rörlig genomsnittsaktie analyserar aktieanalytiker ofta EMA och SMA (enkelt glidande medelvärde) av aktiekurserna för att notera trender i stigande och fallande eller priser och för att hjälpa dem förutsäger framtida beteende. Liksom alla glidande medelvärden kommer hög - och lågnivåerna i EMA-grafen att ligga bakom höga och låga punkter i den ursprungliga ofiltrerade data. Ju högre värdet på N, desto mindre 945 blir och ju mjukare grafen blir. Förutom exponentiellt vägda kumulativa glidmedel, kan man också beräkna linjärt viktiga kumulativa glidmedel, där vikterna minskar linjärt när villkoren blir äldre. Se den linjära, kvadratiska och kubiska kumulativa glidande artikeln och kalkylatorn. Användningsområde True Range-kalkylblad 038 Tutorial Upptäck hur handlare använder genomsnittligt sant intervall som en stoppförlustindikator när man köper amp-försäljningsstrategier och lär sig hur det beräknas i Excel. Ett lager8217s intervall är skillnaden mellan maximi - och minimipriset på en enskild dag och används ofta som en indikator på volatilitet. Dock stoppas handel ofta om priserna ökar eller minskar med stor del på en enskild dag. Detta observeras ibland i råvaruhandel och kan leda till ett gap mellan öppnings - och slutkurser mellan två på varandra följande dagar. Ett dagligt sortiment skulle inte nödvändigtvis fånga denna information. J. Welles Wilder presenterade sant intervall och genomsnittligt sant intervall 1978 för att bättre beskriva detta beteende. Det sanna intervallet fångar skillnaden mellan stängnings - och öppningspriser mellan två på varandra följande dagar. Sann räckvidd är den största skillnaden mellan igår8217s nära och today8217s låg skillnaden mellan yesterday8217s close och today8217s hög skillnaden mellan today8217s high och today8217s low Det ursprungliga värdet av sant intervall är helt enkelt den dagliga höga minus den dagliga låga. Det genomsnittliga sanna intervallet (ATR) är ett exponentiellt n-dygnsmedelvärde. och kan approximeras med denna ekvation. där n är fönstret för glidande medelvärde (vanligtvis 14 dagar) och TR är det sanna intervallet. ATR initieras vanligen (vid t 0) med ett n-dagars efterföljande medelvärde av TR. Genomsnittligt sant intervall anger inte marknadens riktning, utan bara volatiliteten. Ekvationen ger den senaste prisrörelsen större betydelse och används därför för att mäta marknadssynet. Det brukar användas för att analysera risken att ta en viss position på marknaden. Ett sätt att göra detta är att förutsäga dagliga rörelser baserat på historiska värden för ATR, och gå in eller ut ur marknaden i enlighet därmed. Till exempel kan en daglig stoppförlust ställas in på 1,5 eller 2 gånger det genomsnittliga sanna intervallet. Detta ger en frihet för tillgångspriset att variera naturligt under en handelsdag, men ställer fortfarande en rimlig utgångsläge. Dessutom, om det historiska genomsnittliga sanna intervallet avtalar medan priserna trender uppåt, kan det här indikera att marknadssentimentet kan vända. Kombinerat med Bollinger Bands. genomsnittligt sant intervall är ett effektivt verktyg för volatilitetsbaserade handelsstrategier. Beräkna genomsnittlig True Range i Excel Detta Excel-kalkylblad använder dagliga aktiekurser för BP för de fem åren från 2007 (laddade ned med detta kalkylblad). Kalkylbladet är helt annoterat med ekvationer och kommentarer för att hjälpa din förståelse. Följande kalkylblad har dock mycket mer smarts. Den automatiserar automatiskt det genomsnittliga sanna intervallet, det relativa styrkaindexet och den historiska volatiliteten från data som den automatiskt laddar ner från Yahoo Finance. Du anger följande information en stock ticker en start - och slutdatum beräkningsperioder för ATR, RSI och historisk volatilitet Efter att ha tryckt på en knapp, citerar kalkylbladets nedladdningsaktie från Yahoo Finance (specifikt de dagliga öppna, stänga, höga och låga priserna mellan de två datumen). Den visar sedan det genomsnittliga sanna intervallet och den historiska volatiliteten. It8217s väldigt enkelt att använda I8217d älskar att höra vad du tycker eller om du har några förbättringar som du gillar. 11 tankar om ldquo Genomsnittlig True Range Spreadsheet 038 Handledning rdquo Gilla gratis kalkylblad Master Knowledge Base Senaste inlägg22 SQL för analys och rapportering Oracle har förbättrat SQLs analytiska bearbetningskapacitet genom att introducera en ny familj av analytiska SQL-funktioner. Dessa analytiska funktioner gör det möjligt att beräkna: Rankningar och procentiler Flytta fönsterberäkningar Linjär regressionsstatistik Rankingfunktioner inkluderar kumulativa fördelningar, procenttal och N-plattor. Flyttande fönsterberäkningar gör det möjligt att hitta rörliga och kumulativa aggregeringar, som summan och medelvärdena. Lagleadanalys möjliggör direkta radraferenser så att du kan beräkna period-till-period-förändringar. Firstlast analys gör att du kan hitta det första eller sista värdet i en beställd grupp. Andra förbättringar till SQL inkluderar CASE-uttrycket och partitionerad yttre anslutning. CASE uttryck ger if-then logik användbar i många situationer. Partitionerad yttre anslutning är en förlängning till ANSI yttre förbindelsessyntax som tillåter användare att selektivt förtäta vissa dimensioner samtidigt som andra håller sparsamma. Detta gör det möjligt för rapporteringsverktyg att selektivt förtäta dimensioner, till exempel de som visas i sina tvärbordsrapporter medan andra håller sparsamma. För att förbättra prestanda kan de analoga funktionerna parallelleras: flera processer kan samtidigt utföra alla dessa uttalanden. Dessa funktioner gör beräkningarna enklare och effektivare, vilket förbättrar databasens prestanda, skalbarhet och enkelhet. Analytiska funktioner klassificeras som beskrivs i tabell 22-1. Tabell 22-1 Analytiska funktioner och deras användningsområden För att utföra dessa operationer lägger de analytiska funktionerna flera nya element till SQL-bearbetningen. Dessa element bygger på befintlig SQL för att möjliggöra flexibla och kraftfulla beräkningsuttryck. Med några få undantag har de analytiska funktionerna dessa nya element. Processflödet representeras i figur 22-1. Figur 22-1 Behandlingsorder De väsentliga begreppen som används i analytiska funktioner är: Frågebehandling med hjälp av analytiska funktioner sker i tre steg. Först sammanfogar alla, var. GROUP BY och HAVING klausuler utförs. För det andra görs resultatuppsättningen tillgänglig för de analytiska funktionerna, och alla deras beräkningar sker. För det tredje, om frågan har en ORDER BY-klausul i slutet, bearbetas ORDER BY för att möjliggöra exakt utmatning av order. Processordern visas i Figur 22-1. Resultatuppsättningar partitioner De analytiska funktionerna tillåter användare att dela sökresultatet i grupper med rader som kallas partitioner. Observera att termen partitioner som används med analytiska funktioner inte är relaterade till tabellpartitionsfunktionen. Under det här kapitlet avser termen partitioner endast meningen med analysfunktioner. Partitioner skapas efter de grupper som definieras med GROUP BY-klausuler, så de är tillgängliga för alla aggregerade resultat som summan och medelvärdena. Partitionsavdelningar kan baseras på önskade kolumner eller uttryck. En uppsättning av sökresultat kan delas upp i bara en partition med alla rader, några stora partitioner eller många små partitioner som bara innehåller några rader. För varje rad i en partition kan du definiera ett glidande fönster med data. Det här fönstret bestämmer raden av rader som används för att utföra beräkningarna för den aktuella raden. Fönsterstorlekar kan baseras på antingen ett fysiskt antal rader eller ett logiskt intervall som tid. Fönstret har en startrad och en slutlig rad. Beroende på dess definition kan fönstret flytta i ena eller båda ändarna. Exempelvis skulle ett fönster definierat för en kumulativ sumfunktion ha sin startrad fixad vid den första raden i sin partition och dess slutsträcka skulle glida från startpunkten hela vägen till den sista raden i partitionen. Däremot skulle ett fönster definierat för ett glidande medel ha både start - och ändpunkterna glid så att de upprätthåller ett konstant fysiskt eller logiskt intervall. Ett fönster kan ställas in så stort som alla rader i en partition eller bara ett glidfönster i en rad i en partition. När ett fönster ligger nära en gräns returnerar funktionen resultat för endast tillgängliga rader, istället för att varna dig om att resultaten inte är det du vill ha. När du använder fönsterfunktioner ingår nuvarande rad i beräkningarna, så du bör bara ange (n -1) när du hanterar n-objekt. Varje beräkning som utförs med en analytisk funktion baseras på en aktuell rad i en partition. Den aktuella raden fungerar som referenspunkt som bestämmer start och slut på fönstret. Till exempel kan en centrerad glidande medelberäkning definieras med ett fönster som håller den aktuella raden, de sex föregående raderna och följande sex rader. Detta skulle skapa ett glidfönster med 13 rader, som visas i Figur 22-2. Figur 22-2 Exempel på glidande fönster, rankning, vindning och rapportering Funktioner Detta avsnitt illustrerar de grundläggande analytiska funktionerna för ranking, windowing och rapportering. Provlinjär regressionsberäkning I det här exemplet beräknar vi en regressionslinje med vanliga minsta kvadrater som uttrycker den mängd som säljs av en produkt som en linjär funktion av produktlistans pris. Beräkningarna är grupperade efter försäljningskanal. Värdena SLOPE. INTCPT. RSQR är lutning, avlyssning och bestämningskoefficient för regressionslinjen. Värdet (heltal) COUNT är antalet produkter i varje kanal för vilka både såld kvantitet och listprisdata finns tillgängliga. Statistiska aggregat Oracle tillhandahåller en uppsättning SQL-statistiska funktioner och ett statistikpaket, DBMSSTATFUNCS. I det här avsnittet visas några av de nya funktionerna tillsammans med grundläggande syntax. Beskrivande statistik Du kan beräkna följande beskrivande statistik: Median i ett dataset för en dataset Du kan beräkna följande parametriska statistik: Spearmans rho-koefficient Kendalls tau-b-koefficient Förutom funktionerna har den här versionen ett PLSQL-paket, DBMSSTATFUNCS . Den innehåller den beskrivande statistiska funktionen SAMMANFATTNING tillsammans med funktioner för att stödja distributionen. Sammanfattningsfunktionen sammanfattar en numerisk kolumn i ett bord med en mängd beskrivande statistik. De fem distributionsmonteringsfunktionerna stöder normala, enhetliga, Weibull, Poisson och exponentiella fördelningar. Användardefinierade aggregat Oracle erbjuder en möjlighet att skapa egna funktioner, som kallas användardefinierade aggregatfunktioner. Dessa funktioner skrivs i programmeringsspråk som PLSQL, Java och C, och kan användas som analytiska funktioner eller aggregat i materialiserade vyer. Se Oracle Database Data Cartridge Developers Guide för ytterligare information om syntax och begränsningar. Fördelarna med dessa funktioner är: Mycket komplexa funktioner kan programmeras med ett fullständigt processuellt språk. Högre skalbarhet än andra tekniker när användardefinierade funktioner programmeras för parallell bearbetning. Objektdatatyper kan behandlas. Som ett enkelt exempel på en användardefinierad aggregatfunktion, överväga skevstatistiken. Denna beräkning mäter om en dataset har en sned fördelning om dess medelvärde. Det kommer att berätta om en svans av fördelningen är betydligt större än den andra. Om du skapade ett användardefinierat aggregat som kallades utskew och tillämpade det på kreditgränsdata i föregående exempel kan SQL-satsen och resultaten se ut så här: Innan du bygger användardefinierade aggregatfunktioner bör du överväga om dina behov kan uppfyllas i vanlig SQL. Många komplexa beräkningar är möjliga direkt i SQL, särskilt genom att använda CASE-uttrycket. Att hålla sig till regelbunden SQL kommer att möjliggöra en enklare utveckling, och många frågeoperationer är redan väl parallella i SQL. Även det tidigare exemplet kan skevstatistiken skapas med standard, om än långvarig, SQL. Vridningsoperationer Den d ata som returneras av affärsunderrättelsefrågor är ofta mest användbar om den presenteras i ett kryssformat format. Pivotclause av SELECT-satsen kan du skriva crostabuleringsfrågor som roterar rader i kolumner och aggregerar data under rotationsprocessen. Vridning är en viktig teknik i datalager. I det omvandlar du flera rader av inmatningar till färre och i allmänhet bredare rader i datalageret. Vid svängning appliceras en aggregeringsoperatör för varje objekt i pivotkolumnvärdeslistan. Pivot-kolumnen kan inte innehålla ett godtyckligt uttryck. Om du behöver svänga på ett uttryck, bör du alias uttrycket i en vy före PIVOT-operationen. Den grundläggande syntaxen är som följer: För att illustrera användningen av svängning, skapa följande vy som grund för senare exempel: Exempel: Svängning Följande uttalande illustrerar en typisk svängning i kanalkolonnen: Observera att utgången har skapat fyra nya aliased kolumner , DIREKTFÖRSÄLJNING. INTERNETSALES. CATALOGSALES. och TELESALES. en för varje pivotvärde. Utgången är en summa. Om inget alias tillhandahålls kommer kolumnrubriken att vara värdena för IN-listan. Vridning på flera kolumner Du kan svänga på mer än en kolumn. Följande redogörelse illustrerar en typisk kolumnpivot: Observera att det här exemplet anger en IN-lista med flera kolumner med kolumnrubriker som är utformade för att matcha IN-listmedlemmarna. Vridning: Flera aggregat Du kan svänga med flera aggregat, som visas i följande exempel: Observera att frågan skapar kolumnrubriker genom att sammanfatta vridningsvärdena (eller alias) med aliaset för aggregatfunktionen plus ett understreck. Att skilja PIVOT-genererade Nulls från Nulls i Source Data Du kan skilja mellan nollvärden som genereras av användningen av PIVOT och de som finns i källdata. Följande exempel illustrerar nollor som PIVOT genererar. Följande fråga returnerar rader med 5 kolumner, kolumn prodid. och svängande resulterande kolumner Q1. Q1COUNTTOTAL. Q2. Q2COUNTTOTAL. För varje unikt värde av prodid. Q1COUNTTOTAL returnerar det totala antalet rader vars qtr-värde är Q1. det vill säga, och Q2COUNTTOTAL returnerar det totala antalet rader vars qtr-värde är Q2. Antag att vi har en tabellförsäljning2 av följande struktur: Från resultatet vet vi att för prodid 100 finns det två försäljningsrader för kvartalet Q1. och 1 försäljningsrad för kvartalet Q2 för prodid 200 finns det 1 försäljningsrad för kvartalet Q1. och ingen försäljningsrad för kvartalet Q2. Så i Q2COUNTTOTAL. du kan identifiera att NULLlt1gt kommer från en rad i originalbordet vars mått är nollvärde, medan NULLlt2gt beror på att ingen rad finns närvarande i originalbordet för prodid 200 i kvartalet Q2. Unpivoting Operations En unpivot reverserar inte en PIVOT-operation. Istället roterar det data från kolumner till rader. Om du arbetar med pivot data kan en UNPIVOT-operation inte vända om några aggregeringar som har gjorts av PIVOT eller något annat sätt. För att illustrera unpivoting skapar du först ett svängt bord som innehåller fyra kolumner för årets kvartaler: Tabellinnehållet liknar följande: Följande UNPIVOT-funktion roterar kvartkolumnerna i rader. För varje produkt kommer det att finnas fyra rader, en för varje kvartal. Notera användningen av INCLUDE NULLS i det här exemplet. Du kan också använda EXCLUDE NULLS. vilket är standardinställningen. Dessutom kan du också avveckla med hjälp av två kolumner, enligt följande: Wildcard och Subquery svänger med XML-operationer Om du vill använda ett wildcard-argument eller underfrågning i svängande kolumner kan du göra det med PIVOT XML-syntax. Med PIVOT XML är utmatningen av operationen korrekt formaterad XML. Följande exempel illustrerar med användning av wildcard-sökordet, NÅGON. Den matar ut XML som innehåller alla kanalvärden i salesview: Observera att sökordet NÅGON är endast tillgängligt i PIVOT-operationer som en del av en XML-operation. Denna utgång innehåller data för fall där kanalen finns i datasatsen. Observera också att aggregeringsfunktioner måste ange en GROUP BY-klausul för att returnera flera värden, men pivotclause innehåller inte en explicit GROUP BY-klausul. I stället utför pivotclause en implicit GROUP BY. Följande exempel illustrerar användning av en underfråga. Den matar ut XML som innehåller alla kanalvärden och försäljningsdata som motsvarar varje kanal: Utgången förtätar dataen för att inkludera alla möjliga kanaler för varje produkt. Datatäthet för rapporteringsdata lagras normalt i glesform. Det vill säga, om inget värde existerar för en viss kombination av dimensionsvärden finns ingen rad i faktabellen. Men du kanske vill se data i tät form, med rader för alla kombinationer av dimensionerade värden som visas även när det inte finns några faktauppgifter för dem. Om en produkt till exempel inte säljer under en viss tidsperiod, kanske du fortfarande vill se produkten för den tidsperioden med noll försäljningsvärde bredvid det. Dessutom kan tidsserieberäkningar utföras lättast när data är tät längs tidsdimensionen. Detta beror på att täta data fyller ett jämnt antal rader för varje period vilket gör det enkelt att använda de analoga fönsterfunktionerna med fysiska förskjutningar. Dataförtätning är processen att konvertera glesa data till tät form. För att övervinna problemet med sparsitet kan du använda ett partitionerat yttre förbindning för att fylla luckorna i en tidsserie eller någon annan dimension. Ett sådant förbindelse sträcker den konventionella yttre förbindelsessyntaxen genom att anbringa den yttre förbindningen till varje logisk partition som definieras i en fråga. Oracle delar upp raderna i din fråga logiskt, baserat på det uttryck du anger i avsnittet PARTITION BY. Resultatet av en partitionerad yttre anslutning är en UNION av de yttre förbindningarna hos var och en av partitionerna i det logiskt partitionerade bordet med bordet på andra sidan av föreningen. Observera att du kan använda denna typ av anslutning för att fylla luckorna i någon dimension, inte bara tidsdimensionen. De flesta av exemplen här fokuserar på tidsdimensionen, eftersom det är den dimension som oftast används som underlag för jämförelser. Partition Gå med i syntax Syntaxen för partitionerad yttre anslutning utökar ANSI SQL JOIN-klausulen med frasen PARTITION BY följt av en expressionslista. Uttrycken i listan anger den grupp som den yttre anslutningen är applicerad på. Följande är de två formerna av syntax som normalt används för partitionerad yttre anslutning: Observera att FULL OUTER JOIN inte stöds med en partitionerad yttre anslutning. Prov av glesdata En typisk situation med en gles dimension visas i följande exempel, som beräknar den veckovisa försäljningen och den aktuella försäljningen för produkten Bounce i veckor 20-30 år 2000 och 2001: I det här exemplet skulle förvänta sig 22 rader data (11 veckor vardera från 2 år) om uppgifterna var täta. Vi får emellertid bara 18 rader eftersom veckorna 25 och 26 saknas år 2000 och veckorna 26 och 28 år 2001. Fylla luckor i data Vi kan ta de glesa uppgifterna från föregående fråga och göra en partitionerad yttre anslutning med en tät uppsättning av tidsdata. I följande fråga alias vi vår ursprungliga fråga som v och vi väljer data från tidtabellen, som vi alias som t. Här hämtar vi 22 rader eftersom det inte finns några luckor i serien. De fyra tillsatta raderna har var och en 0 som deras försäljningsvärde satt till 0 med hjälp av NVL-funktionen. Observera att i denna fråga var ett WHERE-tillstånd placerat i veckor mellan 20 och 30 i inlinevisningen för tidsdimensionen. Detta infördes för att hålla resultatet litet. Fyllnadsluckor i två dimensioner N-dimensionella data visas vanligen som en tät 2-dimensionell korsflik av (n - 2) siddimensioner. Detta kräver att alla dimensionsvärden för de två dimensionerna som visas i fliken Cross fylls i. Nedan följer ett annat exempel där den partitionerade yttre anslutningsfunktionen kan användas för att fylla luckorna i två dimensioner: I den här frågan är WITH-subquery factoring-klausulen v1 sammanfattar försäljningsdata på produkt-, land - och årsnivå. Detta resultat är gles men användarna kanske vill se hela landet, årskombinationer för varje produkt. För att uppnå detta tar vi varje partition av v1 baserat på produktvärden och yttre anslutning till landdimensionen först. Detta ger oss alla värden för land för varje produkt. Vi tar då det resultatet och partitionerar det på produkt - och landsvärden och sedan lägger det yttre ut på tidsdimensionen. Detta ger oss alla tidsvärden för varje produkt och landskombination. Fyllning av luckor i ett inventaribord En inventeringstabell spårar typiskt antal enheter tillgängliga för olika produkter. Denna tabell är gles: den lagrar endast en rad för en produkt när det finns en händelse. För ett försäljningsbord är evenemanget en försäljning, och för inventeringstabellen är händelsen en förändring av den tillgängliga kvantiteten för en produkt. Tänk på följande inventeringstabell: Inventarietabellen har nu följande rader: För rapporteringsändamål kanske användare vill se denna inventeringsdata på olika sätt. De kan till exempel se alla tidvärden för varje produkt. Detta kan åstadkommas med partitionerad yttre anslutning. Dessutom kan användarna för de nyligen införda raderna av saknade tidsperioder se värdena för antal enheter av kolumnen som ska överföras från den senaste befintliga tidsperioden. Det senare kan åstadkommas med hjälp av det analytiska fönstervärdet LASTVALUE-värdet. Här är frågan och den önskade utmatningen: Den inre frågan beräknar en partitionerad yttre anslutning i tid inom varje produkt. Den inre frågan förtätar data på tidsdimensionen (vilket betyder att tidsdimensionen nu kommer att ha en rad för varje dag i veckan). Måttkolumnen kommer emellertid att ha nollor för de nyligen tillagda raderna (se utmatningen i kolumnmängden i följande resultat. Den yttre frågan använder den analytiska funktionen LASTVALUE. Användning av denna funktion partitionerar data per produkt och beställer data på tidsdimensionskolumn (tidtid). För varje rad hittar funktionen det sista icke-nullvärdet i fönstret på grund av alternativet IGNORE NULLS. som du kan använda med både LASTVALUE och FIRSTVALUE. Vi ser önskad utmatning i kolumnen repeatquantity i Följande utdata: Datorvärden för att fylla luckor Exempel i föregående avsnitt illustrerar hur man använder partitionerad yttre anslutning för att fylla luckor i en eller flera dimensioner. Resultatresultaten som produceras av partitionerad yttre anslutning har dock nullvärden för kolumner som inte ingår i Listan PARTITION BY. Tyckligtvis är dessa mätkolumner. Användare kan använda sig av analytiska SQL-funktioner för att ersätta dessa nullvärden med ett icke-nullvärde. Till exempel följande q uery beräknar månatliga totals för produkter 64 MB minneskort och DVD-R-skivor (produkt-ID 122 och 136) år 2000. Det använder partitionerad yttre anslutning för att förtäta data för alla månader. För de månader som saknas använder den sedan den analytiska SQL-funktionen AVG för att beräkna försäljningen och enheterna som genomsnittet för de månader då produkten såldes. Om du arbetar i SQLPlus, sveper följande två kommandon kolumnrubrikerna för ökad läsbarhet av resultat: Tidsserieberäkningar på Densified Data Densificatio n är inte bara för rapporteringsändamål. Det möjliggör också vissa typer av beräkningar, speciellt tidsserieberäkningar. Tidsserieberäkningar är enklare när data är täta längs tidsdimensionen. Täta data har ett jämnt antal rader för varje tidsperiod vilket i sin tur gör det enkelt att använda analytiska fönsterfunktioner med fysiska förskjutningar. För att illustrera, låt oss först ta exemplet på fyllning av luckor i data. och lägger till en analytisk funktion för den frågan. I följande förbättrade version beräknar vi den veckovisa årsomsättningen tillsammans med den veckovisa försäljningen. NULL värderar att den partitionerade yttre anslutningen sätter in när tidsserierna blir täta hanteras på vanligt sätt: SUM-funktionen behandlar dem som 0s. Period jämförelse för en tidsnivå: Exempel Hur använder vi den här funktionen för att jämföra värden över tidsperioder Specifikt hur beräknar vi en jämförelsestid jämfört med året innan på veckans nivå Följande fråga returneras i samma rad , för varje produkt, den årliga försäljningen för varje vecka 2001 med år 2000. Notera att i det här exemplet börjar vi med en med-klausul. Detta förbättrar frågans läsbarhet och låter oss fokusera på den partitionerade yttre anslutningen. Om du arbetar i SQLPlus, sveper följande kommando kolumnrubrikerna för bättre läsbarhet av resultat: I FROM-klausulen i inline-vyens dalsales. vi använder en partitionerad yttre anslutning med aggregatvyn v och tidvisning t för att fylla luckor i försäljningsdata längs tidsdimensionen. Utsignalen från den uppdelade yttre förbindningen bearbetas sedan av den analytiska funktionen SUM. Över för att beräkna den veckovisa årliga försäljningen (weeklyytdsales-kolumnen). Således beräknar densesalesen de aktuella försäljningsuppgifterna för varje vecka, inklusive de som saknas i den sammanlagda vyn s. Inline-visningsårets återförsäljning beräknar sedan förra året den senaste årliga försäljningen med hjälp av LAG-funktionen. LAG-funktionen märkt weeklyytdsalsprioryear specificerar en PARTITION BY-klausul som parar rader för samma vecka 2000 och 2001 i en enda partition. Vi skickar sedan en förskjutning av 1 till LAG-funktionen för att få försäljningen för det senaste året till det aktuella året. Det yttersta frågeformuläret väljer data från årets återförsäljning med villkoret år 2001. Därför returneras frågan för varje produkt varje vecka årlig försäljning under de angivna veckorna år 2001 och 2000. Period för jämförelse av flera tidsnivåer: Exempel Medan föregående exempel visar oss ett sätt att skapa jämförelser för en enda tidsnivå, skulle det bli ännu mer användbart för att hantera flera tidsnivåer i en enda fråga. Till exempel kunde vi jämföra försäljningen jämfört med den föregående perioden på års-, kvart-, månads - och dagnivåer. Hur kan vi skapa en fråga som utför en jämförelse jämfört med år-till-år-för-försäljning för alla nivåer i vår hierarki Vi tar flera steg för att utföra denna uppgift. Målet är en enda fråga med jämförelser på dagen, veckan, månaden, kvartalet och årsnivån. Stegen är som följer: Vi kommer att skapa en vy kallad cubeprodtime. som håller en hierarkisk kub av försäljning aggregerad över tider och produkter. Då kommer vi att skapa en bild av tidsdimensionen som ska användas som kanten av kuben. Tidskanten, som håller en fullständig uppsättning datum, kommer att delas upp i det yttre som är kopplat till de glesa data i synhållet cubeprodtime. Slutligen, för maximal prestanda, kommer vi att skapa en materialiserad vy, mvprodtime. byggt med samma definition som cubeprodtime. För mer information om hierarkiska kuber, se Kapitel 21, SQL för aggregering i Data Warehouses. Den materialiserade vyn definieras i steg 1 i följande avsnitt. Steg 1 Skapa den hierarkiska kubvyn Den materialiserade vyn som visas i följande kan redan finnas i ditt system, om inte, skapa det nu. Om du måste generera den, notera att vi begränsar frågan till bara två produkter för att fortsätta bearbeta tiden kort: Eftersom denna vy är begränsad till två produkter returneras den strax över 2200 rader. Observera att kolumnen HierarchicalTime innehåller strängpresentationer av tid från alla nivåer i tidshierarkin. CASE-uttrycket som används för kolumnen HierarchicalTime lägger till en markör (0, 1.) till varje datumsträng för att ange värdetid för värdet. A 0 representerar årsnivån, 1 är kvart, 2 är månader och 3 är dag. Observera att GROUP BY-klausulen är en sammanfogad ROLLUP som specificerar rollup-hierarkin för tid och produktdimensioner. GROUP BY-klausulen är vad som bestämmer det hierarkiska kubinnehållet. Steg 2 Skapa visningstid, vilket är en komplett uppsättning datumvärden. Edgetime är källan för att fylla tidsluckor i den hierarkiska kuben med en partitionerad yttre anslutning. Kolumnen HierarchicalTime i edgetime kommer att användas i en partitionerad anslutning med kolumnen HierarchicalTime i view cubeprodtime. Följande redogörelse definierar edetime: Steg 3 Skapa materialiserad vy mvprodtime för att stödja snabbare prestanda Den materialiserade visningsdefinitionen är en duplikat av den tidigare visade visningstiden. Eftersom det är en dubblett fråga kommer hänvisningar till cubeprodtime att skrivas om för att använda mvprodtime materialiserad vy. Följande materialiseras kan redan finnas i ditt system, om inte, skapa det nu. Om du måste generera den, notera att vi begränsar frågan till bara två produkter för att hålla behandlingen kortare. Steg 4 Skapa jämförelsefrågan Vi har nu satt scenen för vår jämförelsefråga. Vi kan få jämförelseberäkning från period till år på alla tidsnivåer. Det kräver att analytiska funktioner appliceras i en hierarkisk kub med täta data längs tidsdimensionen. Några av de beräkningar vi kan uppnå för varje tidsnivå är: Summan av försäljning för tidigare period på alla nivåer. Varians i försäljning under tidigare period. Summan av försäljningen under samma period för ett år sedan på alla nivåer. Varians i försäljning under samma period föregående år. Följande exempel utför alla fyra av dessa beräkningar. Den använder en partitionerad yttre anslutning av åsikterna cubeprodtime och edgetime för att skapa en inlinevisning av täta data som heter densecubeprodtime. Frågan använder sedan LAG-funktionen på samma sätt som det föregående exemplet på en nivå. Den yttre WHERE-klausulen anger tid på tre nivåer: augusti 2001, hela månaden och hela tredje kvartalet 2001. Observera att de två sista raderna av resultaten innehåller månadsnivå och kvartsnivåaggregationer. Notera att göra resultaten lättare att läsa om du använder SQLPlus, kolumnrubrikerna bör anpassas med följande kommandon. Kommandona kommer att vika kolumnrubrikerna för att minska linjelängden: Här är frågan jämförande aktuell försäljning till försäljning före och förra sedan: Den första LAG-funktionen (försäljningsperioden) delar upp data på gidp. katt. Subcat. prod. Gidt och beställer raderna på hela tiden dimensionskolumner. Det får försäljningsvärdet för den tidigare perioden genom att förskjuta 1. Den andra LAG-funktionen (salessameperiodprioryear) delar upp data på ytterligare kolumner qtrnum. monnum. och dagnumret och beställer det på år så att det med en kompensation av 1 kan beräkna årets försäljning för samma period. Den yttersta SELECT-klausulen beräknar variationerna. Skapa en anpassad medlem i en dimension: Exempel I många analytiska SQL-uppgifter är det användbart att definiera anpassade medlemmar i en dimension. Du kan till exempel definiera en specialiserad tidsperiod för analyser. Du kan använda en partitionerad yttre anslutning för att tillfälligt lägga till en medlem i en dimension. Observera att den nya SQL MODEL-klausulen är lämplig för att skapa mer komplexa scenarier som involverar nya medlemmar i dimensioner. Se kapitel 23, SQL för modellering för mer information om detta ämne. Som ett exempel på en uppgift, vad händer om vi vill definiera en ny medlem för vår tidsdimension Vi vill skapa en 13: e medlem i månadsnivån i vår tidsdimension. Den här 13: e månaden definieras som summan av försäljningen för varje produkt under den första månaden för varje kvartal 2001. Lösningen har två steg. Observera att vi kommer att bygga den här lösningen med hjälp av de synpunkter och tabeller som skapats i föregående exempel. Två steg krävs. Först skapa en vy med den nya medlemmen som läggs till i lämplig dimension. Vyn använder en UNION ALL-funktion för att lägga till den nya medlemmen. För att fråga efter användaren använder du ett CASE-uttryck och en partitionerad yttre anslutning. Vår nya medlem för tidsdimensionen skapas med följande vy: I detta uttalande definieras visningstiden genom att utföra en UNION ALL av visningstiden för visningstiden (definierad i föregående exempel) och den användardefinierade 13: e månaden. Gidt-värdet på 8 valdes för att skilja den anpassade medlemmen från standardmedlemmarna. The UNION ALL specifies the attributes for a 13th month member by doing a SELECT from the DUAL table. Note that the grouping id, column gidt. is set to 8, and the quarter number is set to 5. Then, the second step is to use an inline view of the query to perform a partitioned outer join of cubeprodtime with timec. This step creates sales data for the 13th month at each level of product aggregation. In the main query, the analytic function SUM is used with a CASE expression to compute the 13th month, which is defined as the summation of the first months sales of each quarter. The SUM function uses a CASE to limit the data to months 1, 4, 7, and 10 within each year. Due to the tiny data set, with just 2 products, the rollup values of the results are necessarily repetitions of lower level aggregations. For more realistic set of rollup values, you can include more products from the Game Console and Y Box Games subcategories in the underlying materialized view. Miscellaneous Analysis and Reporting Capabilities This section illustrates the following additional analytic capabilities: WIDTHBUCKET Function For a given expression, the WIDTHBUCKET function returns the bucket number that the result of this expression will be assigned after it is evaluated. You can generate equiwidth histograms with this function. Equiwidth histograms divide data sets into buckets whose interval size (highest value to lowest value) is equal. The number of rows held by each bucket will vary. A related function, NTILE. creates equiheight buckets. Equiwidth histograms can be generated only for numeric, date or datetime types. So the first three parameters should be all numeric expressions or all date expressions. Other types of expressions are not allowed. If the first parameter is NULL. the result is NULL. If the second or the third parameter is NULL. an error message is returned, as a NULL value cannot denote any end point (or any point) for a range in a date or numeric value dimension. The last parameter (number of buckets) should be a numeric expression that evaluates to a positive integer value 0, NULL. or a negative value will result in an error. Buckets are numbered from 0 to ( n 1). Bucket 0 holds the count of values less than the minimum. Bucket( n 1) holds the count of values greater than or equal to the maximum specified value. WIDTHBUCKET Syntax The WIDTHBUCKET takes four expressions as parameters. The first parameter is the expression that the equiwidth histogram is for. The second and third parameters are expressions that denote the end points of the acceptable range for the first parameter. The fourth parameter denotes the number of buckets. Consider the following data from table customers. that shows the credit limits of 17 customers. This data is gathered in the query shown in Example 22-24 . In the table customers. the column custcreditlimit contains values between 1500 and 15000, and we can assign the values to four equiwidth buckets, numbered from 1 to 4, by using WIDTHBUCKET (custcreditlimit, 0, 20000, 4). Ideally each bucket is a closed-open interval of the real number line, for example, bucket number 2 is assigned to scores between 5000.0000 and 9999.9999. sometimes denoted 5000, 10000) to indicate that 5,000 is included in the interval and 10,000 is excluded. To accommodate values outside the range 0, 20,000), values less than 0 are assigned to a designated underflow bucket which is numbered 0, and values greater than or equal to 20,000 are assigned to a designated overflow bucket which is numbered 5 (num buckets 1 in general). See Figure 22-3 for a graphical illustration of how the buckets are assigned. Figure 22-3 Bucket Assignments You can specify the bounds in the reverse order, for example, WIDTHBUCKET ( custcreditlimit. 20000. 0. 4 ). When the bounds are reversed, the buckets will be open-closed intervals. In this example, bucket number 1 is ( 15000,20000 , bucket number 2 is ( 10000,15000 , and bucket number 4, is ( 0 ,5000 . The overflow bucket will be numbered 0 ( 20000. infinity ), and the underflow bucket will be numbered 5 (- infinity. 0 . It is an error if the bucket count parameter is 0 or negative. Example 22-24 WIDTHBUCKET The followin g query shows the bucket numbers for the credit limits in the customers table for both cases where the boundaries are specified in regular or reverse order. We use a range of 0 to 20,000. Linear Algebra Linear algebra is a branch of mathematics with a wide range of practical applications. Many areas have tasks that can be expressed using linear algebra, and here are some examples from several fields: statistics (multiple linear regression and principle components analysis), data mining (clustering and classification), bioinformatics (analysis of microarray data), operations research (supply chain and other optimization problems), econometrics (a nalysis of consumer demand data), and finance (asset allocation problems). Various libraries for linear algebra are freely available for anyone to use. Oracles UTLNLA package exposes matrix PLSQL data types and wrapper PLSQL subprograms for two of the most popular and robust of these libraries, BLAS and LAPACK. Linear algebra depends on matrix manipulation. Performing matrix manipulation in PLSQL in the past required inventing a matrix representation based on PLSQLs native data types and then writing matrix manipulation routines from scratch. This required substantial programming effort and the performance of the resulting implementation was limited. If developers chose to send data to external packages for processing rather than create their own routines, data transfer back and forth could be time consuming. Using the UTLNLA package lets data stay within Oracle, removes the programming effort, and delivers a fast implementation. Example 22-25 Linear Algebra Here is an example of how Oracles linear algebra support could be used for business analysis. It invokes a multiple linear regression application built using the UTLNLA package. The multiple regression application is implemented in an object called OLSRegression. Note that sample files for the OLS Regression object can be found in ORACLEHOMEplsqldemo . Consider the scenario of a retailer analyzing the effectiveness of its marketing program. Each of its stores allocates its marketing budget over the following possible programs: media advertisements ( media ), promotions ( promo ), discount coupons ( disct ), and direct mailers ( dmail ). The regression analysis builds a linear relationship between the amount of sales that an average store has in a given year ( sales ) and the spending on the four components of the marketing program. Suppose that the marketing data is stored in the following table: Then you can build the following sales-marketing linear model using coefficients: This model can be implemented as the following view, which refers to the OLS regression object: Using this view, a marketing program manager can perform an analysis such as Is this sales-marketing model reasonable for year 2004 data That is, is the multiple-correlation greater than some acceptable value, say, 0.9 The SQL for such a query might be as follows: You could also solve questions such as What is the expected base-line sales revenue of a store without any marketing programs in 2003 or Which component of the marketing program was the most effective in 2004 That is, a dollar increase in which program produced the greatest expected increase in sales See Oracle Database PLSQL Packages and Types Reference for further information regarding the use of the UTLNLA package and linear algebra. CASE Expressions Oracle now supports simple and searched CASE statements. CASE statements are similar in purpose to the DECODE statement, but they offer more flexibility and logical power. They are also easier to read than traditional DECODE statements, and offer better performance as well. They are commonly used when breaking categories into buckets like age (for example, 20-29, 30-39, and so on). The syntax for simple CASE statements is: Simple CASE expressions test if the expr value equals the comparisonexpr . The syntax for searched CASE statements is: You can use any kind of condition in a searched CASE expression, not just an equality test. You can specify only 65,535 arguments and each WHEN. THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the returnexpr itself is a CASE expression. Example 22-26 CASE Suppose you wanted to find the average salary of all employees in the company. If an employees salary is less than 2000, you want the query to use 2000 instead. Without a CASE statement, you might choose to write this query as follows: Note that this runs against the hr sample schema. In this, foo is a function that returns its input if the input is greater than 2000, and returns 2000 otherwise. The query has performance implications because it needs to invoke a function for each row. Writing custom functions can also add to the development load. Using CASE expressions in the database without PLSQL, this query can be rewritten as: Using a CASE expression lets you avoid developing custom functions and can also perform faster. Example 22-27 CASE for Aggregating Independent Subsets Using CASE inside aggregate functions is a convenient way to perform aggregates on multiple subsets of data when a plain GROUP BY will not suffice. For instance, the preceding example could have included multiple AVG columns in its SELECT list, each with its own CASE expression. We might have had a query find the average salary for all employees in the salary ranges 0-2000 and 2000-5000. It would look like: Although this query places the aggregates of independent subsets data into separate columns, by adding a CASE expression to the GROUP BY clause we can display the aggregates as the rows of a single column. The next section shows the flexibility of this approach with two approaches to creating histograms with CASE . Creating Histograms You can use the CASE statement when you want to obtain histograms with user-defined buckets (both in number of buckets and width of each bucket). The following are two examples of histograms created with CASE statements. In the first example, the histogram totals are shown in multiple columns and a single row is returned. In the second example, the histogram is shown with a label column and a single column for totals, and multiple rows are returned. Example 22-28 Histogram Example 1 Example 22-29 Histogram Example 2 Frequent Itemsets Instead of counting how often a given event occurs (for example, how often someone has purchased milk at the grocery), you may find it useful to count how often multiple events occur together (for example, how often someone has purchased both milk and cereal together at the grocery store). You can count these multiple events using what is called a frequent itemset, which is, as the name implies, a set of items. Some examples of itemsets could be all of the products that a given customer purchased in a single trip to the grocery store (commonly called a market basket), the web pages that a user accessed in a single session, or the financial services that a given customer utilizes. The practical motivation for using a frequent itemset is to find those itemsets that occur most often. If you analyze a grocery stores point-of-sale data, you might, for example, discover that milk and bananas are the most commonly bought pair of items. Frequent itemsets have thus been used in business intelligence environments for many years, with the most common one being for market basket analysis in the retail industry. Frequent itemset calculations are integrated with the database, operating on top of relational tables and accessed through SQL. This integration provides the following key benefits: Applications that previously relied on frequent itemset operations now benefit from significantly improved performance as well as simpler implementation. SQL-based applications that did not previously use frequent itemsets can now be easily extended to take advantage of this functionality. Frequent itemsets analysis is performed with the PLSQL package DBMSFREQUENTITEMSETS. See Oracle Database PLSQL Packages and Types Reference for more information. In addition, there is an example of frequent itemset usage in Frequent itemsets . Scripting on this page enhances content navigation, but does not change the content in any way.