Pivotați cu / fără Tablefunc

Un tabel pivot este un instrument puternic pentru estimarea, compilarea și revizuirea datelor pentru a găsi modele și tendințe și mai ușor. Tabelele pivot pot fi utilizate pentru a agrega, sorta, aranja, rearanja, grupa, totaliza sau media date într-un set de date pentru a înțelege cu adevărat asociațiile și dependențele de date. Utilizarea unui tabel pivot ca ilustrație este cel mai simplu mod de a demonstra modul în care funcționează această metodă. PostgreSQL 8.3 a fost lansat cu câțiva ani în urmă și o nouă versiune numită „tablefunc' a fost adăugat. Tablefunc este o componentă care conține mai multe metode care produc tabele (adică mai multe rânduri). Această modificare vine cu o gamă foarte interesantă de caracteristici. Metoda încrucișată, care va fi utilizată pentru a crea tabele pivot, se numără printre acestea. Metoda tabelului încrucișat ia un argument textual: o comandă SQL care returnează date brute în primul aspect și returnează un tabel în aspectul ulterior.

Exemplu de tabel pivot fără TableFunc:

Pentru a începe să lucrați la pivotarea PostgreSQL cu modulul „tablefunc”, trebuie să încercați să creați un tabel pivot fără acesta. Deci, să deschidem shell-ul de linie de comandă PostgreSQL și să furnizăm valorile parametrilor pentru serverul necesar, baza de date, numărul portului, numele de utilizator și parola. Lăsați acești parametri goi dacă doriți să utilizați parametrii selectați implicit.

Vom crea un nou tabel numit „Test” în baza de date „test” cu câteva câmpuri în el, așa cum este afișat mai jos.

>> CREATE TABLE Test (Id int, nume varchar (20), sal int, job varchar (20));

După crearea unui tabel, este timpul să inserați câteva valori în tabel, așa cum se arată în interogarea de mai jos.

>> INSERT INTO Test (Id, name, sal, job) VALORI (11, 'Aqsa', 45000, 'Writer'), (11, 'Aqsa', 48000, 'Officer'), (11, 'Aqsa', 50000, „Doctor”), (12, „Raza”, 40000, „Ofițer”), (11, „Raza”, 60000, „Doctor”), (12, „Raza”, 67000, „Ofițer”), ( 13, „Saeed”, 85000, „Scriitor”), (13, „Saeed”, 69000, „Ofițer”), (13, „Saeed”, 90000, „Doctor”);

Puteți vedea că datele relevante au fost inserate cu succes. Puteți vedea că acest tabel are mai mult de 1 din aceleași valori pentru id, nume și job.

>> SELECT * FROM intrare;

Să creăm un tabel pivot, care va rezuma înregistrarea tabelului „Test” folosind interogarea de mai jos. Comanda îmbină aceleași valori ale coloanei „Id” și „nume” într-un singur rând în timp ce ia suma valorilor coloanei „salariu” pentru aceleași date conform „Id” și „nume”. De asemenea, indică de câte ori a apărut o valoare în setul de valori.

>> SELECT ID, nume, sumă (sal) sal, sumă ((job = 'Doctor') :: int) Doctor, sumă ((job = 'Scriitor') :: int) Scriitor, sumă ((job = 'Ofițer ') :: int) "Ofițer" DIN GRUPUL DE TEST DUPĂ ID, nume;

Exemplu de tabel pivot cu TableFunc:

Vom începe prin a explica punctul nostru principal dintr-un punct de vedere realist și apoi vom descrie crearea tabelului pivot în pașii care ne plac. Deci, mai întâi de toate, trebuie să adăugați trei tabele pentru a lucra pe un pivot. Primul tabel pe care urmează să îl creăm este „Machiaj”, care va stoca informații referitoare la elementele esențiale de machiaj. Încercați interogarea de mai jos din shell-ul liniei de comandă pentru a crea acest tabel.

>> CREAȚI TABEL DACĂ NU EXISTĂ Machiaj (make_Id int CHEIE PRIMARĂ, p_name VARCHAR (100) NU NUL);

După crearea tabelului „Machiaj”, să adăugăm câteva înregistrări la acesta. Vom executa interogarea de mai jos în shell pentru a adăuga 10 înregistrări la acest tabel.

Trebuie să creăm un alt tabel numit „utilizatori” care va păstra înregistrările utilizatorilor care utilizează produsele respective. Executați interogarea menționată mai jos în shell pentru a crea acest tabel.

>> CREARE TABEL DACĂ NU EXISTĂ utilizatori (user_id int CHEIE PRIMARĂ, u_name varchar (100) NOT NULL);

Am inserat cele 20 de înregistrări pentru tabelul „utilizatori” așa cum se arată în imaginea de mai jos.

Avem un alt tabel, „makeup_user”, care va deține înregistrările reciproce atât ale tabelului „Machiaj”, cât și ale „utilizatorilor”. Are un alt câmp, „preț”, care va economisi prețul produsului. Tabelul a fost generat utilizând interogarea de mai jos.

>> CREAȚI TABEL DACĂ NU EXISTĂ makeup_user (ID int PRIMARY KEY, Mid int NOT NULL REFERENCES Makeup (make_Id), Uid int NOT NULL REFERENCES utilizatori (user_id), preț zecimal (18,2));

Am inserat un total de 56 de înregistrări în acest tabel, așa cum se arată în imagine.

Să creăm o vizualizare în continuare pentru a o utiliza pentru generarea unui tabel pivot. Această vizualizare folosește INNER Join pentru a se potrivi cu valorile coloanei cheii primare ale tuturor celor trei tabele și pentru a prelua „numele”, „numele_produsului” și „costul” unui produs dintr-un tabel „clienții”

>> CREATE VIEW v_makeup_users AS SELECT c.u_name, p.p_name, pc.preț DE LA utilizatori c INNER JOIN makeup_user pc ON c.user_id = pc.Uid INNER JOIN Machiaj p ON pc.Mijloc = p.make_Id;

Pentru a utiliza acest lucru, trebuie să instalați mai întâi pachetul tablefunc pentru baza de date pe care doriți să o utilizați. Acest pachet este încorporat PostgreSQL 9.1 și ulterior lansat executând comanda menționată mai jos. Pachetul tablefunc a fost activat pentru dvs. acum.

>> CREAȚI EXTENSIUNE DACĂ NU EXISTĂ tablefunc;

După crearea extensiei, este timpul să utilizați funcția Crosstab () pentru a crea un tabel pivot. Deci, vom folosi următoarea interogare în shell-ul liniei de comandă pentru a face acest lucru. Această interogare primește mai întâi înregistrarea din noua „Vizualizare” creată. Aceste înregistrări vor fi ordonate și grupate după ordinea crescătoare a coloanelor „u_name” și „p_name”. Am enumerat numele de machiaj pentru fiecare client pe care l-au achiziționat și costul total al produselor achiziționate în tabel. Am aplicat operatorul UNION ALL pe coloana „p_name” pentru a rezuma separat toate produsele cumpărate de un singur client. Aceasta va însuma toate costurile produselor achiziționate de un utilizator într-o singură valoare.

Tabelul nostru pivot a fost gata și afișat în imagine. Puteți vedea clar că unele spații ale coloanelor sunt goale sub fiecare p_name deoarece nu au achiziționat acel produs.

Concluzie:

Acum am învățat strălucit cum să creăm un tabel pivot pentru a rezuma rezultatele tabelelor cu și fără utilizarea pachetului Tablefunc.