Zapytanie krzyżowe PostgreSQL

Czy ktoś wie, jak tworzyć zapytania w tabeli krzyżowej w PostgreSQL?
Na przykład mam następującą tabelę:

Section    Status    Count
A          Active    1
A          Inactive  2
B          Active    4
B          Inactive  5

Chciałbym, aby zapytanie zwróciło następującą tabelę przestawną:

Section    Active    Inactive
A          1         2
B          4         5

czy to możliwe?

Zainstaluj dodatkowy moduł tablefunc raz na bazę danych, która udostępnia tę funkcję crosstab(). Od wersji Postgres 9.1 możesz CREATE EXTENSIONdo tego użyć :

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 parametrem wejściowym:

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);

Zwroty:

Sekcja | Aktywny | Nieaktywny
--------- + -------- + ----------
 A | 1 | 2)
 B | 4 | 5
 C |      7 | - !!

• Nie ma potrzeby przesyłania i zmiany nazw.
• Zwróć uwagę na niepoprawny wynik C: wartość 7jest wypełniana 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 podanym zapytaniu wejściowym: nazwa_wiersza , kategoria , wartość . Nie ma miejsca na dodatkowe kolumny, jak w poniższej opcji 2-parametrowej.

Bezpieczna forma

crosstab(text, text)z 2 parametrami wejściowymi:

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);

Zwroty:

Sekcja | Aktywny | Nieaktywny
--------- + -------- + ----------
 A | 1 | 2)
 B | 4 | 5
 C | |        7   - !!

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

• Drugim parametrem może być dowolny zapytania, który powraca jeden rząd na atrybutu dopasowanie kolejność definicji kolumnowej na końcu. Często będziesz chciał zapytać o różne atrybuty z tabeli bazowej w następujący sposób:

  'SELECT DISTINCT attribute FROM tbl ORDER BY 1'

  To jest w instrukcji.

  Ponieważ i tak musisz przeliterować wszystkie kolumny na liście definicji kolumn (z wyjątkiem wstępnie zdefiniowanych wariantów), zazwyczaj bardziej wydajne jest dostarczenie krótkiej listy w wyrażeniu takim jak pokazano:crosstabN()VALUES

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

  Lub (nie w instrukcji):

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

• Użyłem wyceny w dolarach, aby ułatwić wycenę.

• Możesz nawet wyprowadzać kolumny z różnymi typami danych za pomocą crosstab(text, text)- o ile reprezentacja tekstowa kolumny wartości jest poprawnym wejściem dla typu docelowego. W ten sposób można mieć atrybuty różnego rodzaju i wyjścia text, date, numericitp dla poszczególnych atrybutów. Przykład kodu znajduje się na końcu rozdziału crosstab(text, text)instrukcji .

db <> skrzypce tutaj

Zaawansowane przykłady

• Przestaw się na wiele kolumn za pomocą Tablefunc - demonstrując również wspomniane „dodatkowe kolumny”

• Dynamiczna alternatywa dla pivot z CASE i GROUP BY

\crosstabview w psql

Postgres 9.6 dodał tę meta-komendę do domyślnego terminala interaktywnego psql . Możesz uruchomić zapytanie, którego użyjesz jako pierwszy crosstab()parametr, i podaj je \crosstabview(natychmiast lub w następnym kroku). Lubić:

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

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

Powiązana odpowiedź na dba.SE autorstwa Daniela Vérité (autora funkcji psql):

• Jak wygenerować przestawne połączenie krzyżowe, gdy wynikowa definicja tabeli jest nieznana?

Uprzednio zaakceptowane odpowiedź jest przestarzała.

• Wariant tej funkcji crosstab(text, integer)jest przestarzały. Drugi integerparametr jest ignorowany. Cytuję obecny podręcznik :

  crosstab(text sql, int N) ...

  Przestarzała wersja crosstab(text). Parametr Njest teraz ignorowany, ponieważ liczba kolumn wartości jest zawsze określana przez zapytanie wywołujące

• Niepotrzebne odlewanie i zmiana nazwy.

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

• ORDER BYjest wymagany w postaci jednoparametrowej crosstab(). Instrukcja:

  W praktyce zapytanie SQL powinno zawsze określać, ORDER BY 1,2aby zapewnić prawidłowe uporządkowanie wierszy wejściowych

