Untitled
unknown
plain_text
2 years ago
976 B
10
Indexable
-- exercicio 4 -- o enUnciado é meio confuso, é a avg das observaçoes dos continentes ou dos elementos? -- não está acabado SELECT s.continent_name AS continent, e.name AS element, COUNT(o.VALUE) AS contagem FROM factobservation AS o JOIN dimelement AS e ON o.element_id = e.id JOIN dimstation AS s ON o.station_id = s.id JOIN dimtime AS t ON o.time_id = t.date_id GROUP BY continent, element ORDER BY continent; -- exercicio 5 -- é preciso calcular o mes com valor máximo para cada pais -- normalmente seria MAX(dif1) ... GROUP BY MONTH -- mas não consigo por isso a funcionar SELECT s.country_name AS country, t.month AS month, avg(CASE WHEN e.name = "TMAX" THEN o.value END) - avg(CASE WHEN e.name = "TMIN" THEN o.value END) AS dif1 FROM factobservation AS o JOIN dimelement AS e ON o.element_id = e.id JOIN dimstation AS s ON o.station_id = s.id JOIN dimtime AS t ON o.time_id = t.date_id GROUP BY country, month ORDER BY dif1 DESC
Editor is loading...