Necesito una consulta que me ayude a obtener datos como se describe a continuación
Tengo una tabla como sigue
| IDENTIFICACIÓN | fecha | ESTADO | HORA09_10 | HORA10_11 | HORA11_12 | HORA12_13 |
|---|---|---|---|---|---|---|
| 1 | 2021-09-01 | CORRER | 30 | 60 | 45 | 0 |
| 2 | 2021-09-01 | ANDAR | 15 | 0 | o | 30 |
| 3 | 2021-09-01 | DETENER | 15 | 0 | 15 | 30 |
Quiero que estos datos estén en el siguiente formato. De la tabla anterior, el nombre de las columnas de valor debe reemplazarse con la hora y unirse con la columna de fecha. Realmente agradecería la ayuda de alguien. Estoy usando la base de datos sql de postgres.
| fecha y hora | correr | andar | detener |
|---|---|---|---|
| 2021-09-01 09:00 | 30 | 15 | 15 |
| 2021-09-01 10:00 | 60 | 0 | 0 |
| 2021-09-01 11:00 | 45 | 0 | 15 |
| 2021-09-01 12:00 | 0 | 30 | 30 |
Puede usar la función de crosstab(source_sql text, category_sql text) . Necesitas instalar la extensión tablefunc :
create extension if not exists tablefunc;Lea acerca de la extensión en la documentación.
La función espera datos en el formato de tres columnas (row_name, category, value) . En este caso son date+time , status y duration .
select date+ '8 hour'::interval+ '1 hour'::interval* i as hour, status, (array[time09_10, time10_11, time11_12, time12_13])[i] as duration from my_table cross join generate_series(1, 4) i hour | status | duration ---------------------+--------+---------- 2021-09-01 09:00:00 | RUN | 30 2021-09-01 09:00:00 | WALK | 15 2021-09-01 09:00:00 | STOP | 15 2021-09-01 10:00:00 | RUN | 60 2021-09-01 10:00:00 | WALK | 0 2021-09-01 10:00:00 | STOP | 0 2021-09-01 11:00:00 | RUN | 45 2021-09-01 11:00:00 | WALK | 0 2021-09-01 11:00:00 | STOP | 15 2021-09-01 12:00:00 | RUN | 0 2021-09-01 12:00:00 | WALK | 30 2021-09-01 12:00:00 | STOP | 30 (12 rows)Pase la consulta como primer argumento a la función:
select * from crosstab( $source$ select date+ '8 hour'::interval+ '1 hour'::interval* i as hour, status, (array[time09_10, time10_11, time11_12, time12_13])[i] as duration from my_table cross join generate_series(1, 4) i $source$, $category$ values('RUN'), ('STOP'), ('WALK') $category$ ) as (hour timestamp, run int, stop int, walk int) hour | run | stop | walk ---------------------+-----+------+------ 2021-09-01 09:00:00 | 30 | 15 | 15 2021-09-01 10:00:00 | 60 | 0 | 0 2021-09-01 11:00:00 | 45 | 15 | 0 2021-09-01 12:00:00 | 0 | 30 | 30 (4 rows) Hay una buena alternativa si no quieres usar la extensión. Convierta los primeros resultados de la consulta al resultado esperado con la función jsonb :
select hour, (activities->>'RUN')::int as run, (activities->>'STOP')::int as stop, (activities->>'WALK')::int as walk from ( select hour, jsonb_object_agg(status, duration) as activities from ( select date+ '8 hour'::interval+ '1 hour'::interval* i as hour, status, (array[time09_10, time10_11, time11_12, time12_13])[i] as duration from my_table cross join generate_series(1, 4) i ) s group by hour ) s order by hour Pruebe la solución jsonb en Db<>fiddle.