Phenquestions
Tabela przestawna to potężne narzędzie do szacowania, kompilowania i przeglądania danych w celu jeszcze łatwiejszego znajdowania wzorców i trendów. Tabele przestawne mogą być używane do agregowania, sortowania, porządkowania, przestawiania, grupowania, sumowania lub uśredniania danych w zestawie danych, aby naprawdę zrozumieć asocjacje i zależności danych. Użycie tabeli przestawnej jako ilustracji to najprostszy sposób na zademonstrowanie działania tej metody. PostgreSQL 8.3 został wydany kilka lat temu, a nowa wersja o nazwie 'funkcja stołu' zostało dodane. Funkcja tabeli to komponent, który zawiera kilka metod, które dają tabele (tj. wiele wierszy). Ta modyfikacja ma bardzo fajny zakres funkcji. Wśród nich jest metoda tabeli przestawnej, która posłuży do tworzenia tabel przestawnych. Metoda crosstab przyjmuje argument tekstowy: polecenie SQL, które zwraca surowe dane w pierwszym układzie i zwraca tabelę w kolejnym układzie.
Przykładowa tabela przestawna bez funkcji TableFunc:
Aby rozpocząć pracę nad przestawianiem PostgreSQL z modułem 'tablefunc', musisz spróbować stworzyć tabelę przestawną bez tego modułu. Otwórzmy więc powłokę wiersza poleceń PostgreSQL i podajmy wartości parametrów dla wymaganego serwera, bazy danych, numeru portu, nazwy użytkownika i hasła. Pozostaw te parametry puste, jeśli chcesz użyć domyślnych wybranych parametrów.
Utworzymy nową tabelę o nazwie „Test” w bazie danych „test” z kilkoma polami, jak pokazano poniżej.
>> CREATE TABLE Test(Id int, name varchar(20), sal int, job varchar(20));
Po stworzeniu tabeli nadszedł czas, aby wstawić do niej kilka wartości, tak jak widać z poniższego zapytania.
>> INSERT INTO Test (Id, name, sal, job) WARTOŚCI (11, 'Aqsa', 45000, 'Writer'), (11, 'Aqsa', 48000, 'Officer'), (11, 'Aqsa', 50000, „Doktor”), (12, „Raza”, 40000, „Oficer”), (11, „Raza”, 60000, „Doktor”), (12, „Raza”, 67000, „Oficer”), ( 13, „Saeed”, 85000, „Pisarz”, (13, „Saeed”, 69000, „Oficer”), (13, „Saeed”, 90000, „Doktor”);
Możesz zobaczyć, że odpowiednie dane zostały pomyślnie wstawione. Widać, że ta tabela ma więcej niż 1 takie same wartości dla identyfikatora, nazwiska i zadania.
>> WYBIERZ * Z wejścia;
Utwórzmy tabelę przestawną, która podsumuje rekord tabeli „Test” za pomocą poniższego zapytania. Polecenie łączy te same wartości kolumny „Id” i „imię” w jednym wierszu, biorąc sumę wartości kolumny „wynagrodzenie” dla tych samych danych zgodnie z „Id” i „imię”. Mówi również, ile razy jedna wartość wystąpiła w określonym zestawie wartości.
>> SELECT Id, name, sum(sal) sal, sum((job = 'Doktor')::int) Doctor, sum((job = 'Writer')::int) Writer, sum((job = 'Officer ')::int) "Oficer" Z GRUPY testowej WEDŁUG ID, imienia;
Przykładowa tabela przestawna z funkcją TableFunc:
Zaczniemy od wyjaśnienia naszego głównego punktu z realistycznego punktu widzenia, a następnie opiszemy tworzenie tabeli przestawnej w krokach, które nam się podobają. Przede wszystkim musisz dodać trzy tabele, aby pracować nad osią obrotu. Pierwsza tabela, którą stworzymy, to „Makijaż”, w której będą przechowywane informacje dotyczące niezbędnych do makijażu. Wypróbuj poniższe zapytanie w powłoce wiersza poleceń, aby utworzyć tę tabelę.
>> UTWÓRZ TABELĘ, JEŚLI NIE ISTNIEJE Makeup(make_Id int PRIMARY KEY, p_name VARCHAR(100) NOT NULL);
Po utworzeniu tabeli 'Makijaż' dodajmy do niej kilka rekordów. Będziemy wykonywać poniższe zapytanie w powłoce, aby dodać 10 rekordów do tej tabeli.
Musimy stworzyć kolejną tabelę o nazwie 'users', która będzie zawierała rekordy użytkowników korzystających z tych produktów. Wykonaj poniższe zapytanie w powłoce, aby utworzyć tę tabelę.
>> UTWÓRZ TABELĘ, JEŚLI NIE ISTNIEJE użytkownicy(user_id int PRIMARY KEY, u_name varchar(100) NOT NULL);
Wstawiliśmy 20 rekordów dla tabeli „użytkownicy”, jak pokazano na poniższym obrazku.
Mamy kolejną tabelę „makeup_user”, w której będą przechowywane wzajemne rekordy zarówno tabeli „Makeup” jak i „users”. Posiada jeszcze jedno pole „cena”, które pozwoli zaoszczędzić cenę produktu. Tabela została wygenerowana przy użyciu poniższego zapytania.
>> UTWÓRZ TABELĘ, JEŚLI NIE ISTNIEJE makeup_user( ID int PRIMARY KEY, Mid int NIE ODNIESIENIA NULL Makeup(make_Id), Uid int NIE NULL REFERENCES users(user_id), cena dziesiętna(18,2));
W tej tabeli umieściliśmy łącznie 56 rekordów, jak pokazano na rysunku.
Stwórzmy widok dalej, aby użyć go do wygenerowania tabeli przestawnej. Ten widok używa INNER Join, aby dopasować wartości kolumn klucza podstawowego wszystkich trzech tabel i pobrać „name”, „product_name” i „cost” produktu z tabeli „customers”
>> UTWÓRZ WIDOK v_makeup_users JAKO WYBIERZ c.u_name, p.nazwa_p, komputer.cena OD użytkowników c WEWNĘTRZNA DOŁĄCZ MAKIJAŻ_użytkownik szt. c.identyfikator_użytkownika = komputer.Uid INNER JOIN Makijaż p NA PC.Środek = p.make_Id;
Aby z tego skorzystać, musisz najpierw zainstalować pakiet tablefunc dla bazy danych, której chcesz używać. Ten pakiet jest wbudowany w PostgreSQL 9.1 i później zwolnione przez uruchomienie poniższego polecenia. Pakiet tablefunc został dla Ciebie włączony.
>> UTWÓRZ ROZSZERZENIE JEŚLI NIE ISTNIEJE tablefunc;
Po utworzeniu rozszerzenia nadszedł czas, aby użyć funkcji Crosstab() do utworzenia tabeli przestawnej. W tym celu użyjemy następującego zapytania w powłoce wiersza poleceń. To zapytanie najpierw pobiera rekord z nowo utworzonego „Widoku”. Rekordy te zostaną uporządkowane i pogrupowane w porządku rosnącym kolumn „u_name” i „p_name”. Dla każdego klienta, którego kupił, wymieniliśmy ich imiona do makijażu oraz całkowity koszt zakupionych produktów w tabeli. Zastosowaliśmy operator UNION ALL w kolumnie 'p_name' do zsumowania wszystkich produktów zakupionych przez jednego klienta osobno. Spowoduje to zsumowanie wszystkich kosztów produktów zakupionych przez użytkownika w jedną wartość.
Nasza tabela przestawna jest gotowa i wyświetlona na obrazku. Widać wyraźnie, że niektóre przestrzenie kolumn są puste pod każdą nazwą p_name, ponieważ nie kupili tego konkretnego produktu.
Wniosek:
Teraz doskonale nauczyliśmy się tworzyć tabelę przestawną, aby podsumować wyniki tabel z użyciem pakietu Tablefunc i bez niego.
