In un database di transazioni che abbracciano 1.000 s di soggetti oltre i 18 mesi, vorrei eseguire una query per gruppo ogni possibile periodo di 30 giorni dalla EntityID con una somma dei loro valori di transazione e conte di loro transazioni in quel periodo di 30 giorni, e restituire i dati in un modo che io possa poi interrogare contro. Dopo un sacco di test, questo codice compie gran parte di ciò che voglio: E io userà in una query strutturata più qualcosa di simile: Il caso che questa copertura interrogazione doesnt è quando i conti di transazione sarebbero estendersi su più mesi, ma ancora essere entro 30 giorni di ciascun altro. E 'questo tipo di query possibile con Postgres Se è così, accolgo con favore qualsiasi ingresso. Molti degli altri argomenti discutere in esecuzione aggregati, non a rotazione. Lo script CREATE TABLE: uscita ideale includerebbe SUM (quantità) e COUNT () di tutte le operazioni nel corso di un periodo di 30 giorni a rotazione. Vedere questa immagine, ad esempio: La data di evidenziazione verde indica che cosa è essere inclusi dalla mia domanda. La fila evidenziazione gialla indica record quello che vorrebbe diventare parte del set. Con ogni possibile periodo di 30 giorni dalla EntityID si intende il periodo può iniziare in qualsiasi giorno, quindi 365 possibili periodi in un (non bisestile) dell'anno Oppure solo prendere in considerazione giorni con una transazione reale come inizio di un periodo individualmente per ogni EntityID. In entrambi i casi, si prega di fornire la vostra definizione della tabella, la versione Postgres, alcuni dati di esempio e il risultato previsto per il campione. ndash Erwin Brandstetter 20 luglio 15 a 7:18 In teoria, volevo dire ogni giorno, ma in pratica non vi è alcuna necessità di prendere in considerazione giorni dove non ci sono transazioni. I39ve postato la definizione dei dati di esempio e da tavolo. ndash tufelkinder 20 luglio 15 alle 14:50 Così si vuole accumulare righe della stessa EntityID in una finestra di 30 giorni a partire da ogni transazione reale. Ci può essere più transazioni per lo stesso (TransDate, EntityID), o è quella combinazione unica definita tua definizione di tabella non ha alcun vincolo UNIQUE o PK, ma i vincoli sembrano mancare. ndash Erwin Brandstetter 20 Luglio 15 in 15:44 La query avete Si potrebbe semplificare la query utilizzando una clausola finestra, ma questo è solo accorciare la sintassi, non cambiando il piano di query. Inoltre utilizzando il conteggio leggermente più veloce (). dal momento che id è certamente definita NOT NULL e non avete bisogno di ORDER BY EntityID dal momento che già PARTITION BY EntityID È possibile semplificare ulteriormente, però: Non aggiungere ORDER BY alla definizione finestra a tutti, non è rilevante per la query. Allora non avete bisogno di definire una cornice finestra personalizzata, sia: più semplice, più veloce, ma ancora solo una versione migliore di quello che hai. con mesi statici. La query si potrebbe desiderare non è chiaramente definito, in modo da Ill costruire su questi presupposti: Count transazioni e quantità per ogni periodo di 30 giorni entro il primo e l'ultimo di transazione di qualsiasi EntityID. Escludere iniziali e finali periodi senza attività, ma includono tutti i possibili periodi di 30 giorni all'interno di tali limiti esterni. Questo elenca tutti i periodi di 30 giorni per ogni EntityID con i tuoi inerti e con TransDate essere il primo giorno (incl.) Del periodo. Per ottenere i valori per ogni singola riga si uniscono alla tabella di base ancora una volta. La difficoltà di base è la stessa come discusso qui: La definizione telaio di una finestra non può dipendere valori della riga corrente. La query si vuole realmente Dopo l'aggiornamento domanda e discussione: Accumula righe della stessa EntityID in una finestra di 30 giorni a partire da ogni transazione reale. Dal momento che i dati sono distribuiti scarsamente, dovrebbe essere più efficiente per eseguire un self-join con una condizione di gamma. tanto più che Postgres 9.1 non ha LATERALE si unisce, ancora: una finestra a rotazione potrebbe aver senso solo (rispetto alle prestazioni) con i dati per quasi tutti i giorni. Questo fa i duplicati non aggregate su (TransDate, EntityID) al giorno, ma tutte le righe dello stesso giorno sono sempre inclusi nella finestra di 30 giorni. Per un grande tavolo, un indice di copertura come questo potrebbe aiutare un po ': L'ultima colonna quantità è utile solo se si ottiene scansioni indice solo fuori di esso. Altrimenti cadere. Ma la sua non intenzione di essere utilizzato mentre si seleziona l'intera tabella in ogni caso. Sarebbe supportare query per un piccolo SQL subset.22 di analisi e reporting Oracle ha potenziato SQLs capacità di elaborazione di analisi con l'introduzione di una nuova famiglia di funzioni SQL analitiche. Queste funzioni analitiche consentono di calcolare: Classifica e percentili Moving calcoli finestra lineari statistiche di regressione funzioni Classifica includono distribuzioni cumulative, cento rango, e N-piastrelle. Moving calcoli finestra consentono di trovare in movimento e aggregazioni cumulativi, come le somme e le medie. Analisi Laglead consente diretti riferimenti inter-fila in modo da poter calcolare le variazioni periodo a periodo. analisi FirstLast consente di trovare il primo o l'ultimo valore in un gruppo ordinato. Altri miglioramenti a SQL comprendono l'espressione CASE ed esterno partizionato join. espressioni CASE forniscono if-then logica utile in molte situazioni. Partizionato outer join è un'estensione ANSI outer join sintassi che permette agli utenti per densificare selettivamente certe dimensioni, mantenendo gli altri sparse. In questo modo gli strumenti di reporting per densificare selettivamente dimensioni, ad esempio, quelli che appaiono nelle loro relazioni cross-tabulari, mantenendo gli altri sparse. Per migliorare le prestazioni, funzioni analitiche possono essere parallelizzati: più processi possono simultaneamente eseguire tutte queste affermazioni. Queste funzionalità rendono calcoli più facile e più efficiente, migliorando così le prestazioni del database, scalabilità e semplicità. funzioni analitiche sono classificati come descritto nella tabella 22-1. Tabella 22-1 funzioni analitiche e loro utilizzo per eseguire queste operazioni, le funzioni analitiche aggiungono diversi nuovi elementi al processo SQL. Questi elementi si basano su SQL esistente per consentire espressioni di calcolo flessibili e potenti. Con poche eccezioni, le funzioni analitiche hanno questi nuovi elementi. Il flusso di elaborazione è rappresentata nella Figura 22-1. Figura 22-1 Ordine di elaborazione I concetti essenziali usati in funzioni analitiche sono: l'elaborazione delle query utilizzando funzioni analitiche si svolge in tre fasi. In primo luogo, tutti i join, DOVE. GROUP BY e HAVING vengono eseguite. In secondo luogo, il set di risultati è messo a disposizione le funzioni analitiche, e tutti i loro calcoli. In terzo luogo, se la query ha una clausola ORDER BY alla sua estremità, ORDER BY viene elaborato per consentire preciso ordine di uscita. L'ordine di elaborazione è mostrato nella Figura 22-1. partizioni set di risultati Le funzioni analitiche consentono agli utenti di dividere i set di risultati di query in gruppi di righe chiamate partizioni. Si noti che le partizioni termine utilizzati con funzioni analitiche è estraneo alla funzione di partizioni della tabella. Nel corso di questo capitolo, le partizioni termine si riferisce solo il significato relative a funzioni analitiche. Le partizioni sono create dopo che i gruppi definiti con clausole GROUP BY, in modo che siano disponibili per tutti i risultati aggregati, quali somme e le medie. divisioni partizioni possono essere basate su tutte le colonne o espressioni desiderati. Una serie di risultati della query può essere partizionato in una sola partizione che contiene tutte le righe, poche grandi partizioni, o molte piccole partizioni in possesso di poche righe ciascuna. Per ogni riga in una partizione, è possibile definire una finestra scorrevole di dati. Questa finestra determina l'intervallo di righe utilizzate per eseguire i calcoli per la riga corrente. dimensioni finestre possono essere basate su un numero di righe fisica o un intervallo logico come il tempo. La finestra ha una fila di partenza e una riga finale. A seconda della sua definizione, la finestra può muovere in una o entrambe le estremità. Per esempio, una finestra definita per una funzione somma cumulativa avrebbe la fila iniziante fissata alla prima fila di sua partizione, e la sua fila fine farebbe scorrere dal punto di partenza fino all'ultima riga della partizione. Al contrario, una finestra definita per una media mobile dovrebbe avere sia il suo inizio e di fine slitta in modo da mantenere una gamma fisica o logica costante. Una finestra può essere impostata grande come tutte le righe di una partizione o solo una finestra scorrevole di una riga all'interno di una partizione. Quando una finestra è in prossimità di un confine, la funzione restituisce risultati solo per le righe a disposizione, piuttosto che avverte che i risultati non sono ciò che si desidera. Quando si utilizzano funzioni finestra, la riga corrente è incluso durante i calcoli, così si dovrebbe specificare solo (n -1), quando si tratta di n elementi. Ogni calcolo eseguito con una funzione analitica si basa su una riga corrente all'interno di una partizione. La riga corrente serve come punto di riferimento determina l'inizio e la fine della finestra. Per esempio, un calcolo di media mobile centrata potrebbe essere definita con una finestra che contiene la riga corrente, le sei righe precedenti e seguenti sei righe. Ciò creerebbe una finestra scorrevole di 13 righe, come mostrato nella Figura 22-2. Figura 22-2 Finestra scorrevole Esempio Ranking, Windowing, e Reporting funzioni Questa sezione illustra le funzioni analitiche di base per la classifica, a finestre, e il reporting. Esempio di 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. Statistica Aggregati Oracle fornisce una serie di funzioni statistiche SQL e un pacchetto di statistiche, DBMSSTATFUNCS. Questa sezione elenca alcune delle nuove funzioni con sintassi di base. 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 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. User-Defined Aggregati Oracle offre una struttura per la creazione di funzioni, chiamate funzioni di aggregazione definite dall'utente. Queste funzioni sono scritte in linguaggi di programmazione come PLSQL, Java e C, e possono essere usati come funzioni analitiche o aggregati in viste materializzate. Vedere Oracle Database dati Cartuccia Developers Guide per ulteriori informazioni riguardanti la sintassi e le restrizioni. I vantaggi di queste funzioni sono: funzioni altamente complesse possono essere programmate utilizzando un linguaggio completamente procedurale. scalabilità superiore rispetto ad altre tecniche quando le funzioni definite dall'utente sono programmati per l'elaborazione parallela. i tipi di dati oggetto possono essere trattati. Come semplice esempio di una funzione di aggregazione definita dall'utente, considerare la statistica skew. Questo misure di calcolo se un insieme di dati ha una distribuzione asimmetrica sulla sua media. Essa vi dirà se una coda della distribuzione è significativamente più grande rispetto agli altri. Se è stato creato un aggregato chiamato udskew definita dall'utente ed è applicato ai dati limite di credito nell'esempio precedente, l'istruzione SQL ei risultati potrebbero apparire così: Prima di costruire funzioni di aggregazione definite dall'utente, si dovrebbe considerare se le vostre esigenze possono essere soddisfatte in SQL regolare. Molti calcoli complessi sono possibili direttamente in SQL, in particolare utilizzando l'espressione CASE. Stare con regolare SQL permetterà lo sviluppo più semplice, e molte operazioni di query sono già ben parallelizzato in SQL. Anche l'esempio precedente, la statistica skew, può essere creato utilizzando le normali, anche se lungo, SQL. Operazioni ruotando la d ata restituiti dalle query di business intelligence è spesso più utile se presentati in un formato crosstabular. Il pivotclause dell'istruzione SELECT permette di scrivere query di contingenza che ruotano righe in colonne, aggregando i dati in fase di rotazione. Orientabile è una tecnica fondamentale nel data warehouse. In esso, a trasformare il più righe di input in un minor numero di e generalmente più ampie righe nel data warehouse. Quando girevole, un operatore di aggregazione è applicata per ogni elemento dell'elenco valore della colonna pivot. La colonna perno non può contenere un'espressione arbitraria. Se avete bisogno di ruotare su un'espressione, allora si dovrebbe alias l'espressione in una vista prima dell'operazione PIVOT. La sintassi di base è la seguente: Per illustrare l'uso di imperniamento, creare la seguente vista come base per esempi successivi: Esempio: pivotante L'istruzione seguente illustra un perno tipica colonna channel: Si noti che l'uscita ha creato quattro nuove colonne aliasing , VENDITE DIRETTE. InternetSales. CATALOGSALES. e TELESALES. uno per ciascuno dei valori di articolazione. L'uscita è una somma. Se non viene fornito alcun alias, della colonna saranno i valori del - list IN. Facendo perno su più colonne È possibile ruotare su più di una colonna. La seguente dichiarazione illustra una tipica multipla perno colonna: Si noti che questo esempio specifica un multi-colonna nel - list con intestazioni delle colonne progettate per soddisfare i membri - list IN. Girevole: Aggregati multiple È possibile ruotare con più aggregati, come mostrato nell'esempio seguente: Si noti che la query crea intestazioni delle colonne concatenando i valori del perno (o pseudonimo) con l'alias della funzione di aggregazione, oltre a un carattere di sottolineatura. Distinguere Null-pivot generata da Null in origine dati È possibile distinguere tra valori nulli che vengono generati dall'uso di PIVOT e quelle che esistono nei dati di origine. L'esempio seguente illustra i valori nulli che PIVOT genera. La seguente query restituisce le righe con 5 colonne, colonna ProdID. e perno conseguente colonne Q1. Q1COUNTTOTAL. Q2. Q2COUNTTOTAL. Per ogni valore univoco di ProdID. Q1COUNTTOTAL restituisce il numero totale di righe il cui valore è trim Q1. cioè, e Q2COUNTTOTAL restituisce il numero totale di righe il cui valore è qtr Q2. Supponiamo di avere un tavolo sales2 della seguente struttura: Dal risultato, sappiamo che per ProdID 100, ci sono 2 file di vendita per trimestre Q1. e 1 riga di vendita per trimestre Q2 per ProdID 200, vi è 1 fila di vendita per trimestre Q1. e nessuna riga di vendita per trimestre Q2.Così, in Q2COUNTTOTAL. è possibile identificare che NULLlt1gt proviene da una riga nella tabella originale la cui misura è di valore nullo, mentre NULLlt2gt è dovuta a nessuna riga di essere presente nella tabella originale per prodid 200 nel quartiere Q2. Operazioni Unpivoting Un UNPIVOT non inverte un'operazione PIVOT. Invece, ruota dati di colonne in righe. Se si lavora con i dati basculanti, un'operazione UNPIVOT non può annullare le aggregazioni che sono state fatte da PIVOT o qualsiasi altro mezzo. Per illustrare unpivoting, prima creare una tabella basculante che include quattro colonne, per quarti dell'anno: I contenuti tavoli simile al seguente: La seguente operazione UNPIVOT ruota le colonne trimestre in righe. Per ogni prodotto, ci saranno quattro righe, una per ogni quarto. Si noti l'uso di CONTENERE NULLS in questo esempio. È inoltre possibile utilizzare ESCLUDERE NULLS. che è l'impostazione predefinita. Inoltre, è anche possibile UNPIVOT utilizzando due colonne, come nell'esempio seguente: jolly e sottoquery orientabile con Operations XML Se si desidera utilizzare un argomento jolly o subquery nelle colonne girevoli, è possibile farlo con la sintassi XML PIVOT. Con XML PIVOT, l'output dell'operazione è correttamente formattato XML. L'esempio seguente illustra utilizzando la parola chiave jolly, QUALSIASI. Produce XML che include tutti i valori dei canali in salesview: Si noti che la parola chiave è ANY disponibile nelle operazioni PIVOT solo come parte di un'operazione XML. Questo output include i dati per i casi in cui è presente il canale nel set di dati. Si noti inoltre che le funzioni di aggregazione devono specificare una clausola GROUP BY per restituire più valori, ma il pivotclause non contiene un gruppo esplicito BY. Invece, il pivotclause esegue un gruppo implicita. Il seguente esempio illustra utilizzando una subquery. Produce XML che include tutti i valori di canali e dati di vendita corrispondenti ad ogni canale: L'uscita addensa i dati da includere tutti i possibili canali per ciascun prodotto. I dati densificazione per la segnalazione dei dati viene normalmente conservato in forma sparsa. Cioè, se nessun valore esiste per una data combinazione di valori delle dimensioni, senza riga esiste nella tabella dei fatti. Tuttavia, si consiglia di visualizzare i dati in forma densa, con le righe per tutte le combinazioni di valori delle quote visualizzate anche quando non esistono dati di fatto per loro. Ad esempio, se un prodotto non ha venduto nel corso di un determinato periodo di tempo, si può ancora voglia di vedere il prodotto per quel periodo di tempo con un valore pari a zero di vendita accanto ad essa. Inoltre, i calcoli temporali possono essere eseguite più facilmente quando dati è denso lungo la dimensione temporale. Questo perché i dati densi riempiranno un consistente numero di righe per ciascun periodo, che a sua volta rende più semplice per utilizzare le funzioni di windowing analitiche con offset fisici. densificazione dei dati è il processo di conversione dei dati sparsi in fitto form. To superare il problema della scarsità, è possibile utilizzare un esterno partizionato unirsi per colmare le lacune in una serie temporale o di ogni altra dimensione. Tale unirsi estende l'esterno convenzionale sintassi di join applicando l'outer join a ogni partizione logica definita in una query. Oracle partizioni logicamente le righe nella query in base all'espressione specificata nella partizione BY. Il risultato di un esterno partizionato join è una unione dei join esterni di ciascuna delle partizioni nella tabella logicamente partizionata con la tabella sul lato opposto del join. Si noti che è possibile utilizzare questo tipo di join per colmare le lacune in qualsiasi dimensione, non solo la dimensione temporale. La maggior parte degli esempi qui concentrarsi sulla dimensione tempo perché è la dimensione più frequentemente utilizzata come base per il confronto. Partizione registrazione Sintassi La sintassi per esterno partizionato unirsi estende l'ANSI SQL JOIN clausola con la frase PARTITION BY seguita da una lista di espressioni. Le espressioni nella lista specificano il gruppo a cui unirsi viene applicata l'esterno. Le seguenti sono le due forme di sintassi normalmente utilizzati per esterno partizionato registrazione: Si noti che full outer join non è supportata con un esterno partizionato join. Esempio di Dati Sparsi Una situazione cal Typi con una dimensione rada è mostrato nel seguente esempio, che calcola le vendite settimanali e le vendite anno-to-date per il rimbalzo del prodotto per settimane 20-30 nel 2000 e 2001: In questo esempio, abbiamo ci si aspetta 22 righe di dati (11 settimane ciascuno da 2 anni) se i dati sono stati densi. Tuttavia, si ottiene solo 18 righe, perché le settimane 25 e 26 sono mancanti nel 2000, e le settimane 26 e 28 nel 2001. Lacune Inserimento dei dati possiamo prendere i dati sparsi della query precedente e fare un esterno partizionato unirsi a un insieme denso di dati in tempo. Nella query seguente, abbiamo alias la nostra query originale come v e selezioniamo i dati della tabella tempi, che abbiamo alias t. Qui si recupera 22 righe perché non ci sono lacune nella serie. Le quattro righe aggiunte hanno ciascuno 0 come il loro valore di vendita impostato a 0 utilizzando la funzione NVL. Si noti che in questa ricerca, una condizione WHERE stato posto per settimane tra 20 e 30 nella vista in linea per la dimensione temporale. Questo è stato introdotto per mantenere il set di risultati di piccole dimensioni. Colmare le lacune nei dati Due Dimensioni N-dimensionale è in genere visualizzati come una fitta scheda trasversale 2-dimensionale (n - 2) dimensioni della pagina. Ciò richiede che tutti i valori di quota per le due dimensioni che compaiono nella scheda croce essere compilati Il seguente è un altro esempio in cui l'esterno partizionato uniscono funzionalità può essere utilizzata per colmare le lacune su due dimensioni:. In questa query, la clausola di factoring subquery v1 riassume i dati di vendita al prodotto, al paese, e il livello di anno. Questo risultato è scarsa, ma gli utenti potrebbero voler vedere tutte le combinazioni di campagna, anno per ogni prodotto. Per raggiungere questo obiettivo, prendiamo ogni partizione di v1 in base ai valori di prodotto e outer join sulla dimensione Paese prima. Questo ci darà tutti i valori di paese per ogni prodotto. Abbiamo poi prendiamo questo risultato e la partizione su valori prodotto e per paese e quindi outer join sulla dimensione temporale. Questo ci darà tutti i valori di tempo per ogni combinazione prodotto e per paese. Colmare le lacune in una tabella di inventario Una tabella inventario tracce tipicamente quantità di unità disponibili per i vari prodotti. Questa tabella è rada: memorizza solo una riga per un prodotto quando c'è un evento. Per una tabella vendite, l'evento è una vendita, e per la tabella di magazzino, l'evento è un cambiamento nella quantità disponibile per un prodotto. Ad esempio, si consideri la seguente tabella inventario: La tabella inventario ora ha le seguenti righe: Ai fini dei rapporti, gli utenti potrebbero voler visualizzare questi dati di inventario in modo diverso. Ad esempio, si può decidere di visualizzare tutti i valori di tempo per ogni prodotto. Ciò può essere realizzato utilizzando esterno partizionato join. Inoltre, per le nuove righe inserite su periodi di tempo mancante, gli utenti potrebbero voler vedere i valori per la quantità di colonna d'unità da riportare dal più recente periodo di tempo esistente. Quest'ultimo può essere realizzato utilizzando valore LASTVALUE funzione di finestra analitica. Ecco la query e l'output desiderato: La query interna calcola un esterno partizionato join in tempo all'interno di ciascun prodotto. La query interna addensa i dati sulla dimensione temporale (cioè la dimensione temporale avrà ora una riga per ogni giorno della settimana). Tuttavia, la quantità colonna di misura avrà valori nulli per le righe appena aggiunti (vedi l'uscita nella quantità colonna i seguenti risultati. La query esterna utilizza la funzione LASTVALUE analitica. L'applicazione di questa funzione le partizioni di dati per prodotto e ordina i dati sul colonna dimensione temporale (timeid). per ogni riga, la funzione trova l'ultimo valore non nullo nella finestra causa l'opzione IGNORE NULLS. che può essere utilizzato sia con LASTVALUE e FirstValue. vediamo l'output desiderato nella repeatedquantity colonna il seguente output:. calcolare i valori di dati per colmare le lacune esempi nella sezione precedente illustrano come utilizzare esterno partizionato si uniscono per colmare le lacune in una o più dimensioni Tuttavia, i set di risultati prodotti da esterno partizionato uniscono hanno valori nulli per le colonne che non sono inclusi in la partizione lista. in genere, queste sono le colonne di misura. gli utenti possono usufruire di funzioni SQL analitiche per sostituire quei valori nulli con un valore non nullo. ad esempio, la seguente query calcola totali mensili per la scheda di memoria prodotti da 64 MB e DVD-R dischi (prodotto ID 122 e 136) per l'anno 2000. Esso utilizza esterno partizionato si uniscono per densificare i dati per tutti i mesi. Per i mesi mancanti, quindi utilizza la funzione SQL analitica AVG per calcolare le vendite e le unità per la media dei mesi in cui il prodotto è stato venduto. Se si lavora in SQLPlus, i seguenti due comandi avvolge le intestazioni di colonna per una maggiore leggibilità dei risultati: I calcoli di serie storica su addensato dati Densificatio n non è solo per la segnalazione scopo. Consente inoltre alcuni tipi di calcoli, in particolare, i calcoli delle serie storiche. calcoli serie storiche sono più facili quando i dati è denso lungo la dimensione temporale. dati denso ha un consistente numero di righe per ogni periodo di tempo che a sua volta lo rendono semplice da usare funzioni finestra analitica con offset fisici. Per illustrare, dobbiamo prima dare l'esempio a colmare le lacune nei dati. e aggiungiamo una funzione analitica a quella query. Nella seguente versione migliorata, calcoliamo vendite settimanali year-to-date accanto alle vendite settimanali. I valori NULL che l'esterno partizionato join inserti nel fare la serie storica densa vengono gestiti nel solito modo: la funzione SOMMA li tratta come 0s. Periodo-to-periodo di confronto per un livello Tempo: Esempio Come possiamo utilizzare questa funzione per confrontare In particolare i valori attraverso periodi di tempo, come si fa a calcolare il confronto delle vendite anno su anno a livello settimana i seguenti rendimenti di query sulla stessa riga , per ciascun prodotto, le vendite anno-to-date per ogni settimana del 2001 con quello del 2000. si noti che in questo esempio si comincia con una clausola WITH. Questo migliora la leggibilità della query e ci permette di concentrarci sul esterno partizionato join. Se si lavora in SQLPlus, il seguente comando avvolge le intestazioni delle colonne per una maggiore leggibilità dei risultati: nella clausola FROM delle densesales vista in linea. usiamo un esterno partizionato join di aggregati vista V e il tempo t vista per colmare le lacune nei dati di vendita lungo la dimensione temporale. L'uscita del esterno partizionato join viene quindi elaborato dalla funzione SUM analitica. OLTRE per calcolare le vendite settimanali year-to-date (la colonna weeklyytdsales). Così, i densesales vista calcola i dati anno-to-date di vendita per ogni settimana, compresi quelli mancanti nella vista aggregata s. I yearoveryearsales vista in linea calcola poi l'anno fa le vendite settimanali year-to-date con la funzione GAL. La funzione GAL etichettato weeklyytdsalesprioryear specifica una partizione dalla clausola che le coppie righe per la stessa settimana di anni 2000 e 2001 in una singola partizione. Abbiamo poi passiamo un offset di 1 alla funzione GAL per ottenere l'anno settimanale per le vendite di data per la prima e all'anno più esterno blocco query seleziona i dati da yearoveryearsales con la condizione yr 2001 e quindi i rendimenti di query, per ogni prodotto, il suo settimanale le vendite anno-to-date nelle settimane specificate anni 2001 e 2000. periodo-to-periodo di confronto per più livelli temporali: esempio Mentre l'esempio prima ci mostra un modo per creare il confronto per un singolo livello di tempo, sarebbe ancora più utile per gestire più livelli temporali in una singola query. Ad esempio, potremmo confrontare le vendite rispetto al periodo precedente ai livelli dell'anno, trimestre, mese e giorno. Come possiamo creare una query che esegue un confronto anno su anno delle vendite anno-to-date per tutti i livelli della nostra gerarchia volta prenderemo diversi passaggi per eseguire questa operazione. L'obiettivo è una singola query con i confronti al giorno, settimana, mese, trimestre, e il livello dell'anno. I passi sono i seguenti: Creeremo una vista chiamato cubeprodtime. che detiene un cubo gerarchica delle vendite aggregate attraverso i tempi ed i prodotti. Poi creeremo una vista della dimensione temporale da utilizzare come un bordo del cubo. Il bordo di tempo, che contiene un set completo di date, esterno sarà partizionato unito ai dati sparse nel cubeprodtime vista. Infine, per le massime prestazioni, creeremo una vista materializzata, mvprodtime. costruita utilizzando la stessa definizione cubeprodtime. Per ulteriori informazioni riguardanti i cubi gerarchici, vedere il capitolo 21, SQL per l'aggregazione nei data warehouse. La vista materializzata è definito nel passaggio 1 nella sezione seguente. Fase 1 Creare il cubo gerarchica vista La vista materializzata mostrato nella seguente potrebbe essere già presente nel vostro sistema, se non, creare ora. Se è necessario generare esso, si noti che ci limitiamo la query di solo due prodotti per tenere il tempo di elaborazione breve: Perché questo punto di vista è limitato a due prodotti, restituisce poco più di 2200 righe. Si noti che la colonna HierarchicalTime contiene rappresentazioni di stringa di tempo da tutti i livelli della gerarchia tempo. L'espressione CASE utilizzato per la colonna HierarchicalTime aggiunge un marcatore (0, 1) per ciascuna stringa data per indicare il livello temporale del valore. A 0 rappresenta il livello dell'anno, 1 è quarti, 2 è mesi, e 3 è il giorno. Si noti che la clausola GROUP BY è un rollup concatenato che specifica la gerarchia cumulativo per le dimensioni temporali e di prodotto. La clausola GROUP BY è ciò che determina il contenuto gerarchici cubo. Fase 2 Creare il edgetime vista, che è un set completo di valori di data edgetime è la fonte per colmare le lacune di tempo nel cubo gerarchico utilizzando un esterno partizionato join. Il HierarchicalTime colonna edgetime sarà utilizzato in una partizionato unirsi con la colonna HierarchicalTime nel cubeprodtime vista. La seguente dichiarazione definisce edgetime: Fase 3 creare la vista mvprodtime materializzato per supportare prestazioni più veloci La vista materializzata definizione è un duplicato della cubeprodtime vista definita in precedenza. Poiché si tratta di una query duplicato, i riferimenti a cubeprodtime saranno riscritti per utilizzare il mvprodtime vista materializzata. Di seguito materializzata può essere già presente nel vostro sistema, se non, creare ora. Se è necessario generare esso, si noti che ci limitiamo la query di solo due prodotti per tenere il tempo di elaborazione breve. Fase 4 Creare la query confronto Abbiamo ora impostare la fase per la nostra interrogazione confronto. Siamo in grado di ottenere i calcoli di confronto periodo a periodo a tutti i livelli di tempo. Richiede applicando funzioni analitiche a un cubo gerarchico con dati densi lungo la dimensione temporale. Alcuni dei calcoli che possiamo raggiungere per ogni livello di tempo sono: somma delle vendite per periodo precedente a tutti i livelli di tempo. Varianza delle vendite oltre periodo precedente. Somma delle vendite nello stesso periodo di un anno fa a tutti i livelli di tempo. Varianza delle vendite rispetto allo stesso periodo dello scorso anno. L'esempio seguente esegue tutti e quattro di questi calcoli. Esso utilizza un esterno partizionato unire dei punti di vista e cubeprodtime edgetime per creare una vista in linea dei dati dense chiamati densecubeprodtime. La query utilizza la funzione GAL nello stesso modo come l'esempio a livello singolo prima. L'esterno clausola WHERE specifica volta a tre livelli: i giorni di agosto del 2001, tutto il mese, e tutto il terzo trimestre del 2001. Si noti che le ultime due righe dei risultati contengono il livello mese e il livello di quartiere aggregations. Note che per rendere i risultati più facili da leggere se si utilizza SQLPlus, le intestazioni delle colonne devono essere regolati con i seguenti comandi. I comandi si piegheranno le intestazioni delle colonne per ridurre la lunghezza della linea: Ecco la query confrontando vendite attuali per prima e le vendite anno fa: La prima funzione GAL (salespriorperiod) suddivide i dati su GIDP. gatto. Subcat. prod. gidt e ordina le righe su tutte le colonne dimensione temporale. Si ottiene il valore delle vendite del periodo precedente passando un offset di 1. La seconda funzione GAL (salessameperiodprioryear) Pareti i dati sulle colonne aggiuntive qtrnum. monnum. e daynum e ordini su yr modo che, con un offset di 1, si può calcolare l'anno fa vendite per lo stesso periodo. La clausola SELECT più esterna calcola le varianze. Creazione di un membro personalizzato in una dimensione: Esempio In molti compiti SQL di analisi, è utile definire membri personalizzati in una dimensione. Per esempio, si potrebbe definire un periodo di tempo specializzato per le analisi. È possibile utilizzare un esterno partizionato si uniscono per aggiungere temporaneamente un membro di una dimensione. Si noti che la nuova clausola SQL modello è adatto per la creazione di scenari più complessi che coinvolgono i nuovi membri nelle dimensioni. Vedere il Capitolo 23, SQL per la modellazione per ulteriori informazioni su questo argomento. Come esempio di un compito, quello che se vogliamo definire un nuovo membro per la nostra dimensione temporale Vogliamo creare un membro 13 ° del livello di mese nella nostra dimensione tempo. Questo 13 ° mese, è definito come la somma delle vendite per ogni prodotto nel primo mese di ogni trimestre del 2001. La soluzione ha due fasi. Si noti che costruiremo questa soluzione utilizzando i punti di vista e le tabelle create nell'esempio precedente. Sono necessari due passaggi. In primo luogo, creare una vista con il nuovo membro aggiunto alla dimensione appropriata. The view uses a UNION ALL operation to add the new member. To query using the custom member, use a CASE expression and a partitioned outer join. Our new member for the time dimension is created with the following view: In this statement, the view timec is defined by performing a UNION ALL of the edgetime view (defined in the prior example) and the user-defined 13th month. The gidt value of 8 was chosen to differentiate the custom member from the standard members. The UNION ALL specifies the attributes for a 13th month member by doing a SELECT from the DUAL table. Note that the grouping id, column gidt. is set to 8, and the quarter number is set to 5. Then, the second step is to use an inline view of the query to perform a partitioned outer join of cubeprodtime with timec. This step creates sales data for the 13th month at each level of product aggregation. In the main query, the analytic function SUM is used with a CASE expression to compute the 13th month, which is defined as the summation of the first months sales of each quarter. The SUM function uses a CASE to limit the data to months 1, 4, 7, and 10 within each year. Due to the tiny data set, with just 2 products, the rollup values of the results are necessarily repetitions of lower level aggregations. For more realistic set of rollup values, you can include more products from the Game Console and Y Box Games subcategories in the underlying materialized view. Miscellaneous Analysis and Reporting Capabilities This section illustrates the following additional analytic capabilities: WIDTHBUCKET Function For a given expression, the WIDTHBUCKET function returns the bucket number that the result of this expression will be assigned after it is evaluated. You can generate equiwidth histograms with this function. Equiwidth histograms divide data sets into buckets whose interval size (highest value to lowest value) is equal. The number of rows held by each bucket will vary. A related function, NTILE. creates equiheight buckets. Equiwidth histograms can be generated only for numeric, date or datetime types. So the first three parameters should be all numeric expressions or all date expressions. Other types of expressions are not allowed. If the first parameter is NULL. the result is NULL. If the second or the third parameter is NULL. an error message is returned, as a NULL value cannot denote any end point (or any point) for a range in a date or numeric value dimension. The last parameter (number of buckets) should be a numeric expression that evaluates to a positive integer value 0, NULL. or a negative value will result in an error. Buckets are numbered from 0 to ( n 1). Bucket 0 holds the count of values less than the minimum. Bucket( n 1) holds the count of values greater than or equal to the maximum specified value. WIDTHBUCKET Syntax The WIDTHBUCKET takes four expressions as parameters. The first parameter is the expression that the equiwidth histogram is for. The second and third parameters are expressions that denote the end points of the acceptable range for the first parameter. The fourth parameter denotes the number of buckets. Consider the following data from table customers. that shows the credit limits of 17 customers. This data is gathered in the query shown in Example 22-24 . In the table customers. the column custcreditlimit contains values between 1500 and 15000, and we can assign the values to four equiwidth buckets, numbered from 1 to 4, by using WIDTHBUCKET (custcreditlimit, 0, 20000, 4). Ideally each bucket is a closed-open interval of the real number line, for example, bucket number 2 is assigned to scores between 5000.0000 and 9999.9999. sometimes denoted 5000, 10000) to indicate that 5,000 is included in the interval and 10,000 is excluded. To accommodate values outside the range 0, 20,000), values less than 0 are assigned to a designated underflow bucket which is numbered 0, and values greater than or equal to 20,000 are assigned to a designated overflow bucket which is numbered 5 (num buckets 1 in general). See Figure 22-3 for a graphical illustration of how the buckets are assigned. Figure 22-3 Bucket Assignments You can specify the bounds in the reverse order, for example, WIDTHBUCKET ( custcreditlimit. 20000. 0. 4 ). When the bounds are reversed, the buckets will be open-closed intervals. In this example, bucket number 1 is ( 15000,20000 , bucket number 2 is ( 10000,15000 , and bucket number 4, is ( 0 ,5000 . The overflow bucket will be numbered 0 ( 20000. infinity ), and the underflow bucket will be numbered 5 (- infinity. 0 . It is an error if the bucket count parameter is 0 or negative. Example 22-24 WIDTHBUCKET The followin g query shows the bucket numbers for the credit limits in the customers table for both cases where the boundaries are specified in regular or reverse order. We use a range of 0 to 20,000. Linear Algebra Linear algebra is a branch of mathematics with a wide range of practical applications. Many areas have tasks that can be expressed using linear algebra, and here are some examples from several fields: statistics (multiple linear regression and principle components analysis), data mining (clustering and classification), bioinformatics (analysis of microarray data), operations research (supply chain and other optimization problems), econometrics (analysis of consumer demand data), and finance (asset allocation problems). Various libraries for linear algebra are freely available for anyone to use. Oracles UTLNLA package exposes matrix PLSQL data types and wrapper PLSQL subprograms for two of the most popular and robust of these libraries, BLAS and LAPACK. Linear algebra depends on matrix manipulation. Performing matrix manipulation in PLSQL in the past required inventing a matrix representation based on PLSQLs native data types and then writing matrix manipulation routines from scratch. This required substantial programming effort and the performance of the resulting implementation was limited. If developers chose to send data to external packages for processing rather than create their own routines, data transfer back and forth could be time consuming. Using the UTLNLA package lets data stay within Oracle, removes the programming effort, and delivers a fast implementation. Example 22-25 Linear Algebra Here is an example of how Oracles linear algebra support could be used for business analysis. It invokes a multiple linear regression application built using the UTLNLA package. The multiple regression application is implemented in an object called OLSRegression. Note that sample files for the OLS Regression object can be found in ORACLEHOMEplsqldemo . Consider the scenario of a retailer analyzing the effectiveness of its marketing program. Each of its stores allocates its marketing budget over the following possible programs: media advertisements ( media ), promotions ( promo ), discount coupons ( disct ), and direct mailers ( dmail ). The regression analysis builds a linear relationship between the amount of sales that an average store has in a given year ( sales ) and the spending on the four components of the marketing program. Suppose that the marketing data is stored in the following table: Then you can build the following sales-marketing linear model using coefficients: This model can be implemented as the following view, which refers to the OLS regression object: Using this view, a marketing program manager can perform an analysis such as Is this sales-marketing model reasonable for year 2004 data That is, is the multiple-correlation greater than some acceptable value, say, 0.9 The SQL for such a query might be as follows: You could also solve questions such as What is the expected base-line sales revenue of a store without any marketing programs in 2003 or Which component of the marketing program was the most effective in 2004 That is, a dollar increase in which program produced the greatest expected increase in sales See Oracle Database PLSQL Packages and Types Reference for further information regarding the use of the UTLNLA package and linear algebra. CASE Expressions Oracle now supports simple and searched CASE statements. CASE statements are similar in purpose to the DECODE statement, but they offer more flexibility and logical power. They are also easier to read than traditional DECODE statements, and offer better performance as well. They are commonly used when breaking categories into buckets like age (for example, 20-29, 30-39, and so on). The syntax for simple CASE statements is: Simple CASE expressions test if the expr value equals the comparisonexpr . The syntax for searched CASE statements is: You can use any kind of condition in a searched CASE expression, not just an equality test. You can specify only 65,535 arguments and each WHEN. THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the returnexpr itself is a CASE expression. Example 22-26 CASE Suppose you wanted to find the average salary of all employees in the company. If an employees salary is less than 2000, you want the query to use 2000 instead. Without a CASE statement, you might choose to write this query as follows: Note that this runs against the hr sample schema. In this, foo is a function that returns its input if the input is greater than 2000, and returns 2000 otherwise. The query has performance implications because it needs to invoke a function for each row. Writing custom functions can also add to the development load. Using CASE expressions in the database without PLSQL, this query can be rewritten as: Using a CASE expression lets you avoid developing custom functions and can also perform faster. Example 22-27 CASE for Aggregating Independent Subsets Using CASE inside aggregate functions is a convenient way to perform aggregates on multiple subsets of data when a plain GROUP BY will not suffice. For instance, the preceding example could have included multiple AVG columns in its SELECT list, each with its own CASE expression. We might have had a query find the average salary for all employees in the salary ranges 0-2000 and 2000-5000. It would look like: Although this query places the aggregates of independent subsets data into separate columns, by adding a CASE expression to the GROUP BY clause we can display the aggregates as the rows of a single column. The next section shows the flexibility of this approach with two approaches to creating histograms with CASE . Creating Histograms You can use the CASE statement when you want to obtain histograms with user-defined buckets (both in number of buckets and width of each bucket). The following are two examples of histograms created with CASE statements. In the first example, the histogram totals are shown in multiple columns and a single row is returned. In the second example, the histogram is shown with a label column and a single column for totals, and multiple rows are returned. Example 22-28 Histogram Example 1 Example 22-29 Histogram Example 2 Frequent Itemsets Instead of counting how often a given event occurs (for example, how often someone has purchased milk at the grocery), you may find it useful to count how often multiple events occur together (for example, how often someone has purchased both milk and cereal together at the grocery store). You can count these multiple events using what is called a frequent itemset, which is, as the name implies, a set of items. Some examples of itemsets could be all of the products that a given customer purchased in a single trip to the grocery store (commonly called a market basket), the web pages that a user accessed in a single session, or the financial services that a given customer utilizes. The practical motivation for using a frequent itemset is to find those itemsets that occur most often. If you analyze a grocery stores point-of-sale data, you might, for example, discover that milk and bananas are the most commonly bought pair of items. Frequent itemsets have thus been used in business intelligence environments for many years, with the most common one being for market basket analysis in the retail industry. Frequent itemset calculations are integrated with the database, operating on top of relational tables and accessed through SQL. This integration provides the following key benefits: Applications that previously relied on frequent itemset operations now benefit from significantly improved performance as well as simpler implementation. SQL-based applications that did not previously use frequent itemsets can now be easily extended to take advantage of this functionality. Frequent itemsets analysis is performed with the PLSQL package DBMSFREQUENTITEMSETS. See Oracle Database PLSQL Packages and Types Reference for more information. In addition, there is an example of frequent itemset usage in Frequent itemsets . Scripting on this page enhances content navigation, but does not change the content in any way. Introduction The previous article looked at what moving averages are and how to calculate them. Questo articolo esamina ora come implementare questi in Web Intelligence. La formula usata qui sono compatibili con la versione XIr3 di SAP BOE tuttavia alcuni formula può funzionare nelle versioni precedenti se disponibili. We8217ll iniziare cercando il modo per calcolare una media mobile semplice prima di guardare le forme ponderate ed esponenziali. Esempi pratici Gli esempi che seguono utilizzano tutti lo stesso insieme di dati che è di dati di prezzo delle azioni in un file Excel che può essere scaricato. La prima colonna del file è il giorno del prezzo delle azioni e poi colonne di prezzo di apertura, prezzo più alto nel corso della giornata, prezzo più basso, prezzo, volume e il prezzo di chiusura rettificato di chiusura. We8217ll usare prezzo di chiusura nella nostra analisi di seguito con l'oggetto Date. Media mobile semplice C'è un paio di modi con cui siamo in grado di calcolare semplici medie mobili. Una possibilità è quella di utilizzare la funzione precedente per ottenere il valore di una riga precedente. Per esempio la seguente formula calcola una media mobile sulla nostra chiusura prezzo delle azioni per un set di dati media mobile di dimensioni 3, questo è abbastanza semplice formula tuttavia è ovvio che non è pratico quando abbiamo un gran numero di periodi qui possiamo fare uso di RunningSum formula e per un insieme di dati di dimensione N abbiamo finalmente abbiamo una terza tecnica, che anche se più complicato può avere prestazioni migliori in quanto sta calcolando il nuovo valore in base al valore precedente, piuttosto che due somme in esecuzione sui dati completo impostato. Tuttavia questa formula funziona solo dopo il punto Nth nei dati generali stabiliti e poiché si riferisce ad un valore precedente bisogna anche impostare un valore iniziale. Qui di seguito è la formula piena utilizzato per la nostra analisi del prezzo delle azioni in cui il nostro periodo di media mobile è di 15 giorni, la data di 1.252.010 è il punto di dati 15 nei nostri set di dati e quindi per questo punto calcoliamo una media normale utilizzando il RunningSum. Per tutte le date di là di questo valore usiamo la nostra formula SMA e lasciamo vuoto tutte le date prima di tale data. Figura 1 è un grafico in Web Intelligence visualizza i nostri dati di prezzo delle azioni, con una media mobile semplice. Figura 1. Documento Web Intelligenza visualizzazione di una commovente ponderata formula semplice media ponderata media Una media mobile con un periodo di 3 è, come con la nostra prima mobile semplice formula superiore alla media questo è pratico solo per un piccolo numero di periodi. Non ho ancora stato in grado di trovare una formula semplice che può essere utilizzato per le più grandi in movimento periodi medi. Matematicamente è possibile, ma le limitazioni con Web Intelligence significa che queste formule don8217t convertono. Se qualcuno è in grado di fare questo mi piacerebbe sentire La figura seguente è un WMA del periodo di 6 implementato in Web Intelligence. Figura 2. Web documento intelligenza di una ponderata media mobile media mobile esponenziale Una media mobile esponenziale è abbastanza semplice da implementare in Web Intelligence e quindi è una valida alternativa ad una ponderata media mobile. La formula di base è qui we8217ve difficile codificato 0.3 come il nostro rapporto qualità-alfa. Applichiamo solo questa formula per periodi più grande del nostro secondo periodo in modo che possiamo utilizzare un'istruzione if per filtrare questi fuori. Per il nostro primo e secondo periodo possiamo usare il valore precedente e così la nostra formula finale per EMA è, qui sotto è un esempio di un EMA applicata ai nostri dati di stock. visualizzare Figura documento Intelligenza 3. Web un mobile esponenziale Controlli media di ingresso come la nostra formula EMA doesn8217t contare sulla dimensione del periodo di media mobile e la nostra unica variabile è alfa possiamo utilizzare controlli di input per consentire all'utente di regolare il valore di alfa. Per fare questo, creare una nuova variabile denominata 8216alpha8217 e definire it8217s formula come, aggiornare la nostra formula EMA, creare un nuovo controllo di input selezionando la nostra variabile alfa come il rapporto oggetto di controllo in ingresso utilizzare un semplice dispositivo di scorrimento e impostare le seguenti proprietà, una volta hai fatto dovrebbe essere in grado di spostare il cursore e vedere immediatamente le modifiche alla linea di tendenza nella Conclusione grafico Abbiamo guardato a come implementare tre tipi di media mobile in Web intelligence e anche se tutto fosse possibile la media mobile esponenziale è probabilmente il più semplice e più flessibile . Spero che hai trovato questo articolo interessante e come sempre tutte le risposte è molto gradito. Messaggio di navigazione Lascia un commento Cancella risposta Devi essere loggato per pubblicare un commento. Il trucco per Weighted Moving Average (WMA) è che devi creare una variabile che rappresenta i numeratori di WMA (vedi Wikipedia per riferimento.) Questa dovrebbe essere simile alla seguente: Precedente (Self) (n chiudi) 8211 (precedente (RunningSum ( Chiudi)) 8211 precedente (RunningSum (Chiudi) n1) dove n è il numero di periodi Poi il WMA8217s formula attuale sarebbe come questo:. numeratore (n (n 1) 2) dove numeratore è la variabile creata in precedenza.
No comments:
Post a Comment