Pivot s / bez Tablefunc

Kontingenčná tabuľka je mocný nástroj na odhadovanie, kompiláciu a kontrolu údajov, vďaka čomu je možné nájsť vzory a trendy ešte jednoduchšie. Kontingenčné tabuľky možno použiť na agregáciu, triedenie, usporiadanie, zmenu usporiadania, zoskupenie, celkové alebo priemerné údaje v množine údajov, aby ste skutočne pochopili asociácie údajov a závislosti. Použitie kontingenčnej tabuľky ako ilustrácie je najjednoduchší spôsob, ako demonštrovať, ako táto metóda funguje. PostgreSQL 8.3 bola uvedená na trh pred niekoľkými rokmi a jej nová verzia s názvom „tablefunc' bol pridaný. Tablefunc je komponent, ktorý obsahuje niekoľko metód, ktoré poskytujú tabuľky (tj viac riadkov). Táto úprava má veľmi skvelú škálu funkcií. Medzi nimi je aj krížová metóda, ktorá sa použije na vytvorenie kontingenčných tabuliek. Metóda krížovej tabuľky má textový argument: príkaz SQL, ktorý vráti nespracované údaje v prvom rozložení a vráti tabuľku v nasledujúcom rozložení.

Príklad kontingenčnej tabuľky bez TableFunc:

Ak chcete začať pracovať na otáčaní PostgreSQL s modulom 'tablefunc', musíte sa pokúsiť vytvoriť kontingenčnú tabuľku bez nej. Poďme teda otvoriť shell príkazového riadku PostgreSQL a poskytnúť hodnoty parametrov pre požadovaný server, databázu, číslo portu, používateľské meno a heslo. Ak chcete použiť predvolené vybrané parametre, nechajte tieto parametre prázdne.

Vytvoríme novú tabuľku s názvom „Test“ v databáze „test“, ktorá bude obsahovať niektoré polia, ako je zobrazené nižšie.

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

Po vytvorení tabuľky je čas vložiť do tabuľky niektoré hodnoty, ako ukazuje nasledujúci dotaz.

>> INSERT INTO Test (Id, name, sal, job) VALUES (11, 'Aqsa', 45000, 'Writer'), (11, 'Aqsa', 48000, 'Officer'), (11, 'Aqsa', 50000, „Doctor“), (12, „Raza“, 40000, „Officer“), (11, „Raza“, 60000, „Doctor“), (12, „Raza“, 67000, „Officer“), ( 13, „Saeed“, 85000, „Spisovateľ“), (13, „Saeed“, 69000, „Dôstojník“), (13, „Saeed“, 90000, „Doktor“);

Uvidíte, že príslušné údaje boli úspešne vložené. Môžete vidieť, že táto tabuľka obsahuje viac ako 1 rovnakých hodnôt pre ID, meno a úlohu.

>> SELECT * FROM vstup;

Vytvorme kontingenčnú tabuľku, ktorá pomocou nasledujúceho dotazu zhrnie záznam tabuľky „Test“. Príkaz zlúči rovnaké hodnoty stĺpca „Id“ a „meno“ do jedného riadku, pričom zohľadní súčet hodnôt stĺpca „plat“ pre rovnaké údaje podľa „Id“ a „meno“. Hovorí tiež o tom, koľkokrát sa v konkrétnej množine hodnôt vyskytla jedna hodnota.

>> SELECT Id, name, sum (sal) sal, sum ((job = 'Doctor') :: int) Doctor, sum ((job = 'Writer') :: int) Writer, sum ((job = 'Officer) ') :: int) "Dôstojník" ZO SKÚŠOBNEJ SKUPINY PODĽA ID, meno;

Príklad kontingenčnej tabuľky s TableFunc:

Začneme vysvetlením nášho hlavného bodu z realistického hľadiska a potom popíšeme vytvorenie kontingenčnej tabuľky v krokoch, ktoré sa nám páčia. Najskôr teda musíte pridať tri tabuľky, aby ste mohli pracovať na pivot. Prvá tabuľka, ktorú vytvoríme, je „Makeup“, v ktorej budú uložené informácie týkajúce sa základných makeupov. Vyskúšajte nasledujúci dotaz v prostredí príkazového riadku, aby ste vytvorili túto tabuľku.

