Last active
May 17, 2024 00:15
-
-
Save vituchon/54be5fcc836e1ff3dbd0bb1349a8b5af to your computer and use it in GitHub Desktop.
Análisis de temperatura dia a dia correspondiente al mes abril (04) del año 2024, usando datos de alerta del INA
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| FUENTE DE DATOS: https://alerta.ina.gob.ar/a5/secciones?generalCategory=&varId=&redId=&estacionId=&seriesId=32843&procId=×tart=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; |
Author
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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
