sql - tabla - Alternativa dinámica para pivotar con CASE y GROUP BY

sql server transponer tabla

Question

Tengo una mesa que se ve así:

id    feh    bar
1     10     A
2     20     A
3      3     B
4      4     B
5      5     C
6      6     D
7      7     D
8      8     D

Y quiero que se vea así:

bar  val1   val2   val3
A     10     20 
B      3      4 
C      5        
D      6      7     8

Tengo esta consulta que hace esto:

SELECT bar, 
   MAX(CASE WHEN abc."row" = 1 THEN feh ELSE NULL END) AS "val1",
   MAX(CASE WHEN abc."row" = 2 THEN feh ELSE NULL END) AS "val2",
   MAX(CASE WHEN abc."row" = 3 THEN feh ELSE NULL END) AS "val3"
FROM
(
  SELECT bar, feh, row_number() OVER (partition by bar) as row
  FROM "Foo"
 ) abc
GROUP BY bar

Este es un enfoque muy astuto y se vuelve difícil de manejar si se crean muchas columnas nuevas. Me preguntaba si las declaraciones CASE se pueden mejorar para hacer que esta consulta sea más dinámica. Además, me gustaría ver otros enfoques para hacer esto.

Answer 1

Aunque esta es una vieja pregunta, me gustaría agregar otra solución hecha posible por las recientes mejoras en PostgreSQL. Esta solución logra el mismo objetivo de devolver un resultado estructurado de un conjunto de datos dinámico sin usar la función de tabla cruzada. En otras palabras, este es un buen ejemplo de reexamen suposiciones involuntarias e implícitas que nos impiden descubrir nuevas soluciones a viejos problemas. ;)

Para ilustrar, solicitó un método para transponer datos con la siguiente estructura:

id    feh    bar
1     10     A
2     20     A
3      3     B
4      4     B
5      5     C
6      6     D
7      7     D
8      8     D

en este formato:

bar  val1   val2   val3
A     10     20 
B      3      4 
C      5        
D      6      7     8

La solución convencional es un enfoque inteligente (e increíblemente bien informado) para crear consultas dinámicas cruzadas que se explica con detalles exquisitos en la respuesta de Erwin Brandstetter.

Sin embargo, si su caso de uso particular es lo suficientemente flexible como para aceptar un formato de resultado ligeramente diferente, entonces es posible otra solución que maneje los pivotes dinámicos de manera hermosa. Esta técnica, que aprendí aquí

• Tablas dinámico dinámicas con JSON y PostgreSQL

utiliza la nueva función jsonb_object_agg PostgreSQL para construir datos pivotados sobre la marcha en forma de un objeto JSON.

Utilizaré el "caso de prueba más simple" del Sr. Brandstetter para ilustrar:

CREATE TEMP TABLE tbl (row_name text, attrib text, val int);
INSERT INTO tbl (row_name, attrib, val) VALUES
   (''A'', ''val1'', 10)
 , (''A'', ''val2'', 20)
 , (''B'', ''val1'', 3)
 , (''B'', ''val2'', 4)
 , (''C'', ''val1'', 5)
 , (''D'', ''val3'', 8)
 , (''D'', ''val1'', 6)
 , (''D'', ''val2'', 7);

Usando la función jsonb_object_agg , podemos crear el conjunto de resultados pivote requerido con esta belleza concisa:

SELECT
  row_name AS bar,
  json_object_agg(attrib, val) AS data
FROM tbl
GROUP BY row_name
ORDER BY row_name;

Qué salidas:

 bar |                  data                  
-----+----------------------------------------
 A   | { "val1" : 10, "val2" : 20 }
 B   | { "val1" : 3, "val2" : 4 }
 C   | { "val1" : 5 }
 D   | { "val3" : 8, "val1" : 6, "val2" : 7 }

Como puede ver, esta función funciona creando pares clave / valor en el objeto JSON a partir de las columnas attrib y value en los datos de muestra, todos agrupados por row_name .

