Creación de tablas temporales para analítica

Las tablas temporales en PostgreSQL son tablas que se crean para almacenar datos de forma temporal y se eliminan automáticamente al terminar la sesión o la transacción (depende de la configuración). Son súper útiles para simplificar operaciones complejas y acelerar la ejecución de consultas.

Imagínate que eres chef en un restaurante con platos de tres estrellas. No vas a tener una mesa solo para los ingredientes de una ensalada, ¿verdad? Pues las tablas temporales hacen de superficie de trabajo temporal: procesas, usas y luego la quitas.

Ventajas:

• Aislamiento de datos: la tabla temporal solo está disponible dentro de la sesión o transacción actual. Nadie más puede meterse en su contenido.
• Eficiencia: te permiten guardar resultados intermedios para no tener que repetir consultas complejas.
• Limpieza: cuando terminas, las tablas temporales se borran solas.

Sintaxis para crear tablas temporales

PostgreSQL te da una forma fácil y cómoda de crear tablas temporales usando el comando CREATE TEMP TABLE.

Sintaxis general:

CREATE TEMP TABLE table_name (
    column_name1 data_type constraints,
    column_name2 data_type constraints,
    ...
);

• TEMP o TEMPORARY — palabra clave que indica que la tabla es temporal.
• table_name — nombre de la tabla. Puedes poner el que quieras, pero mejor que tenga sentido.
• column_name — nombre de la columna.
• data_type — tipo de dato de la columna (INTEGER, TEXT, DATE, etc.).
• constraints — restricciones, por ejemplo, NOT NULL, UNIQUE, etc.

Ejemplo de cómo crear una tabla temporal sencilla:

CREATE TEMP TABLE temp_sales (
    id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL,
    sale_date DATE NOT NULL,
    amount NUMERIC(10, 2) NOT NULL
);

• Aquí se crea una tabla temporal para guardar datos de ventas.
• El campo id genera automáticamente un identificador para cada fila.
• El campo amount guarda un valor decimal (por ejemplo, 1234.56).

Ejemplos de uso de tablas temporales

Ejemplo 1: guardar datos intermedios para análisis

Imagina que tienes una tabla orders donde se guardan todos los pedidos. Necesitamos saber el total de ventas por categorías de productos. En vez de hacer una consulta súper compleja, puedes guardar los datos en una tabla temporal.

Vamos a crear una tabla temporal:

CREATE TEMP TABLE temp_category_sales (
    category TEXT,
    total_sales NUMERIC(10, 2)
);

Rellenamos la tabla temporal:

INSERT INTO temp_category_sales
SELECT category, SUM(amount)
FROM orders
GROUP BY category;

Ahora puedes usar la tabla temporal, por ejemplo, para sacar el top 3 de categorías:

SELECT *
FROM temp_category_sales
ORDER BY total_sales DESC
LIMIT 3;

Ejemplo 2: combinar datos de diferentes tablas

Supón que tienes dos tablas: students (info de estudiantes) y enrollments (registros de inscripción). Queremos juntar los datos de sus inscripciones en un solo sitio.

Vamos a crear una tabla temporal:

CREATE TEMP TABLE temp_student_enrollments (
    student_id INTEGER,
    student_name TEXT,
    course_id INTEGER,
    enrollment_date DATE
);

Combinamos los datos:

INSERT INTO temp_student_enrollments
SELECT s.id, s.name, e.course_id, e.enrollment_date
FROM students s
JOIN enrollments e ON s.id = e.student_id;

Ahora puedes analizar fácilmente esta tabla, por ejemplo, contar cuántas inscripciones tiene cada estudiante:

SELECT student_name, COUNT(course_id) AS enrollment_count
FROM temp_student_enrollments
GROUP BY student_name
ORDER BY enrollment_count DESC;

Tablas temporales en transacciones

Además, puedes limitar las tablas temporales al ámbito de una transacción usando la palabra clave ON COMMIT.

Opciones de ON COMMIT:

• ON COMMIT DELETE ROWS: limpia la tabla temporal al terminar la transacción.
• ON COMMIT PRESERVE ROWS: mantiene los datos después de terminar.
• ON COMMIT DROP: elimina la tabla al terminar la transacción.

Ejemplo:

CREATE TEMP TABLE temp_analysis (
    category TEXT,
    total_sales NUMERIC(10, 2)
) ON COMMIT DROP;

En este caso, la tabla temporal desaparece en cuanto termina la transacción.

Importancia práctica y casos de uso

Las tablas temporales son ideales para proyectos reales. Mira un par de situaciones:

Casos:

1. Informes complejos en negocios: puedes hacer cálculos múltiples y guardar resultados intermedios.
2. Preparación de datos para ETL: las tablas temporales se usan mucho en procesos de extracción, transformación y carga de datos (ETL).
3. Desarrollo y pruebas de consultas: usa tablas temporales para aislar datos mientras depuras tus consultas.

Por ejemplo, si estás desarrollando un informe de ventas, las tablas temporales te ayudan a guardar resultados intermedios como ingresos totales o tendencias por regiones, sin afectar la estructura principal de la base de datos.

Características y errores típicos

Trabajar con tablas temporales casi siempre es una fiesta, pero hay que tener en cuenta algunos detalles:

• Restricción de acceso: las tablas temporales solo se ven en la sesión o transacción actual. Si cierras la sesión, las tablas desaparecen.
• Errores con nombres únicos: no puedes crear una tabla temporal con un nombre que ya esté ocupado por otra tabla en la misma sesión.
• Pérdida de datos: asegúrate de sacar los datos que necesitas de la tabla temporal antes de cerrar la sesión.
• Rendimiento: si la tabla temporal tiene muchas filas, puede afectar el uso de la memoria RAM.