Flytte Gjennomsnittet Sql Orakel

SQL-funksjoner SQL-funksjoner er bygd inn i Oracle Database og er tilgjengelige for bruk i forskjellige relevante SQL-setninger. Ikke forveksle SQL-funksjoner med brukerdefinerte funksjoner skrevet i PLSQL. Hvis du ringer en SQL-funksjon med et argument av en datatype enn datatypen som forventes av SQL-funksjonen, forsøker Oracle å konvertere argumentet til den forventede datatypen før du utfører SQL-funksjonen. Hvis du ringer en SQL-funksjon med null-argument, returnerer SQL-funksjonen null automatisk. De eneste SQL-funksjonene som ikke nødvendigvis følger denne oppførselen, er CONCAT. NVL. ERSTATTE. og REGEXPREPLACE. De kombinerte verdiene til NLSCOMP - og NLSSORT-innstillingene bestemmer reglene som karakterer sorteres og sammenlignes med. Hvis NLSCOMP er satt til LINGUISTIC for databasen, blir alle enheter i dette kapitlet tolket i henhold til reglene som er spesifisert av NLSSORT-parameteren. Hvis NLSCOMP ikke er satt til LINGUISTIC. da tolkes funksjonene uten hensyn til NLSSORT-innstillingen. NLSSORT kan eksplisitt settes. Hvis det ikke er angitt eksplisitt, kommer det fra NLSLANGUAGE. Vennligst se Oracle Database Globalization Support Guide for mer informasjon om disse innstillingene. I syntaksdiagrammer for SQL-funksjoner angis argumenter av deres datatyper. Når parameterfunksjonen vises i SQL-syntaks, erstatt den med en av funksjonene som er beskrevet i denne delen. Funksjoner grupperes av datatyper av deres argumenter og deres returverdier. Når du bruker SQL-funksjoner til LOB-kolonner, oppretter Oracle Database midlertidige LOB'er under SQL - og PLSQL-behandling. Du bør sørge for at midlertidig tabellplasskvote er tilstrekkelig til å lagre disse midlertidige LOB for din søknad. Brukerdefinerte funksjoner for informasjon om brukerfunksjoner og Datakonvertering for implisitt konvertering av datatyper Oracle Text Reference for informasjon om funksjoner som brukes med Oracle Text Oracle Data Mining Application Utviklerveiledning for informasjon om hyppige elementsettingsfunksjoner som brukes med Oracle Data Mining Syntaxen som viser kategoriene av funksjonene følger: Avsnittene som følger følger de innebygde SQL-funksjonene i hver av gruppene som er illustrert i de foregående diagrammene, bortsett fra brukerdefinerte funksjoner. Alle de innebygde SQL-funksjonene beskrives deretter i alfabetisk rekkefølge. Enkeltradningsfunksjoner Enkeltradsfunksjoner returnerer en enkelt resultatrader for hver rad i et forespurt tabell eller visning. Disse funksjonene kan vises i utvalgslister, WHERE-klausuler, START MED og CONNECT BY-klausuler og HAVING-klausuler. Numeriske funksjoner Numeriske funksjoner aksepterer numerisk inngang og returnerer numeriske verdier. De fleste numeriske funksjoner som returnerer NUMBER-verdier som er nøyaktige til 38 desimaltall. De transcendentale funksjonene COS. COSH. EXP. LN. LOGG. SYND. SINH. ROT. TAN. og TANH er nøyaktig til 36 desimaltall. Transcendental funksjonene ACOS. SOM I. BRUNFARGE. og ATAN2 er nøyaktig til 30 desimaltall. De numeriske funksjonene er: Tegn Funksjoner Retur Karakter Verdier Karakterfunksjoner som returnerer tegnverdier returverdier av følgende datatyper med mindre annet er dokumentert: Hvis inngangsargumentet er CHAR eller VARCHAR2. så returneres verdien VARCHAR2. Hvis inngangsargumentet er NCHAR eller NVARCHAR2. da returneres verdien NVARCHAR2. Lengden på verdien returnert av funksjonen er begrenset av den maksimale lengden på datatypen som returneres. For funksjoner som returnerer CHAR eller VARCHAR2. Hvis lengden på returverdien overstiger grensen, avkortes Oracle Database og returnerer resultatet uten en feilmelding. For funksjoner som returnerer CLOB-verdier, hvis lengden på returverdiene overstiger grensen, øker Oracle en feil og returnerer ingen data. Karakterfunksjonene som returnerer karakterverdier er: NLS Karakterfunksjoner NLS-karakterfunksjonene returnerer informasjon om tegnsettet. NLS-tegnfunksjonene er: Karakterfunksjoner Returnerende nummerverdier Karakterfunksjoner som returnerer tallverdier kan ta som argument ethvert tegn datatype. Tegnet funksjonene som returnerer tallverdier er: Datetime Funksjoner Datetime funksjoner opererer på dato (DATE), tidsstempel (TIMESTAMP. TIMESTAMP WITH TIME ZONE. Og TIMESTAMP WITH LOCAL TIME ZONE), og intervall (INTERVAL DAG TIL ANDRE INTERVALÅR TIL MÅNED) verdier. Noen av datetime-funksjonene ble designet for datatypen Oracle DATE (ADDMONTHS. CURRENTDATE. LASTDAY. NEWTIME og NEXTDAY). Hvis du oppgir en tidsstempelverdi som argument, konverterer Oracle Database interntype til en DATE-verdi og returnerer en DATE-verdi. Unntakene er MONTHSBETWEEN-funksjonen, som returnerer et nummer, og ROUND og TRUNC-funksjonene, som ikke aksepterer tidsstempel eller intervallverdier i det hele tatt. De resterende datetime-funksjonene ble utformet for å akseptere noen av de tre typer data (dato, tidsstempel og intervall) og å returnere en verdi av en av disse typene. Datetime-funksjonene er: Generelle sammenligningsfunksjoner De generelle sammenligningsfunksjonene bestemmer den største og minste verdien fra et sett med verdier. De generelle sammenligningsfunksjonene er: Konverteringsfunksjoner Konverteringsfunksjoner konverterer en verdi fra en datatype til en annen. Vanligvis følger skjemaet til funksjonsnavnene konvensjonens datatype til datatype. Den første datatypen er inngangsdatatypen. Den andre datatypen er utdata datatypen. SQL-konverteringsfunksjonene er: Store objektfunksjoner De store objektfunksjonene opererer på LOBs. De store objektfunksjonene er: Samlingsfunksjoner Samlingsfunksjonene opererer på innlejrede tabeller og varianter. SQL-samlingsfunksjonene er: Hierarkisk funksjon Den hierarkiske funksjonen bruker hierarkisk stiinformasjon til et resultatsett. Data Mining Funksjoner Data mining funksjonene fungerer på modeller som er bygget ved hjelp av DBMSDATAMINING pakken eller Oracle Data Mining Java API. SQL data mining funksjoner er: XML Funksjoner XML-funksjonene opererer på eller returnerer XML-dokumenter eller fragmenter. Hvis du vil ha mer informasjon om valg og spørring av XML-data ved hjelp av disse funksjonene, inkludert informasjon om formateringsutdata, vennligst se Oracle XML DB Developers Guide. SQL XML-funksjonene er: Kodings - og dekodingsfunksjoner Kodings - og dekodingsfunksjonene lar deg inspisere og avkode data i databasen. NULL-relaterte funksjoner De NULL-relaterte funksjonene gjør det mulig å håndtere null. NULL-relaterte funksjoner er: Miljø - og identifikasjonsfunksjoner Miljø - og identifiseringsfunksjonene gir informasjon om forekomsten og økten. Disse funksjonene er: Aggregate Funksjoner Aggregate funksjoner returnerer en enkelt resultatrad basert på grupper av rader, i stedet for på enkelte rader. Samlede funksjoner kan vises i utvalgte lister og i ORDER BY og HAVING-klausuler. De brukes vanligvis med GROUP BY-klausulen i en SELECT-setning, hvor Oracle Database deler rader av et forespurt tabell eller visning i grupper. I en spørring som inneholder en GROUP BY-setning, kan elementene i seleksjonslisten være aggregatfunksjoner, GROUP BY-uttrykk, konstanter eller uttrykk som involverer en av disse. Oracle bruker aggregatfunksjonene til hver gruppe av rader og returnerer en enkelt rad for hver gruppe. Hvis du unnlater GROUP BY-klausulen, bruker Oracle aggregatfunksjoner i valglisten til alle rader i spørrekategorien eller visningen. Du bruker aggregatfunksjoner i HAVING-klausulen for å eliminere grupper fra utgangen basert på resultatene av aggregatfunksjonene, i stedet for på verdiene for de enkelte radene i spørrekategorien eller visningen. Bruk av GROUP BY-klausulen: Eksempler og HAVING-klausulen for mer informasjon om GROUP BY-klausulen og HAVING-klausuler i spørringer og undersøkelser Mange (men ikke alle) aggregatfunksjoner som tar et enkelt argument, godtar disse klausulene: DISTINCT fører til at en samlet funksjon vurderes bare forskjellige verdier av argumentet uttrykket. ALLE forårsaker en samlet funksjon for å vurdere alle verdier, inkludert alle duplikater. For eksempel er DISTINCT-gjennomsnittet på 1, 1, 1 og 3 2. ALLE gjennomsnittet er 1,5. Hvis du ikke angir verken, er standard ALL. Alle aggregatfunksjoner unntatt COUNT () og GROUPING ignorer nulls. Du kan bruke NVL-funksjonen i argumentet til en aggregatfunksjon for å erstatte en verdi for null. COUNT returnerer aldri null, men returnerer enten et tall eller null. For alle gjenværende aggregatfunksjoner, hvis datasettet ikke inneholder noen rader, eller bare inneholder rader med nuller som argumenter for aggregatfunksjonen, returnerer funksjonen null. Aggregatet fungerer MIN. MAX. SUM. AVG. TELLE. Varians. og STDDEV. når etterfulgt av KEEP søkeordet, kan brukes i forbindelse med FIRST eller LAST-funksjonen for å operere på et sett med verdier fra et sett med rader som rangerer som FIRST eller LAST i forhold til en gitt sorteringsspesifikasjon. Vennligst se FIRST for mer informasjon. Du kan hekke aggregatfunksjoner. Eksempelvis beregner følgende eksempel gjennomsnittet av de maksimale lønnene til alle avdelingene i prøveskjemaet: Denne beregningen vurderer det indre aggregatet (MAX (lønn)) for hver gruppe definert av GROUP BY-klausulen (avdelingstid) og aggregater resultatene igjen. De samlede funksjonene er: Analytiske funksjoner Analytiske funksjoner beregner en samlet verdi basert på en gruppe rader. De adskiller seg fra aggregerte funksjoner ved at de returnerer flere rader for hver gruppe. Gruppen av rader kalles et vindu og er definert av analyticclause. For hver rad er et glidende vindu av rader definert. Vinduet bestemmer rekkevidden av rader som brukes til å utføre beregningene for den nåværende raden. Vinduestørrelser kan baseres på enten et fysisk antall rader eller et logisk intervall som tid. Analytiske funksjoner er det siste settet av operasjoner som utføres i en spørring, bortsett fra den endelige ORDER BY-klausulen. Alle blir sammen og alle der. GRUPPE AV. og HAVING klausuler er ferdig før de analytiske funksjonene blir behandlet. Derfor kan analytiske funksjoner bare vises i valglisten eller BESTILL BY-klausulen. Analytiske funksjoner blir ofte brukt til å beregne kumulative, flytte, sentrert og rapporterende aggregater. Jeg gjorde noen Googling og kunne ikke finne et svar på dette spørsmålet senere enn noen få år siden, så jeg trodde Id spør. Oracle RAC-funksjonen tilbyr lastbalansering for både lese - og skrivetransaksjoner, samt utrangering og høy tilgjengelighet uten nedetid (i hvert fall som jeg forstår det - skulle distribuere våre første databaser som bruker RAC, så vel se hvordan Det går). Er det noen SQL Server-funksjonssett (eller tredjepartskomponent du kan installere på toppen) som gir tilsvarende funksjonalitet. Vi har alltid brukt Windows-klynging, hvor en failover-hendelse forårsaker ca. 20-30 sekunder med SQL-nedetid - alltid tålelig, men ikke ideell. Nå, med AlwaysOn i SQL 2012, krymper SQL Server det til omtrent 15 sekunder og legger til konseptet av skrivebeskyttet sekundære databaser, men de krever fortsatt at skrivetransaksjoner kveles gjennom et enkelt tilkoblingspunkt (mye bedre, siden mange transaksjoner er bare les, men likevel ikke egentlig laste balansering), og i tilfelle et knutepunktfeil eller behovet for å lappe, er det fortsatt stilletid. Jeg antar at det er bare mer nysgjerrighet - jeg føler at dette er det eneste området som SQL Server faller bak Oracle (minst blant funksjonene som jeg personlig har sett). Jeg ønsket å se om det finnes alternativer der ute for å lukke dette gapet og muligens forbedre vår egen SQL Server-distribusjon mens vi venter på Microsofts tilsvarende funksjon som skal legges til - kanskje i SQL 20142015 spurte 13. juli klokken 22:04 rwmnau igjen, jeg foreslår at du holder din glødende ros av RAC til du har implementert den og har brukt den i noen måneder. -) Du kan være riktig det kan være alt det lover og fungerer perfekt for deg. Men du kan bli bamboozled av den skinnende glitteren på boksen. -) ndash Aaron Bertrand 9830 14 juli 12 på 2: 5122 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 vinduberegninger Linjær regresjonsstatistikk Rangeringsfunksjoner inkluderer kumulative distribusjoner, prosentranger og N-fliser. Flytte vindu beregninger lar deg finne bevegelige og kumulative aggregasjoner, for eksempel summer og gjennomsnitt. Laglead analyse muliggjør direkte mellomrange referanser, slik at du kan beregne period-to-period 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. Avhengig av definisjonen kan vinduet bevege seg i en eller begge ender. For eksempel vil et vindu definert for en kumulativ sum-funksjon ha sin startrute festet på den første raden i partisjonen, og den enderende raden vil glide fra startpunktet helt til den siste raden i partisjonen. I motsetning til dette ville et vindu definert for et bevegelig gjennomsnittssted ha både start - og sluttpunktslid slik at de opprettholder et konstant fysisk eller logisk område. Et vindu kan settes så stort som alle rader i en partisjon eller bare et skyvevindu på en rad i en partisjon. Når et vindu er nær en kantlinje, returnerer funksjonen resultater for bare tilgjengelige rader, i stedet for å advare deg om at resultatene ikke er det du vil ha. Når du bruker vindusfunksjoner, er den nåværende raden inkludert under beregningene, så du bør kun spesifisere (n -1) når du arbeider med n elementer. Hver beregning utført med en analytisk funksjon er basert på en nåværende rad i en partisjon. Den nåværende raden tjener som referansepunkt som bestemmer start og slutt på vinduet. For eksempel kan en sentrert glidende gjennomsnittlig beregning defineres med et vindu som holder nåværende rad, de seks foregående radene og de følgende seks radene. Dette ville skape et glidende vindu med 13 rader, som vist i figur 22-2. Figur 22-2 Skyvevindueksempel Rangering, vindsetting og rapporteringsfunksjoner Denne delen illustrerer de grunnleggende analytiske funksjonene for rangering, windowing og rapportering. Eksempel linjær regresjonsberegning I dette eksemplet beregner vi en regresjonslinje med ordinært minste kvadrat som uttrykker mengden som er solgt av et produkt som en lineær funksjon av produktlisten. Beregningene er gruppert etter salgskanal. Verdiene SLOPE. INTCPT. RSQR er henholdsvis helling, avskjæring og bestemmelseskoeffisient av regresjonslinjen. Verdien (heltall) COUNT er antall produkter i hver kanal for hvem både solgt kvantum og listeprisdata er tilgjengelige. Statistiske aggregater Oracle gir et sett med SQL statistiske funksjoner og en statistikkpakke, DBMSSTATFUNCS. Denne delen viser noen av de nye funksjonene sammen med grunnleggende syntaks. Beskrivende statistikk Du kan beregne følgende beskrivende statistikk: Median av en datasettmodus for et datasett Du kan beregne følgende parametriske statistikk: Spearmans rho-koeffisient Kendalls tau-b Koeffisient I tillegg til funksjonene har denne utgivelsen en PLSQL-pakke, DBMSSTATFUNCS . Den inneholder den beskrivende statistiske funksjonen SAMMENDRAG sammen med funksjoner for å støtte distribusjonsmontering. Sammendragsfunksjonen oppsummerer en numerisk kolonne av et bord med en rekke beskrivende statistikker. De fem distribusjonsfunksjonene støtter normal, uniform, Weibull, Poisson og eksponentielle distribusjoner. Brukerdefinerte aggregater Oracle tilbyr et anlegg for å lage dine egne funksjoner, kalt brukerdefinerte aggregatfunksjoner. Disse funksjonene er skrevet i programmeringsspråk som PLSQL, Java og C, og kan brukes som analytiske funksjoner eller aggregater i materialiserte visninger. Se Oracle Database Data Cartridge Developers Guide for ytterligere informasjon om syntaks og begrensninger. Fordelene ved disse funksjonene er: Meget komplekse funksjoner kan programmeres ved hjelp av et fullt prosessorisk språk. Høyere skalerbarhet enn andre teknikker når brukerdefinerte funksjoner er programmert for parallellbehandling. Objektdatatyper kan behandles. Som et enkelt eksempel på en brukerdefinert aggregatfunksjon, bør du vurdere skråstatistikken. Denne beregningen måler hvis et datasett har en skrå fordeling om dens gjennomsnitt. Det vil fortelle deg om en hale av fordelingen er betydelig større enn den andre. Hvis du opprettet et brukerdefinert aggregat som kalles utskew og brukt det til kredittgrenseverdiene i forrige eksempel, kan SQL-setningen og resultatene se slik ut: Før du bygger brukerdefinerte aggregatfunksjoner, bør du vurdere om dine behov kan oppfylles i vanlig SQL. Mange komplekse beregninger er mulige direkte i SQL, spesielt ved å bruke CASE-uttrykket. Å holde fast med vanlig SQL vil muliggjøre enklere utvikling, og mange spørringsoperasjoner er allerede godt parallellisert i SQL. Selv det tidligere eksempelet, skråstatistikken, kan opprettes ved hjelp av standard, om enn langvarig, SQL. Dreieoperasjoner D-ataene som returneres av forretningsinformasjonsforespørsler, er ofte mest anvendbare hvis de presenteres i et krysstabulært format. Pivotclause av SELECT-setningen lar deg skrive crosstabuleringsforespørsler som roterer rader i kolonner, aggregere data i rotasjonsprosessen. Pivoting er en viktig teknikk i datalager. I den forvandler du flere rader med innspill til færre og generelt bredere rader i datalageret. Når du svinger, blir en aggregeringsoperatør søkt for hvert element i pivotkolonneverdislisten. Pivottkolonnen kan ikke inneholde et vilkårlig uttrykk. Hvis du må svinge på et uttrykk, bør du alias uttrykket i en visning før PIVOT-operasjonen. Den grunnleggende syntaksen er som følger: For å illustrere bruken av sving, opprett følgende visning som grunnlag for senere eksempler: Eksempel: Dreiing Følgende setning illustrerer en typisk sving på kanalkolonnen: Merk at utgangen har opprettet fire nye aliased kolonner , DIREKTE SALG. INTERNETSALES. CATALOGSALES. og TELESALES. en for hver av pivottverdiene. Utgangen er summen. Hvis ingen alias er oppgitt, vil kolonneoverskriften være verdiene til IN-listen. Vri på flere kolonner Du kan pivotere på mer enn en kolonne. Følgende setning illustrerer en typisk flere kolonnepivot: Merk at dette eksemplet angir en IN-liste med flere kolonner med kolonneoverskrifter utformet for å matche IN-listen-medlemmene. Vridning: Flere aggregater Du kan pivotere med flere aggregater, som vist i følgende eksempel: Merk at spørringen oppretter kolonneoverskrifter ved å sammenkoble dreieverdiene (eller aliaset) med aliaset for aggregatfunksjonen, pluss en understrekning. Distinguishing PIVOT-Generated Nulls from Nulls i Source Data Du kan skille mellom nullverdier som genereres fra bruk av PIVOT og de som eksisterer i kildedataene. Følgende eksempel illustrerer nuller som PIVOT genererer. Følgende spørring returnerer rader med 5 kolonner, kolonne prodid. og pivot resulterende kolonner Q1. Q1COUNTTOTAL. Q2. Q2COUNTTOTAL. For hver unik verdi av prodid. Q1COUNTTOTAL returnerer totalt antall rader hvis qtr-verdi er Q1. det vil si, og Q2COUNTTOTAL returnerer totalt antall rader hvis qtr-verdi er Q2. Anta at vi har en bordsalg2 av følgende struktur: Fra resultatet vet vi at for prodid 100 er det to salgsrader for kvartalet Q1. og 1 salgsstrek for kvartalet Q2 for prodid 200, er det 1 salgsstrek for kvartalet Q1. og ingen salgsstrek for kvartalet 2. Så i Q2COUNTTOTAL. Du kan identifisere at NULLlt1gt kommer fra en rad i den opprinnelige tabellen, hvis mål er null verdi, mens NULLlt2gt skyldes at ingen rad er tilstede i den opprinnelige tabellen for prodid 200 i kvartalet Q2. Unpivoting Operations En unpivot reverserer ikke en PIVOT-operasjon. I stedet roterer det data fra kolonner til rader. Hvis du arbeider med pivoted data, kan en UNPIVOT-operasjon ikke reversere noen aggregeringer som er gjort av PIVOT eller på noen annen måte. For å illustrere unpivoting, må du først opprette et pivotbord som inneholder fire kolonner, for kvartaler av året: Tabellinnholdene ligner på følgende: Den følgende UNPIVOT-operasjonen roterer kvartkolonnene i rader. For hvert produkt vil det være fire rader, en for hvert kvartal. Legg merke til bruken av INCLUDE NULLS i dette eksemplet. Du kan også bruke EXCLUDE NULLS. som er standardinnstillingen. I tillegg kan du også unpivot bruke to kolonner, som i følgende: Wildcard og Subquery Pivoting med XML-operasjoner Hvis du vil bruke et wildcard-argument eller subquery i svingbare kolonnene, kan du gjøre det med PIVOT XML-syntaks. Med PIVOT XML er utdataene fra operasjonen riktig formatert XML. Følgende eksempel illustrerer ved hjelp av wildcard søkeordet, noe. Den sender ut XML som inneholder alle kanalverdier i salgsvisning: Merk at søkeordet ALLE er bare tilgjengelig i PIVOT-operasjoner som en del av en XML-operasjon. Denne utdataen inneholder data for tilfeller der kanalen finnes i datasettet. Vær også oppmerksom på at aggregeringsfunksjoner må angi en GROUP BY-klausul for å returnere flere verdier, men pivotclause inneholder ikke en eksplisitt GROUP BY-klausul. I stedet utfører pivotclause en implisitt GROUP BY. Følgende eksempel illustrerer ved hjelp av en underforespørsel. Den utsender XML som inneholder alle kanalverdier og salgsdata som tilsvarer hver kanal: Utgangen tetter dataene for å inkludere alle mulige kanaler for hvert produkt. Datadensifisering for rapporteringsdata lagres normalt i sparsom form. Det vil si at hvis ingen verdi eksisterer for en gitt kombinasjon av dimensjonsverdier, eksisterer ingen rad i faktatabellen. Du vil imidlertid kanskje se dataene i tett form, med rader for alle kombinasjoner av dimensjonsverdier som vises selv når det ikke finnes noen faktumdata for dem. For eksempel, hvis et produkt ikke solgte i en bestemt tidsperiode, vil du kanskje fortsatt se produktet for den perioden med null salgsverdi ved siden av den. Videre kan tidsserier beregninger utføres lettest når dataene er tette langs tidsdimensjonen. Dette skyldes at tette data vil fylle et konsistent antall rader for hver periode, noe som igjen gjør det enkelt å bruke de analytiske vinduene med fysiske forskyvninger. Datadensifisering er prosessen med å konvertere sparsomme data til tett form. For å overvinne problemet med sparsity, kan du bruke et partisjonert ytre sammenføyning for å fylle hullene i en tidsserie eller en annen dimensjon. En slik samling utvider den konvensjonelle ytre sammenkoblingssyntaxen ved å påføre den ytre sammenføyning til hver logisk partisjon definert i en forespørsel. Oracle skiller logisk partiene i spørringen din basert på uttrykket du angir i PARTITION BY-klausulen. Resultatet av en partisjonert ytre sammenføyning er en UNION av ytre leddene til hver av partisjonene i det logisk oppdelte bordet med bordet på den andre siden av festet. Legg merke til at du kan bruke denne typen til å fylle hullene i en hvilken som helst dimensjon, ikke bare tidsdimensjonen. De fleste eksemplene her fokuserer på tidsdimensjonen, fordi det er den dimensjonen som oftest brukes som grunnlag for sammenligninger. Partisjon Bli med syntaks Syntaksen for partisjonert ytre slett utvider ANSI SQL JOIN-klausulen med setningen PARTISJON BY etterfulgt av en uttrykksliste. Uttrykkene i listen angir gruppen som det ytre medlemmet er brukt på. Følgende er de to syntaksformene som vanligvis brukes for partisjonert ytre sammenføyning: Merk at FULL OUTER JOIN ikke støttes med en partisjonert ytre sammenføyning. Eksempel på sparsomme data En typisk situasjon med en sparsom dimensjon er vist i følgende eksempel, som beregner det ukentlige salget og det årlige salg for produktet Bounce for uker 20-30 i 2000 og 2001: I dette eksemplet ville forvente 22 rader med data (11 uker hver fra 2 år) dersom dataene var tette. Vi får imidlertid kun 18 rader fordi uker 25 og 26 mangler i 2000 og uker 26 og 28 i 2001. Fylling av hull i data Vi kan ta de sparsomme dataene fra foregående spørring og gjøre en partisjonert ytre sammenføyning med et tett sett med tidsdata. I det følgende spørsmålet alias vi vårt opprinnelige spørsmål som v, og vi velger data fra tidstabellen, som vi alias som t. Her henter vi 22 rader fordi det ikke er noen hull i serien. De fire tilsatte radene har hver 0 som deres salgsverdi satt til 0 ved hjelp av NVL-funksjonen. Merk at i denne spørringen ble en WHERE-tilstand plassert i uker mellom 20 og 30 i inlinevisningen for tidsdimensjonen. Dette ble introdusert for å holde resultatet satt lite. Fylle hull i to dimensjoner N-dimensjonale data vises vanligvis som en tett 2-dimensjonell tverrfane med (n - 2) sidedimensjoner. Dette krever at alle dimensjonsverdier for de to dimensjonene som vises i kryssfanen fylles inn. Følgende er et annet eksempel hvor den fordelte ytre sammenkoblingsfunksjonen kan brukes til å fylle hullene i to dimensjoner: I denne spørringen er WITH subquery factoring-klausulen v1 oppsummerer salgsdata på produkt, land og årsnivå. Dette resultatet er sparsomt, men brukere vil kanskje se hele landet, årkombinasjoner for hvert produkt. For å oppnå dette, tar vi hver partisjon av v1 basert på produktverdier og ytterligere bli med på landsdimensjonen først. Dette vil gi oss alle verdier av land for hvert produkt. Vi tar da det resultatet og partisjonerer det på produkt og land verdier og deretter ytre bli med på tid dimensjon. Dette gir oss alle tidsverdier for hver produkt og landkombinasjon. Fylle hull i en beholdningstabel En oversiktstabell sporer vanligvis antall enheter tilgjengelig for ulike produkter. Denne tabellen er sparsom: den lagrer bare en rad for et produkt når det er en hendelse. For et salgstabell er arrangementet et salg, og for oppslagstabellen er hendelsen en endring i mengden som er tilgjengelig for et produkt. For eksempel, tenk på følgende oppslagstabell: Innholdstabellen har nå følgende rader: For rapporteringsformål, kan brukere kanskje se denne beholdningsdata forskjellig. For eksempel vil de kanskje se alle tidsverdier for hvert produkt. Dette kan oppnås ved å bruke partisjonert ytre sammenføyning. I tillegg, for de nylig innførte rader med manglende tidsperioder, kan brukere kanskje se verdiene for mengden enhetskolonne som skal overføres fra den siste eksisterende tidsperioden. Sistnevnte kan oppnås ved bruk av analytisk vindusfunksjon LASTVALUE-verdi. Her er spørringen og ønsket utdata: Det interne spørsmålet beregner en partisjonert ytre tilkobling til tiden innen hvert produkt. Det indre spørsmålet tetter dataene på tidsdimensjonen (det betyr at tidsdimensjonen nå har en rad for hver ukedag). Målkolonnekvantiteten vil imidlertid ha nuller for de nylig tilførte raderne (se utgangen i kolonnekvantiteten i følgende resultater. Den ytre spørringen bruker den analytiske funksjonen LASTVALUE. Ved å bruke denne funksjonen partisjonerer dataene etter produkt og bestiller dataene på tidsdimensjonskolonne (timeid). For hver rad finner funksjonen den siste ikke-nullverdien i vinduet på grunn av alternativet IGNORE NULLS. som du kan bruke med både LASTVALUE og FIRSTVALUE. Vi ser ønsket utgang i kolonnen repeatedquantity i Følgende utdata: Datamengder for datamengder til utfyllingsfel Eksempler i forrige avsnitt illustrerer hvordan du bruker partisjonert ytre sammenføyning for å fylle hull i en eller flere dimensjoner. Resultatsettene som er produsert av partisjonert ytre sammenføyning, har nullverdier for kolonner som ikke er inkludert i PARTITION BY-listen. Disse er vanligvis målekolonner. Brukere kan benytte seg av analytiske SQL-funksjoner for å erstatte disse nullverdiene med en null-verdi. For eksempel er følgende q Du beregner månedlige totals for produkter 64 MB minne og DVD-R-plater (produkt ID 122 og 136) for år 2000. Det bruker partisjonert ytre sammenføyning for å tette dataene for alle måneder. For de manglende månedene bruker den den analytiske SQL-funksjonen AVG til å beregne salget og enhetene som gjennomsnittet av månedene da produktet ble solgt. Hvis du arbeider i SQLPlus, bryter følgende to kommandoer kolonneoverskriftene for større lesbarhet av resultater: Tidsserieregninger på Densified Data Densificatio n er ikke bare for rapporteringsformål. Det muliggjør også bestemte typer beregninger, spesielt tidsrekkeberegninger. Tidsserier beregninger er lettere når dataene er tette langs tidsdimensjonen. Tette data har et konsekvent antall rader for hver tidsperiode, noe som igjen gjør det enkelt å bruke analytiske vindufunksjoner med fysiske forskyvninger. 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 . Scripting on this page enhances content navigation, but does not change the content in any way.