Análisis de temperatura dia a dia correspondiente al mes abril (04) del año 2024, usando datos de alerta del INA

gistfile1.txt

FUENTE DE DATOS: https://alerta.ina.gob.ar/a5/secciones?generalCategory=&varId=&redId=&estacionId=&seriesId=32843&procId=&timestart=2024-04-01&timeend=2024-04-30&submit=&data_availability=h&west=&north=&east=&south=&cal_grupo_id=&cal_id=&forecast_date=&fuentesId=

Y me bajé un JSON... lo pase a CSV usando herramientas online https://data.page/json/csv

Luego 

CREATE DATABASE "INA"
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;
    
CREATE TABLE observation (
    id BIGSERIAL,
    tipo VARCHAR(50),
    series_id BIGINT,
    timestart TIMESTAMP WITH TIME ZONE,
    timeend TIMESTAMP WITH TIME ZONE,
    nombre VARCHAR(255),
    descripcion TEXT,
    unit_id TEXT,
    timeupdate TIMESTAMP WITH TIME ZONE,
    valor FLOAT,
    stats TEXT,
	  CONSTRAINT observation_pk PRIMARY KEY (id)
);

psql -h localhost -p 5431 -U postgres -d INA

INA=# \COPY observation(id, tipo, series_id, timestart, timeend, nombre, descripcion, unit_id, timeupdate, valor, stats) FROM '/home/vituchon/Downloads/result.csv' DELIMITER ',' CSV HEADER;

-- ARHOA LO DIVERTIDO... HACER QUERIES!

-- LA PRIMERA Y BASICA...
select to_char(timestart,'DD/MM/YYYY'), max(valor), min(valor), avg(valor)
from observation
GROUP by 1
order by 1 asc


--- LA QUE ME GUSTA
WITH aggregated_observation AS (
    SELECT 
        to_char(timestart, 'DD/MM/YYYY') AS day,
        MAX(valor) AS max_temp,
        MIN(valor) AS min_temp,
        AVG(valor) AS avg_temp
    FROM 
        observation
    GROUP BY 
        to_char(timestart, 'DD/MM/YYYY')
)
SELECT *
FROM aggregated_observation
ORDER BY day ASC;

-- AHORA A DIVERTIRSE UN POCO 😎🤓 con LATERAL JOINS!!

-- más rapida
SELECT 
    fecha,
    stats.temperatura_maxima,
    stats.temperatura_minima,
    stats.temperatura_promedio
FROM (
    SELECT DISTINCT to_char(timestart, 'DD/MM/YYYY') AS fecha
    FROM observation
) AS fechas
CROSS JOIN LATERAL (
    SELECT 
        MAX(valor) AS temperatura_maxima,
        MIN(valor) AS temperatura_minima,
        AVG(valor) AS temperatura_promedio
    FROM observation
    WHERE to_char(timestart, 'DD/MM/YYYY') = fechas.fecha
) AS stats
ORDER BY fecha ASC;

-- Más lenta
SELECT DISTINCT to_char(timestart,'DD/MM/YYYY') as day, stats.*
FROM observation o
CROSS JOIN LATERAL (
	SELECT 
	MAX(valor) AS temperatura_maxima,
	MIN(valor) AS temperatura_minima,
	AVG(valor) AS temperatura_promedio
    FROM observation o2
    WHERE to_char(o2.timestart, 'DD/MM/YYYY') = to_char(o.timestart,'DD/MM/YYYY')
) stats


Y BUENO FINALMENTE LO QUE TERMINE USNADO FUE EL CTE


WITH aggregated_observation AS (
    SELECT 
        to_char(timestart, 'DD/MM/YYYY') AS day,
        MAX(valor) AS max_temp,
        MIN(valor) AS min_temp,
        AVG(valor) AS avg_temp
    FROM 
        observation
    GROUP BY 
        1 --, 1 se refiere a la primera columna de la proyección o sea a "to_char(timestart, 'DD/MM/YYYY')" que el alias es day
)
SELECT *
FROM aggregated_observation
WHERE min_temp <= 10
ORDER BY day ASC;

NICE BROOO

Otra línea de ataque

Suponte que tienes una tabla de registros tabla con id de registro -id-, id de serie -id_serie- (que refiere a un punto, una variable y un procedimiento de medición en una tabla series, para hacerlo un poquito más generalizable), un inicio del registro de medicion -t0-, un final del intante de medición -t1- (timestamps), y valor (val)

ponele:

\d tabla

devolvería

id integer primary key
id_serie integer foreign key (vincula a la tabla de series, por si tenés más de un punto, variable o procedimiento)
t0 timestamp
t1 timestamp
val

luego, se me ocurre que con

select t0::date as day,min(val) as min_val,avg(val) as avg_val,max(val) as max_val from tabla where id_serie=$ID_SERIE group by day order by day

obtenés los agregados diarios para esa serie

Abz!

Me gusta @leangior la propueta de convertir a date (::date) 👍 ... ahora bien no sé muy bien que significa serie pues yo creo que en este caso estoy trabajndo dentro de una misma serie

Pero me gusta la idea ::date como alternativa a usar to_char(timestart,'DD/MM/YYYY') ... para obtener el dia... ojo to_char es más poderosa!

Bueno acá dejo imagenes ilustradoras