Untitled
unknown
plain_text
2 years ago
6.4 kB
5
Indexable
---3 --1 SELECT first_year FROM inventory WHERE element = 'PRCP' ORDER BY first_year ASC LIMIT 1; --2 SELECT s.id, s.name FROM station AS s JOIN inventory AS i ON s.id = i.station AND i.first_year = ( SELECT MIN(first_year) FROM inventory WHERE element = 'PRCP' ); --3 SELECT c.name AS continent, COUNT(*) AS count FROM Station AS s, Country AS p, Continent AS c WHERE s.country = p.fips AND p.continent = c.cc GROUP BY c.name ORDER BY count DESC; --4 SELECT YEAR(date_id) AS year, AVG(TMAX) AS avg_max_temp FROM Observation AS o, Station AS s WHERE o.station = s.id AND s.country = 'PO' GROUP BY YEAR(date_id) ORDER BY YEAR(date_id) ASC; --5 SELECT MONTH(date_id) AS month, AVG(PRCP) AS precipitation, MAX(TMAX) AS max_temp, MIN(TMIN) AS min_temp, MAX(TMAX) - MIN(TMIN) AS temp_amplitude FROM Observation AS o, Station AS s WHERE o.date_id >= 2013-01-01 AND o.station = s.id AND s.country = 'UK' GROUP BY MONTH(date_id) ORDER BY MONTH ASC; --6 SELECT AVG(CASE WHEN YEAR(date_id) = 2021 THEN PRCP END) - AVG(CASE WHEN YEAR(date_id) = 2006 THEN PRCP END) AS diff_avg_precip, AVG(CASE WHEN YEAR(date_id) = 2021 THEN TMAX END) - AVG(CASE WHEN YEAR(date_id) = 2006 THEN TMAX END) AS diff_max_temp, AVG(CASE WHEN YEAR(date_id) = 2021 THEN TMIN END) - AVG(CASE WHEN YEAR(date_id) = 2006 THEN TMIN END) AS diff_min_temp FROM Observation AS o, Station AS s WHERE YEAR(date_id) IN (2006,2021) AND o.station = s.id AND s.country = 'UK'; ---4 --I dimtime SELECT date_id AS date, year(date_id) AS year, quarter(date_id) AS quarter, month(date_id) AS month, day(date_id) AS day, dayofweek(date_id) AS day_week, dayofyear(date_id) AS day_year, (CASE WHEN (day(date_id)>=21 AND month(date_id)=12) OR (month(date_id)<3) OR (month(date_id)=3 AND day(date_id)<21) THEN "winter" WHEN (day(date_id)>=21 AND month(date_id)=3) OR (month(date_id)<6 AND month(date_id)>3) OR (month(date_id)=6 AND day(date_id)<21) THEN "spring" WHEN (day(date_id)>=21 AND month(date_id)=6) OR (month(date_id)>6 AND month(date_id)<9) OR (month(date_id)=9 AND day(date_id)<23) THEN "summer" ELSE "autumn" END) AS season FROM observation ORDER BY date_id ASC; --II dimstation SELECT station.id AS id, station.name AS name, CASE WHEN country.fips = 'US' THEN state.alias ELSE 'XX' END AS state_alias, CASE WHEN country.fips = 'US' THEN state.name ELSE 'not applicable' END AS state_name, CASE WHEN country.fips = 'US' THEN state.population ELSE 0 END AS state_population, CASE WHEN country.fips = 'US' THEN state.total_area ELSE 0 END AS state_total_area, CASE WHEN country.fips = 'US' THEN state.land_area ELSE 0 END AS state_land_area, CASE WHEN country.fips = 'US' THEN state.water_area ELSE 0 END AS state_water_area, CASE WHEN country.fips = 'US' THEN state.nr_reps ELSE 0 END AS state_nr_reps, country.fips AS country_fips, country.name AS country_name, country.license_plate AS country_license_plate, country.domain AS country_domain, continent.cc AS continent_code, continent.name AS continent_name FROM station JOIN country ON station.country = country.fips JOIN continent ON country.continent = continent.cc JOIN state ON station.state = state.alias; --III dimelement SELECT ROW_NUMBER() OVER (ORDER BY name) AS id, name, property, description, unit FROM element; --IV factobservation SELECT station.id AS station_id, ROW_NUMBER() OVER (ORDER BY element.name) AS element_id, observation.date_id AS time_id, (CASE WHEN element.name = 'PRCP' THEN observation.PRCP WHEN element.name = 'TMAX' THEN observation.TMAX WHEN element.name = 'TMIN' THEN observation.TMIN END) AS value FROM observation as o JOIN element ON o.element_id = ROW_NUMBER() OVER (ORDER BY element.name) JOIN station ON o.station = station.id; --??????? ---5 --1 SELECT t.year AS year, AVG(o.value) AS avg_prcp FROM factobservation AS o JOIN dimtime AS t ON o.time_id = t.date_id JOIN dimelement AS e ON o.element_id = e.id AND e.name = 'PRCP' JOIN dimstation AS s ON o.station_id = s.id AND s.country_name = 'Portugal' GROUP BY year ORDER BY year ASC; --2 SELECT s.country_name AS country, t.year AS year, AVG(o.value) AS avg_max_temp FROM factobservation AS o JOIN dimstation AS s ON o.station_id = s.id JOIN dimelement AS e ON o.element_id = e.id AND e.name = 'TMAX' JOIN dimtime AS t ON o.time_id = t.date_id GROUP BY country, year ORDER BY avg_max_temp DESC LIMIT 1; --3 SELECT t.day_week AS weekday, t.year AS year, AVG(o.value) AS avg_prcp FROM factobservation AS o JOIN dimtime AS t ON o.time_id = t.date_id JOIN dimelement AS e ON o.element_id = e.id AND e.name = 'PRCP' GROUP BY weekday, year ORDER BY avg_prcp DESC LIMIT 1; --4 --5 errado! SELECT t.month AS month, s.country_name AS country, AVG(o_tmax.value - o_tmin.value) AS avg_range FROM factobservation AS o JOIN factobservation AS o_tmax ON o.time_id = o_tmax.time_id AND o.station_id = o_tmax.station_id JOIN factobservation AS o_tmin ON o.time_id = o_tmin.time_id AND o.station_id = o_tmin.station_id JOIN dimtime AS t ON o.time_id = t.date_id JOIN dimelement AS e_tmax ON o_tmax.element_id = e_tmax.id AND e_tmax.name='TMAX' JOIN dimelement AS e_tmin ON o_tmin.element_id = e_tmin.id AND e_tmin.name='TMIN' JOIN dimstation AS s ON o.station_id = s.id GROUP BY month, country, o.time_id, o.station_id ORDER BY avg_range DESC LIMIT 10; --o mes com mais de cada pais --6 SELECT AVG(CASE WHEN t.year = 2006 AND e.name = "PRCP" THEN o.value END) - AVG(CASE WHEN t.year = 2021 AND e.name = "PRCP" THEN o.value END) AS diff_PCRP, AVG(CASE WHEN t.year = 2006 AND e.name = "TMAX" THEN o.value end) - AVG(CASE WHEN t.year = 2021 AND e.name = "TMAX" THEN o.value END) AS diff_TMAX, AVG(CASE WHEN t.year = 2006 AND e.name = "TMIN" THEN o.value END) - AVG(CASE WHEN t.year = 2021 AND e.name = "TMIN" THEN o.value END) AS diff_TMIN 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 WHERE s.country_name = "United Kingdom";
Editor is loading...