Możesz użyć crosstab()funkcji dodatkowego modułu tablefunc - który musisz zainstalować raz na bazę danych. Od PostgreSQL 9.1 możesz CREATE EXTENSIONdo tego użyć :

CREATE EXTENSION tablefunc;

W twoim przypadku wydaje mi się, że wyglądałoby to mniej więcej tak:

CREATE TABLE t (Section CHAR(1), Status VARCHAR(10), Count integer);

INSERT INTO t VALUES ('A', 'Active',   1);
INSERT INTO t VALUES ('A', 'Inactive', 2);
INSERT INTO t VALUES ('B', 'Active',   4);
INSERT INTO t VALUES ('B', 'Inactive', 5);

SELECT row_name AS Section,
       category_1::integer AS Active,
       category_2::integer AS Inactive
FROM crosstab('select section::text, status, count::text from t',2)
            AS ct (row_name text, category_1 text, category_2 text);

SELECT section,
       SUM(CASE status WHEN 'Active' THEN count ELSE 0 END) AS active, --here you pivot each status value as a separate column explicitly
       SUM(CASE status WHEN 'Inactive' THEN count ELSE 0 END) AS inactive --here you pivot each status  value as a separate column explicitly

FROM t
GROUP BY section

Rozwiązanie z agregacją JSON:

CREATE TEMP TABLE t (
  section   text
, status    text
, ct        integer  -- don't use "count" as column name.
);

INSERT INTO t VALUES 
  ('A', 'Active', 1), ('A', 'Inactive', 2)
, ('B', 'Active', 4), ('B', 'Inactive', 5)
                   , ('C', 'Inactive', 7); 


SELECT section,
       (obj ->> 'Active')::int AS active,
       (obj ->> 'Inactive')::int AS inactive
FROM (SELECT section, json_object_agg(status,ct) AS obj
      FROM t
      GROUP BY section
     )X

Przykro nam, ale nie jest to kompletne, ponieważ nie mogę go tutaj przetestować, ale może doprowadzić cię do właściwego kierunku. Tłumaczę z czegoś, z czego korzystam, tworząc podobne zapytanie:

select mt.section, mt1.count as Active, mt2.count as Inactive
from mytable mt
left join (select section, count from mytable where status='Active')mt1
on mt.section = mt1.section
left join (select section, count from mytable where status='Inactive')mt2
on mt.section = mt2.section
group by mt.section,
         mt1.count,
         mt2.count
order by mt.section asc;

Kod, z którego pracuję to:

select m.typeID, m1.highBid, m2.lowAsk, m1.highBid - m2.lowAsk as diff, 100*(m1.highBid - m2.lowAsk)/m2.lowAsk as diffPercent
from mktTrades m
   left join (select typeID,MAX(price) as highBid from mktTrades where bid=1 group by typeID)m1
   on m.typeID = m1.typeID
   left join (select typeID,MIN(price) as lowAsk  from mktTrades where bid=0 group by typeID)m2
   on m1.typeID = m2.typeID
group by m.typeID, 
         m1.highBid, 
         m2.lowAsk
order by diffPercent desc;

co zwróci identyfikator typu, najwyższą cenę ofertową i najniższą cenę zadaną oraz różnicę między nimi dwoma (dodatnia różnica oznaczałaby, że można kupić coś za mniej niż można sprzedać).

Crosstabfunkcja jest dostępna pod tablefuncrozszerzeniem. Będziesz musiał utworzyć to rozszerzenie jeden raz dla bazy danych.

UTWÓRZ ROZSZERZENIE tablefunc;

Możesz użyć poniższego kodu, aby utworzyć tabelę przestawną za pomocą tabulacji krzyżowej:

create table test_Crosstab( section text,
<br/>status text,
<br/>count numeric)

<br/>insert into test_Crosstab values ( 'A','Active',1)
                <br/>,( 'A','Inactive',2)
                <br/>,( 'B','Active',4)
                <br/>,( 'B','Inactive',5)

select * from crosstab(
<br/>'select section
    <br/>,status
    <br/>,count
    <br/>from test_crosstab'
    <br/>)as ctab ("Section" text,"Active" numeric,"Inactive" numeric)