PostgreSQL Crosstab Query

Zainstaluj dodatkowy moduł tablefunc raz NA bazę danych, która dostarcza funkcję crosstab(). Od Postgres 9.1 można używać CREATE EXTENSION za to:

CREATE EXTENSION IF NOT EXISTS tablefunc;

Ulepszony przypadek testowy

CREATE TABLE tbl (
   section   text
 , status    text
 , ct        integer  -- "count" is a reserved word in standard SQL
);

INSERT INTO tbl VALUES 
  ('A', 'Active', 1), ('A', 'Inactive', 2)
, ('B', 'Active', 4), ('B', 'Inactive', 5)
                    , ('C', 'Inactive', 7);  -- ('C', 'Active') is missing

Prosta forma - nie nadaje się do brakujących atrybutów

crosstab(text) z 1 parametr wejściowy:

SELECT *
FROM   crosstab(
   'SELECT section, status, ct
    FROM   tbl
    ORDER  BY 1,2'  -- needs to be "ORDER BY 1,2" here
   ) AS ct ("Section" text, "Active" int, "Inactive" int);

Zwraca:

 Section | Active | Inactive
---------+--------+----------
 A       |      1 |        2
 B       |      4 |        5
 C       |      7 |           -- !!

• nie ma potrzeby odlewania i zmiany nazwy.
• zwróć uwagę na niepoprawny wynik dla C: wartość 7 jest wypełniona dla pierwszej kolumny. Czasami takie zachowanie jest pożądane, ale nie w tym przypadku użycia.
• prosta forma jest również ograniczona do dokładnie trzech kolumn w dostarczonym zapytaniu wejściowym: nazwa wiersza, Kategoria, wartość . Nie ma miejsca na dodatkowe kolumny jak w alternatywie 2-parametrowej poniżej.

Bezpieczna forma

crosstab(text, text) z 2 parametry wejściowe:

SELECT *
FROM   crosstab(
   'SELECT section, status, ct
    FROM   tbl
    ORDER  BY 1,2'  -- could also just be "ORDER BY 1" here

  , $$VALUES ('Active'::text), ('Inactive')$$
   ) AS ct ("Section" text, "Active" int, "Inactive" int);

Zwraca:

 Section | Active | Inactive
---------+--------+----------
 A       |      1 |        2
 B       |      4 |        5
 C       |        |        7  -- !!

• Zwróć uwagę na poprawny wynik dla C.

• drugi parametr może być dowolnym zapytaniem, które zwraca jeden wiersz na atrybut odpowiadający kolejności definicji kolumny na końcu. Często będziesz chciał odpytywać różne atrybuty z bazowej tabeli w następujący sposób:

  'SELECT DISTINCT attribute FROM tbl ORDER BY 1'
  

  To jest w instrukcji.

  Ponieważ musisz przeliterować wszystkie kolumny w liście definicji kolumn w każdym razie( z wyjątkiem wstępnie zdefiniowanych wariantów crosstabN()), zwykle bardziej wydajne jest dostarczenie krótkiej listy w wyrażeniu VALUES, Jak pokazano:

  $$VALUES ('Active'::text), ('Inactive')$$)
  

  Lub (nie w instrukcji):

  $$SELECT unnest('{Active,Inactive}'::text[])$$  -- short syntax for long lists
  

• Używałem notowania dolara aby ułatwić cytowanie.

• Możesz nawet wypisywać kolumny z różne typy danych Z crosstab(text, text) - o ile tekstowa reprezentacja wartości kolumna jest poprawnym wejściem dla typu docelowego. W ten sposób możesz mieć atrybuty innego rodzaju i wyjścia text, date, numeric itd. dla odpowiednich atrybutów. Na końcu rozdziału znajduje się przykład koducrosstab(text, text) w instrukcji .

db fiddle tutaj

Zaawansowane przykłady

• Pivot na wielu kolumnach za pomocą Tablefunc - również demonstrując wspomniane " extra kolumny "

• Dynamiczna alternatywa dla Pivota z CASE I GROUP BY

\crosstabview w psql

Postgres 9.6 dodano tę meta-komendę do domyślnego interaktywnego terminala psql. Możesz uruchomić zapytanie, którego chcesz użyć jako pierwszego parametru crosstab() i podać je do \crosstabview (natychmiast lub w następnym kroku). Like:

db=> SELECT section, status, ct FROM tbl \crosstabview

Podobny wynik jak wyżej, ale jest to funkcja reprezentacji na wyłącznie po stronie klienta. Wiersze wejściowe są traktowane nieco inaczej, dlatego ORDER BY nie jest wymagane. Szczegóły dla \crosstabview w instrukcji. na dole tej strony jest więcej przykładów kodu.

Pokrewna odpowiedź na dba.SE w 2011 roku został wybrany na członka zarządu psql.]}

• w jaki sposób wygenerować przestawne połączenie krzyżowe, w przypadku gdy wynikowa definicja tabeli jest nieznana?

Poprzednio zaakceptowane odpowiedź jest nieaktualna.

• Wariant funkcji {[30] } jest przestarzały. Drugi parametr integer jest ignorowany. Cytuję: current manual :

  crosstab(text sql, int N) ...

  Przestarzała wersja crosstab(text). Parametr {[34] } jest teraz ignorowany, ponieważ ilość kolumn wartości jest zawsze określana przez wywołujące zapytanie

• Niepotrzebne Odlewanie i zmiana nazwy.

• Nie powiedzie się, jeśli wiersz nie mają wszystkich atrybutów. Zobacz bezpieczny wariant z dwoma parametrami wejściowymi powyżej, aby poprawnie obsłużyć brakujące atrybuty.

• ORDER BY jest wymagane w postaci jednoparametrowej crosstab(). Instrukcja:

  W praktyce zapytanie SQL powinno zawsze określać ORDER BY 1,2, aby zapewnić że wiersze wejściowe są prawidłowo uporządkowane