Eksponentiell Bevegelse Gjennomsnittet Sql

6 Formatering av SQLPlus-rapporter Du må legge ved bindestrek i anførselstegn ellers, SQLPlus tolker punktumet som en bindestrek som indikerer at du vil fortsette kommandoen på en annen linje. Formatering av NUMBER-kolonner Når du viser NUMBER kolonner, kan du enten godta SQLPlus standard visningsbredde eller du kan endre den ved hjelp av kommandoen COLUMN. Senere seksjoner beskriver standardvisningen og hvordan du kan endre den med kommandoen COLUMN. Formatmodellen vil være i kraft til du skriver inn en ny, tilbakestill kolonnene format med eller avslutt SQLPlus. Standardvisning En bredde på NUMBER kolonner er bredden på overskriften eller bredden på FORMAT pluss ett mellomrom for tegnet. det som er større. Hvis du ikke uttrykkelig bruker FORMAT, vil kolonnens bredde alltid være minst verdien av SET NUMBY. SQLPlus viser vanligvis tall med så mange siffer som kreves for nøyaktighet, opp til en standard skjermbredde bestemt av verdien av NUMWIDTH-variabelen i SET-kommandoen (normalt 10). Hvis et tall er større enn verdien av SET NUMRIDTH, runder SQLPlus nummeret opp eller ned til maksimum antall tegn tillatt hvis det er mulig, eller viser hashes hvis nummeret er for stort. Du kan velge et annet format for en hvilken som helst NUMBER-kolonne ved hjelp av en formatmodell i en COLUMN-kommando. En formatmodell er en representasjon av måten du vil at tallene i kolonnen skal vises ved hjelp av 9s for å representere sifre. Endre standardskjermbildet KOLOMN-kommandoen identifiserer kolonnen du vil formatere, og hvilken modell du vil bruke, som vist: Bruk formatmodeller til å legge til kommaer, dollarskilt, vinkelbeslag (rundt negative verdier) og ledende nuller til tall i en gitt kolonne. Du kan også omdanne verdiene til et gitt antall desimaler, vise minus tegn til høyre for negative verdier (i stedet for til venstre), og vise verdier i eksponentiell notasjon. For å bruke mer enn en formatmodell for en enkelt kolonne, kombiner de ønskede modellene i en COLUMN-kommando (se eksempel 6-4). Se COLUMN for en komplett liste over formatmodeller og ytterligere detaljer. Eksempel 6-4 Formatering av en NUMBER-kolonne For å vise SALARY med et dollarskilt. et komma. og tallet null i stedet for et tomt for noen nullverdier, skriv inn følgende kommando: Gjenta nå gjeldende spørring: Standardvisning Standardbredden til datatype-kolonner er bredden på kolonnen i databasen. Kolonnebredden for en LONG, CLOB, NCLOB eller XMLType-standard er satt til verdien av SET LONGCHUNKSIZE eller SET LONG, avhengig av hvilken som er den minste. Standardbredden og formatet for uformaterte DATE-kolonnene i SQLPlus bestemmes av databasen NLSDATEFORMAT-parameter. Ellers er standardformatbredden A9. Se FORMAT-klausulen i kommandoen COLUMN for mer informasjon om formatering av DATE-kolonner. Venstre begrunnelse er standard for datatyper. Endre standardvisning Du kan endre den viste bredden til en datatype eller DATE, ved å bruke kommandoen COLUMN med en formatmodell bestående av bokstaven A (for alfanumerisk) etterfulgt av et tall som representerer bredden på kolonnen i tegn. Innenfor kommandoen COLUMN identifiserer du kolonnen du vil formatere og modellen du vil bruke: Hvis du angir en bredde som er kortere enn kolonneoverskriften. SQLPlus avkorter overskriften. Se kommandoen COLUMN for flere detaljer. Eksempel 6-5 Formatering av tegnkolonne Hvis du vil angi bredden på kolonnen LASTNAME til fire tegn og gjenopprette gjeldende spørring, skriv inn Hvis WRAP-variabelen i SET-kommandoen er satt til ON (standardverdien), vikler ansattes navne til Neste linje etter fjerde tegn, som vist i Eksempel 6-5, Formatering av tegnkolonne. Hvis WRAP er satt til OFF, blir navnene avkortet (avskåret) etter fjerde tegn. Systemvariabelen WRAP styrer alle kolonnene du kan overstyre innstillingen for WRAP for en gitt kolonne gjennom WRAPPED. WORDWRAPPED. og TRUNCATED klausuler i kommandoen COLUMN. Se kommandoen COLUMN for mer informasjon om disse klausulene. Du vil bruke WORDWRAPPED-klausulen i COLUMN senere i dette kapittelet. NCLOB eller multibyte CLOB-kolonner kan ikke formateres med WORDWRAPPED-alternativet. Hvis du formaterer en NCLOB eller multibyte CLOB-kolonne med COLUMN WORDWRAPPED, opptrer kolonnedataene som om COLUMN WRAPPED ble brukt i stedet. Kolonneoverskriften er avkortet uavhengig av innstillingen for WRAP eller noen COLUMN kommandoklausuler. Nå returnerer kolonnen til det forrige formatet: Eksempel 6-6 Formatering av en XMLType-kolonne Før du illustrerer hvordan du formaterer en XMLType-kolonne, må du opprette en tabell med en XMLType-kolonndefinisjon, og sette inn noen data i tabellen. Du kan opprette en XMLType-kolonne som enhver annen brukerdefinert kolonne. For å opprette et bord som inneholder en XMLType-kolonne, skriv inn For å sette inn en ny post som inneholder warehouseid og warehousespec-verdier i det nye varehusbordet, skriv inn For å angi XMLType-kolonnebredden til 20 tegn og velg deretter XMLType-kolonnen, skriv inn undertrykking og gjenoppretting av kolonnevisningsattributter Du kan undertrykke og gjenopprette skjermattributtene du har gitt en bestemt kolonne. For å undertrykke en kolonnevisningsattributt, skriv inn en COLUMN-kommando i følgende form: OFF forteller SQLPlus for å bruke standardvisningsattributtene for kolonnen, men fjerner ikke attributene du har definert gjennom COLUMN-kommandoen. For å gjenopprette attributter du definerte gjennom COLUMN. bruk ON-klausulen: Skrive ut en linje med tegn etter innpakket kolonneverdier Som du har sett, standardiserer SQLPlus kolonneverdier til flere linjer når verdien ikke passer til kolonnebredden. Hvis du vil sette inn en plateseparator (en linje med tegn eller en tom linje) etter hver innpakket produksjonslinje (eller etter hver rad), bruk RECSEP - og RECSEPCHAR-variablene i SET-kommandoen. RECSEP bestemmer når tegnlinjen er skrevet ut, setter du RECSEP til hver for å skrive ut etter hver linje, til WRAPPED for å skrive ut etter innpakket linjer, og til AV for å undertrykke utskrift. Standardinnstillingen for RECSEP er WRAPPED. RECSEPCHAR setter tegnet skrevet ut i hver linje. Du kan sette RECSEPCHAR til et hvilket som helst tegn. Du kan ønske å pakke hele ord til flere linjer når en kolonneverdi brytes til flere linjer. For å gjøre dette, bruk WORDWRAPPED-klausulen i COLUMN-kommandoen som vist: Eksempel 6-9 Skrive ut en linje med tegn etter innpakket kolonneverdier For å skrive ut en linje med bindestreker etter hver innpakket kolonneverdi, skriv inn kommandoene: Skriv inn følgende spørring : Begrens nå bredden på kolonnen JOBTITLE og fortell SQLPlus å pakke hele ord til flere linjer når det er nødvendig: Hvis du setter RECSEP til hver, skriver SQLPlus en linje med tegn etter hver rad (etter hver avdeling, for eksempelet ovenfor). Før du fortsetter, still inn RECSEP til OFF for å undertrykke utskrift av plateseparatorer: Avklare rapporten med mellomrom og sammendragslinjer Når du bruker en ORDER BY-setning i SQL SELECT-kommandoen, er rader med samme verdi i den bestilte kolonnen (eller uttrykket) vises sammen i utgangen din. Du kan gjøre denne utdataen mer nyttig for brukeren ved å bruke SQLPlus BREAK og COMPUTE-kommandoerene for å lage delsett av poster og legge til mellomrom eller sammendragslinjer etter hver delmengde. Kolonnen du angir i en BREAK-kommando kalles en pause kolonne. Ved å inkludere break-kolonnen i ORDER BY-klausulen, oppretter du meningsfulle delsett av poster i utdataene dine. Du kan deretter legge til formatering til delsettene i samme BREAK-kommando, og legge til en sammendragslinje (som inneholder totaler, gjennomsnitt og så videre) ved å angi pausekolonnen i en COMPUTE-kommando. For å gjøre denne rapporten mer nyttig, vil du bruke BREAK til å etablere DEPARTMENTID som pause kolonnen. Gjennom BREAK kan du undertrykke dupliserte verdier i DEPARTMENTID og plassere tomme linjer eller starte en ny side mellom avdelinger. Du kan bruke BREAK sammen med COMPUTE til å beregne og skrive ut sammendragslinjer som inneholder totallønnen for hver avdeling og for alle avdelinger. Du kan også skrive ut sammendragslinjer som inneholder gjennomsnittlig, maksimum, minimum, standardavvik, varians eller radtelling. Undertrykkelse av dupliserte verdier i brytekolonner BREAK-kommandoen undertrykker dupliserte verdier som standard i kolonnen eller uttrykket du nevner. For å undertrykke de dupliserte verdiene i en kolonne som er angitt i en ORDER BY-setning, bruk BREAK-kommandoen i sin enkleste form: Når du angir en kolonne eller et uttrykk i en BREAK-kommando, bruker du en ORDER BY-setning som angir samme kolonne eller uttrykk. Hvis du ikke gjør dette, oppstår pauser hver gang kolonneverdien endres. Eksempel 6-10 Undertrykkelse av duplikatverdier i en pausekolonne For å undertrykke visningen av dupliserte avdelingsnumre i de viste søkeresultatene, skriv inn følgende kommandoer: For følgende spørring (hvilken gjeldende forespørsel er lagret i bufferen): Du kan ønske å sett inn tomme linjer eller en tom side etter hver rad. For å hoppe over n linjer etter hver rad, bruk BREAK i følgende skjema: Hvis du vil hoppe over en side etter hver rad, bruker SKIP PAGE ikke at et fysisk sideskiftkarakter skal genereres, med mindre du har angitt NEWPAGE 0. Bruke flere mellomromsteknikker Anta Du har mer enn en kolonne i ORDER BY-klausulen og ønsker å sette inn plass når hver kolonne verdier endres. Hver BREAK-kommando du oppgir erstatter den forrige. Hvis du vil bruke forskjellige avstandsteknikker i en rapport eller sette inn plass etter at verdien endres i mer enn en bestilt kolonne, må du angi flere kolonner og handlinger i en enkelt BREAK-kommando. Eksempel 6-12 Kombinere mellomromsteknikker Skriv inn følgende: For å hoppe over en side når verdien av DEPARTMENTID endres og en linje når verdien av JOBID endres, skriver du inn følgende kommando: For å vise at SKIP PAGE har tatt i bruk, opprett en TTITLE med et sidetal: Kjør den nye spørringen for å se resultatene: Oppføring og fjerning av brudddefinisjoner Før du fortsetter, slå av topptiteldisplayet uten å endre definisjonen: Du kan liste din nåværende pausedefinisjon ved å skrive inn BREAK-kommandoen uten bestemmelser: Du kan fjerne den nåværende pausedefinisjonen ved å skrive inn CLEAR-kommandoen med BREAKS-klausulen: Du kan ønske å legge kommandoen CLEAR BREAKS i begynnelsen av hvert skript for å sikre at tidligere innkomne BREAK-kommandoer ikke påvirker spørringer du kjører i en gitt fil. Computing Summary Linjer når en verdi for endring av bruddkolumner Hvis du organiserer rader av en rapport i delminner med BREAK-kommandoen, kan du utføre ulike beregninger på radene i hvert delsett. Du gjør dette med funksjonene i SQLPlus COMPUTE-kommandoen. Bruk kommandoene BREAK og COMPUTE sammen i følgende former: Du kan inkludere flere pause kolonner og handlinger, for eksempel hoppelinjer i BREAK-kommandoen, så lenge kolonnen du navngi etter PÅ i COMPUTE-kommandoen, også vises etter ON i BREAK kommando. For å inkludere flere pause kolonner og handlinger i BREAK når du bruker den i forbindelse med COMPUTE, bruk disse kommandoene i følgende former: COMPUTE-kommandoen har ingen effekt uten en tilsvarende BREAK-kommando. Du kan COMPUTE på NUMBER kolonner og i visse tilfeller på alle typer kolonner. For mer informasjon, se COMPUTE-kommandoen. Tabellen nedenfor viser beregningsfunksjoner og deres effekter Tabell 6-1 Beregne funksjoner Funksjonen du angir i COMPUTE-kommandoen gjelder for alle kolonnene du skriver inn etter OF og før PÅ. Beregnede verdier skrives ut på en egen linje når verdien av den bestilte kolonnen endres. Etiketter for ON REPORT og ON ROW beregninger vises i den første kolonnen ellers, vises de i kolonnen som er angitt i ON-klausulen. Du kan endre beregningsetiketten ved å bruke COMPUTE LABEL. Hvis du ikke definerer en etikett for den beregnede verdien, skriver SQLPlus det ubegrensede funksjonens søkeord. Beregningsetiketten kan undertrykkes ved å bruke NOPRINT-alternativet til COLUMN-kommandoen på pausekolonnen. Se COMPUTE-kommandoen for flere detaljer. Hvis du bruker NOPRINT-alternativet for kolonnen som COMPUTEN blir utført på, blir også COMPUTE-resultatet undertrykt. Eksempel 6-13 Beregning og utskrift av subtotaler For å beregne total SALARY ved avdeling, må du først oppgi den nåværende BREAK-definisjonen: som viser nåværende BREAK-definisjoner: Skriv inn følgende COMPUTE-kommando og kjør gjeldende spørring: For å beregne summen av lønn for avdelinger 10 og 20 uten å skrive ut beregningsetiketten: For å beregne lønnene bare i slutten av rapporten: Når du etablerer formatet til en NUMBER-kolonne, må du tillate størrelsen på summene som er inkludert i rapporten. Computing Summary Linjer i slutten av rapporten Du kan beregne og skrive ut sammendragslinjer basert på alle verdier i en kolonne ved å bruke BREAK og COMPUTE på følgende skjemaer: Eksempel 6-14 Computing og utskrift av et stort antall For å beregne og skrive ut totalverdien av lønn for alle selgere og endre beregningsetiketten, må du først skrive inn følgende BREAK - og COMPUTE-kommandoer: Neste, skriv inn og kjøre en ny spørring: For å skrive ut en total total (eller grand gjennomsnitt, høyeste maksimum og så videre) i tillegg til subtotaler (eller under-gjennomsnitt og så videre), inkluderer en pause kolonne og en ON REPORT-klausul i BREAK-kommandoen. Deretter oppgir du en COMPUTE-kommando for pausekolonnen og en annen til å beregne ON REPORT: Computing Multiple Summary Values ​​and Lines Du kan beregne og skrive ut samme type sammendragsverdi på forskjellige kolonner. For å gjøre dette, skriv inn en egen COMPUTE-kommando for hver kolonne. Eksempel 6-15 Beregning av samme type sammendragsverdi på forskjellige kolonner For å skrive ut summen av lønn og provisjoner for alle salgsmedlemmer, må du først skrive inn følgende COMPUTE-kommando: Du må ikke skrive inn en BREAK-kommando, den BREAK du skrev inn i eksempel 6 -14, Computing og utskrift av et stort antall er fortsatt i kraft. Endre først den første linjen i den valgte spørringen for å inkludere COMMISSIONPCT: Endre kjør den reviderte spørringen for å se resultatene: Du kan også skrive ut flere sammendragslinjer i samme pausekolonne. For å gjøre dette, inkludere funksjonen for hver oppsummeringslinje i COMPUTE-kommandoen som følger: Hvis du inkluderer flere kolonner etter OF og før ON, beregner og skriver COMPUTE verdier for hver kolonne du angir. Eksempel 6-16 Beregning av flere sammendragslinjer på samme bruddkolonne For å beregne gjennomsnittet og summen av lønnene til salgsavdelingen, må du først skrive inn følgende BREAK - og COMPUTE-kommandoer: Skriv inn og kjør følgende spørring: Oppføring og fjerning av COMPUTE-definisjoner Du kan liste dine nåværende COMPUTE-definisjoner ved å skrive inn COMPUTE-kommandoen uten noen bestemmelser: Eksempel 6-17 Fjerne COMPUTE-definisjoner For å fjerne alle COMPUTE-definisjoner og den vedlagte BREAK-definisjonen, skriv inn følgende kommandoer: Du kan ønske å plassere kommandoene CLEAR BREAKS og CLEAR COMPUTES i begynnelsen av hvert skript for å sikre at tidligere innkomne BREAK - og COMPUTE-kommandoer ikke påvirker spørringer du kjører i en gitt fil. Definere side - og rapporttitler og dimensjoner Ordet siden refererer til en skjerm full av informasjon på skjermen eller en side av en spolet (trykt) rapport. Du kan plassere topp - og bunntitler på hver side, angi antall linjer per side og bestemme bredden på hver linje. Ordrapporten refererer til de komplette resultatene av en spørring. Du kan også plassere overskrifter og bunntekster på hver rapport og formatere dem på samme måte som topp - og bunntitler på sider. Angi topp - og bunntitler og overskrifter og bunntekster Som du allerede har sett, kan du angi en tittel som skal vises øverst på hver side i en rapport. Du kan også angi en tittel som skal vises nederst på hver side. TTITLE-kommandoen definerer topptitelen, BTITLE-kommandoen definerer bunntitelen. Du kan også angi en overskrift og bunntekst for hver rapport. REPHEADER-kommandoen definerer rapportoverskriften REPFOOTER-kommandoen definerer rapportfoten. En TTITLE-, BTITLE-, REPHEADER - eller REPFOOTER-kommandoen består av kommandoenavnet etterfulgt av ett eller flere klausuler som spesifiserer en posisjon eller et format og en CHAR-verdi du vil plassere i den aktuelle posisjonen eller gi det formatet. Du kan inkludere flere sett med klausuler og CHAR-verdier: For beskrivelser av alle TTITLE-, BTITLE-, REPHEADER - og REPFOOTER-klausulene, se TTITLE-kommandoen og REPHEADER-kommandoen. Eksempel 6-18 Plassere topp og bunntittel på en side For å sette titler øverst og nederst på hver side av en rapport, skriv inn Nå kjør gjeldende spørring: For å undertrykke rapportoverskriften uten å endre definisjonen, skriv inn posisjoneringselementelementer The Rapport i de foregående øvelsene kan se mer attraktivt hvis du gir bedriftsnavnet mer vekt og plasserer typen rapport og avdelingsnavnet på hver ende av en egen linje. Det kan også bidra til å redusere linjestørrelsen og dermed sentrere titlene nærmere rundt dataene. Du kan oppnå disse endringene ved å legge til noen klausuler i TTITLE-kommandoen og ved å tilbakestille systemvariabelen LINESIZE. som følgende eksempel viser. Du kan formatere rapportoverskrifter og bunntekster på samme måte som BTITLE og TTITLE ved hjelp av REPHEADER - og REPFOOTER-kommandoer. Eksempel 6-20 Posisjonering av tittelelementer For å vise personellrapporten med en omplassert toppittel, skriv inn følgende kommandoer: VENSTRE. IKKE SANT. og CENTER-klausulene plasserer følgende verdier i begynnelsen, slutten og midten av linjen. SKIP-klausulen forteller SQLPlus å flytte ned en eller flere linjer. Legg merke til at det ikke lenger er mellomrom mellom siste rad av resultatene og nederste tittelen. Den siste linjen i den nederste tittelen skrives ut på den siste linjen på siden. Mengden mellomrom mellom siste rad i rapporten og den nederste tittelen avhenger av den totale sidestørrelsen, antallet linjer som er tatt av topptitelen, og antall rader på en gitt side. I eksemplet ovenfor har topptitelen tre linjer enn topptitelen i det forrige eksempelet. Du lærer å angi antall linjer per side senere i dette kapittelet. For å alltid skrive ut tomme linjer før nederste tittel, bruk SKIP n-klausulen i begynnelsen av BTITLE-kommandoen. Hvis du for eksempel vil hoppe over en linje før bunntitelen i eksemplet ovenfor, kan du skrive inn følgende kommando: Indusere et tittelelement Du kan bruke COL-klausulen i TTITLE eller BTITLE for å taste inn tittelelementet et bestemt antall mellomrom. For eksempel plasserer COL 1 følgende verdier i den første tegnposisjonen, og det vil si lik VINST, eller et innrykk på null. KOL 15 plasserer tittelelementet i 15-tegnsposisjon, innrykker det 14 mellomrom. Eksempel 6-21 Sperre et tittelelement Hvis du vil skrive ut firmanavnet som er venstrejustert med rapportnavnet, har du inntatt fem mellomrom på neste linje, skriv inn Nå gjenopprett gjeldende spørring for å se resultatene: Oppføring, undertrykking og gjenoppretting av sidetitteldefinisjoner Til liste en sidetitteldefinisjon, skriv inn den aktuelle tittelkommandoen uten bestemmelser: For å undertrykke en titteldefinisjon, skriv inn: Disse kommandoene forårsaker at SQLPlus slutter å vise titler på rapporter, men ikke tøm gjeldende definisjoner av titlene. Du kan gjenopprette de gjeldende definisjonene ved å skrive inn: Vise kolonneverdier i titler Du kan ønske å opprette en masterdetaljrapport som viser en endring av masterkolonneverdi øverst på hver side med detaljerte spørringsresultater for denne verdien under. Du kan referere til en kolonneverdi i en toppittel ved å lagre ønsket verdi i en variabel og referere til variabelen i en TTITLE-kommando. Bruk følgende form for COLUMN-kommandoen til å definere variabelen: Du må inkludere master-kolonnen i en ORDER BY-setning og i en BREAK-kommando ved hjelp av SKIP PAGE-klausulen. Eksempel 6-24 Opprette en MasterDetail Report Anta at du vil opprette en rapport som viser to forskjellige ledere ansattes tall, hver øverst på en egen side, og de som rapporterer til lederen på samme side som lederens ansattes nummer. Først opprett en variabel, MGRVAR, for å holde verdien av den nåværende lederens ansattes nummer: Fordi du bare vil vise lederens ansattes tall i tittelen, vil du ikke at de skal skrives ut som en del av detaljene. NOPRINT-klausulen du skrev inn ovenfor, forteller at SQLPlus ikke skal skrive ut kolonnen MANAGERID. Deretter inkluderer du en etikett og verdien i sidetittelen din, skriv inn den riktige BREAK-kommandoen, og undertrykk bunnteksten fra det siste eksemplet: Endelig, skriv inn og kjør følgende spørring: Hvis du vil skrive ut verdien av en kolonne på nederst på siden kan du bruke kommandoen COLUMN i følgende skjema: SQLPlus skriver ut den nederste tittelen som en del av prosessen med å bryte til en ny side etter at den nye verdien for master-kolonnen er funnet. Derfor, hvis du bare refererte til NEWVALUE av master-kolonnen, ville du få verdien for neste sett med detaljer. OLDVALUE husker verdien av hovedkolonnen som var i kraft før sideskiftet startet. Vise gjeldende dato i titler Du kan selvfølgelig oppgi rapportene dine ved å bare skrive inn en verdi i tittelen. Dette er tilfredsstillende for ad hoc-rapporter, men hvis du vil kjøre den samme rapporten gjentatte ganger, vil du sannsynligvis foretrekke at datoen automatisk vises når rapporten kjøres. Du kan gjøre dette ved å opprette en variabel for å holde gjeldende dato. Du kan referere til den forhåndsdefinerte substitusjonsvariabelen DATE for å vise gjeldende dato i en tittel som du ville ha en annen variabel. Datomodellmodellen du inkluderer i din LOGIN-fil, eller i SELECT-setningen, bestemmer formatet der SQLPlus viser datoen. Se Oracle Database SQL Reference for mer informasjon om datoformatmodeller. Se Endre LOGIN-filen din for mer informasjon om LOGIN-filen. Du kan også skrive disse kommandoene interaktivt. Se COLUMN for mer informasjon. Angi sidemått Typisk inneholder en side i en rapport antall tomme linjer som er angitt i NEWPAGE-variabelen i SET-kommandoen, en toppittel, kolonneoverskrifter, spørringsresultatene og en bunntekst. SQLPlus viser en rapport som er for lang til å passe på en side på flere påfølgende sider, hver med egne titler og kolonneoverskrifter. Mengden data SQLPlus som vises på hver side avhenger av gjeldende sidedimensjoner. Standardsiderdimensjonene som brukes av SQLPlus, vises under: Antall linjer før topptitelen: 1 Antall linjer per side, fra topp tittelen til bunnen av siden: 14 Antall tegn per linje: 80 Du kan endre disse innstillingene for å matche størrelsen på skjermen eller, for utskrift, størrelsen på et ark papir. Du kan endre sidelengden med systemvariabelen PAGESIZE. For eksempel kan det være lurt å gjøre det når du skriver ut en rapport. For å angi antall linjer mellom begynnelsen av hver side og topptitelen. bruk NEWPAGE-variabelen i SET-kommandoen: Hvis du stiller NEWPAGE til null, hopper SQLPlus på null linjer og viser og skriver ut et formet tegn for å starte en ny side. På de fleste typer dataskjermer rydder skjemaet tegn på skjermen og beveger markøren til begynnelsen av første linje. Når du skriver ut en rapport, gjør skriverformat-tegnet seg til toppen av et nytt ark, selv om den totale sidelengden er mindre enn papiret. Hvis du angir NEWPAGE til NONE, skriver ikke SQLPlus en tom linje eller formfelt mellom rapport sider. For å angi antall linjer på en side, bruk PAGESIZE-variabelen i SET-kommandoen: Du kan ønske å redusere linjestørrelsen for å sentrere en tittel riktig over produksjonen din, eller du vil kanskje øke linjestørrelsen for utskrift på bredt papir. Du kan endre linjebredden ved hjelp av LINESIZE-variabelen i SET-kommandoen: Eksempel 6-25 Innstille sidemått Hvis du vil sette sidestørrelsen til 66 linjer, fjerner du skjermen (eller flytt skriveren til et nytt ark) ved starten av hver side, og angi linjestørrelsen til 70, skriv inn følgende kommandoer: Skriv inn og kjør følgende kommandoer for å se resultatene: Kjør nå følgende spørring: Nå tilbakestill PAGESIZE, NEWPAGE og LINESIZE til standardverdiene: For å liste opp nåværende verdier av disse variablene, bruk SHOW-kommandoen: Gjennom SQLPlus-kommandoen SPOOL. Du kan lagre søkeresultatene dine i en fil eller skrive dem ut på datamaskinens standardskriver. Lagre og skrive ut søkeresultater Send forespørselsresultatene til en fil når du vil redigere dem med en tekstbehandler før du skriver ut eller inkludere dem i et brev, e-post eller annet dokument. For å lagre resultatene av en spørring i et filmmusk og fremdeles vise dem på skjermdriveren SPOOL-kommandoen i følgende form: Hvis du ikke følger filnavnet med en periode og en utvidelse, legger SPOOL en standard filtype til filnavnet for å identifisere det som en utdatafil. Standarden varierer med operativsystemet på de fleste verter, det er LST eller LIS. Utvidelsen legges ikke til når du spole til systemgenererte filer som devnull og devstderr. Se den plattformspesifikke Oracle-dokumentasjonen som er gitt for operativsystemet ditt for mer informasjon. SQLPlus fortsetter å spole informasjon til filen til du slår av, ved hjelp av følgende form for SPOOL: Opprette en flatfil Når du flytter data mellom forskjellige programvareprodukter, er det noen ganger nødvendig å bruke en flat fil (en operativsystemfil uten å unnslippe tegn, overskrifter eller ekstra tegn innebygd). Hvis du for eksempel ikke har Oracle Net, må du opprette en flat fil for bruk med SQLLoader når du flytter data fra Oracle9 til Oracle Database 10 g. For å lage en flatfil med SQLPlus må du først skrive inn følgende SET-kommandoer: Når du har skrevet inn disse kommandoene, bruker du SPOOL-kommandoen som vist i forrige seksjon for å lage den flate filen. SET COLSEP-kommandoen kan være nyttig for å avgrense kolonnene. For mer informasjon, se SET-kommandoen. Sende resultater til en fil For å lagre resultatene av en spørring i en filmmask og fremdeles vise dem på skjermdisplayet SPOOL-kommandoen i følgende form: SQLPlus lagrer all informasjon som vises på skjermen etter at du har tastet inn SPOOL-kommandoen i filen du angir. Sende resultater til en skriver For å skrive ut søkeresultater. spool dem til en fil som beskrevet i forrige avsnitt. Deretter, i stedet for å bruke SPOOL OFF, skriv inn kommandoen i følgende form: SQLPlus stopper spoling og kopierer innholdet i den spolede filen til datamaskinens standard (standard) skriver. SPOOL OUT sletter ikke spoolfilen etter utskrift. Eksempel 6-26 Sende spørringsresultater til en skriver For å generere en endelig rapport og spole og skrive ut resultatene, opprett et skript med navnet EMPRPT som inneholder følgende kommandoer. Først bruk EDIT for å lage skriptet med operativsystemet tekstredigeringsprogrammet. Deretter skriver du inn følgende kommandoer i filen, ved hjelp av teksteditoren: Hvis du ikke vil se utgangen på skjermen, kan du også legge til SET TERMOUT OFF til begynnelsen av filen og SET TERMOUT ON til slutten av fil. Lagre og lukk filen i tekstredigeringsprogrammet (du kommer automatisk tilbake til SQLPlus). Kjør nå skriptet EMPRPT: SQLPlus viser utgangen på skjermen din (med mindre du setter TERMOUT til OFF), og spoler den til filen TEMP: Innledning Den forrige artikkelen så på hvilke bevegelige gjennomsnitt som er og hvordan man beregner dem. Denne artikkelen ser nå på hvordan man implementerer disse i Web Intelligence. Formelen som brukes her er kompatibel med XIr3-versjonen av SAP BOE, men noen formler kan fungere i tidligere versjoner hvis tilgjengelig. We8217ll begynner med å se på hvordan du skal beregne et enkelt glidende gjennomsnitt før du ser på vektede og eksponentielle former. Arbeidte eksempler Eksemplene nedenfor bruker alle samme datasett som er av aksjekursdata i en Excel-fil som du kan laste ned. Første kolonne i filen er aksjekursdagen og deretter kolonner med åpningspris, høyeste pris på dagen, laveste pris, sluttkurs, volum og justert sluttkurs. We8217ll bruker sluttkurs i analysen nedenfor sammen med Date-objektet. Enkel Flytende Gjennomsnitt Det er et par måter der vi kan beregne enkle bevegelige gjennomsnitt. Et alternativ er å bruke funksjonen Forrige for å få verdien av en tidligere rad. For eksempel beregner følgende formel et flytende gjennomsnitt på vår sluttkurs for et flytende gjennomsnittsdatasett med størrelse 3, Dette er en ganske enkel formel, men det er åpenbart det er ikke praktisk når vi har et stort antall perioder her vi kan lage bruk av RunningSum formel og for et datasett av størrelse N vi har Endelig har vi en tredje teknikk, som, selv om det er mer komplisert, kan det ha bedre ytelse, da det beregner den nye verdien basert på tidligere verdi i stedet for to løpende summer over de fulle dataene sett. Men denne formelen fungerer bare etter Nth-punktet i det generelle datasettet, og siden det refererer til en tidligere verdi, må vi også angi en startverdi. Nedenfor er den fulle formelen brukt til vår aksjekursanalyse hvor vår bevegelige gjennomsnittlige periode er 15 dager. Datoen 1252010 er det 15. datapunktet i vårt datasett, og så beregner vi for dette punkt et normalt gjennomsnitt ved hjelp av RunningSum. For alle datoer utover denne verdien bruker vi vår SMA-formel og vi legger blank alle datoer før denne datoen. Figur 1 nedenfor er et diagram i Web Intelligence som viser våre aksjekursdata med et enkelt glidende gjennomsnitt. Figur 1. Web Intelligence Document viser en enkel, flytende Gjennomsnittlig vektet Flytende Gjennomsnitt En vektet glidende gjennomsnittlig formel med en periode på 3 er, Som med vår første enkle glidende gjennomsnittlige formel over, er dette bare praktisk i et lite antall perioder. Jeg har ennå ikke vært i stand til å finne en enkel formel som kan brukes til større bevegelige gjennomsnittsperioder. Matematisk er det mulig, men begrensninger med Web Intelligence betyr at disse formlene don8217t konverterer. Hvis noen er i stand til å gjøre dette, vil jeg gjerne høre Figuren under er et WMA i periode 6 implementert i Web Intelligence. Figur 2. Web Intelligence-dokument av en vektet Flytende Gjennomsnittlig Eksponentiell Flytende Gjennomsnitt Et eksponentielt glidende gjennomsnitt er ganske rett frem for å implementere i Web Intelligence, og det er et egnet alternativ til et veidende flytende gjennomsnitt. Den grunnleggende formelen er Here we8217ve hardcoded 0.3 som vår verdi for alpha. Vi bruker bare denne formelen for perioder som er større enn vår andre periode, så vi kan bruke en if-setning for å filtrere disse ut. For vår første og andre periode kan vi bruke den forrige verdien og så er vår endelige formel for EMA, Nedenfor er et eksempel på en EMA anvendt på våre lagerdata. Figur 3. Web Intelligence-dokument viser en eksponentiell Moving Average Input Controls. Siden vår EMA-formel doesn8217t er avhengig av størrelsen på den bevegelige gjennomsnittlige perioden, og vår eneste variabel er alfa, kan vi bruke Input Controls for å la brukeren justere verdien av alfa. For å gjøre dette, opprett en ny variabel som heter 8216alpha8217 og definer it8217s formel som, Oppdater vår EMA-formel til, Opprett ny inngangskontroll, velg vår alfa-variabel som inputkontrollrapportobjektet Bruk en enkel skyvekontroll og angi følgende egenskaper, Når du har gjort det bør kunne flytte glidebryteren og umiddelbart se endringene i trendlinjen i diagrammet. Konklusjon Vi så på hvordan du implementerer tre typer bevegelige gjennomsnitt i Web Intelligence, og selv om alt var mulig, er det eksponensielle flytende gjennomsnittet sannsynligvis den enkleste og mest fleksible . Jeg håper du fant denne artikkelen interessant, og som alltid er noen tilbakemeldinger veldig velkommen. Post navigasjon Legg igjen en kommentar Avbryt svar Du må være logget inn for å skrive en kommentar. Tricket til Weighted Moving Average (WMA) er at du må opprette en variabel som representerer tellerne av WMA (se Wikipedia for referanse.) Dette skal se ut som følgende: Forrige (Selv) (n Lukk) 8211 (Forrige (RunningSum Lukk)) 8211 Forrige (RunningSum (Lukk) n1) hvor n er antall perioder. Da vil den faktiske WMA8217s formel være slik: Numerator (n (n 1) 2) hvor Numerator er variabelen du opprettet tidligere.22 SQL for analyse og rapportering Oracle har forbedret SQLs analytiske behandlingskapasiteter ved å introdusere en ny familie av analytiske SQL-funksjoner. Disse analytiske funksjonene gjør det mulig å beregne: Rangeringer og prosentiler Flytte vindusberegninger Linjær regresjonsstatistikk Rangeringsfunksjoner inkluderer kumulative distribusjoner, prosentranger og N - flytte vindu beregninger lar deg finne bevegelige og kumulative aggregeringer, for eksempel summer og gjennomsnitt. Laglead analyse muliggjør direkte inter-rad referanser, slik at du kan beregne periode-til-peri od endringer. Firstlast analyse lar deg finne den første eller siste verdien i en bestilt gruppe. Andre forbedringer til SQL inkluderer CASE-uttrykket og partisjonert ytre tilkobling. CASE uttrykkene gir hvis-da logikk nyttig i mange situasjoner. Partisjonert ytre sammenføyning er en utvidelse av ANSI ytre tilkoblingssyntax som lar brukerne selektivt tette visse dimensjoner mens andre holdes sparsomme. Dette gjør det mulig for rapporteringsverktøyene å selektivt tette dimensjoner, for eksempel de som vises i sine tverrgående rapporter samtidig som de holder andre sparsomme. For å forbedre ytelsen, kan analytiske funksjoner parallelliseres: flere prosesser kan samtidig utføre alle disse setningene. Disse funksjonene gjør beregningene enklere og mer effektive, og forbedrer databasens ytelse, skalerbarhet og enkelhet. Analytiske funksjoner er klassifisert som beskrevet i tabell 22-1. Tabell 22-1 Analytiske funksjoner og deres bruksområder For å utføre disse operasjonene, legger de analytiske funksjonene flere nye elementer til SQL-behandling. Disse elementene bygger på eksisterende SQL for å tillate fleksible og kraftige beregningsuttrykk. Med noen få unntak har de analytiske funksjonene disse nye elementene. Behandlingsstrømmen er representert i figur 22-1. Figur 22-1 Behandlingsordre De essensielle begrepene som brukes i analytiske funksjoner er: Søkebehandling ved hjelp av analytiske funksjoner foregår i tre trinn. Først går alle sammen, hvor. GROUP BY og HAVING klausuler utføres. For det andre blir resultatsettet gjort tilgjengelig for de analytiske funksjonene, og alle deres beregninger finner sted. For det tredje, hvis spørringen har en ORDER BY-klausul ved slutten, behandles ORDER BY for å tillate presis utskriftsbestilling. Behandlingsordren er vist i figur 22-1. Resultatopprettede partisjoner De analytiske funksjonene tillater brukere å dele spørringsresultatsettene i grupper av rader som kalles partisjoner. Vær oppmerksom på at begrepet partisjoner som brukes med analytiske funksjoner ikke er relatert til tabellpartisjonen. Gjennom dette kapittelet refererer begrepet partisjoner bare til meningen som er relatert til analytiske funksjoner. Partisjoner opprettes etter at gruppene er definert med GROUP BY-klausuler, slik at de er tilgjengelige for eventuelle aggregerte resultater som summer og gjennomsnitt. Partisjon divisjoner kan være basert på eventuelle ønskede kolonner eller uttrykk. Et spørringsresultat sett kan deles inn i bare en partisjon som holder alle rader, noen store partisjoner, eller mange små partisjoner som holder bare noen få rader hver. For hver rad i en partisjon, kan du definere et glidende vindu med data. Dette vinduet bestemmer rekkevidden av rader som brukes til å utføre beregningene for den aktuelle raden. Vinduestørrelser kan baseres på enten et fysisk antall rader eller et logisk intervall som tid. Vinduet har en startrute og en enderig rad. Depending on its definition, the window may move at one or both ends. For instance, a window defined for a cumulative sum function would have its starting row fixed at the first row of its partition, and its ending row would slide from the starting point all the way to the last row of the partition. In contrast, a window defined for a moving average would have both its starting and end points slide so that they maintain a constant physical or logical range. A window can be set as large as all the rows in a partition or just a sliding window of one row within a partition. When a window is near a border, the function returns results for only the available rows, rather than warning you that the results are not what you want. When using window functions, the current row is included during calculations, so you should only specify ( n -1) when you are dealing with n items. Each calculation performed with an analytic function is based on a current row within a partition. The current row serves as the reference point determining the start and end of the window. For instance, a centered moving average calculation could be defined with a window that holds the current row, the six preceding rows, and the following six rows. This would create a sliding window of 13 rows, as shown in Figure 22-2 . Figure 22-2 Sliding Window Example Ranking, Windowing, and Reporting Functions This section illustrates the basic analytic functions for ranking, windowing, and reporting. Sample Linear Regression Calculation In this example, we compute an ordinary-least-squares regression line that expresses the quantity sold of a product as a linear function of the products list price. The calculations are grouped by sales channel. The values SLOPE. INTCPT. RSQR are slope, intercept, and coefficient of determination of the regression line, respectively. The (integer) value COUNT is the number of products in each channel for whom both quantity sold and list price data are available. Statistical Aggregates Oracle provides a set of SQL statistical functions and a statistics package, DBMSSTATFUNCS. This section lists some of the new functions along with basic syntax. Descriptive Statistics You can calculate the following descriptive statistics: Median of a Data Set Mode of a Data Set You can calculate the following parametric statistics: Spearmans rho Coefficient Kendalls tau-b Coefficient In addition to the functions, this release has a PLSQL package, DBMSSTATFUNCS. It contains the descriptive statistical function SUMMARY along with functions to support distribution fitting. The SUMMARY function summarizes a numerical column of a table with a variety of descriptive statistics. The five distribution fitting functions support normal, uniform, Weibull, Poisson, and exponential distributions. User-Defined Aggregates Oracle offers a facility for creating your own functions, called user-defined aggregate functions. These functions are written in programming languages such as PLSQL, Java, and C, and can be used as analytic functions or aggregates in materialized views. See Oracle Database Data Cartridge Developers Guide for further information regarding syntax and restrictions. The advantages of these functions are: Highly complex functions can be programmed using a fully procedural language. Higher scalability than other techniques when user-defined functions are programmed for parallel processing. Object data types can be processed. As a simple example of a user-defined aggregate function, consider the skew statistic. This calculation measures if a data set has a lopsided distribution about its mean. It will tell you if one tail of the distribution is significantly larger than the other. If you created a user-defined aggregate called udskew and applied it to the credit limit data in the prior example, the SQL statement and results might look like this: Before building user-defined aggregate functions, you should consider if your needs can be met in regular SQL. Many complex calculations are possible directly in SQL, particularly by using the CASE expression. Staying with regular SQL will enable simpler development, and many query operations are already well-parallelized in SQL. Even the earlier example, the skew statistic, can be created using standard, albeit lengthy, SQL. Pivoting Operations The d ata returned by business intelligence queries is often most usable if presented in a crosstabular format. The pivotclause of the SELECT statement lets you write crosstabulation queries that rotate rows into columns, aggregating data in the process of the rotation. Pivoting is a key technique in data warehouses. In it, you transform multiple rows of input into fewer and generally wider rows in the data warehouse. When pivoting, an aggregation operator is applied for each item in the pivot column value list. The pivot column cannot contain an arbitrary expression. If you need to pivot on an expression, then you should alias the expression in a view before the PIVOT operation. The basic syntax is as follows: To illustrate the use of pivoting, create the following view as a basis for later examples: Example: Pivoting The following statement illustrates a typical pivot on the channel column: Note that the output has created four new aliased columns, DIRECTSALES. INTERNETSALES. CATALOGSALES. and TELESALES. one for each of the pivot values. The output is a sum. If no alias is provided, the column heading will be the values of the IN - list. Pivoting on Multiple Columns You can pivot on more than one column. The following statement illustrates a typical multiple column pivot: Note that this example specifies a multi-column IN - list with column headings designed to match the IN - list members. Pivoting: Multiple Aggregates You can pivot with multiple aggregates, as shown in the following example: Note that the query creates column headings by concatenating the pivot values (or alias) with the alias of the aggregate function, plus an underscore. Distinguishing PIVOT-Generated Nulls from Nulls in Source Data You can distinguish between null values that are generated from the use of PIVOT and those that exist in the source data. The following example illustrates nulls that PIVOT generates. The following query returns rows with 5 columns, column prodid. and pivot resulting columns Q1. Q1COUNTTOTAL. Q2. Q2COUNTTOTAL. For each unique value of prodid. Q1COUNTTOTAL returns the total number of rows whose qtr value is Q1. that is, and Q2COUNTTOTAL returns the total number of rows whose qtr value is Q2 . Assume we have a table sales2 of the following structure: From the result, we know that for prodid 100, there are 2 sales rows for quarter Q1. and 1 sales row for quarter Q2 for prodid 200, there is 1 sales row for quarter Q1. and no sales row for quarter Q2.So, in Q2COUNTTOTAL. you can identify that NULLlt1gt comes from a row in the original table whose measure is of null value, while NULLlt2gt is due to no row being present in the original table for prodid 200 in quarter Q2 . Unpivoting Operations An unpivot does not reverse a PIVOT operation. Instead, it rotates data from columns into rows. If you are working with pivoted data, an UNPIVOT operation cannot reverse any aggregations that have been made by PIVOT or any other means. To illustrate unpivoting, first create a pivoted table that includes four columns, for quarters of the year: The tables contents resemble the following: The following UNPIVOT operation rotates the quarter columns into rows. For each product, there will be four rows, one for each quarter. Note the use of INCLUDE NULLS in this example. You can also use EXCLUDE NULLS. which is the default setting. In addition, you can also unpivot using two columns, as in the following: Wildcard and Subquery Pivoting with XML Operations If you want to use a wildcard argument or subquery in your pivoting columns, you can do so with PIVOT XML syntax. With PIVOT XML, the output of the operation is properly formatted XML. The following example illustrates using the wildcard keyword, ANY. It outputs XML that includes all channel values in salesview : Note that the keyword ANY is available in PIVOT operations only as part of an XML operation. This output includes data for cases where the channel exists in the data set. Also note that aggregation functions must specify a GROUP BY clause to return multiple values, yet the pivotclause does not contain an explicit GROUP BY clause. Instead, the pivotclause performs an implicit GROUP BY . The following example illustrates using a subquery. It outputs XML that includes all channel values and the sales data corresponding to each channel: The output densifies the data to include all possible channels for each product. Data Densification for Reporting Data is normally stored in sparse form. That is, if no value exists for a given combination of dimension values, no row exists in the fact table. However, you may want to view the data in dense form, with rows for all combination of dimension values displayed even when no fact data exist for them. For example, if a product did not sell during a particular time period, you may still want to see the product for that time period with zero sales value next to it. Moreover, time series calculations can be performed most easily when data is dense along the time dimension. This is because dense data will fill a consistent number of rows for each period, which in turn makes it simple to use the analytic windowing functions with physical offsets. Data densification is the process of converting sparse data into dense form. To overcome the problem of sparsity, you can use a partitioned outer join to fill the gaps in a time series or any other dimension. Such a join extends the conventional outer join syntax by applying the outer join to each logical partition defined in a query. Oracle logically partitions the rows in your query based on the expression you specify in the PARTITION BY clause. The result of a partitioned outer join is a UNION of the outer joins of each of the partitions in the logically partitioned table with the table on the other side of the join. Note that you can use this type of join to fill the gaps in any dimension, not just the time dimension. Most of the examples here focus on the time dimension because it is the dimension most frequently used as a basis for comparisons. Partition Join Syntax The syntax for partitioned outer join extends the ANSI SQL JOIN clause with the phrase PARTITION BY followed by an expression list. The expressions in the list specify the group to which the outer join is applied. The following are the two forms of syntax normally used for partitioned outer join: Note that FULL OUTER JOIN is not supported with a partitioned outer join. Sample of Sparse Data A typi cal situation with a sparse dimension is shown in the following example, which computes the weekly sales and year-to-date sales for the product Bounce for weeks 20-30 in 2000 and 2001: In this example, we would expect 22 rows of data (11 weeks each from 2 years) if the data were dense. However, we get only 18 rows because weeks 25 and 26 are missing in 2000, and weeks 26 and 28 in 2001. Filling Gaps in Data We can take the sparse data of the preceding query and do a partitioned outer join with a dense set of time data. In the following query, we alias our original query as v and we select data from the times table, which we alias as t. Here we retrieve 22 rows because there are no gaps in the series. The four added rows each have 0 as their Sales value set to 0 by using the NVL function. Note that in this query, a WHERE condition was placed for weeks between 20 and 30 in the inline view for the time dimension. This was introduced to keep the result set small. Filling Gaps in Two Dimensions N-dimensional data is typically displayed as a dense 2-dimensional cross tab of (n - 2) page dimensions. This requires that all dimension values for the two dimensions appearing in the cross tab be filled in. The following is another example where the partitioned outer join capability can be used for filling the gaps on two dimensions: In this query, the WITH subquery factoring clause v1 summarizes sales data at the product, country, and year level. This result is sparse but users may want to see all the country, year combinations for each product. To achieve this, we take each partition of v1 based on product values and outer join it on the country dimension first. This will give us all values of country for each product. We then take that result and partition it on product and country values and then outer join it on time dimension. This will give us all time values for each product and country combination. Filling Gaps in an Inventory Table An inventory table typically tracks quantity of units available for various products. This table is sparse: it only stores a row for a product when there is an event. For a sales table, the event is a sale, and for the inventory table, the event is a change in quantity available for a product. For example, consider the following inventory table: The inventory table now has the following rows: For reporting purposes, users may want to see this inventory data differently. For example, they may want to see all values of time for each product. This can be accomplished using partitioned outer join. In addition, for the newly inserted rows of missing time periods, users may want to see the values for quantity of units column to be carried over from the most recent existing time period. The latter can be accomplished using analytic window function LASTVALUE value. Here is the query and the desired output: The inner query computes a partitioned outer join on time within each product. The inner query densifies the data on the time dimension (meaning the time dimension will now have a row for each day of the week). However, the measure column quantity will have nulls for the newly added rows (see the output in the column quantity in the following results. The outer query uses the analytic function LASTVALUE. Applying this function partitions the data by product and orders the data on the time dimension column ( timeid ). For each row, the function finds the last non-null value in the window due to the option IGNORE NULLS. which you can use with both LASTVALUE and FIRSTVALUE. We see the desired output in the column repeatedquantity in the following output: Computing Data Values to Fill Gaps Examples in previous section illustrate how to use partitioned outer join to fill gaps in one or more dimensions. However, the result sets produced by partitioned outer join have null values for columns that are not included in the PARTITION BY list. Typically, these are measure columns. Users can make use of analytic SQL functions to replace those null values with a non-null value. For example, the following q uery computes monthly totals for products 64MB Memory card and DVD-R Discs (product IDs 122 and 136) for the year 2000. It uses partitioned outer join to densify data for all months. For the missing months, it then uses the analytic SQL function AVG to compute the sales and units to be the average of the months when the product was sold. If working in SQLPlus, the following two commands wraps the column headings for greater readability of results: Time Series Calculations on Densified Data Densificatio n is not just for reporting purpose. It also enables certain types of calculations, especially, time series calculations. Time series calculations are easier when data is dense along the time dimension. Dense data has a consistent number of rows for each time periods which in turn make it simple to use analytic window functions with physical offsets. To illustrate, let us first take the example on Filling Gaps in Data. and lets add an analytic function to that query. In the following enhanced version, we calculate weekly year-to-date sales alongside the weekly sales. The NULL values that the partitioned outer join inserts in making the time series dense are handled in the usual way: the SUM function treats them as 0s. Period-to-Period Comparison for One Time Level: Example How do we use this feature to compare values across time periods Specifically, how do we calculate a year-over-year sales comparison at the week level The following query returns on the same row, for each product, the year-to-date sales for each week of 2001 with that of 2000. Note that in this example we start with a WITH clause. This improves readability of the query and lets us focus on the partitioned outer join. If working in SQLPlus, the following command wraps the column headings for greater readability of results: In the FROM clause of the inline view densesales. we use a partitioned outer join of aggregate view v and time view t to fill gaps in the sales data along the time dimension. The output of the partitioned outer join is then processed by the analytic function SUM. OVER to compute the weekly year-to-date sales (the weeklyytdsales column). Thus, the view densesales computes the year-to-date sales data for each week, including those missing in the aggregate view s. The inline view yearoveryearsales then computes the year ago weekly year-to-date sales using the LAG function. The LAG function labeled weeklyytdsalesprioryear specifies a PARTITION BY clause that pairs rows for the same week of years 2000 and 2001 into a single partition. We then pass an offset of 1 to the LAG function to get the weekly year to date sales for the prior year. The outermost query block selects data from yearoveryearsales with the condition yr 2001. and thus the query returns, for each product, its weekly year-to-date sales in the specified weeks of years 2001 and 2000. Period-to-Period Comparison for Multiple Time Levels: Example While the prior example shows us a way to create comparisons for a single time level, it would be even more useful to handle multiple time levels in a single query. For example, we could compare sales versus the prior period at the year, quarter, month and day levels. How can we create a query which performs a year-over-year comparison of year-to-date sales for all levels of our time hierarchy We will take several steps to perform this task. The goal is a single query with comparisons at the day, week, month, quarter, and year level. The steps are as follows: We will create a view called cubeprodtime. which holds a hierarchical cube of sales aggregated across times and products . Then we will create a view of the time dimension to use as an edge of the cube. The time edge, which holds a complete set of dates, will be partitioned outer joined to the sparse data in the view cubeprodtime . Finally, for maximum performance, we will create a materialized view, mvprodtime. built using the same definition as cubeprodtime . For more information regarding hierarchical cubes, see Chapter 21, SQL for Aggregation in Data Warehouses. The materialized view is defined in Step 1 in the following section. Step 1 Create the hierarchical cube view The materialized view shown in the following may already exist in your system if not, create it now. If you must generate it, note that we limit the query to just two products to keep processing time short: Because this view is limited to two products, it returns just over 2200 rows. Note that the column HierarchicalTime contains string representations of time from all levels of the time hierarchy. The CASE expression used for the HierarchicalTime column appends a marker (0, 1. ) to each date string to denote the time level of the value. A 0 represents the year level, 1 is quarters, 2 is months, and 3 is day. Note that the GROUP BY clause is a concatenated ROLLUP which specifies the rollup hierarchy for the time and product dimensions. The GROUP BY clause is what determines the hierarchical cube contents. Step 2 Create the view edgetime, which is a complete set of date values edgetime is the source for filling time gaps in the hierarchical cube using a partitioned outer join. The column HierarchicalTime in edgetime will be used in a partitioned join with the HierarchicalTime column in the view cubeprodtime. The following statement defines edgetime : Step 3 Create the materialized view mvprodtime to support faster performance The materialized view definition is a duplicate of the view cubeprodtime defined earlier. Because it is a duplicate query, references to cubeprodtime will be rewritten to use the mvprodtime materialized view. The following materialized may already exist in your system if not, create it now. If you must generate it, note that we limit the query to just two products to keep processing time short. Step 4 Create the comparison query We have now set the stage for our comparison query. We can obtain period-to-period comparison calculations at all time levels. It requires applying analytic functions to a hierarchical cube with dense data along the time dimension. Some of the calculations we can achieve for each time level are: Sum of sales for prior period at all levels of time. Variance in sales over prior period. Sum of sales in the same period a year ago at all levels of time. Variance in sales over the same period last year. The following example performs all four of these calculations. It uses a partitioned outer join of the views cubeprodtime and edgetime to create an inline view of dense data called densecubeprodtime. The query then uses the LAG function in the same way as the prior single-level example. The outer WHERE clause specifies time at three levels: the days of August 2001, the entire month, and the entire third quarter of 2001. Note that the last two rows of the results contain the month level and quarter level aggregations. Note that to make the results easier to read if you are using SQLPlus, the column headings should be adjusted with the following commands. The commands will fold the column headings to reduce line length: Here is the query comparing current sales to prior and year ago sales: The first LAG function ( salespriorperiod ) partitions the data on gidp. cat. subcat. prod. gidt and orders the rows on all the time dimension columns. It gets the sales value of the prior period by passing an offset of 1. The second LAG function ( salessameperiodprioryear ) partitions the data on additional columns qtrnum. monnum. and daynum and orders it on yr so that, with an offset of 1, it can compute the year ago sales for the same period. The outermost SELECT clause computes the variances. Creating a Custom Member in a Dimension: Example In many analytical SQL tasks, it is helpful to define custom members in a dimension. For instance, you might define a specialized time period for analyses. You can use a partitioned outer join to temporarily add a member to a dimension. Note that the new SQL MODEL clause is suitable for creating more complex scenarios involving new members in dimensions. See Chapter 23, SQL for Modeling for more information on this topic. As an example of a task, what if we want to define a new member for our time dimension We want to create a 13th member of the Month level in our time dimension. This 13th month is defined as the summation of the sales for each product in the first month of each quarter of year 2001. The solution has two steps. Note that we will build this solution using the views and tables created in the prior example. Two steps are required. First, create a view with the new member added to the appropriate dimension. The view uses a UNION ALL operation to add the new member. To query using the custom member, use a CASE expression and a partitioned outer join. Our new member for the time dimension is created with the following view: In this statement, the view timec is defined by performing a UNION ALL of the edgetime view (defined in the prior example) and the user-defined 13th month. The gidt value of 8 was chosen to differentiate the custom member from the standard members. 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 . Skripting på denne siden forbedrer innholdsnavigasjon, men endrer ikke innholdet på noen måte.