Aunque este conjunto de resultados obviamente se ve diferente, creo que realmente satisfará muchos (si no la mayoría) casos de uso del mundo real, especialmente aquellos donde los datos requieren un pivote generado dinámicamente, o donde los datos resultantes son consumidos por una aplicación principal (por ejemplo, necesita ser reformateado para la transmisión en una respuesta http).

Beneficios de este enfoque:

• Sintaxis más limpia. Creo que todos estarían de acuerdo en que la sintaxis de este enfoque es mucho más clara y fácil de entender que los ejemplos más básicos de la tabla cruzada.

• Completamente dinámico. No se debe especificar previamente ninguna información sobre los datos subyacentes. Ni los nombres de las columnas ni sus tipos de datos deben conocerse con anticipación.

• Maneja grandes cantidades de columnas. Como los datos pivoteados se guardan como una sola columna jsonb, no se ejecutará contra el límite de columna de PostgreSQL (≤1,600 columnas, creo). Todavía hay un límite, pero creo que es el mismo que para los campos de texto: 1 GB por objeto JSON creado (corríjanme si me equivoco). ¡Esa es una gran cantidad de pares clave / valor!

• Manejo simplificado de datos. Creo que la creación de datos JSON en el DB simplificará (y probablemente acelerará) el proceso de conversión de datos en las aplicaciones principales. (Observará que los datos enteros en nuestro caso de prueba de muestra se almacenaron correctamente como tales en los objetos JSON resultantes. PostgreSQL maneja esto convirtiendo automáticamente sus tipos de datos intrínsecos a JSON de acuerdo con la especificación JSON.) Esto eliminará efectivamente la necesidad para transmitir manualmente los datos pasados ​​a las aplicaciones principales: todo se puede delegar al analizador JSON nativo de la aplicación.

Diferencias (y posibles inconvenientes):

• Se ve diferente No se puede negar que los resultados de este enfoque son diferentes. El objeto JSON no es tan bonito como el conjunto de resultados de la tabla cruzada; sin embargo, las diferencias son puramente cosméticas. Se produce la misma información, y en un formato que probablemente sea más amigable para el consumo de las aplicaciones principales.

• Llaves perdidas. Los valores faltantes en el enfoque de tabla cruzada se completan con valores nulos, mientras que los objetos JSON simplemente faltan las claves aplicables. Tendrás que decidir por ti mismo si esto es una compensación aceptable para tu caso de uso. Me parece que cualquier intento de solucionar este problema en PostgreSQL complicará en gran medida el proceso y probablemente implique una introspección en forma de consultas adicionales.

• El orden clave no se conserva. No sé si esto se puede abordar en PostgreSQL, pero este problema también es más bien cosmético, ya que es poco probable que las aplicaciones principales dependan del orden de las claves, o que tengan la capacidad de determinar el orden de claves correcto por otros medios. El peor caso probablemente solo requiera una consulta adicional de la base de datos.

Conclusión

Tengo mucha curiosidad por escuchar las opiniones de los demás (especialmente @ ErwinBrandstetter) sobre este enfoque, especialmente en lo que respecta al rendimiento. Cuando descubrí este enfoque en el blog de Andrew Bender, fue como recibir un golpe en el costado de la cabeza. Qué manera más hermosa de abordar de nuevo un problema difícil en PostrgeSQL. Resolvió mi caso de uso perfectamente, y creo que también servirá a muchos otros.

Answer 2

En tu caso, supongo que una matriz es buena. SQL Fiddle

select
    bar,
    feh || array_fill(null::int, array[c - array_length(feh, 1)]) feh
from
    (
        select bar, array_agg(feh) feh
        from foo
        group by bar
    ) s
    cross join (
        select count(*)::int c
        from foo
        group by bar
        order by c desc limit 1
    ) c(c)
;
 bar |      feh      
-----+---------------
 A   | {10,20,NULL}
 B   | {3,4,NULL}
 C   | {5,NULL,NULL}
 D   | {6,7,8}

Answer 3

Esto es para completar @Damian buena respuesta. Ya he sugerido el enfoque JSON en otras respuestas antes de la práctica función json_object_agg del 9.6. Simplemente requiere más trabajo con el conjunto de herramientas anterior.

