Requête croisée PostgreSQL

Question

Quelqu'un sait-il comment créer des tableaux croisés dans PostgreSQL ?
Par exemple, j'ai le tableau suivant :

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

Je voudrais que la requête renvoie le tableau croisé suivant :

Section    Active    Inactive
A          1         2
B          4         5

Est-ce possible ?

Answer 1

Actuellement accepté réponse est obsolète.

• La variante de la fonction crosstab(text, integer) est dépassée. Le deuxième integer paramètre est ignoré. Je cite l' actuel manuel:

tableau croisé(texte sql, int N) ...

Version obsolète de tableau croisé(texte). Le paramètre N est ignoré, étant donné que le nombre de colonnes de valeur est toujours déterminée par l'appel de la requête

• Inutile de casting, et de le renommer.

• Il échoue si une ligne n'a pas tous les attributs. Il existe une variante en toute sécurité avec le texte de deux paramètres de traiter correctement avec des attributs manquants.

• ORDER BY est requis. Franchement, l'on a accepté la réponse est tout simplement faux. Je cite le manuel ici:

Dans la pratique, la requête SQL doit toujours spécifier l'ORDRE de 1,2 à assurer que les lignes d'entrée sont bien ordonné

(Applicable uniquement pour le paramètre de forme de tableau croisé(), qui est utilisé.)

---

La bonne réponse

Installer le module additionnel tablefunc qui assure la fonction crosstab() une fois par base de données. Depuis PostgreSQL 9.1, vous pouvez utiliser CREATE EXTENSION pour que:

CREATE EXTENSION tablefunc;

L'amélioration de cas de test

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);  -- no row for C with 'Active'

• count est un mot réservé, ne pas l'utiliser comme nom de colonne.

Forme Simple - pas d'ajustement pour les attributs manquants

crosstab() avec un paramètre:

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

Retourne:

 Section | Actif | Inactif
---------+--------+----------
 Un | 1 | 2
 B | 4 | 5
 C | 7|

• Pas besoin de casting, et de le renommer.
• Remarque les incorrecte résultat pour C: la valeur 7 est rempli pour la première colonne.

Forme sûre

crosstab() avec deux paramètres:

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

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

Retourne:

 Section | Actif | Inactif
---------+--------+----------
 Un | 1 | 2
 B | 4 | 5
 C | | 7

• Noter le bon résultat pour C.

• Le second paramètre peut être n'importe quelle requête qui ne renvoie qu'une seule ligne par attribut correspondant à l'ordre de la définition de la colonne à la fin. Souvent, vous voulez à la requête d'attributs distincts de la table sous-jacente comme ceci:

  'SELECT DISTINCT attribute FROM tbl ORDER BY 1'
  

  C'est dans le manuel.

  Depuis que vous avez à énoncer toutes les colonnes dans une colonne d'une liste de définition de toute façon (sauf pour les pré-définies crosstab variantes), il est régulièrement plus efficace de fournir une courte liste dans N expression comme je viens de le démontrer:

  ()

  Ou:

  VALUES

  C'est pas dans le manuel.

• J'ai utilisé dollar citant à faire en citant plus facile.

Des exemples avancés

• Pivot sur Plusieurs Colonnes à l'aide de Tablefunc

• Dynamique alternative à pivot de l'AFFAIRE et PAR GROUPE

Answer 2

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

Answer 3

Vous pouvez utiliser le crosstab() fonction de la module supplémentaire tablefunc - que vous devez installer une fois par base de données. Depuis PostgreSQL 9.1, vous pouvez utiliser CREATE EXTENSION pour ça :

CREATE EXTENSION tablefunc;

Dans votre cas, je crois que cela ressemblerait à quelque chose comme ceci :

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

Answer 4

Solution avec agrégation 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

Answer 5

Désolé, ce n'est pas complet car je ne peux pas le tester ici, mais cela peut vous mettre sur la bonne voie. Je traduis à partir de quelque chose que j'utilise et qui fait une requête similaire :

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;

Le code sur lequel je travaille est le suivant :

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;

qui renvoie un ID de type, le prix le plus élevé offert et le prix le plus bas demandé, ainsi que la différence entre les deux (une différence positive signifie qu'un objet peut être acheté à un prix inférieur à celui auquel il peut être vendu).