Untitled

---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,
    ROW_NUMBER() OVER (ORDER BY element.name) AS element,
    observation.date_id AS time
    observation. AS value --??????? não sei como por os values e os elements sendoo que na observation só tem tmin, tmax e prcp
FROM observation
JOIN station ON observation.station = station.id
JOIN element ON observation. = element. ;