Dos de los posibles inconvenientes citados realmente no lo son. El orden de la clave aleatoria se corrige trivialmente si es necesario. Las claves faltantes, si es relevante, requieren una cantidad casi trivial de código para ser tratadas:

select
    row_name as bar,
    json_object_agg(attrib, val order by attrib) as data
from
    tbl
    right join
    (
        (select distinct row_name from tbl) a
        cross join
        (select distinct attrib from tbl) b
    ) c using (row_name, attrib)
group by row_name
order by row_name
;
 bar |                     data                     
-----+----------------------------------------------
 a   | { "val1" : 10, "val2" : 20, "val3" : null }
 b   | { "val1" : 3, "val2" : 4, "val3" : null }
 c   | { "val1" : 5, "val2" : null, "val3" : null }
 d   | { "val1" : 6, "val2" : 7, "val3" : 8 }

Para un consumidor de consulta final que entiende JSON no hay inconvenientes. El único es que no se puede consumir como una fuente de tabla.

Answer 4

Lamento haber regresado en el pasado, pero la solución "Dynamic Crosstab" devuelve una tabla de resultados errónea. Por lo tanto, los valores de valN están erróneamente "alineados a la izquierda" y no corresponden a los nombres de las columnas. Cuando la tabla de entrada tiene "agujeros" en los valores, por ejemplo, "C" tiene val1 y val3 pero no val2. Esto produce un error: el valor de val3 se alineará en la columna val2 (es decir, la siguiente columna libre) en la tabla final.

CREATE TEMP TABLE tbl (row_name text, attrib text, val int); 
INSERT INTO tbl (row_name, attrib, val) VALUES (''C'', ''val1'', 5) (''C'', ''val3'', 7);

SELECT * FROM crosstab(''SELECT row_name, attrib, val FROM tbl 
ORDER BY 1,2'') AS ct (row_name text, val1 int, val2 int, val3 int);

row_name|val1|val2|val3
 C      |   5|  7 |

Para devolver las celdas correctas con "agujeros" en la columna de la derecha, la consulta de referencias cruzadas requiere un 2º SELECCIONAR en la tabla cruzada, algo así como "crosstab(''SELECT row_name, attrib, val FROM tbl ORDER BY 1,2'', ''select distinct row_name from tbl order by 1'')"

Answer 5

Si no ha instalado el módulo adicional tablefunc , ejecute este comando una vez por cada base de datos:

CREATE EXTENSION tablefunc;

Responder a la pregunta

Una solución de tabla cruzada muy básica para su caso:

SELECT * FROM crosstab(
  ''SELECT bar, 1 AS cat, feh
   FROM   tbl_org
   ORDER  BY bar, feh'')
 AS ct (bar text, val1 int, val2 int, val3 int);  -- more columns?

La dificultad especial aquí es que no hay ninguna categoría ( cat ) en la tabla base. Para la forma básica de 1 parámetro , podemos proporcionar una columna ficticia con un valor ficticio que sirva como categoría. El valor se ignora de todos modos.

Este es uno de los pocos casos en que no se necesita el segundo parámetro para la función de crosstab() , ya que todos los valores NULL solo aparecen en columnas colgantes a la derecha por definición de este problema. Y el orden puede ser determinado por el valor .

Si tuviéramos una columna de categoría real con nombres que determinaran el orden de los valores en el resultado, necesitaríamos la forma de 2 parámetros de crosstab() . Aquí sintetizo una columna de categoría con la ayuda de la función de ventana row_number() , para basar la crosstab() row_number() crosstab() en:

SELECT * FROM crosstab(
   $$
   SELECT bar, val, feh
   FROM  (
      SELECT *, ''val'' || row_number() OVER (PARTITION BY bar ORDER BY feh) AS val
      FROM tbl_org
      ) x
   ORDER BY 1, 2
   $$
 , $$VALUES (''val1''), (''val2''), (''val3'')$$         -- more columns?
) AS ct (bar text, val1 int, val2 int, val3 int);  -- more columns?

El resto es más o menos run-of-the-mill. Encuentre más explicaciones y enlaces en estas respuestas estrechamente relacionadas.