>> VYTVORTE TABUĽKU, AK NEEXISTUJE Make-up (make_Id int PRIMARY KEY, p_name VARCHAR (100) NOT NULL);

Po vytvorení tabuľky „Makeup“ k nej pridajme nejaké záznamy. V shelli vykonáme nižšie uvedený dotaz, aby sme do tejto tabuľky pridali 10 záznamov.

Musíme vytvoriť ďalšiu tabuľku s názvom „používatelia“, ktorá bude uchovávať záznamy o používateľoch, ktorí používajú tieto produkty. Vykonajte nižšie uvedený dotaz v prostredí shell, aby ste vytvorili túto tabuľku.

>> VYTVORIŤ TABUĽKU, AK NEEXISTUJE používateľov (user_id int PRIMARY KEY, u_name varchar (100) NOT NULL);

Vložili sme 20 záznamov tabuľky „používatelia“, ako je to znázornené na obrázku nižšie.

Máme ďalšiu tabuľku „makeup_user“, ktorá bude uchovávať vzájomné záznamy tabuľky „Makeup“ aj „users“. Má ďalšie pole „cena“, ktoré ušetrí cenu produktu. Tabuľka bola vygenerovaná pomocou nižšie uvedeného dotazu.

>> CREATE TABLE IF NOT EXISTS makeup_user (ID int PRIMARY KEY, Mid int NOT NULL REFERENCES Makeup (make_Id), Uid int NOT NULL REFERENCES users (user_id), price decimal (18,2));

Do tejto tabuľky sme vložili celkom 56 záznamov, ako je to znázornené na obrázku.

Vytvorme ďalšie zobrazenie, ktoré ho použijeme na generovanie kontingenčnej tabuľky. Toto zobrazenie používa funkciu INNER Join na priradenie hodnôt stĺpca primárneho kľúča všetkých troch tabuliek a na získanie parametrov „name“, „product_name“ a „cost“ produktu z tabuľky „customers“

>> VYTVORTE ZOBRAZENIE v_makeup_users AKO VYBERTE c.u_name, str.p_name, ks.cena OD POUŽÍVATEĽOV c INNER JOIN makeup_user pc ON c.user_id = ks.Uid INNER JOIN Makeup p ON ks.Stred = str.make_Id;

Ak to chcete použiť, musíte najskôr nainštalovať balík tablefunc pre databázu, ktorú chcete použiť. Tento balík má zabudovanú PostgreSQL 9.1 a neskôr uvoľnená spustením nižšie uvedeného príkazu. Balík tablefunc je pre vás teraz povolený.

>> VYTVORTE ROZŠÍRENIE, AK NEEXISTUJE tablefunc;

Po vytvorení rozšírenia je čas použiť funkciu Crosstab () na vytvorenie kontingenčnej tabuľky. Použijeme na to teda nasledujúci dotaz v prostredí príkazového riadku. Tento dopyt najskôr získava záznam z novovytvoreného poľa „Zobraziť“. Tieto záznamy budú zoradené a zoskupené podľa vzostupného poradia stĺpcov „u_name“ a „p_name“. V tabuľke sme uviedli ich meno pre každého zákazníka, ktoré si kúpil, a celkové náklady na výrobky zakúpené v tabuľke. Na zhrnutie všetkých produktov zakúpených jedným zákazníkom zvlášť sme použili operátor UNION ALL v stĺpci „p_name“. Takto sa všetky náklady na produkty zakúpené používateľom zhrnú do jednej hodnoty.

Naša kontingenčná tabuľka je pripravená a zobrazuje sa na obrázku. Jasne vidíte, že niektoré medzery v stĺpcoch sú prázdne pod každým názvom p_name, pretože si nekúpili konkrétny produkt.

Záver:

Teraz sme sa geniálne naučili, ako vytvoriť kontingenčnú tabuľku na zhrnutie výsledkov tabuliek s použitím balíka Tablefunc a bez neho.