Consulta de tabulación cruzada de PostgreSQL

Este enunciado fue aprobado por nuestros especialistas así se asegura la veracidad de esta sección.

Solución:

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

CREATE EXTENSION IF NOT EXISTS tablefunc;

Caso de prueba mejorado

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

Forma simple: no apto para faltar attributes

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

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

Devoluciones:

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

• No hay necesidad de lanzar y renombrar.
• Nota la incorrecto resultado para C: el valor 7 se rellena para la primera columna. A veces, este comportamiento es deseable, pero no para este caso de uso.
• La forma simple también se limita a exactamente tres columnas en la consulta de entrada proporcionada: nombre_fila, categoría, valor. no hay lugar 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   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);

Devoluciones:

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

• Tenga en cuenta el resultado correcto para C.

• los segundo parámetro puede ser cualquier consulta que devuelva uno fila por attribute haciendo coincidir el orden de la definición de columna al final. A menudo querrá consultar distintos attributes de la tabla subyacente como esta:

  'SELECT DISTINCT attribute FROM tbl ORDER BY 1'
  

  Eso está en el manual.

  Dado que tiene que deletrear todas las columnas en una lista de definición de columnas de todos modos (excepto las predefinidas crosstabN() variantes), por lo general es más eficiente proporcionar una lista corta en un VALUES expresión como se demuestra:

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

  O (no en el manual):

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

• solía cotización de dólar para facilitar la cotización.

• Incluso puede generar columnas con diferente 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 podrías tener attributes de diferente tipo y salida text, date, numeric etc. para los respectivos attributes. Hay un ejemplo de código al final del capítulo. crosstab(text, text) en el manual

db<>violín aquí

Ejemplos avanzados

• Pivote en múltiples columnas usando Tablefunc – también demostrando las “columnas adicionales” mencionadas

• Alternativa dinámica al pivote con CASE y GROUP BY

crosstabview en psql

postgres 9.6 agregó este metacomando a su terminal interactivo predeterminado psql. Puede ejecutar la consulta que usaría como primera crosstab() parámetro y alimentarlo a crosstabview (inmediatamente o en el siguiente paso). Me gusta:

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

Resultado similar al anterior, pero es un función de representación en el lado del cliente exclusivamente. Las filas de entrada se tratan de forma ligeramente diferente, por lo tanto ORDER BY no es requerido. 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) Esta anticuado. El segundo integer se ignora el parámetro. cito el Actual manual:

  crosstab(text sql, int N) …

  Versión obsoleta de crosstab(text). El parámetro N ahora se ignora, ya que el número de columnas de valor siempre está determinado por la consulta de llamada

• Casting y cambio de nombre innecesarios.

• Falla si una fila no tiene todos attributes. Vea la variante segura con dos parámetros de entrada arriba para manejar la falta attributes adecuadamente.

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

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

Puedes usar el crosstab() función del módulo adicional tablefunc – que tienes que instalar una vez por base de datos. Desde PostgreSQL 9.1 puedes usar CREATE EXTENSION para eso:

CREATE EXTENSION tablefunc;

En tu caso, creo que sería algo como esto:

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

Comentarios y valoraciones del artículo

Si sostienes alguna desconfianza o disposición de enriquecer nuestro enunciado puedes realizar un comentario y con deseo lo analizaremos.

---

---

Utiliza Nuestro Buscador