es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

crosstab en PostgreSQL

25 Jul, 2023 Programación

Tengo las siguientes tablas:

CREATE TABLE sensor (
    id int PRIMARY KEY,
    abbrv varchar(255) NOT NULL UNIQUE
);

y

CREATE TABLE readings (
    time timestamp without time zone NOT NULL,
    device_id int NOT NULL,
    sensor_id int REFERENCES sensor (id) NOT NULL,
    value float4 NOT NULL
);

¿Cómo puedo hacer una consulta a la base de datos para que devuelva la tabla:

time | device_id | abbrv $1 | ... | abbrv $n

donde n varía en las filas de la tabla sensor y ‘abbrv $i’ es reemplazado por el valor correspondiente en la tabla sensor?

La siguiente consulta:

SELECT * FROM crosstab(
    'SELECT time, device_id, sensor_id, value FROM readings ORDER BY 1, 2',
    'SELECT id FROM sensor'
) AS (time timestamp without time zone, device_id int, "sensor_1" float4, "sensor_2" float4, "sensor_3" float4);

funciona hasta cierto punto: necesito saber cuántas filas hay en la tabla sensor y tengo que establecer manualmente el nombre de las columnas.

Tags:  , ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Para generar dinámicamente los nombres de las columnas y evitar la necesidad de establecerlos manualmente, puedes usar una consulta PIVOT con SQL dinámico. Aquí tienes un ejemplo de consulta que debería funcionar:

    SELECT tiempo, id_dispositivo, datos_sensor.*
FROM (
  SELECT tiempo, id_dispositivo, abreviatura, valor
  FROM lecturas
  JOIN sensor ON lecturas.id_sensor = sensor.id
) AS datos_lectura
PIVOT (
  MAX(valor) FOR abreviatura IN (
    SELECT DISTINCT abreviatura FROM sensor ORDER BY abreviatura
  )
) AS datos_sensor
ORDER BY tiempo, id_dispositivo;

    Esta consulta une primero las tablas lecturas y sensor para obtener la abreviatura del sensor (abreviatura) asociada a cada lectura. Luego utiliza la sintaxis PIVOT para transformar estos datos de manera que cada abreviatura de sensor se convierte en un nombre de columna, con el valor correspondiente como contenido de la celda.

    La función MAX() se utiliza como una función agregadora porque es poco probable que una combinación dada de (tiempo, id_dispositivo, abreviatura) aparezca más de una vez en la tabla lecturas. Si se esperan valores duplicados, puedes utilizar una función agregadora diferente como AVG() o SUM().

    Ten en cuenta que esta consulta asume que cada abreviatura de sensor es única dentro de la tabla sensor. Si esto no es así, tendrás que modificar la consulta para agregar o filtrar las abreviaturas duplicadas de manera apropiada.

Comments are closed.