Pivot z/bez stolika

Tabela obrotu jest potężnym narzędziem do szacowania, kompilacji i przeglądu danych, aby jeszcze łatwiej znaleźć wzorce i trendy. Tabele obrotowe mogą być używane do agregowania, sortowania, układania, przemieszczania, grupy, ogółem lub przeciętnego danych w zestawie danych, aby naprawdę zrozumieć skojarzenia i zależności danych. Używanie tabeli obrotowej jako ilustracji jest najłatwiejszym sposobem wykazania, jak działa ta metoda. PostgreSQL 8.3 zostało uruchomione kilka lat temu, a nowa wersja o nazwie 'stolik' zostało dodane. Stolik jest składnikiem, który zawiera kilka metod, które dają tabele (to znaczy wiele wierszy). Ta modyfikacja zawiera bardzo fajną gamę funkcji. Wśród nich jest metoda Crosstab, która zostanie użyta do tworzenia tabel obrotowych. 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 obrotowa bez stolika:

Aby rozpocząć pracę nad obrotem PostgreSQL z modułem „stolic”, musisz bez niego spróbować zrobić tabelę obrotową. Otwórzmy więc powłokę wiersza polecenia 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.

Będziemy tworzyć nową tabelę o nazwie „Test” w bazie danych „Test” z niektórymi pólami, jak pokazano poniżej.

>> Utwórz test tabeli (ID int, Name Varchar (20), Sal int, Job varchar (20));

Po utworzeniu tabeli nadszedł czas, aby wstawić niektóre wartości w tabeli, jak pokazano z poniższego zapytania.

>> Wstaw do testu (ID, Nazwa, Sal, Job) Wartości (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, „pisarz”), (13, „Saeed”, 69000, „Officer”), (13, „Saeed”, 90000, „Doctor”);

Widać, że odpowiednie dane zostały pomyślnie wstawione. Możesz zobaczyć, że ta tabela ma więcej niż 1 tych samych wartości dla identyfikatora, nazwy i pracy.

>> Wybierz * z wejścia;

Utwórzmy tabelę obrotową, która podsumuje zapis tabeli „test” za pomocą poniższego zapytania. Polecenie łączy te same wartości kolumny „id” i „nazwa” w jednym wierszu, przyjmując sumę wartości kolumny „wynagrodzenie” dla tych samych danych zgodnie z „id” i „nazwa”. Mówi również, ile razy jedna wartość miała miejsce w określonym zestawie wartości.

>> Wybierz identyfikator, nazwisko, sum (sal) sal, sum ((job = „Doctor”) :: int) Doctor, sum ((job = „pisarz”) :: int) pisarz, sum ((job = 'oficer ') :: int) „Oficer” z grupy testowej według id, nazwa;

Przykładowa tabela obrotowa ze stolikiem:

Zaczniemy od wyjaśnienia naszego głównego punktu z realistycznego punktu widzenia, a następnie opiszemy tworzenie tabeli Pivot w krokach, które lubimy. Przede wszystkim musisz dodać trzy tabele, aby działać na obrotu. Pierwsza tabela, którą zamierzamy stworzyć, to „makijaż”, który będzie przechowywać informacje dotyczące niezbędnych makijażu. Wypróbuj poniższe zapytanie w powładzie wiersza polecenia, aby utworzyć tę tabelę.

>> Utwórz tabelę, jeśli nie istnieje makijaż (Make_ID int podstawowy klucz, p_name varchar (100) nie null);

Po utworzeniu tabeli „makijaż” dodajmy do niego kilka rekordów. Będziemy wykonywać zapytanie poniżej listy w powłoce, aby dodać 10 rekordów do tej tabeli.

Musimy utworzyć inną tabelę o nazwie „Użytkownicy”, która będzie przechowywać rekordy użytkowników za pomocą tych produktów. Wykonaj poniżej podane zapytanie w skorupce, aby utworzyć tę tabelę.

>> Utwórz tabelę, jeśli nie istnieje użytkownicy (klucz podstawowy User_ID int, U_Name varchar (100) nie null);

Włożyliśmy 20 rekordów dla tabeli „Użytkownicy”, jak pokazano na poniższym obrazku.

Mamy inną tabelę „Makeup_user”, która będzie utrzymywała wzajemne zapisy zarówno tabeli „makijażu”, jak i „użytkowników”. Ma inne pole, „cena”, która zaoszczędzi cenę produktu. Tabela została wygenerowana za pomocą podanego poniżej zapytania.

>> Utwórz tabelę, jeśli nie istnieje Makeup_user (ID int podstawowy klucz, Mid Int Not Null References Makeup (Make_ID), UID INT NOT NOLL REFERENCJE Użytkownicy (user_id), cena dziesiętna (18,2));

W tej tabeli wstawiliśmy w sumie 56 rekordów, jak pokazano na zdjęciu.

Utwórzmy widok dalej, aby użyć go do generowania tabeli obrotowej. Ten widok używa wewnętrznego połączenia, aby dopasować wartości kolumny klucza podstawowego wszystkich trzech tabel i pobrać „nazwę”, „produkt_name” oraz „koszt” produktu z tabeli „Klienci”

>> Utwórz View v_makeup_users jak wybierz C.U_Name, s.P_name, komputer.Cena od użytkowników C Włącz PC Makeup_user na C.user_id = pc.UID Wewnętrzny Makeup P na PC.Mid = p.Make_id;

Aby to użyć, musisz najpierw zainstalować pakiet stolifurzy dla bazy danych, których chcesz użyć. Ten pakiet jest wbudowany PostgreSQL 9.1 i nowsze wydane przez uruchamianie poniższego polecenia. Pakiet stolika został teraz włączony.

>> Utwórz rozszerzenie, jeśli nie istnieje stolik;

Po utworzeniu rozszerzenia nadszedł czas, aby użyć funkcji Crosstab (), aby utworzyć tabelę obrotu. Będziemy więc używać następującego zapytania w skorupce wiersza polecenia, aby to zrobić. To zapytanie najpierw przynosi rekord z nowo utworzonego „Widok”. Te rekordy zostaną zamówione i zgrupowane według kolejności rosnącej kolumn „U_Name” i „P_name”. Wymieniliśmy ich nazwę makijażu dla każdego zakupionego klienta, oraz całkowity koszt produktów zakupionych w tabeli. Zastosowaliśmy Union wszystkich operatorów w kolumnie „P_name”, aby podsumować wszystkie produkty zakupione przez jednego klienta osobno. To podsumuje wszystkie koszty produktów zakupionych przez użytkownika do jednej wartości.

Nasz tabela obrotu była gotowa i wyświetlona na obrazie. Widać wyraźnie, że niektóre przestrzenie kolumnowe są puste poniżej każdej nazwy P_, ponieważ nie kupiły tego konkretnego produktu.

Wniosek:

Teraz doskonale nauczyliśmy się tworzyć tabelę obrotową, aby podsumować wyniki tabel z użyciem pakietu stolika i bez niego.