Phenquestions
Una tabella pivot è un potente strumento per stimare, compilare e rivedere i dati per trovare modelli e tendenze ancora più facilmente. Le tabelle pivot possono essere utilizzate per aggregare, ordinare, organizzare, riorganizzare, raggruppare, totali o dati medi in un set di dati per comprendere veramente le associazioni e le dipendenze dei dati. Usare una tabella pivot come illustrazione è il modo più semplice per dimostrare come funziona questo metodo. PostgreSQL 8.3 è stato lanciato alcuni anni fa e una nuova versione denominata 'tablefunc' è stato aggiunto. Tablefunc è un componente che contiene diversi metodi che producono tabelle (ovvero più righe). Questa modifica include una gamma molto interessante di funzionalità. Il metodo della tabella incrociata, che verrà utilizzato per creare tabelle pivot, è tra questi. Il metodo crosstab accetta un argomento testuale: un comando SQL che restituisce dati grezzi nel primo layout e restituisce una tabella nel layout successivo.
Esempio di tabella pivot senza TableFunc:
Per iniziare a lavorare sul pivot di PostgreSQL con il modulo 'tablefunc', devi provare a creare una tabella pivot senza di esso. Quindi apriamo la shell della riga di comando di PostgreSQL e forniamo i valori dei parametri per il server, il database, il numero di porta, il nome utente e la password richiesti. Lascia questi parametri vuoti se vuoi usare i parametri selezionati di default.
Creeremo una nuova tabella denominata "Test" nel database "test" con alcuni campi al suo interno, come mostrato di seguito.
>> CREATE TABLE Test(Id int, nome varchar(20), sal int, job varchar(20));
Dopo aver creato una tabella, è il momento di inserire alcuni valori nella tabella, come mostrato dalla query seguente.
>> INSERT INTO Test (Id, name, sal, job) VALUES (11, 'Aqsa', 45000, 'Writer'), (11, 'Aqsa', 48000, 'Officer'), (11, 'Aqsa', 50000, 'Dottore'), (12, 'Raza', 40000, 'Ufficiale'), (11, 'Raza', 60000, 'Dottore'), (12, 'Raza', 67000, 'Ufficiale'), ( 13, 'Saeed', 85000, 'Scrittore'), (13, 'Saeed', 69000, 'Ufficiale'), (13, 'Saeed', 90000, 'Dottore');
Puoi vedere che i dati relativi sono stati inseriti con successo. Puoi vedere che questa tabella ha più di uno degli stessi valori per id, nome e lavoro.
>> SELEZIONA * DA ingresso;
Creiamo una tabella pivot, che riassumerà il record della tabella "Test" utilizzando la query seguente. Il comando unisce gli stessi valori della colonna "Id" e "nome" in una riga mentre prende la somma dei valori della colonna "stipendio" per gli stessi dati in base a "Id" e "nome". Indica anche quante volte un valore si è verificato in un particolare insieme di valori.
>> SELECT Id, name, sum(sal) sal, sum((job = 'Doctor')::int) Doctor, sum((job = 'Writer')::int) Writer, sum((job = 'Officer' ')::int) "Ufficiale" FROM Test GROUP BY Id, nome;
Esempio di tabella pivot con TableFunc:
Inizieremo spiegando il nostro punto principale da un punto di vista realistico, quindi descriveremo la creazione della tabella pivot in passaggi che ci piacciono. Quindi, prima di tutto, devi aggiungere tre tabelle per lavorare su un pivot. La prima tabella che creeremo è "Trucco", che conserverà le informazioni relative agli elementi essenziali del trucco. Prova la query seguente nella shell della riga di comando per creare questa tabella.
>> CREATE TABLE IF NOT EXISTS Makeup(make_Id int PRIMARY KEY, p_name VARCHAR(100) NOT NULL);
Dopo la creazione della tabella 'Makeup', aggiungiamo alcuni record ad essa. Eseguiremo la query elencata di seguito nella shell per aggiungere 10 record a questa tabella.
Dobbiamo creare un'altra tabella denominata "utenti" che conterrà i record degli utenti che utilizzano quei prodotti. Esegui la query indicata di seguito nella shell per creare questa tabella.
>> CREATE TABLE IF NOT EXISTS users(user_id int PRIMARY KEY, u_name varchar(100) NOT NULL);
Abbiamo inserito i 20 record per la tabella 'utenti' come mostrato nell'immagine qui sotto.
Abbiamo un'altra tabella, 'makeup_user', che conterrà i record reciproci della tabella 'Makeup' e 'users'. Ha un altro campo, 'prezzo', che farà risparmiare il prezzo del prodotto. La tabella è stata generata utilizzando la query indicata di seguito.
>> CREATE TABLE IF NOT EXISTS makeup_user( ID int PRIMARY KEY, Mid int NOT NULL REFERENCES Makeup(make_Id), Uid int NOT NULL REFERENCES utenti (user_id), prezzo decimale(18,2));
Abbiamo inserito un totale di 56 record in questa tabella, come mostrato nell'immagine.
Creiamo ulteriormente una vista per usarla per la generazione di una tabella pivot. Questa vista utilizza INNER Join per abbinare i valori della colonna della chiave primaria di tutte e tre le tabelle e recuperare il "nome", "nome_prodotto" e il "costo" di un prodotto da una tabella "clienti"
>> CREA VISTA v_makeup_users COME SELEZIONARE c.u_name, p.p_name, pc.prezzo DA utenti c INNER JOIN makeup_user pc ON c.user_id = pc.Uid INNER JOIN Trucco su PC.medio = p.make_Id;
Per usarlo, devi prima installare il pacchetto tablefunc per il database che vuoi usare. Questo pacchetto è PostgreSQL 9 integrato.1 e versioni successive rilasciate eseguendo il comando indicato di seguito. Il pacchetto tablefunc è stato abilitato per te ora.
>> CREA ESTENSIONE SE NON ESISTE tablefunc;
Dopo aver creato l'estensione, è il momento di utilizzare la funzione Crosstab() per creare una tabella pivot. Quindi useremo la seguente query nella shell della riga di comando per farlo. Questa query sta prima recuperando il record dalla "Vista" appena creata. Questi record verranno ordinati e raggruppati in base all'ordine crescente delle colonne 'u_name' e 'p_name'. Abbiamo elencato il loro nome di trucco per ogni cliente, che hanno acquistato e il costo totale dei prodotti acquistati nella tabella. Abbiamo applicato l'operatore UNION ALL sulla colonna 'p_name' per riassumere separatamente tutti i prodotti acquistati da un cliente. Questo riassumerà tutti i costi dei prodotti acquistati da un utente in un unico valore.
La nostra tabella Pivot è stata pronta e visualizzata nell'immagine. Puoi vedere chiaramente che alcuni spazi di colonna sono vuoti sotto ogni p_name perché non hanno acquistato quel particolare prodotto.
Conclusione:
Ora abbiamo imparato brillantemente come creare una tabella pivot per riassumere i risultati delle tabelle con e senza l'uso del pacchetto Tablefunc.