Lo esencial:
_{¡Lea esto primero si no está familiarizado con la función de crosstab() !}

• Consulta de tabla cruzada de PostgreSQL

Avanzado:

• Pivote en múltiples columnas usando Tablefunc
• Combinar una tabla y un registro de cambios en una vista en PostgreSQL

Configuración adecuada de la prueba

Así es como debería proporcionar un caso de prueba para comenzar:

CREATE TEMP TABLE tbl_org (id int, feh int, bar text);
INSERT INTO tbl_org (id, feh, bar) VALUES
   (1, 10, ''A'')
 , (2, 20, ''A'')
 , (3,  3, ''B'')
 , (4,  4, ''B'')
 , (5,  5, ''C'')
 , (6,  6, ''D'')
 , (7,  7, ''D'')
 , (8,  8, ''D'');

Tabla de referencias dinámica?

No muy dinámico , sin embargo, como comentó @Clodoaldo . Los tipos de devolución dinámica son difíciles de lograr con plpgsql. Pero hay formas de evitarlo, con algunas limitaciones .

Para no complicar aún más el resto, lo demuestro con un caso de prueba más simple :

CREATE TEMP TABLE tbl (row_name text, attrib text, val int);
INSERT INTO tbl (row_name, attrib, val) VALUES
   (''A'', ''val1'', 10)
 , (''A'', ''val2'', 20)
 , (''B'', ''val1'', 3)
 , (''B'', ''val2'', 4)
 , (''C'', ''val1'', 5)
 , (''D'', ''val3'', 8)
 , (''D'', ''val1'', 6)
 , (''D'', ''val2'', 7);

Llamada:

SELECT * FROM crosstab(''SELECT row_name, attrib, val FROM tbl ORDER BY 1,2'')
AS ct (row_name text, val1 int, val2 int, val3 int);

Devoluciones:

 row_name | val1 | val2 | val3
----------+------+------+------
 A        | 10   | 20   |
 B        |  3   |  4   |
 C        |  5   |      |
 D        |  6   |  7   |  8

Característica tablefunc módulo tablefunc

El módulo tablefunc proporciona una infraestructura simple para las llamadas de crosstab() genéricas sin proporcionar una lista de definición de columna. Una cantidad de funciones escritas en C (típicamente muy rápido):

crosstabN()

crosstab1() - crosstab4() están predefinidos. Un punto menor: requieren y devuelven todo el text . Entonces, debemos convertir nuestros valores integer . Pero simplifica la llamada:

SELECT * FROM crosstab4(''SELECT row_name, attrib, val::text  -- cast!
                         FROM tbl ORDER BY 1,2'')

Resultado:

 row_name | category_1 | category_2 | category_3 | category_4
----------+------------+------------+------------+------------
 A        | 10         | 20         |            |
 B        | 3          | 4          |            |
 C        | 5          |            |            |
 D        | 6          | 7          | 8          |

Función personalizada crosstab()

Para más columnas u otros tipos de datos , creamos nuestro propio tipo compuesto y función (una vez).
Tipo:

CREATE TYPE tablefunc_crosstab_int_5 AS (
  row_name text, val1 int, val2 int, val3 int, val4 int, val5 int);

Función:

CREATE OR REPLACE FUNCTION crosstab_int_5(text)
  RETURNS SETOF tablefunc_crosstab_int_5
AS ''$libdir/tablefunc'', ''crosstab'' LANGUAGE c STABLE STRICT;

Llamada:

SELECT * FROM crosstab_int_5(''SELECT row_name, attrib, val   -- no cast!
                              FROM tbl ORDER BY 1,2'');

Resultado:

 row_name | val1 | val2 | val3 | val4 | val5
----------+------+------+------+------+------
 A        |   10 |   20 |      |      |
 B        |    3 |    4 |      |      |
 C        |    5 |      |      |      |
 D        |    6 |    7 |    8 |      |

Una función polimórfica y dinámica para todos

Esto va más allá de lo que cubre el módulo tablefunc .
Para que el tipo de devolución sea dinámico, utilizo un tipo polimórfico con una técnica detallada en esta respuesta relacionada:

