ejemplos - Consulta de tabla cruzada de PostgreSQL

pivot postgresql ejemplos

Question

¿Alguien sabe cómo crear consultas cruzadas en PostgreSQL?
Por ejemplo, tengo la siguiente tabla:

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

Me gustaría que la consulta devuelva la siguiente tabla cruzada:

Section    Active    Inactive
A          1         2
B          4         5

es posible?

Answer 1

Instale el módulo adicional tablefunc una vez por base de datos, que proporciona la función crosstab() . Desde Postgres 9.1 puedes usar CREATE EXTENSION para eso:

CREATE EXTENSION tablefunc;

Caso de prueba mejorado

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);  -- ''C'' with ''Active'' is missing

• count es una palabra reservada en SQL estándar. Postgres lo permite, pero prefiero evitarlos como identificadores.

Forma simple - no apto para los atributos que faltan

crosstab(text) con 1 parámetro de entrada:

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

Devoluciones:

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

• No hay necesidad de lanzar y cambiar el nombre.
• Tenga en cuenta el resultado incorrecto para C : el valor 7 se completa para la primera columna. Algunas veces, este comportamiento es deseable, pero no para este caso de uso.
• La forma simple también está limitada a exactamente tres columnas en la consulta de entrada proporcionada: row_name , category , value . No hay espacio para columnas adicionales como en la alternativa de 2 parámetros a continuación.

Forma segura

crosstab(text, text) con 2 parámetros de entrada:

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

Devoluciones:

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

• Tenga en cuenta el resultado correcto para C

• El segundo parámetro puede ser cualquier consulta que devuelva una fila por atributo que coincida con el orden de la definición de la columna al final. A menudo querrá consultar atributos distintos de la tabla subyacente como esta:

  ''SELECT DISTINCT attribute FROM tbl ORDER BY 1''

  Eso está en el manual.

  Como debe deletrear todas las columnas en una lista de definición de columnas de todos modos (excepto las variantes predefinidas de la crosstab N () ), regularmente es más eficiente proporcionar una lista breve en una expresión VALUES como la que demuestro:

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

  O (no en el manual):

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

• Usé cotizaciones en dólares para facilitar las cotizaciones.

• Incluso puede generar columnas con diferentes tipos de datos con crosstab(text, text) , siempre que la representación de texto de la columna de valor sea una entrada válida para el tipo de destino. De esta manera, puede tener atributos de diferente tipo y text salida, date , numeric , etc. para los atributos respectivos. Hay un ejemplo de código al final de la crosstab(text, text) del capítulo crosstab(text, text) en el manual .

Ejemplos avanzados

• Pivote en columnas múltiples usando Tablefunc - también demostrando las "columnas extra" mencionadas

• Alternativa dinámica para pivotar con CASE y GROUP BY

/crosstabview crosstabview en psql

Postgres 9.6 agregó este meta-comando a su terminal interactivo predeterminado psql . Puede ejecutar la consulta que usaría como primer parámetro de crosstab() y alimentarla a /crosstabview (de inmediato o en el siguiente paso). Me gusta:

db=> SELECT section, status, ct FROM t /crosstabview

Resultado similar al anterior, pero es una función de representación exclusivamente del lado del cliente . Las filas de entrada se tratan de forma ligeramente diferente, por lo que no se requiere ORDENAR. Detalles para /crosstabview en el manual. Hay más ejemplos de código en la parte inferior de esa página.

Respuesta relacionada en dba.SE por Daniel Vérité (el autor de la función psql):

• ¿Cómo genero un CROSS JOIN pivotado donde se desconoce la definición de la tabla resultante?

La respuesta previamente aceptada está desactualizada.

• La variante de la función crosstab(text, integer) está desactualizada. El segundo parámetro integer se ignora. Cito el manual actual :

  crosstab(text sql, int N) ...

  Versión obsoleta de crosstab(text) . El parámetro N ahora se ignora, ya que la cantidad de columnas de valores siempre está determinada por la consulta de llamada

• Casting innecesario y cambio de nombre.

• Falla si una fila no tiene todos los atributos. Consulte la variante segura con dos parámetros de entrada anteriores para manejar los atributos que faltan correctamente.

• ORDER BY se requiere en la forma de un solo parámetro de crosstab() . El manual:

  En la práctica, la consulta SQL siempre debe especificar ORDER BY 1,2 para garantizar que las filas de entrada estén ordenadas correctamente.

Answer 2

Lo siento, esto no está completo porque no puedo probarlo aquí, pero puede llevarlo en la dirección correcta. Estoy traduciendo de algo que uso que hace una consulta similar:

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;

El código del que estoy trabajando es:

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;

que devolverá un ID de tipo, la oferta de precio más alto y el precio más bajo pedido y la diferencia entre los dos (una diferencia positiva significaría que algo podría comprarse por menos de lo que puede venderse).

Answer 3

Puede usar la función crosstab crosstab() del módulo adicional tablefunc , que debe instalar una vez por base de datos. Desde PostgreSQL 9.1 puede usar CREATE EXTENSION para eso:

CREATE EXTENSION tablefunc;

En tu caso, creo que se vería así:

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

Solución con agregación 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

Crosstab función de tablefunc está disponible bajo la extensión tablefunc . Tendrá que crear esta extensión una vez para la base de datos.

CREATE EXTENSION tablefunc ;

Puede usar el siguiente código para crear una tabla dinámica usando una tabla cruzada:

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)

Answer 6

SELECT section,
       SUM(CASE status WHEN ''Active'' THEN count ELSE 0 END) AS active,
       SUM(CASE status WHEN ''Inactive'' THEN count ELSE 0 END) AS inactive
FROM t
GROUP BY section