Oracle Bewegende Gemiddelde Query

Bewegende gemiddelde in T-SQL n gemeenskaplike berekening tendens analise is die bewegende (of rollende) gemiddelde. 'N bewegende gemiddelde is die gemiddeld van die byvoorbeeld laaste 10 rye. Die bewegende gemiddelde wys 'n meer gladde kurwe as die werklike waardes, meer so met 'n langer tydperk vir die bewegende gemiddelde, maak dit 'n goeie hulpmiddel vir tendens analise. Hierdie blog post sal wys hoe om te bereken bewegende gemiddelde in T-SQL. Verskillende metodes sal gebruik word, afhangende van die weergawe van SQL Server. Die onderstaande grafiek toon die smoothing effek (rooi lyn) met 'n 200 dae bewegende gemiddelde. Die voorraadkwotasies is die blou lyn. Die langtermyn tendens is duidelik sigbaar. T-SQL Moving Avergage 200 dae onderstaande demonstrasie vereis die TAdb databasis wat geskep kan word met die script hier geleë. In die komende voorbeeld sal ons 'n bewegende gemiddelde te bereken vir die afgelope 20 dae. Afhangende van die weergawe van SQL Server, sal daar 'n ander metode om die berekening te doen. En, soos ons later sal sien, die nuwer weergawes van SQL Server het funksies in staat stel 'n baie meer doeltreffende berekening. SQL Server 2012 en later Moving Gemiddelde Hierdie weergawe maak gebruik van 'n totaal venster funksie. Wat is nuut in SQL 2012 is die moontlikheid om die grootte van die venster beperk deur hoeveel rye voor die venster moet bevat: Rye voorafgaande is 19, want ons het die huidige ry sowel in die berekening in te sluit. Soos jy kan sien, die berekening van bewegende gemiddelde in SQL Server 2012 is redelik eenvoudig. Die onderstaande figuur toon die windows beginsel. Huidige ry gemerk met 'n geel. Die venster is gemerk met 'n blou agtergrond. Die bewegende gemiddelde is eenvoudig die gemiddelde van QuoteClose in die blou lyne: T-SQL Moving gemiddelde venster. Die resultate van die berekeninge in ouer weergawes van SQL Server is dieselfde, so hulle sal nie weer gewys word. SQL Server 2005 8211 2008R2 Moving Gemiddelde Hierdie weergawe maak gebruik van 'n gemeenskaplike tafel uitdrukking. Die CTE is self gekla tot die laaste 20 rye vir elke ry te kry: Moving Gemiddelde voor SQL Server 2005 Die pre 2005 weergawe sal 'n links buitenste gebruik te sluit om dieselfde tafel met die laaste 20 rye te kry. Die buitenste tafel kan gesê word dat die venster wat ons wil 'n gemiddelde op te bereken bevat: Performance Vergelijking As ons die drie verskillende metodes gelyktydig hardloop en gaan die gevolglike uitvoering plan, daar is 'n dramatiese verskil in prestasie tussen die metodes: Vergelijking van drie verskillende metodes te bereken bewegende gemiddelde Soos jy kan sien, die windows funksie verbeterings in SQL 2012 maak 'n groot verskil in prestasie. Gebruik Soos in die begin van hierdie post, is bewegende gemiddeldes gebruik as 'n instrument om tendense te illustreer. 'N Algemene benadering is om bewegende gemiddeldes van verskillende lengtes te kombineer, ten einde veranderinge in die kort-, medium - en langtermyn tendense onderskeidelik te sien. Van besondere belang is die kruising van die tendens lyne. Byvoorbeeld, wanneer die kort tendens oor die lang of medium tendens beweeg, dit kan geïnterpreteer word as 'n koopsein in tegniese ontleding. En toe die kort tendens beweeg onder 'n langer tendens lyn, kan dit vertolk word as 'n sell sein. Die onderstaande grafiek toon Kwotasies, Ma20, Ma50 en Ma200. T-SQL Ma20, Ma50, Ma200 koop en verkoop seine. Hierdie blog post is deel van 'n reeks oor tegniese ontleding, TA, in SQL Server. Sien die ander poste hier. Geplaas deur Tomas LindHow 'n SQL bewegende gemiddelde sonder 'n wyser Werk Bereken: As jy besig is met die nuutste weergawes van SQL Server, kan jy die windows funksies gebruik om dieselfde ding te bewerkstellig. Ek gepos word om die updated kode aan die einde van die post. Om hierdie video, ek nog graag die denkproses van anker om 'n datum. Video: 3 daagse bewegende gemiddelde in SQL 'n doeltreffende manier om 'n bewegende gemiddelde in SQL te bereken met behulp van 'n paar truuks op datum ankers stel. Daar is debatte oor die beste manier om 'n SQL bewegende gemiddelde in SQL Server doen. Sommige mense dink daar is tye wanneer 'n wyser is mees doeltreffende. Ander dink dat jy dit alles kan doen in 'n stel wat gebaseer is weg sonder die wyser. Die ander dag het ek gaan 'n bewegende gemiddelde te bereken en my eerste gedagte was om 'n wyser gebruik. Ek het 'n paar vinnige navorsing en het gevind dat hierdie forum vraag: Moving Gemiddelde in TSQL Daar is 'n pos wat 'n subquery met 'n anker datum te help vind die 1 en 2 dag geneutraliseer toon. Hier is die skrif wat jy kan gebruik om die 3 dag SQL Gemiddeld finale uitslag Moving toets. Hier is die finale navraag. Hier is die vraag wat jy sou gebruik met SQL Server 2012. Deel hierdie: Drie Klein korf UDFs: Deel 3 Deur dan. mcclary op 7 April 2013 Inleiding In die finale paaiement in ons reeks oor korf UDFs, gaan die minste intuïtief aan te pak van die drie tipes: die gebruiker gedefinieerde Aggregating funksie. Terwyl hulle is 'n uitdaging om te implementeer, UDAFs is nodig as ons wil werksaamhede waarvoor die onderskeid van kaart-kant v. Verminder side bedrywighede is ondeursigtig aan die gebruiker. As 'n gebruiker 'n navraag skryf, die meeste sou verkies om te fokus op die data theyre probeer om te bereken, nie watter deel van die plan is die bestuur van 'n gegewe funksie. Die UDAF bied ook 'n waardevolle geleentheid om 'n paar van die nuanses van verspreide programmering en parallel databasis bedrywighede oorweeg. Aangesien elke taak in 'n MapReduce werk bedryf in 'n bietjie van 'n vakuum (bv Kaart taak nie weet wat data Map taak B het), 'n UDAF moet uitdruklik vir meer operasionele state as 'n eenvoudige UDF. Wel terug te keer na die idee van 'n eenvoudige bewegende gemiddelde funksie. maar vra jouself af: hoe doen ons bereken 'n bewegende gemiddelde as ons hoef nie die staat of bevel om die data Soos tevore, die kode is beskikbaar op GitHub. maar ook hier die uittreksel van die belangrikste dele. Prefix Sum: Moving Gemiddelde sonder Staat Ten einde 'n bewegende gemiddelde sonder staat te bereken, gaan 'n gespesialiseerde parallel algoritme nodig. Vir bewegende gemiddelde, die quottrickquot is 'n voorvoegsel som gebruik. effektief te hou 'n tafel van die bestuur van totale vir 'n vinnige berekening (en recomputation) van ons bewegende gemiddelde. 'N Volledige bespreking van voorvoegsel somme vir bewegende gemiddeldes is buite lengte van 'n blog post, maar John Jenq bied 'n uitstekende bespreking van die tegniek soos toegepas op CUDA implementering. Wat goed dekking hier is die nodige implementering van 'n paar van die klasse te slaan en werk op ons voorvoegsel som inskrywing in die UDAF. Hier het ons die definisie van ons bewegende gemiddelde klas en die statiese innerlike klas wat dien as 'n inskrywing in ons tafel. Wat is belangrik hier is 'n paar van die veranderlikes wat ons definieer vir elke inskrywing in die tabel: die tyd-indeks of tydperk van die waarde (sy orde), die waarde self, die voorvoegsel som, die deelstring totale, en die bewegende gemiddelde self. Elke inskrywing in ons tafel vereis nie net die huidige waarde van die bewegende gemiddelde bereken, maar ook som van inskrywings in ons bewegende gemiddelde venster. Dit is die paar van hierdie twee waardes wat dit moontlik maak voorvoegsel som metodes om hul magie werk. Bogenoemde is eenvoudig inisialisering roetines: 'n konstruktor, 'n metode om die tafel te herstel, en 'n Boole metode oor die vraag of die voorwerp het 'n voorvoegsel som tafel waarop te werk. Van hier is daar 3 belangrike metodes te ondersoek: voeg, saam te smelt, en afleveringen. Die eerste is intuïtief, soos ons scan rye in korf ons wil om hulle by ons voorvoegsel som tafel. Die tweede is belangrik as gevolg van gedeeltelike samevoeging. Ons kan nie voor die tyd waar hierdie UDAF sal loop sê, en gedeeltelike samevoeging nodig mag wees. Dit wil sê, sy heeltemal moontlik dat sommige waardes deur middel van die UDAF kan hardloop tydens 'n kaart taak, maar dan word geslaag om 'n verminder taak word gekombineer met ander waardes. Die afleveringen metode sal toelaat korf die gedeeltelike uitslae slaag uit die kaart af tot by die vermindering van kant. Die merge metode laat reducers om die resultate van gedeeltelike riffen kombineer vanaf die kaart take. Die eerste deel van dié byvoeging taak is eenvoudig: ons die element toe te voeg tot die lys en werk ons ​​tafels voorvoegsel somme. In die tweede helfte van dié byvoeging funksie, bereken ons bewegende gemiddeldes gebaseer op die voorvoegsel somme. backEntry. prefixSum - - wat geneutraliseer tussen die huidige tabelinskrywing en sy nde voorganger maak die hele ding werk thisEntry. prefixSum: sy hier kan jy die skarnier waarop die algoritme swaaie sien. Die afleveringen metode moet die resultate van ons algoritme te verpak om te slaag om 'n ander geval van dieselfde algoritme, en dit moet doen op 'n tipe wat Hadoop kan afleveringen. In die geval van 'n metode soos som, sou dit relatief eenvoudig wees: ons sou net nodig het om die som te slaag tot op hierdie punt. Maar omdat ons nie seker kan wees of dit byvoorbeeld van ons algoritme al die waardes het gesien, of hulle gesien in die korrekte volgorde, moet ons eintlik om die hele tafel afleveringen. Om dit te doen, skep ons 'n lys van DoubleWritables. pak die grootte venster op sy hoof, en dan elke tydperk en waarde. Dit gee ons 'n struktuur dis maklik om uit te pak en saam te smelt met ander lyste met dieselfde konstruksie. Kombineer die resultate is dalk die mees ingewikkelde ding wat ons nodig het om te hanteer. In die eerste plek gaan ons die saak waarin daar was geen gedeeltelike gevolg geslaag - net terug en voort te gaan. In die tweede plek gaan ons om te sien of hierdie geval van PrefixSumMovingAverage het reeds 'n tafel. As dit nie die geval is, kan ons net pak die serialized gevolg en hanteer dit as ons venster. Die derde geval is die nie-triviale een: As dit byvoorbeeld 'n tafel en ontvang 'n serialized tafel, moet ons hulle saam smelt. Dink aan 'n Verminder taak: as dit ontvang uitgange uit verskeie Map take, wat dit nodig het om almal met mekaar saam te smelt om 'n groter tafel te vorm. Dus, sal saamsmelt baie keer om hierdie resultate te voeg en weer aanmekaar 'n groter tydreekse genoem. Hierdie deel moet kyk vertroud, dit is net soos dié byvoeging metode. Noudat ons nuwe inskrywings in ons tafel, moet ons uit te sorteer deur tydperk en recompute die bewegende gemiddeldes. Trouens, die res van die merge metode is presies soos dié byvoeging metode, sodat ons kan oorweeg om te sorteer en Herbereken in 'n aparte metode. Orkestreer Gedeeltelike Aggregasie Weve het 'n slim klein algoritme vir die berekening bewegende gemiddelde in parallel, maar korf kan niks daarmee te doen nie, tensy ons 'n UDAF wat verstaan ​​hoe om ons algoritme gebruik. Op hierdie punt moet ons begin skryf 'n paar werklike UDAF kode. Soos voorheen, brei ons 'n generiese klas, in hierdie geval GenericUDAFEvaluator. Soos in die geval van 'n UDTF, skep ons ObjectInspectors tipe toetsing te hanteer. Maar, let op dat ons inspekteurs vir verskillende lande: PARTIAL1, PARTIAL2, kompleet, en finale. Hierdie ooreenstem met die verskillende state waarin ons UDAF kan uitvoer. Sedert ons serialized voorvoegsel som tafel isnt dieselfde insette tipe as die waardes van ons byvoeging metode neem, moet ons verskillende tipe toetsing vir elke. Hier is die begin van ons overrided inisialisering funksie. Ons gaan die parameters vir twee modes, PARTIAL1 en voltooi. Hier neem ons aan dat die argumente vir ons UDAF is dieselfde as die gebruiker gaan in 'n navraag: die tydperk, die insette, en die grootte van die venster. As die UDAF byvoorbeeld is die verbruik van die resultate van ons gedeeltelike samevoeging, moet ons 'n ander ObjectInspector. Spesifiek, dié een: Soortgelyk aan die UDTF, moet ons ook tik die ondersoek na die tipe uitset - maar vir beide gedeeltelike en vol samevoeging. In die geval van gedeeltelike samevoeging, was die terugkeer van lyste van DoubleWritables: Maar in die geval van finale of volledige, is die hantering van die tipes wat sal teruggestuur word aan die korf gebruiker, sodat ons nodig het om 'n ander produksie terugkeer. Op pad was om 'n lys van structs dat die tydperk bevat, bewegende gemiddelde en residue terugkeer (sedert hulle is goedkoop om te bereken). Volgende kom metodes te beheer wat gebeur wanneer 'n kaart of Verminder taak is klaar met sy data. In die geval van gedeeltelike samevoeging, moet ons die data afleveringen. In die geval van 'n volledige samevoeging, moet ons die resultaat vir korf gebruikers te verpak. Ons moet ook opdrag gee oor hoe korf die resultate van gedeeltelike samevoeging moet saamsmelt. Gelukkig het ons reeds hierdie hanteer in ons PrefixSumMovingAverage klas, sodat ons kan net noem dat. Natuurlik, samesmelting en serializeren isnt baie nuttig, tensy die UDAF het logika vir iterating oor waardes. Die iteraat metode hanteer hierdie en - as 'n mens sou verwag - geheel en al afhanklik van die PrefixSumMovingAverage klas ons geskep. Samevoeging Buffers: Connecting Algoritmes met die uitvoering van 'n Mens kan sien dat die kode is vir ons UDAF verwys na 'n voorwerp van tipe AggregationBuffer heelwat. Dit is omdat die AggregationBuffer is die koppelvlak wat dit moontlik maak om ons persoonlike PrefixSumMovingAverage klas aan te sluit op Korwe uitvoering raamwerk. Terwyl dit nie die geval neerkom op 'n groot deel van die kode, die gom wat ons logika bind aan Korwe uitvoering raamwerk. Ons implementeer dit as sodanig: Die gebruik van die UDAF Die doel van 'n goeie UDAF is dat, maak nie saak hoe ingewikkeld dit was vir ons te implementeer, sy dat dit eenvoudig vir ons gebruikers. Vir almal wat kode en parallel denke, gebruik van die UDAF is baie eenvoudig: Hier is die toepassing van die UDAF om die bewegende gemiddelde van aankoms vertraging te kry van 'n bepaalde vlug. Dit is 'n baie eenvoudige navraag vir alles wat werk wat ons gedoen het daaronder. Ons kan 'n bietjie meer en hefboom Korwe vermoëns doen om komplekse vorme as kolomme, hier is 'n navraag wat 'n tafel van tijdreeksen as skikkings skep hanteer. WAAR TailNum SOOS N GROEP DEUR TailNum Opsomming Weve gedek allerhande UDFs: van eenvoudige klas uitbreidings wat baie maklik geskryf kan baie ingewikkeld UDAFs wat ons nodig het om te dink oor versprei uitvoering en plan orkestrasie gedoen deur navraag enjins. Met 'n bietjie geluk, het die bespreking wat u verskaf met die vertroue om uit te gaan en te implementeer jou eie UDFs - of ten minste betaal bietjie aandag aan die kompleksiteit van die kinders in gebruik elke dag. Kategorie: Big Data21 SQL vir verwerking en rapportering Behandeling van NULLs as invoer tot Venster Funksies Venster funksies NULL semantiek ooreenstem met die NULL semantiek vir SQL totale funksies. Ander semantiek kan verkry word deur die gebruiker-gedefinieerde funksies, of deur die gebruik van die DECODE of 'n saak uitdrukking binne die venster funksie. Windows Funksies met Logiese Offset n logiese geneutraliseer kan word vermeld met konstantes soos REEKS 10 VOORGAANDE. of 'n uitdrukking wat evalueer om 'n konstante, of deur 'n interval spesifikasie soos REEKS INTERVAL N dag / maand / jaar voor of 'n uitdrukking wat evalueer om 'n interval. Met logiese verreken, kan daar net een uitdrukking in die ORDER BY uitdrukking lys in die funksie wees, met tipe versoenbaar te NUMERIC as geneutraliseer is numeriese, of 'n datum as 'n interval gespesifiseer. Voorbeeld 21-7 Kumulatiewe Totale Function Die volgende is 'n voorbeeld van kumulatiewe amountsold deur kliënt ID deur kwartaal in 1999: In hierdie voorbeeld, die analitiese funksie som definieer, vir elke ry, 'n venster wat begin by die begin van die verdeling (ongeleide VOORGAANDE ) en eindig by verstek teen die huidige ry. Geneste som s is nodig in hierdie voorbeeld aangesien ons vaar 'n bedrag meer as 'n waarde wat op sigself 'n som. Geneste riffen word dikwels gebruik in analitiese totale funksies. Voorbeeld 21-8 Moving Totale Function Hierdie voorbeeld van 'n tyd-gebaseerde venster toon, vir 'n kliënt, die bewegende gemiddelde van verkope vir die huidige maand en voorafgaande twee maande: Let daarop dat die eerste twee rye vir die drie maande bewegende gemiddelde berekening in die uitset data is gebaseer op 'n kleiner interval grootte as gespesifiseerde omdat die venster berekening nie kan bereik verby die opgespoor deur die navraag data. Jy moet die verskillende venster groottes gevind by die grense van gevolg stelle oorweeg. Met ander woorde, moet jy dalk die navraag verander om in te sluit presies wat jy wil. Gesentreer Totale Function berekening te venster totaal funksies gesentreer rondom die huidige ry is eenvoudig. Hierdie voorbeeld bere vir alle kliënte 'n gesentreerde bewegende gemiddelde van verkope vir 'n week in die einde van Desember 1999. Dit vind 'n gemiddeld van die verkope totaal vir die eendag die huidige ry en een dag na die huidige ry insluitend die huidige ry sowel voorafgaande. Voorbeeld 21-9 gesentreerde Totale Die begin - en rye vir elke produkte gesentreer bewegende gemiddelde berekening in die uitset data is gebaseer op slegs twee dae, sedert die venster berekening nie kan bereik verby die opgespoor deur die navraag data. Gebruikers moet die ander venster groottes gevind by die grense van gevolg stelle oorweeg: die navraag kan aangepas moet word. Windows Totale funksies in die teenwoordigheid van Duplikate Die volgende voorbeeld illustreer hoe venster totaal funksies te bereken waardes wanneer daar duplikate, dit is, wanneer verskeie rye terug vir 'n enkele bestel waarde. Die navraag gekry die verkoop aan verskeie kliënte hoeveelheid tydens 'n bepaalde tyd reeks. (Alhoewel ons 'n geïntegreerde siening te gebruik om ons basis datastel te definieer, dit het geen spesiale betekenis en kan geïgnoreer word.) Die vraag definieer 'n bewegende venster wat strek vanaf die datum van die huidige ry tot 10 dae earlier. Note dat die reeks navraag word gebruik om die windows klousule van hierdie voorbeeld te definieer. Dit beteken dat die venster potensieel kan hou baie rye vir elke waarde in die reeks. In hierdie geval, is daar drie pare rye met dubbele datum waardes. Voorbeeld 21-10 windows Totale Funksies met Logiese Offsets In die produksie van hierdie voorbeeld, al datums behalwe 6 Mei en 12 Mei terugkeer twee rye met dubbele datums. Kyk na die kommentaar nommers aan die regterkant van die uitvoer om te sien hoe die waardes bereken. Let daarop dat elke groep in hakies verteenwoordig die waardes teruggekeer vir 'n enkele dag. Let daarop dat hierdie voorbeeld is van toepassing slegs wanneer jy die reeks navraag eerder gebruik as die plantrye gebruik navraag. Dit is ook belangrik om te onthou dat met die reeks. jy kan net 1 ORDER BY uitdrukking in die analitiese funksies ORDER BY klousule. Met Die rye navraag, kan jy verskeie volgorde gebruik deur uitdrukkings in die analitiese funksies ORDER BY klousule. Wisselende venster grootte vir elke ry Daar is situasies waar dit nuttig om die grootte van 'n venster vir elke ry, gebaseer op 'n bepaalde toestand wissel. Byvoorbeeld, kan jy die venster groter vir sekere datums en kleiner vir ander te maak. Aanvaar dat jy die bewegende gemiddelde van aandele prys te bereken oor drie werksdae. As jy 'n gelyke aantal rye vir elke dag vir al werksdae en geen nie-werksdae gestoor word, dan kan jy 'n fisiese venster funksie gebruik. Maar, as die bekende voorwaardes nie nagekom word nie, kan jy nog steeds bereken 'n bewegende gemiddelde met behulp van 'n uitdrukking in die venster grootte parameters. Uitdrukkings in 'n venster grootte spesifikasie kan gemaak word in verskillende bronne. die uitdrukking kan 'n verwysing na 'n kolom in 'n tabel, soos 'n rooster. Dit kan ook 'n funksie wat die toepaslike grens vir die venster wat gebaseer is op waardes in die huidige ry terug wees. Die volgende verklaring vir 'n hipotetiese aandeelprys databasis gebruik 'n gebruiker-gedefinieerde funksie in sy reeks klousule venster grootte te stel: In hierdie stelling, ttimekey is 'n datum in die veld. Hier, FN kan 'n PL / SQL funksie met die volgende spesifikasie wees: 4 As ttimekey is Maandag, Dinsdag Indien enige van die vorige dae is openbare vakansiedae, dit pas die telling gepas. Let daarop dat, wanneer venster gespesifiseer deur 'n nommer in 'n venster funksie met ORDER BY op 'n datum kolom, dan is dit omgeskakel word na die aantal dae beteken. Jy kan ook gebruik word om die interval letterlike omskakeling funksie, soos NUMTODSINTERVAL (FN (ttimekey), DAG) in plaas van net fn (ttimekey) om dieselfde ding beteken. Jy kan ook skryf 'n PL / SQL funksie wat 'n interval data type waarde terug. Windows Totale Funksies met Fisiese Offsets vir Windows uitgedruk in rye, moet die ordening uitdrukkings uniek aan deterministiese resultate te lewer nie. Byvoorbeeld, die volgende navraag is nie deterministiese omdat timeid is nie uniek in hierdie resultaat stel. Voorbeeld 21-11 windows Totale Funksies met fisieke Offsets Een manier om hierdie probleem te hanteer sou wees om die prodid kolom toe te voeg tot die gevolg stel en orde aan albei timeid en prodid. FIRSTVALUE en LASTVALUE Funksies Die FIRSTVALUE en LASTVALUE funksies toelaat om die eerste en laaste rye van 'n venster te kies. Hierdie rye is veral waardevol omdat hulle dikwels gebruik word as die basislyne in berekeninge. Byvoorbeeld, met 'n verdeling hou verkope data bestel oordag, kan jy vra hoeveel is elk dae verkope in vergelyking met die eerste verkope dag (FIRSTVALUE) van die tydperk of jy kan wens om te weet, vir 'n stel van rye in die verhoging van verkope orde Wat was die persentasie grootte van elke koop in die streek in vergelyking met die grootste verkope (LASTVALUE) in die streek As die NULLS opsie IGNOREER gebruik met FIRSTVALUE. Dit sal die eerste nie-nul waarde terug in die stel, of NULL as alle waardes is NULL. As IGNOREER NULLS gebruik met LASTVALUE. Dit sal die laaste nie-nul waarde terug in die stel, of NULL as alle waardes is NULL. Die IGNOREER NULLS opsie is veral nuttig in mense aan 'n inventaris tafel behoorlik. Verslagdoening Totale Funksies Na 'n soektog verwerk is, kan totaal waardes soos die aantal lei rye of 'n gemiddelde waarde in 'n kolom maklik bereken word binne 'n partisie en beskikbaar is om ander verslagdoening funksies gemaak. Verslagdoening totaal funksies terugkeer dieselfde gemiddelde waarde vir elke ry in 'n partisie. Hul gedrag met betrekking tot NULLs is dieselfde as die SQL totale funksies. Die sintaksis is: In Daarbenewens het die volgende voorwaardes geld: 'n asterisk () is slegs toegelaat in COUNT () DISTINCT ondersteun slegs indien ooreenstemmende totaal funksies toelaat dat dit waarde expression1 en waarde expression2 kan enige geldige uitdrukking wat kolom verwysings of aggregate wees. Die afsluiting met klousule omskryf die groepe waarop die windows funksies sal bereken word. As die afsluiting met klousule afwesig is, dan is die funksie word bereken oor die hele navraag gevolg stel. Verslagdoening funksies kan verskyn net in die SELECT klousule of die ORDER BY klousule. Die groot voordeel van verslagdoening funksies is hul vermoë om verskeie passe van data te doen in 'n enkele navraag blok en bespoedig navraag prestasie. Navrae soos Tel die aantal handelaars met verkope van meer as 10 van die stad verkoop nie vereis sluit tussen aparte navraag blokke. Byvoorbeeld, kyk na die vraag vir elke produk kategorie, vind die streek waarin hy moes maksimum verkope. Die ekwivalent SQL navraag met behulp van die MAX verslagdoening totaal funksie sou wees: die innerlike navraag met die verslag totaal funksie MAX (som (amountsold)) gee terug Die volledige navraag resultate: Voorbeeld 21-12 verslag Totale Voorbeeld Verslagdoening aggregate gekombineer met geneste navrae in staat te stel jy komplekse navrae doeltreffend te beantwoord. Byvoorbeeld, wat as jy wil die beste verkoop produkte in jou belangrikste produk weet subkategorië Die volgende is 'n navraag wat die 5-topverkoper produkte vind vir elke produk subcategory dat meer as 20 van die verkope binne sy kategorie produk bydra: RATIOTOREPORT funksioneer die RATIOTOREPORT funksie bere die verhouding van 'n waarde van die som van 'n stel waardes. As die uitdrukking waarde uitdrukking evalueer om nul. RATIOTOREPORT evalueer ook null. maar dit word beskou as 'n nul vir die berekening van die bedrag van waardes vir die deler. Die sintaksis is: In hierdie, die volgende van toepassing: Expr kan enige geldige uitdrukking wat kolom verwysings of aggregate wees. Die afsluiting met klousule omskryf die groepe waarop die RATIOTOREPORT funksie word bereken. As die afsluiting met klousule afwesig is, dan is die funksie word bereken oor die hele navraag gevolg stel. Om RATIOTOREPORT van verkope te bereken vir elke kanaal, kan jy gebruik maak van die volgende opdrag: LAG / LEAD Funksies Die LAG en lood funksies is nuttig vir die vergelyking van waardes wanneer die relatiewe posisies van rye betroubaar kan wees nie. Hulle werk deur die spesifiseer van die telling van rye wat die teiken ry skei van die huidige ry. Omdat die funksies bied toegang tot meer as een ry van 'n tafel op dieselfde tyd sonder 'n self-sluit, hulle kan verwerkingspoed te verbeter. Die LAG funksie bied toegang tot 'n ry op 'n gegewe geneutraliseer voor die huidige posisie, en die voortou funksie bied toegang tot 'n ry op 'n gegewe verreken na die huidige posisie. LAG / LEAD Syntax Hierdie funksies het die volgende opdrag: verreken is 'n opsionele parameter en gebreke aan 1. verstek is 'n opsionele parameter en die waarde teruggekeer as geneutraliseer val buite die grense van die tafel of partisie. Sien Data Verdigting vir Rapportering vir inligting wat wys hoe om die LAG / LEAD funksies gebruik om dit te doen tydperk tot tydperk vergelyking navrae oor yl data. Naam / Voor funksies die eerste / laaste totaal funksies toelaat om 'n datastel rang en werk met sy top-posisie of onderkant ingedeel rye. Na die vind van die bo-of onderkant ingedeel rye, is 'n totaal funksie toegepas op enige gewenste kolom. Dit is die eerste / laaste kan jou rang op kolom A, maar die standaard van die uitslag van 'n totaal van toepassing op die eerste gekeurde of laaste-posisie rye kolom B. Dit is waardevol omdat dit vermy die behoefte aan 'n self-sluit of subquery, dus die verbetering van prestasie. Hierdie funksies sintaksis begin met 'n gereelde totaal funksie (MIN. MAX. Som. AVG. Tel. Afwyking. STDDEV) dat 'n enkele terugkeer waarde per groep produseer. Om die posisie gebruik spesifiseer, die eerste / laaste funksies by te voeg 'n nuwe klousule wat begin met die woord te hou. Naam / Voor Syntax Hierdie funksies het die volgende opdrag: Let daarop dat die ORDER BY klousule verskeie uitdrukkings kan neem. Naam / Voor as gereelde Aggregates Jy kan die eerste / laaste familie van aggregate as gereelde totaal funksies gebruik. Voorbeeld 21-15 FIRST / laaste voorbeeld 1 Die volgende navraag laat ons vergelyk minimum prys en prys lys van ons produkte. Vir elke produk subcategory binne die kategorie klere Mens, dit gee die volgende: pryslys van die produk met die laagste minimum prys laagste minimum prys lys prys van die produk met die hoogste minimum prys Hoogste minimumprys eerste / laaste Soos verslag Aggregates Jy kan ook gebruik die eerste / laaste familie van aggregate as verslagdoening totale funksies. 'N Voorbeeld is die berekening van watter maande het die grootste en die minste toename in koppetelling regdeur die jaar. Die sintaksis vir hierdie funksies is soortgelyk aan die sintaksis vir enige ander verslagdoening totaal. Kyk na die voorbeeld in Voorbeeld 21-15 vir eerste / laaste uitgekom. Wat gebeur as ons wou die lys pryse van individuele produkte te vind en dit vergelyk met die lys pryse van die produkte in hul subcategory dat die hoogste en laagste minimum pryse Die volgende navraag laat ons vind dat die inligting vir die dokumentasie subcategory met behulp eerste / laaste as verslagdoening aggregate. Voorbeeld 21-16 FIRST / laaste voorbeeld 2 gebruik van die eerste en laaste funksioneer as verslagdoening aggregate maak dit maklik om die resultate in berekeninge insluit soos salaris as 'n persent van die hoogste salaris. Inverse Percentile funksies met behulp van die CUMEDIST funksie, kan jy die kumulatiewe verspreiding (persentiel) van 'n stel waardes te vind. Maar die omgekeerde werking (vind watter waarde bere om 'n sekere persentiel) is nie maklik om te doen nie doeltreffend bereken. Om hierdie probleem te oorkom, is die PERCENTILECONT en PERCENTILEDISC funksies bekendgestel. Dit kan beide gebruik word as venster verslagdoening funksies sowel as die normale gemiddelde funksies. Hierdie funksies moet 'n soort spesifikasie en 'n parameter wat 'n persentiel waarde tussen 0 en 1. Die soort spesifikasie neem hanteer deur gebruik te maak van 'n bevel deur klousule met een uitdrukking. Wanneer dit gebruik word as 'n normale gemiddelde funksie, dit gee 'n enkele waarde vir elke geordende stel. PERCENTILECONT. wat is 'n kontinue funksie bereken deur interpolasie, en PERCENTILEDISC. Dit is 'n stap funksie wat diskrete waardes veronderstel. Soos ander aggregate, PERCENTILECONT en PERCENTILEDISC werk op 'n groep van rye in 'n gegroepeerde navraag, maar met die volgende verskille: Hulle benodig 'n parameter tussen 0 en 1 (ingesluit). A parameter gespesifiseer uit hierdie reeks sal lei tot foute. Hierdie parameter moet gespesifiseer as 'n uitdrukking wat evalueer om 'n konstante. Hulle vereis 'n soort spesifikasie. Hierdie soort spesifikasie is 'ORDER BY klousule met 'n enkele uitdrukking. Meervoudige uitdrukkings word nie toegelaat nie. Normale Totale Syntax Inverse Percentile Voorbeeld Basis Ons gebruik die volgende navraag aan die 17 rye data wat in die voorbeelde van hierdie artikel terugkeer: PERCENTILEDISC (x) word bereken deur die skandering van die CUMEDIST waardes in elke groep totdat jy die eerste een groter as vind of gelyk aan x. waar x die gespesifiseerde persentiel waarde. Vir die voorbeeld navraag waar PERCENTILEDISC (0,5), die resultaat is 5000, na gelang van die volgende illustreer: Die resultaat van PERCENTILECONT word bereken deur lineêre interpolasie tussen die rye agter hulle bestel. Om PERCENTILECONT bereken (x). ons eerste bereken die rijnummer RN (1x (N-1)), waar n die aantal rye in die groep en x is die gespesifiseerde persentiel waarde. Die finale uitslag van die totale funksie word bereken deur lineêre interpolasie tussen die waardes van rye by ry getalle CRN oordek (RN) en FRN FLOOR (RN). Die finale uitslag sal wees: PERCENTILECONT (X) indien (CRN FRN RN), dan (waarde van uitdrukking van ry op RN) anders (CRN - RN) (waarde van uitdrukking vir ry op FRN) (RN - FRN) (waarde van uitdrukking vir ry op CRN). Kyk na die vorige voorbeeld navraag, waar ons bereken PERCENTILECONT (0,5). Hier N is 17. Die rynommer RN (1 0.5 (N-1)) 9 vir beide groepe. Om dit in die formule, (FRNCRN9), keer ons die waarde van ry 9 as die resultaat. Nog 'n voorbeeld is, as jy wil bereken PERCENTILECONT (0.66). Die berekende rijnummer RN (1 0.66 (N -1)) (1 0,6616) 11.67. PERCENTILECONT (0.66) (12-11,67) (waarde van ry 11) (11,67-11) (waarde van ry 12). Hierdie resultate is: Inverse persentiel totaal funksies kan verskyn in die WAT klousule van 'n navraag soos ander bestaande gesamentlike funksies. As verslag Aggregates Jy kan ook gebruik om die totale funksies PERCENTILECONT. PERCENTILEDISC as verslagdoening totale funksies. Wanneer dit gebruik word as verslagdoening totaal funksies, die sintaksis is soortgelyk aan dié van ander verslagdoening aggregate. Hierdie navraag bere dieselfde ding (mediaan kredietlimiet vir kliënte in hierdie resultaat stel, maar verslae die resultaat vir elke ry in die resultaat stel, soos in die volgende afvoer: Inverse Percentile beperkings vir PERCENTILEDISC die uitdrukking in die ORDER BY klousule kan. wees van enige soort data wat jy kan sorteer (numeriese, string, datum, en so aan). Maar die uitdrukking in die ORDER bY klousule moet 'n numeriese of DATETIME tipe (insluitend tussenposes), want lineêre interpolasie gebruik word om PERCENTILECONT evalueer. As die uitdrukking is van tipe DATUM. die geïnterpoleer gevolg word afgerond tot die kleinste eenheid vir die tipe. vir 'n datum tipe, sal die geïnterpoleer waarde word afgerond tot die naaste sekonde, vir interval tipes tot die naaste sekonde (iNTERVAL DAG tOT tWEEDE) of om die maand (INTERVAL JAAR tot maand). Soos ander aggregate, die omgekeerde persentiel funksies te ignoreer NULLs in die evaluering van die resultaat. byvoorbeeld, wanneer jy wil die mediaanwaarde in 'n stel te vind, Oracle databasis ignoreer die NULLs en bevind dat die mediaan onder die nie-nul waardes. Jy kan gebruik maak van die laaste opsie NULLS eerste / NULLS in die ORDER BY klousule, maar hulle sal geïgnoreer word as NULLs geïgnoreer. Hipotetiese posisie en verdelingsfunksies Hierdie funksies bied funksies nuttig vir wat-nou-ontleding. As 'n voorbeeld, sou wat die rang van 'n ry wees, indien die ry hipoteties in 'n reeks van ander rye Hierdie familie van aggregate neem een ​​of meer argumente van 'n hipotetiese ry en 'n geordende groep rye is ingevoeg, die terugkeer van die rank. DENSERANK. PERCENTRANK of CUMEDIST van die ry asof dit hipoteties plaas in die groep. Hipotetiese posisie en verspreiding Syntax Hier konstante uitdrukking verwys na 'n uitdrukking wat evalueer om 'n konstante, en daar mag meer as een so 'n uitdrukking wat geslaag as argumente om die funksie. Die ORDER BY klousule kan een of meer uitdrukkings wat die sorteer orde waarop die posisie sal gebaseer definieer bevat. ASC. Latere. NULLS EERSTE. NULLS LAASTE opsies sal beskikbaar wees vir elke uitdrukking in die ORDER BY wees. Voorbeeld 21-17 Hipotetiese posisie en verspreiding Voorbeeld 1 Gebruik die lys prys data van die produkte tafel gebruik in hierdie artikel, kan jy die rang te bereken. PERCENTRANK en CUMEDIST vir 'n hipotetiese trui met 'n prys van 50 vir hoe dit inpas binne elk van die trui subkategorië. Die navraag en resultate is: In teenstelling met die omgekeerde persentiel aggregate, mag die ORDER BY klousule in die soort spesifikasie vir hipotetiese rang en verspreiding funksies verskeie uitdrukkings te neem. Die aantal argumente en die uitdrukkings in die ORDER BY klousule moet dieselfde wees en die argumente moet konstante uitdrukkings van dieselfde of versoenbaar tipe met die ooreenstemmende ORDER BY uitdrukking wees. Die volgende is 'n voorbeeld gebruik van twee argumente in 'n paar hipotetiese posisie funksies. Voorbeeld 21-18 Hipotetiese posisie en verspreiding Voorbeeld 2 Hierdie funksies kan verskyn in die WAT klousule van 'n navraag, net soos ander totaal funksies. Hulle kan nie gebruik word as óf verslagdoening totaal funksies of te venster totaal funksies. Lineêre regressie Funksies Die regressie funksies ondersteun die pas van 'n gewone-minste-regressielyn om 'n stel van verskeie pare. Jy kan dit gebruik as beide totaal funksies of windows of verslagdoening funksies. Die funksies is soos volg: Oracle geld die funksie om die stel van pare na die uitskakeling van alle pare waarvoor enigeen van E1 of e2 is van nul (E1 E2.). E1 word geïnterpreteer as 'n waarde van die afhanklike veranderlike (a y-waarde), en E2 word geïnterpreteer as 'n waarde van die onafhanklike veranderlike ( 'n x-waarde). Beide uitdrukkings moet getalle. Die regressie funksies is almal gelyktydig bereken tydens 'n enkele slaag deur die data. Hulle word dikwels gekombineer met die COVARPOP. COVARSAMP. en CORR funksies. REGRCOUNT Function REGRCOUNT gee die aantal nie-nul getal pare gebruik om die regressielyn te pas. As toegepas op 'n leë versameling (of as daar geen (E1, E2) pare waar nie een van E1 of e2 is van nul), die funksie gee terug 0. REGRAVGY en REGRAVGX Funksies REGRAVGY en REGRAVGX bereken die gemiddeld van die afhanklike veranderlike en die onafhanklike veranderlike van die regressielyn, onderskeidelik. REGRAVGY bere die gemiddeld van die eerste argument (E1) na die uitskakeling van (e1. E2) pare waar een van E1 of e2 is nul. Net so, REGRAVGX bere die gemiddelde van sy tweede argument (E2) na nul uitskakeling. Beide funksies terugkeer NULL indien dit op 'n leë versameling. REGRSLOPE en REGRINTERCEPT Funksies Die REGRSLOPE funksie bere die helling van die regressielyn toegerus om nie-nul (e1. E2) pare. Die REGRINTERCEPT funksie bere die y-afsnit van die regressielyn. REGRINTERCEPT terugkeer NULL wanneer helling of die regressie gemiddeldes is NULL. REGRR2 Funksie Die REGRR2 funksie bere die bepaaldheidskoëffisiënt (gewoonlik genoem R-kwadraat of passingstoetse) vir die regressielyn. REGRR2 terugkeer waardes tussen 0 en 1 wanneer die regressielyn word gedefinieer (helling van die lyn is nie nul) en dit terug NULL anders. Hoe nader die waarde aan 1, hoe beter is die regressielyn pas die data. REGRSXX, REGRSYY, en REGRSXY Funksies REGRSXX. REGRSYY en REGRSXY funksies gebruik word in die berekening van verskillende diagnostiese statistieke vir regressie-analise. skep equiheight emmers. WIDTHBUCKET Sintaksis Die WIDTHBUCKET neem vier uitdrukkings as parameters. Sien Figuur 21-3 vir 'n grafiese illustrasie van hoe die emmers is opgedra. Hierdie berekening maatreëls as 'n datastel het 'n skewe verdeling oor die gemiddelde. Die resultaat van 'n afgeskorte buitenste deel is 'n unie van die buitenste sluit van elk van die mure in die logies verdeel tafel met die tafel aan die ander kant van die aan te sluit. Let daarop dat die kolom HierarchicalTime bevat string vertoë van tyd uit alle vlakke van die tyd hiërargie. Omdat dit 'n dubbele navraag, sal verwysings na cubeprodtime herskryf om die mvprodtime gebruik bewaarheid oog. Ons kan tydperk tot tydperk vergelyking berekeninge verkry te alle tye vlakke.