Patrones de tablas cruzadas (crosstab() de PostgreSQL)
Genere verdaderas tablas pivot con la función crosstab() de la extensión tablefunc.
Patrones de tablas cruzadas (crosstab() de PostgreSQL) es una lección gratuita de SQL Academy en CoddyKit. Esta es la lección 4 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de SQL Academy, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de SQL Academy incluye 4 lecciones en total.
¿Por qué una tabla crosstab verdadera?
Las tablas dinámicas con CASE requieren enumerar cada columna de destino. Para tablas dinámicas realmente anchas (por ejemplo, una columna por producto), la extensión tablefunc ofrece la herramienta crosstab().
Habilitar la extensión
tablefunc se incluye con las extensiones contrib de PostgreSQL:
CREATE EXTENSION IF NOT EXISTS tablefunc;Firma básica de crosstab
crosstab recibe una cadena SQL de 3 columnas (row_key, category, value) y devuelve row_key más una columna por categoría:
SELECT * FROM crosstab(
$$
SELECT user_id, status, COUNT(*)::INT
FROM orders
GROUP BY user_id, status
ORDER BY user_id, status
$$
) AS ct (
user_id BIGINT,
paid INT,
pending INT,
cancelled INT
);Por qué debe declarar las columnas de salida
SQL tiene tipado estático: el planificador necesita conocer las columnas de salida al analizar la consulta. Por eso debe especificar el esquema en la cláusula AS, incluidos los tipos de datos.
crosstab con dos argumentos (con conjunto de categorías)
Para datos dispersos, proporcione la lista de categorías por separado para que los valores que falten se conviertan en NULL en lugar de provocar una desalineación:
SELECT * FROM crosstab(
$$
SELECT user_id, status, COUNT(*)::INT
FROM orders GROUP BY user_id, status
ORDER BY user_id
$$,
$$ VALUES ('paid'), ('pending'), ('cancelled') $$
) AS ct (
user_id BIGINT, paid INT, pending INT, cancelled INT
);Cuándo CASE supera a crosstab
Para un conjunto pequeño y conocido de categorías, CASE/FILTER es más sencillo: no requiere ninguna extensión ni presenta las particularidades de la versión con dos argumentos. Utilice crosstab cuando:
- Tenga muchas categorías
- Las categorías se carguen dinámicamente
- Genere datos para un consumidor externo de tablas dinámicas
Tablas dinámicas dinámicas
Para categorías desconocidas en tiempo de ejecución, genere el SQL en la aplicación o utilice PL/pgSQL con format() + EXECUTE.
-- Build the SQL dynamically:
SELECT string_agg(format('SUM(CASE WHEN status = %L THEN 1 END) AS %I',
status, status), ', ')
FROM (SELECT DISTINCT status FROM orders) s;Convertir a formato ancho para hojas de cálculo
Los informes para analistas suelen requerir el formato ancho. Genérelo en SQL o entregue el formato largo y deje que la herramienta de BI cree la tabla dinámica.
Unpivot: la operación inversa
Para pasar de ancho → largo, utilice UNION ALL o jsonb_each_text() de PostgreSQL:
SELECT id, key AS month, (value)::NUMERIC AS revenue
FROM monthly_wide,
jsonb_each_text(to_jsonb(monthly_wide) - 'id');Rendimiento
crosstab() ejecuta una vez la consulta interna y crea la tabla dinámica en memoria. El cuello de botella es el mismo que en una consulta GROUP BY normal.
Limitaciones de crosstab
PostgreSQL no tiene una palabra clave PIVOT nativa, a diferencia de Oracle y SQL Server. crosstab() es la alternativa.
Resumen
Para la mayoría de las tablas dinámicas, CASE/FILTER es la opción más clara. crosstab() es la herramienta adecuada cuando hay muchas categorías o no se conocen de antemano.
Comprobación rápida
¿Qué extensión proporciona la función crosstab() de PostgreSQL?
Preguntas frecuentes
¿La lección «Patrones de tablas cruzadas (crosstab() de PostgreSQL)» es gratis?
Sí — el texto completo de «Patrones de tablas cruzadas (crosstab() de PostgreSQL)» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de SQL Academy, actualiza a CoddyKit PRO. El curso de SQL Academy incluye 4 lecciones en total.
¿Qué aprenderé en «Patrones de tablas cruzadas (crosstab() de PostgreSQL)»?
Genere verdaderas tablas pivot con la función crosstab() de la extensión tablefunc. Practicas SQL Academy con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.
¿Necesito experiencia previa para empezar SQL Academy?
No se requiere experiencia previa. SQL Academy en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 4 de 4.
¿Cuánto tiempo toma la lección «Patrones de tablas cruzadas (crosstab() de PostgreSQL)»?
La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.
¿Puedo escribir y ejecutar código en esta lección de SQL Academy?
Sí. Cada lección de SQL Academy incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.
Todas las lecciones de este curso
- UNION, INTERSECT y EXCEPT
- UNION ALL frente a UNION (coste de eliminar duplicados)
- Expresiones CASE y consultas pivot
- Patrones de tablas cruzadas (crosstab() de PostgreSQL)