wake-up-neo.com

PostgreSQL-Kreuztabellenabfrage

Weiß jemand, wie man Kreuztabellenabfragen in PostgreSQL erstellt?
Zum Beispiel habe ich folgende Tabelle:

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

Ich möchte, dass die Abfrage die folgende Kreuztabelle zurückgibt:

Section    Active    Inactive
A          1         2
B          4         5

Ist das möglich?

172
schone

Installieren Sie das Zusatzmodul tablefunc einmal pro Datenbank, die zur Verfügung stellt die Funktion crosstab(). Seit Postgres 9.1 können Sie dafür CREATE EXTENSION verwenden:

CREATE EXTENSION IF NOT EXISTS tablefunc;

Verbesserter Testfall

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

Einfaches Formular - nicht für fehlende Attribute geeignet

crosstab(text) mit 1 Eingabeparameter:

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

Kehrt zurück:

 Abschnitt | Aktiv | Inaktiv 
 --------- + -------- + ---------- 
 A | 1 | 2 
 B | 4 | 5 
 C | 7 | - !! 
  • Casting und Umbenennung entfallen.
  • Beachten Sie, dass das falsche Ergebnis für C ist: Der Wert 7 Wird für die erste Spalte ausgefüllt. Manchmal ist dieses Verhalten wünschenswert, aber nicht für diesen Anwendungsfall.
  • Die einfache Form ist auch auf genau drei Spalten in der angegebenen Eingabeabfrage beschränkt: Zeilenname, Kategorie, Wert. Es ist kein Platz für zusätzliche Spalten wie in der 2-Parameter-Alternative unten.

Sichere Form

crosstab(text, text) mit 2 Eingabeparametern:

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

Kehrt zurück:

 Abschnitt | Aktiv | Inaktiv 
 --------- + -------- + ---------- 
 A | 1 | 2 
 B | 4 | 5 
 C | | 7  - !! 
  • Beachten Sie das richtige Ergebnis für C.

  • Der zweite Parameter kann eine beliebige Abfrage sein, die eine Zeile pro Attribut zurückgibt, die mit der Reihenfolge der Spaltendefinition am Ende übereinstimmt. Häufig möchten Sie bestimmte Attribute aus der zugrunde liegenden Tabelle wie folgt abfragen:

    'SELECT DISTINCT attribute FROM tbl ORDER BY 1'
    

    Das steht im Handbuch.

    Da Sie ohnehin alle Spalten in einer Spaltendefinitionsliste buchstabieren müssen (mit Ausnahme der vordefinierten crosstabN() -Varianten), ist es in der Regel effizienter, eine kurze Liste in einem VALUES -Ausdruck wie diesem bereitzustellen gezeigt:

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

    Oder (nicht im Handbuch):

    $$SELECT unnest('{Active,Inactive}'::text[])$$  -- short syntax for long lists
    
  • Ich habe Dollar-Quotierung verwendet, um die Quotierung zu vereinfachen.

  • Sie können mit crosstab(text, text) - sogar Spalten mit different Datentypen ausgeben, solange der Text vorhanden ist Die Darstellung der Wertespalte ist eine gültige Eingabe für den Zieltyp. Auf diese Weise können Sie Attribute unterschiedlicher Art haben und text, date, numeric usw. für die jeweiligen Attribute ausgeben. Es gibt ein Codebeispiel am Ende des Kapitel crosstab(text, text) im Handbuch .

db <> fiddle hier

Fortgeschrittene Beispiele


\crosstabview In psql

Postgres 9.6 hat diesen Meta-Befehl zu seinem interaktiven Standardterminal psql hinzugefügt. Sie können die Abfrage ausführen, die Sie als ersten crosstab() -Parameter verwenden würden, und sie an \crosstabview Weiterleiten (sofort oder im nächsten Schritt). Mögen:

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

Ähnliches Ergebnis wie oben, aber es ist eine Repräsentationsfunktion auf der Clientseite Exklusiv. Eingabezeilen werden leicht unterschiedlich behandelt, daher ist ORDER BY Nicht erforderlich. Details für \crosstabview Im Handbuch. Es gibt weitere Codebeispiele am Ende dieser Seite.

Verwandte Antwort auf dba.SE von Daniel Vérité (dem Autor des PSQL-Features):



Die zuvor akzeptierte Antwort ist veraltet.

  • Die Variante der Funktion crosstab(text, integer) ist veraltet. Der zweite Parameter integer wird ignoriert. Ich zitiere das aktuell Handbuch :

    crosstab(text sql, int N) ...

    Veraltete Version von crosstab(text). Der Parameter N wird jetzt ignoriert, da die Anzahl der Wertespalten immer von der aufrufenden Abfrage bestimmt wird

  • Unnötiges Casting und Umbenennen.

  • Es schlägt fehl, wenn eine Zeile nicht alle Attribute aufweist. Weitere Informationen zum korrekten Umgang mit fehlenden Attributen finden Sie oben in der sicheren Variante mit zwei Eingabeparametern.

  • ORDER BY Wird in der Ein-Parameter-Form von crosstab() benötigt. Das Handbuch:

    In der Praxis sollte die SQL-Abfrage immer ORDER BY 1,2 Angeben, um sicherzustellen, dass die Eingabezeilen ordnungsgemäß sortiert sind

288

Sie können die Funktion crosstab() des Zusatzmoduls tablefunc - verwenden, das Sie einmal pro Datenbank installieren müssen. Seit PostgreSQL 9.1 können Sie CREATE EXTENSION dafür:

CREATE EXTENSION tablefunc;

In Ihrem Fall würde es meiner Meinung nach ungefähr so ​​aussehen:

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);
27
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
23
araqnid

Lösung mit JSON-Aggregation:

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
4
Milos

Tut mir leid, das ist nicht vollständig, weil ich es hier nicht testen kann, aber es könnte dich in die richtige Richtung bringen. Ich übersetze von etwas, das ich verwende und das eine ähnliche Abfrage macht:

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;

Der Code, mit dem ich arbeite, ist:

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;

dies gibt eine typeID, das höchste Preisgebot und den niedrigsten angeforderten Preis und die Differenz zwischen den beiden zurück (eine positive Differenz würde bedeuten, dass etwas für weniger gekauft werden könnte, als es verkauft werden kann).

1
LanceH