Moving Media Oracolo Function

Se vedi questo messaggio, il browser o ha disattivato o non supporta JavaScript. Per utilizzare tutte le funzionalità di questo sistema di aiuto, come la ricerca, il browser deve avere il supporto JavaScript abilitato. Medie Mobili calibrati con medie mobili semplici, ogni valore di dati nel quotwindowquot in cui viene eseguito il calcolo viene dato un significato o di pari peso. È spesso il caso, soprattutto nell'analisi dei dati prezzo finanziaria, che dati più recenti cronologicamente dovrebbero avere un peso maggiore. In questi casi, ponderata media mobile (o media mobile esponenziale - vedere il seguente argomento) funzionalità è spesso preferito. Si consideri la stessa tabella di valori dei dati di vendita per dodici mesi: Per calcolare una media mobile ponderata: calcolare quanti intervalli di dati partecipano al calcolo della media mobile (vale a dire la dimensione del quotwindowquot di calcolo). Se la finestra di calcolo si dice che sia n, allora il più recente valore di dati nella finestra è moltiplicato per n, il prossimo più recente moltiplicato per n-1, il valore prima che moltiplicato per n-2 e così via per tutti i valori nella finestra. Dividere la somma di tutti i valori moltiplicato per la somma dei pesi per dare la media mobile ponderata su quella finestra. Posizionare il Moving valore medio ponderato in una nuova colonna a seconda del posizionamento media di trascinamento sopra descritto. Per illustrare questi passaggi, considerare se è necessario un 3 mesi ponderata media mobile di vendite nel mese di dicembre (utilizzando la tabella sopra dei valori di vendita). Il termine quot3-monthquot implica che il quotwindowquot calcolo è 3, quindi il mobile ponderata algoritmo di media di calcolo per questo caso dovrebbe essere: Oppure, se un 3 mesi Weighted Moving Average sono stati valutati su tutta la gamma originale dei dati, i risultati sarebbero : 3 mesi mobile ponderata AveragePreviously abbiamo discusso come scrivere rotolamento medie in Postgres. A grande richiesta sono stati che vi mostra come fare lo stesso in MySQL e SQL Server. Bene coprire come annotare i grafici rumorosi in questo modo: Con una precedente linea di media di 7 giorni in questo modo: La grande idea nostro primo grafico qui sopra è piuttosto rumoroso e difficile da ottenere informazioni utili da. Siamo in grado di lisciare fuori tracciando una media di 7 giorni in cima ai dati sottostanti. Questo può essere fatto con le funzioni delle finestre, auto-join, o sottoquery correlate - così coprire i primi due. Ebbene iniziare con una media precedente, il che significa che il punto medio sul 7 del mese è la media dei primi sette giorni. Visivamente questo sposta i picchi nel grafico a destra, come un grande picco viene calcolata sulle seguenti sette giorni. In primo luogo, creare una tabella conteggio intermedio Vogliamo calcolare una media sul totale delle iscrizioni per ogni giorno. Supponendo di avere una tabella utenti tipici con una riga per ogni nuovo utente e un createdat timestamp, possiamo creare il nostro aggregato nostro tavolo iscrizioni in questo modo: In Postgres e SQL Server è possibile utilizzare questo come un CTE. In MySQL è possibile salvarlo come una tabella temporanea. Postgres media mobile Fortunatamente Postgres ha funzioni delle finestre, che sono il modo più semplice per calcolare una media in esecuzione. Questa interrogazione presuppone che le date non hanno lacune. La query è una media negli ultimi sette file, non il passato sette date. Se i dati non ha lacune, riempirli con generateseries o unirsi contro una tabella con righe data dense. MySQL media mobile MySQL manca funzioni finestra, ma possiamo fare un calcolo simile utilizzando auto-join. Per ogni riga nella nostra tabella conteggio, ci uniamo ogni riga che è stata negli ultimi sette giorni e fare la media. Questa query gestisce automaticamente gli spazi di data, come siamo di fronte a righe all'interno di un intervallo di date, piuttosto che i precedenti N righe. SQL Server media mobile SQL Server ha funzioni della finestra, in modo da calcolare la media mobile può essere fatto in uno stile Postgres o lo stile di MySQL. Per semplicità, sono stati utilizzando la versione di MySQL con un auto join. Questo è concettualmente identico a MySQL. Le uniche traduzioni sono la funzione DateAdd e nominato esplicitamente gruppo da colonne. Altri Medie ci siamo concentrati sulla media finale di 7 giorni in questo post. Se volessimo guardare il leader media di 7 giorni, la sua semplice come l'ordinamento le date nella direzione opposta. Se volessimo guardare una media centrato, mer uso: Postgres: righe tra 3 precedente e 3 seguente MySql: tra signups. date - 3 e signups. date 3 in MySQL SQL Server: tra DateAdd (giorno, -3, iscrizioni. data) e DateAdd (giorno, 3, signups. date) sono in grado di calcolare MA200 facilmente. La mia domanda attuale sembra qualcosa di simile con base come (selezionare RowNumber () OVER (partizione da codice interno ORDER BY pxDate RowNo). A. Da prezzo a), base2 come (selezionare caso MA200160160160160160 quando RowNo GT 200 poi avg (vicino) OVER ( partizione codice interno ORDER bY RIGHE pxDate TRA 200 precedente e riga corrente) fine altro NULL come MA200, MA100160160160160160 caso in cui RowNo GT 100 poi avg (vicino) OVER (partizione da codice interno ORDER bY RIGHE pxDate TRA 100 precedente e riga corrente) fine altro nullo come MA100, MA50160160160160160 caso in cui RowNo GT 50 poi avg (vicino) OVER (partizione da codice interno ORDER bY RIGHE pxDate TRA 50 precedenti e riga corrente) fine il resto nulla da MA50, b. dalla base b) scegliere base2. : 6 Qualsiasi funzione per calcolare media mobile esponenziale a Oracle. Non che Im a conoscenza. Se il calcolo di un periodo di 5 EMA è qualcosa di simile al di sotto, sarebbe abbastanza facile scrivere uno script di restituire il codice per calcolare EMA 200 Saluti A cura di: Etbin il 2010/05/30 18:29 se i valori ultimi dovrebbero avere maggior peso, la potenza ( (1 -: fattore), n) deve essere applicato il contrario mi dispiace, non capisco che cosa la vostra domanda è. Ogni volta che hai un problema, inserire un po 'di dati del campione (CREATE TABLE e INSERT) e i risultati desiderati da tali dati. non citando la versione di Oracle state usando. Semplificare il più possibile. Ad esempio, invece di una media di oltre 200 righe, inviare un problema in cui si media oltre forse 3 righe. data di chiusura stockname SMA (in grado di utilizzare AVG, interrogare partizione e finestre per calcolare semplice media mobile) 03-MAG-101601601601601603.69160160160160160CAPITALAND LIMITATA nullo 04-MAG-101601601601601603.66160160160160160CAPITALAND LIMITATA nullo 05-MAG-101601601601601603.63160160160160160CAPITALAND LIMITATA nullo 06-MAG-101601601601601603.6160160160160160CAPITALAND nullo LIMITATA 07-MAG-101601601601601603.63160160160160160CAPITALAND LIMITATA 3.642 10-mag-101601601601601603.7160160160160160CAPITALAND LIMITATA 3.644 03-MAG-101601601601601601.68160160160160160YANLORD LAND GROUP LIMITED nullo 04-MAG-101601601601601601.64160160160160160YANLORD LAND GROUP LIMITED nullo 05-MAG-101601601601601601.61160160160160160YANLORD LAND GROUP LIMITED nullo 06- mAGGIO-101601601601601601.6160160160160160YANLORD GROUP Land Limited nullo 07-mAG-101601601601601601.61160160160160160YANLORD LAND GROUP LIMITED 1.628 10-mag-101601601601601601.67160160160160160YANLORD LAND GROUP LIMITED 1.626 Dato 2 stock CapitaLand e Yanlord per il periodo compreso tra il 3 maggio al 10 maggio posso calcolare semplici 5 giorni media mobile utilizzando la funzione avg. Il problema è che sto avendo difficoltà a trovare il media mobile esponenziale 5 giorni. Nota: la mia base di dati sta avendo 1000 sul scorte dati per alcuni anni e ho diverso periodo di media mobile esponenziale a calcolare, ecco perché chiedo se non vi è una funzione disponibile che non sono a conoscenza off. Non esiste una funzione standard disponibile per il calcolo della media mobile esponenziale. Ma questo è il tipo di calcolo in cui la clausola modello brilla davvero: ho usato la formula come descritto qui: download. oracledocscdE1203201docepm.921htmlirstudioframeset. htmdocscdE1203201docepm.921htmlirstudioirstudio-15-74.html 1 quot quot (0) (0) wow. che è fresco. non sapevo che era tale modello callled cosa. Farò più ricerca su di esso e vedere se riesco a realizzare su di esso. grazie a SQL lot21 di analisi e reporting Trattamento di NULL come input per le funzioni di funzioni di finestra semantica NULL corrisponde la semantica NULL per le funzioni di aggregazione SQL. Altri semantica possono essere ottenuti funzioni definite dall'utente, oppure utilizzando il DECODE o un'espressione CASE all'interno della funzione finestra. Finestre Funzioni con Logical compensare una logica offset può essere specificato con costanti come GAMMA 10 precedente. o un'espressione che restituisce una costante, o con una specifica intervallo come GAMMA intervallo n giorno mese anno precedente o un'espressione che restituisce un intervallo. Con logica di offset, non ci può essere una sola espressione nella ORDER BY lista espressione nella funzione, con il tipo compatibile con NUMERICO se offset è numerica, o DATE se viene specificato un intervallo. Esempio 21-7 cumulativo della funzione di aggregazione Il seguente è un esempio di amountsold cumulativo per ID cliente per trimestre nel 1999: In questo esempio, la funzione SOMMA analitica definisce, per ogni riga, una finestra che comincia all'inizio della partizione (UNBOUNDED PRECEDENTE ) e finisce, per impostazione predefinita, alla riga corrente. Annidato SUM s sono necessari in questo esempio dato che stiamo eseguendo una somma su un valore che di per sé un SUM è. aggregazioni nidificate sono utilizzati molto spesso in funzioni di aggregazione analitiche. Esempio 21-8 Spostamento Funzione di aggregazione In questo esempio di un tempo a base di spettacoli finestra, per un cliente, la media mobile di vendite per il mese in corso e che precede di due mesi: Si noti che le prime due righe per i tre mesi in movimento calcolo della media nel dati di uscita sono basati su un formato intervallo inferiore a quanto specificato perché il calcolo finestra non può raggiungere oltre i dati recuperati dalla query. È necessario considerare le diverse dimensioni della finestra si trovano ai margini del set di risultati. In altre parole, potrebbe essere necessario modificare la query per includere esattamente quello che vuoi. Centrato Funzione Aggregate Calcolo a finestre funzioni di aggregazione incentrati la riga corrente è semplice. Questo esempio calcola per tutti i clienti una media mobile centrata di vendite per una settimana a fine dicembre 1999. Essa trova una media del totale delle vendite per un giorno che precede la riga corrente e un giorno dopo la riga corrente compresa la riga corrente pure. Esempio 21-9 Centrato aggregato di inizio e fine righe per ogni prodotto centrato movimento calcolo della media dei dati di uscita sono basati su soli due giorni, dal momento che il calcolo della finestra non può raggiungere oltre i dati recuperati dalla query. Gli utenti hanno bisogno di prendere in considerazione le diverse dimensioni della finestra si trovano ai confini del set di risultati: la query può essere necessario un aggiustamento. Windowing funzioni di aggregazione in presenza di duplicati L'esempio seguente mostra come le funzioni di aggregazione delle finestre calcolano i valori quando ci sono duplicati, cioè quando più righe vengono restituite per un singolo valore ordinamento. La query recupera la quantità venduta a diversi clienti durante un intervallo di tempo specificato. (Anche se usiamo una vista in linea per definire il nostro insieme di dati di base, non ha un significato particolare e può essere ignorato.) La query definisce una finestra mobile che decorre dalla data della riga corrente a 10 giorni earlier. Note che la parola chiave GAMMA viene utilizzato per definire la clausola windowing di questo esempio. Ciò significa che la finestra può potenzialmente contenere più righe per ogni valore nell'intervallo. In questo caso, ci sono tre coppie di righe con valori di data duplicati. Funzioni Esempio 21-10 Windowing aggregate con offset logici nell'uscita di questo esempio, tutte le date tranne 6 e il maggio 12 andata e ritorno due righe con date duplicate. Esaminate i numeri commentati alla destra dell'uscita per vedere come i valori sono calcolati. Si noti che ogni gruppo tra parentesi rappresentano i valori restituiti per un solo giorno. Si noti che questo esempio si applica solo quando si utilizza la parola chiave GAMMA piuttosto che la parola chiave FILE. E 'anche importante ricordare che con la gamma. è possibile utilizzare solo 1 ORDER BY espressione nel funzioni analitiche clausola ORDER BY. Con la parola chiave righe, è possibile usare ordine multipla da espressioni in funzioni analitiche clausola ORDER BY. Variando Dimensioni finestra per ogni riga Ci sono situazioni in cui è utile per variare la dimensione di una finestra per ogni riga, in base a una condizione specificata. Per esempio, si può decidere di rendere la finestra più grande per alcune date e più piccolo per gli altri. Si supponga che si desidera calcolare la media mobile di prezzo delle azioni nel corso di tre giorni lavorativi. Se si dispone di un numero uguale di righe per ogni giorno per tutti i giorni lavorativi e senza giorni non lavorativi sono memorizzati, quindi è possibile utilizzare una funzione finestra fisico. Tuttavia, se non sono soddisfatte le condizioni noti, è ancora possibile calcolare una media mobile utilizzando una espressione nei parametri di dimensione della finestra. Espressioni in una specifica dimensione della finestra possono essere realizzate in diverse fonti. l'espressione potrebbe essere un riferimento a una colonna in una tabella, ad esempio una tabella di tempo. Potrebbe anche essere una funzione che restituisce il confine appropriata per la finestra in base ai valori della riga corrente. La seguente dichiarazione di un database di prezzo delle azioni ipotetico utilizza una funzione definita dall'utente nella sua clausola RANGE per impostare la dimensione della finestra: In questa istruzione, ttimekey è un campo data. Qui, fn potrebbe essere una funzione PLSQL con le seguenti caratteristiche: 4 se ttimekey è Lunedi, Martedì Se uno qualsiasi dei giorni precedenti sono le vacanze, si regola il conteggio in modo appropriato. Si noti che, quando la finestra viene specificato utilizzando un numero in una funzione finestra con ORDER BY su una colonna di data, allora è convertito a significare il numero di giorni. Si potrebbe avere usato anche la funzione di conversione letterale intervallo, come NUMTODSINTERVAL (fn (ttimekey), DAY) invece di fn (ttimekey) per indicare la stessa cosa. È inoltre possibile scrivere una funzione PLSQL che restituisce un valore di tipo di dati INTERVALLO. Windowing funzioni di aggregazione con offset fisiche per le finestre espresse in fila, le espressioni di ordinamento deve essere unica per produrre risultati deterministici. Ad esempio, la seguente query non è deterministico perché timeid non è unico in questo set di risultati. Funzioni Esempio 21-11 Windowing aggregate con offset fisiche Un modo per gestire questo problema potrebbe essere quella di aggiungere la colonna prodid per il set di risultati e l'ordine sia timeid e ProdID. FirstValue e funzioni LASTVALUE Le funzioni FirstValue e LASTVALUE consentono di selezionare le prime e le ultime righe da una finestra. Queste righe sono particolarmente importanti perché sono spesso utilizzati come linee di base a calcoli. Per esempio, con una partizione che contiene i dati di vendita ordinate per giorno, si potrebbe chiedere quanto è stato ogni giorni le vendite rispetto al primo giorno di vendita (FirstValue) del periodo Oppure si potrebbe desiderare di conoscere, per una serie di righe in ordine crescente di vendita , Qual è stata la dimensione percentuale di ogni vendita nella regione rispetto al più grande vendita (LASTVALUE) nella regione Se l'opzione IGNORE NULLS viene utilizzata con FirstValue. verrà restituito il primo valore non nullo nel set, oppure nullo se tutti i valori sono NULL. Se IGNORE NULLS viene utilizzato con LASTVALUE. verrà restituito l'ultimo valore non nullo nel set, oppure nullo se tutti i valori sono NULL. L'opzione IGNORE NULLS è particolarmente utile in popola una tabella di inventario correttamente. Segnalazione Funzioni di aggregazione Dopo una query è stata elaborata, valori di aggregazione, come il numero di righe risultanti o di un valore medio in una colonna possono essere facilmente calcolati all'interno di una partizione e messi a disposizione di altre funzioni di reporting. Segnalazione funzioni di aggregazione restituiscono lo stesso valore aggregato per ogni riga in una partizione. Il loro comportamento rispetto NULL è lo stesso come le funzioni di aggregazione SQL. La sintassi è: Inoltre, si applicano le seguenti condizioni: un asterisco () è consentito solo in COUNT () DISTINCT è supportato solo se corrispondenti funzioni aggregate permettono valore espressione1 e espressione2 valore può essere qualsiasi espressione valida che coinvolge riferimenti di colonna o aggregati. La partizione BY definisce i gruppi su cui sarebbero stati calcolati le funzioni a finestre. Se la partizione clausola è assente, la funzione viene calcolata su tutto il set di risultati della query. funzioni di reporting possono essere visualizzati solo nella clausola SELECT o la clausola ORDER BY. Il vantaggio principale di funzioni di reporting è la loro capacità di fare più passaggi di dati in un unico blocco di query e accelerare le prestazioni delle query. Le query quali Contare il numero di venditori con un fatturato di oltre il 10 delle vendite di città non richiedono unisce tra i blocchi di query separati. Ad esempio, si consideri la questione Per ogni categoria di prodotto, trovare la regione in cui ha avuto il massimo delle vendite. La query SQL equivalente utilizzando la funzione di aggregazione di segnalazione MAX sarebbe: La query interna con il segnalazione funzione di aggregazione MAX (SUM (amountsold)) restituisce: I risultati completi della query sono: Esempio 21-12 di dati aggregati Esempio aggregati in combinazione con le query nidificate Segnalazione consentire di rispondere a query complesse in modo efficiente. Ad esempio, cosa succede se si desidera conoscere i prodotti più venduti nel prodotto più significativo sottocategorie La seguente è una query che trova i 5 prodotti più venduti per ogni sottocategoria prodotto che rappresenta oltre il 20 delle vendite all'interno della sua categoria di prodotto: RATIOTOREPORT funzione la funzione RATIOTOREPORT calcola il rapporto di un valore alla somma di un insieme di valori. Se l'espressione valore di espressione restituisce NULL. RATIOTOREPORT valuta anche NULL. ma è considerato zero per calcolare la somma dei valori per il denominatore. La sua sintassi è: In questo, si applica quanto segue: espr può essere qualsiasi espressione valida che coinvolge riferimenti di colonna o aggregati. La partizione BY definisce i gruppi su cui la funzione RATIOTOREPORT deve essere calcolata. Se la partizione clausola è assente, la funzione viene calcolata su tutto il set di risultati della query. Per calcolare RATIOTOREPORT delle vendite per ciascun canale, è possibile utilizzare la seguente sintassi: funzioni Funzioni LAGLEAD GAL e piombo sono utili per confrontare i valori in cui le posizioni relative delle righe possono essere conosciuti in modo affidabile. Lavorano specificando il numero di righe che separano la riga di destinazione dalla riga corrente. Poiché le funzioni forniscono accesso a più di una riga di una tabella allo stesso tempo senza un self-join, possono aumentare la velocità di elaborazione. La funzione GAL fornisce accesso a una riga in un determinato prima della posizione attuale, e la funzione LEAD fornisce accesso offset per una riga di un equivalente dopo la posizione corrente. LAGLEAD sintassi Queste funzioni hanno la seguente sintassi: offset è un parametro opzionale e il valore predefinito è 1. di default è un parametro opzionale ed è il valore restituito se di offset non rientra nei confini della tavola o della partizione. Vedere Dati densificazione per Reporting per le informazioni che mostra come utilizzare le funzioni GAL piombo per fare le query di confronto periodo a periodo sui dati sparsi. Funzioni FirstLast Le funzioni di aggregazione FirstLast consentono di classificare un insieme di dati e lavorare con le sue file top-ranked o in basso in classifica. Dopo aver trovato le righe superiore o inferiore classificato, una funzione di aggregazione viene applicato a qualsiasi colonna desiderata. Cioè, prima Ultimo permette di rango sulla colonna A, ma restituire il risultato di un aggregato applicato il primo classificato o le ultime in classifica righe della colonna B. Questo è importante perché evita la necessità di un self-join o sottoquery, quindi migliorando le prestazioni. Questi sintassi funzioni inizia con una normale funzione di aggregazione (MIN. MAX. SUM. AVG. COUNT. Varianza. STDDEV) che produce un valore di ritorno singolo per gruppo. Per specificare la classifica utilizzato, le funzioni ULTIMI innanzitutto aggiungere una nuova clausola di iniziare con la parola TENERE. FirstLast sintassi Queste funzioni hanno la seguente sintassi: Si noti che la clausola ORDER BY può assumere molteplici espressioni. FirstLast come aggregati regolari È possibile utilizzare il primo ultima famiglia di aggregati come funzioni di aggregazione regolari. Esempio 21-15 FirstLast Esempio 1 La seguente interrogazione permette di confrontare il prezzo minimo e prezzo di listino dei nostri prodotti. Per ogni sottocategoria prodotto all'interno della categoria abbigliamento uomo, restituisce il seguente: Prezzo di listino del prodotto con il più basso prezzo minimo più basso prezzo minimo Prezzo di listino del prodotto con il più alto FirstLast minima prezzo più alto prezzo minimo come la segnalazione Aggregati È inoltre possibile utilizzare il PRIMO ultima famiglia di aggregati come segnalazione funzioni di aggregazione. Un esempio è il calcolo che mesi hanno avuto il più grande e meno aumento del numero di testa per tutto l'anno. La sintassi per queste funzioni è simile alla sintassi per qualsiasi altro aggregato segnalazione. Si consideri l'esempio in Esempio 21-15 per FirstLast. Che cosa succede se volevamo trovare i prezzi di listino dei singoli prodotti e confrontarle con i prezzi di listino dei prodotti in loro sottocategoria che hanno i prezzi più alti e più bassi minimi La query seguente ci fa scoprire che le informazioni per la sottocategoria documentazione tramite FirstLast come la segnalazione aggregati. Esempio 21-16 FirstLast Esempio 2 Utilizzando il primo e funzioni di reporting scorso come aggregati rende facile includere i risultati di calcoli tale stipendio come percentuale di stipendio più alto. Funzioni percentili inverse Utilizzando la funzione CUMEDIST, è possibile trovare la distribuzione cumulativa (percentile) di un insieme di valori. Tuttavia, l'operazione inversa (trovare che valore calcola ad un certo percentile) è né facile da fare né efficiente calcolato. Per superare questa difficoltà, sono state introdotte le funzioni PERCENTILECONT e PERCENTILEDISC. Questi possono essere utilizzati sia come funzioni di segnalazione finestra così come normali funzioni di aggregazione. Queste funzioni necessitano di una specifica ordinamento e un parametro che assume un valore percentile tra 0 e 1. La specifica ordinamento viene gestito utilizzando una clausola ORDER BY con un'espressione. Quando viene utilizzato come funzione di aggregazione normale, restituisce un singolo valore per ogni insieme ordinato. PERCENTILECONT. che è una funzione continua calcolato per interpolazione, e PERCENTILEDISC. che è una funzione a gradino che assume valori discreti. Come altri aggregati, PERCENTILECONT e PERCENTILEDISC operano su un gruppo di righe di una query raggruppata, ma con le seguenti differenze: Essi richiedono un parametro tra 0 e 1 (compreso). Un parametro specificato fuori di questo intervallo si tradurrà in errore. Questo parametro deve essere specificato come un'espressione che restituisce una costante. Essi richiedono una specifica specie. Questa specifica è sorta una clausola ORDER BY con una sola espressione. espressioni multiple non sono ammessi. Normale Aggregate Sintassi Inverse percentile Esempio Base Usiamo la seguente query per restituire i 17 righe di dati utilizzati negli esempi di questa sezione: PERCENTILEDISC (x) viene calcolato attraverso la scansione i valori CUMEDIST in ciascun gruppo fino a trovare il primo superiore o uguale a x. dove x è il valore percentile specificato. Per la query esempio in cui PERCENTILEDISC (0,5), il risultato è 5.000, come segue illustra: Il risultato di PERCENTILECONT è calcolato per interpolazione lineare tra righe dopo ordinazione. Per calcolare PERCENTILECONT (x). prima calcoliamo il numero di riga RN (1x (n-1)), dove n è il numero di righe nel gruppo e x è il valore percentile specificato. Il risultato finale della funzione di aggregazione viene calcolata mediante interpolazione lineare tra i valori di righe in numeri di riga CRN ceil (RN) e FRN PIANO (RN). Il risultato finale sarà: PERCENTILECONT (X) se (CRN FRN RN), poi (il valore di espressione dalla fila in RN) altro (CRN - RN) (valore di espressione per fila a FRN) (RN - FRN) (valore espressione per riga alla CRN). Si consideri la query di esempio precedente, in cui calcoliamo PERCENTILECONT (0,5). Qui n è 17. Il numero di riga RN (1 0,5 (n-1)) 9 per entrambi i gruppi. Mettendo questo nella formula, (FRNCRN9), torniamo al valore della riga 9 come il risultato. Un altro esempio è, se si vuole calcolare PERCENTILECONT (0,66). Il numero di riga calcolata RN (1 0.66 (n -1)) (1 0,6616) 11,67. PERCENTILECONT (0,66) (12-11,67) (valore della riga 11) (11,67-11) (valore della riga 12). Questi risultati sono: Inverse funzioni di aggregazione percentili possono apparire nella clausola HAVING di una query come le altre funzioni di aggregazione esistenti. Come Report Aggregati È inoltre possibile utilizzare le funzioni di aggregazione PERCENTILECONT. PERCENTILEDISC come la segnalazione funzioni di aggregazione. Quando viene utilizzato come segnalazione funzioni di aggregazione, la sintassi è simile a quella di altri aggregati di segnalazione. Questa query calcola la stessa cosa (limite di credito medio per i clienti in questo set di risultati, ma riporta il risultato per ogni riga del set di risultati, come mostrato nella seguente output: Restrizioni Inverse percentili delle PERCENTILEDISC l'espressione nella clausola ORDER BY può. essere di qualsiasi tipo di dati che è possibile ordinare (numerico, stringa, data, e così via). Tuttavia, l'espressione nella clausola ORDER BY deve essere un tipo numerico o datetime (compresi gli intervalli), perché l'interpolazione lineare è usata per valutare PERCENTILECONT. Se l'espressione è di tipo DATE. il risultato interpolato viene arrotondato al più piccola unità per il tipo. per un tipo di data, il valore interpolato verrà arrotondato al secondo più vicino, per i tipi di intervallo al secondo più vicino (intervallo di giorni al seconda) o al mese (intervallo di anno al mese). Come altri aggregati, le funzioni percentili inverse ignorare NULL nel valutare il risultato. ad esempio, quando si desidera trovare il valore mediano in un insieme, Oracle Database ignora i NULL e trova la mediana Tra i valori non nulli. È possibile utilizzare l'opzione LAST NULLS FIRST NULLS nella clausola ORDER BY, ma saranno ignorati come NULL vengono ignorati. Ipotetici rango e di distribuzione delle funzioni Queste funzioni forniscono funzionalità utili per analisi what-if. Come esempio, quale sarebbe la posizione di una riga, se la riga è stata ipoteticamente inserita in una serie di altre righe Questa famiglia di aggregati prende uno o più argomenti di un ipotetico fila e un gruppo ordinato di righe, restituendo il RANK. DENSERANK. PERCENT. RANGO o CUMEDIST della fila come se fosse ipoteticamente inserito nel gruppo. Classifica ipotetica e sintassi distribuzione Qui, espressione costante si riferisce a un'espressione che restituisce una costante, e ci possono essere più di uno di tali espressioni che vengono passate come argomenti alla funzione. La clausola ORDER BY può contenere una o più espressioni che definiscono l'ordine di ordinamento su cui si baserà la classifica. ASC. DESC. NULLS FIRST. NULLS ultime opzioni saranno disponibili per ogni espressione nella ORDER BY. Esempio 21-17 ipotetico Rank e distribuzione Esempio 1 Utilizzando i dati relativi ai prezzi lista dalla tabella di prodotti utilizzati in questa sezione, è possibile calcolare il rango. PERCENT. RANGO e CUMEDIST per un ipotetico maglione con un prezzo di 50 per come si inserisce all'interno di ciascuna delle sottocategorie maglione. La query ed i risultati sono i seguenti: a differenza degli aggregati percentile inverse, la clausola ORDER BY nelle specifiche di ordinamento per le funzioni di rango e di distribuzione ipotetici può assumere molteplici espressioni. Il numero di argomenti e le espressioni nella clausola ORDER BY devono essere gli stessi e gli argomenti devono essere espressioni costanti dello stesso tipo o compatibile al corrispondente ORDER BY espressione. Il seguente è un esempio utilizzando due argomenti a diverse funzioni ipotetici classifica. Esempio 21-18 Rank e distribuzione esempio ipotetico 2 Queste funzioni possono apparire nella clausola HAVING di una query, proprio come le altre funzioni di aggregazione. Essi non possono essere utilizzati sia come segnalazione funzioni di aggregazione o windowing funzioni di aggregazione. Regressione lineare Funzioni Le funzioni di regressione supportano il montaggio di una linea di regressione ordinaria-minimi quadrati a una serie di coppie di numeri. È possibile utilizzarli come entrambe le funzioni di aggregazione o funzioni a finestre o di reporting. Le funzioni sono le seguenti: Oracle applica la funzione per l'insieme delle coppie dopo aver eliminato tutte le coppie di cui una di e1 o e2 è nullo (e1 e2.). e1 viene interpretato come valore della variabile dipendente (un valore y), e e2 è interpretato come valore della variabile indipendente (un valore x). Entrambe le espressioni devono essere numeri. Le funzioni di regressione sono tutti calcolati simultaneamente in un singolo passaggio nei dati. Essi sono spesso combinati con il COVARPOP. COVARSAMP. e le funzioni di CORR. REGRCOUNT REGRCOUNT funzione restituisce il numero di coppie di numeri non nulli utilizzati per adattare la linea di regressione. Se applicato ad un insieme vuoto (o se non ci sono le coppie (E1, E2), dove nessuno dei due E1 o E2 IS NULL), la funzione restituisce 0. REGRAVGY e REGRAVGX funzioni REGRAVGY e REGRAVGX calcolare le medie della variabile dipendente e indipendente variabile della linea di regressione, rispettivamente. REGRAVGY calcola la media del suo primo argomento (e1) dopo aver eliminato le coppie (E1. E2) dove uno di E1 o E2 è nullo. Allo stesso modo, REGRAVGX calcola la media del suo secondo argomento (e2) dopo l'eliminazione null. Entrambe le funzioni restituiscono NULL se applicato ad un insieme vuoto. REGRSLOPE e REGRINTERCEPT Funzioni La funzione REGRSLOPE calcola la pendenza della retta di regressione montato coppie non nullo (E1. E2). La funzione REGRINTERCEPT calcola l'intercetta della retta di regressione. REGRINTERCEPT restituisce NULL quando pendenza o la regressione medie sono NULL. REGRR2 Funzione La funzione REGRR2 calcola il coefficiente di determinazione (di solito chiamato R-squared o bontà di adattamento) per la linea di regressione. REGRR2 restituisce valori compresi tra 0 e 1 quando si definisce la linea di regressione (pendenza della linea non è nullo), e restituisce NULL altrimenti. Più il valore è 1, migliore è la linea di regressione adatta ai dati. REGRSXX, REGRSYY, e REGRSXY Funzioni REGRSXX. funzioni REGRSYY e REGRSXY sono utilizzati nel calcolo varie statistiche diagnostici per l'analisi di regressione. Dopo aver eliminato coppie (E1 E2.), Dove uno di E1 o E2 è nullo, queste funzioni rendono i seguenti calcoli: Esempi di regressione lineare Statistiche Alcune statistiche diagnostiche comuni che accompagnano l'analisi di regressione lineare sono riportati nella tabella 21-2, statistiche diagnostiche comuni e loro espressioni. Si noti che questo rilascia nuove funzioni permettono di calcolare tutti questi. Tabella 21-2 Statistiche diagnostici comuni e le loro espressioni di esempio calcolo di regressione lineare In questo esempio, calcoliamo una linea di regressione ordinaria-minimi quadrati che esprime la quantità venduta di un prodotto come una funzione lineare dei prodotti prezzo di listino. I calcoli sono raggruppati per canale di vendita. La PENDENZA valori. INTCPT. RSQR sono pendenza, intercetta e coefficiente di determinazione della retta di regressione, rispettivamente. L'(intero) il valore COUNT è il numero di prodotti in ciascun canale per il quale sono disponibili entrambi i dati relativi ai prezzi di quantità vendute e della lista. Itemsets frequenti invece di contare quante volte si verifica un determinato evento (ad esempio, quante volte qualcuno ha acquistato il latte al supermercato), set di elementi frequenti fornisce un meccanismo per contare quante volte si verificano più eventi insieme (ad esempio, quante volte qualcuno ha acquistato sia il latte e cereali insieme al supermercato). L'immissione dell'operazione frequenti-itemsets è un insieme di dati che rappresenta collezioni di oggetti (itemsets). Alcuni esempi di set di elementi potrebbero essere tutti i prodotti che un determinato cliente ha acquistato in un singolo viaggio al negozio di alimentari (comunemente chiamato un paniere di mercato), le pagine web che un utente accede a una singola sessione, oppure i servizi finanziari che un determinato cliente utilizza. La nozione di un set di elementi frequente è quello di trovare quei set di elementi che si verificano più spesso. Se si applica l'operatore frequent itemset ad un negozi di alimentari dati point-of-sale, si potrebbe, ad esempio, scoprire che il latte e le banane sono la coppia più comunemente comprato degli articoli. itemsets frequenti sono stati quindi utilizzati in ambienti di business intelligence per molti anni, con quella più comune è per l'analisi del paniere di mercato nel settore della vendita al dettaglio. itemsets frequenti sono integrati con il database, che operano in cima tabelle relazionali e accessibili tramite SQL. Questa integrazione fornisce un paio di vantaggi chiave: Le applicazioni che in precedenza si basavano sulle operazioni di itemset frequenti ora beneficiare di prestazioni significativamente migliorate e più semplice implementazione. applicazioni SQL-based che non hanno utilizzato in precedenza itemset frequenti possono ora essere facilmente estese per sfruttare questa funzionalità. analisi itemsets frequente viene effettuata con le DBMSFREQUENTITEMSETS pacchetto PLSQL. Vedi Pacchetti PLSQL e tipi di riferimento per ulteriori informazioni. Altre funzioni statistiche Oracle introduce una serie di funzioni statistiche SQL e un pacchetto di statistiche, DBMSSTATFUNCS. Questa sezione elenca alcune delle nuove funzioni con sintassi di base. Vedere PLSQL Pacchetti e tipi di riferimento per informazioni dettagliate sul pacchetto DBMSSTATFUNCS e Oracle Database SQL Reference per la sintassi e la semantica. Statistiche descrittive È possibile calcolare le seguenti statistiche descrittive: mediana di un modo impostazione dei dati di un set di dati È possibile calcolare le seguenti statistiche parametriche: spearmans Rho Coefficiente Kendalls tau-b Coefficiente In aggiunta alle funzioni, questa versione ha un nuovo pacchetto PLSQL, DBMSSTATFUNCS. Esso contiene la funzione di rilevazione statistica descrittiva con le funzioni per supportare il montaggio di distribuzione. La funzione Sommario sintetizza una colonna numerica di una tabella con una varietà di statistiche descrittive. Le funzioni di montaggio cinque distribuzione supportano normale, uniforme, Weibull, di Poisson e distribuzione esponenziale. 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 21-19 . 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 21-3 for a graphical illustration of how the buckets are assigned. 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. 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. User-Defined Aggregate Functions 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 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 datatypes 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. 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 statements is: The syntax for searched statements is: You can specify only 255 arguments and each WHEN. THEN pair counts as two arguments. For a workaround to this limit, see Oracle Database SQL Reference . 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 would have to write this query as follows, 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. Creating Histograms With User-Defined Buckets 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 21-21 Histogram Example 1 Example 21-22 Histogram Example 2 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 spare 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 sub-query 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 query 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 will wrap 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, lets 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 will wrap the column headings for greater readability of results: In the FROM clause of the in-line 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 in-line 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 20, SQL for Aggregation in Data Warehouses. The materialized view is defined using the following statement: 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, please 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, please 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 in-line 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: 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 OLAP 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 22, 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.