• Refactorizar una función PL / pgSQL para devolver el resultado de varias consultas SELECT

Forma de 1 parámetro:

CREATE OR REPLACE FUNCTION crosstab_n(_qry text, _rowtype anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE 
   (SELECT format(''SELECT * FROM crosstab(%L) t(%s)''
                , _qry
                , string_agg(quote_ident(attname) || '' '' || atttypid::regtype
                           , '', '' ORDER BY attnum))
    FROM   pg_attribute
    WHERE  attrelid = pg_typeof(_rowtype)::text::regclass
    AND    attnum > 0
    AND    NOT attisdropped);
END
$func$  LANGUAGE plpgsql;

Sobrecarga con esta variante para la forma de 2 parámetros:

CREATE OR REPLACE FUNCTION crosstab_n(_qry text, _cat_qry text, _rowtype anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE 
   (SELECT format(''SELECT * FROM crosstab(%L, %L) t(%s)''
                , _qry, _cat_qry
                , string_agg(quote_ident(attname) || '' '' || atttypid::regtype
                           , '', '' ORDER BY attnum))
    FROM   pg_attribute
    WHERE  attrelid = pg_typeof(_rowtype)::text::regclass
    AND    attnum > 0
    AND    NOT attisdropped);
END
$func$  LANGUAGE plpgsql;

pg_typeof(_rowtype)::text::regclass : Hay un tipo de fila definido para cada tipo compuesto definido por el usuario, de modo que los atributos (columnas) se enumeren en el catálogo del sistema pg_attribute . El carril rápido para obtenerlo: regtype tipo registrado ( regtype ) al text y regclass este text a regclass .

Crear tipos compuestos una vez:

Debe definir una vez cada tipo de devolución que va a utilizar:

CREATE TYPE tablefunc_crosstab_int_3 AS (
    row_name text, val1 int, val2 int, val3 int);

CREATE TYPE tablefunc_crosstab_int_4 AS (
    row_name text, val1 int, val2 int, val3 int, val4 int);

...

Para llamadas ad-hoc, también puede simplemente crear una tabla temporal con el mismo efecto (temporal):

CREATE TEMP TABLE temp_xtype7 AS (
    row_name text, x1 int, x2 int, x3 int, x4 int, x5 int, x6 int, x7 int);

O utilice el tipo de una tabla existente, vista o vista materializada, si está disponible.

Llamada

Usando los tipos de filas anteriores:

Forma de 1 parámetro (sin valores faltantes):

SELECT * FROM crosstab_n(
   ''SELECT row_name, attrib, val FROM tbl ORDER BY 1,2''
 , NULL::tablefunc_crosstab_int_3);

Forma de 2 parámetros (algunos valores pueden faltar):

SELECT * FROM crosstab_n(
   ''SELECT row_name, attrib, val FROM tbl ORDER BY 1''
 , $$VALUES (''val1''), (''val2''), (''val3'')$$
 , NULL::tablefunc_crosstab_int_3);

Esta función funciona para todos los tipos de devolución, mientras que la estructura crosstab N () la crosstab N () proporcionada por el módulo tablefunc necesita una función separada para cada uno.
Si ha nombrado sus tipos en secuencia como se demostró anteriormente, solo tiene que reemplazar el número en negrita. Para encontrar la cantidad máxima de categorías en la tabla base:

SELECT max(count(*)) OVER () FROM tbl  -- returns 3
GROUP  BY row_name
LIMIT  1;

Eso es tan dinámico como esto si quieres columnas individuales . Las matrices como las demostradas por @Clocoaldo o una representación de texto simple o el resultado envuelto en un tipo de documento como json o hstore pueden funcionar para cualquier cantidad de categorías dinámicamente.

Renuncia:
Siempre es potencialmente peligroso cuando la entrada del usuario se convierte en código. Asegúrese de que esto no se pueda usar para la inyección de SQL. No acepte entradas de usuarios que no sean de confianza (directamente).

Llame para una pregunta original:

SELECT * FROM crosstab_n(''SELECT bar, 1, feh FROM tbl_org ORDER BY 1,2''
                       , NULL::tablefunc_crosstab_int_3);