샘플 데이터: OpenWeatherMap — 사용되지 않음¶

테이블¶

데이터 세트에는 다음과 같은 테이블이 포함되며, 이러한 테이블은 모두 기본 JSON 형식으로 저장되고 시간 경과에 따라 누적됩니다.

쿼리의 예¶

검침값의 위도 및 경도와 함께 섭씨에서 화씨 온도로 변환된 뉴욕시의 최근 고온 및 저온 검침값을 검색하는 쿼리는 다음과 같습니다.

select (V:main.temp_max - 273.15) * 1.8000 + 32.00 as temp_max_far,
       (V:main.temp_min - 273.15) * 1.8000 + 32.00 as temp_min_far,
       cast(V:time as TIMESTAMP) time,
       V:city.coord.lat lat,
       V:city.coord.lon lon,
       V
from snowflake_sample_data.weather.WEATHER_14_TOTAL
where v:city.name = 'New York'
and   v:city.country = 'US'
order by time desc
limit 10;

Copy

일기 예보와 실제 날씨 검침값과 비교하는 쿼리는 다음과 같습니다.

with
forecast as
(select ow.V:time         as prediction_dt,
        ow.V:city.name    as city,
        ow.V:city.country as country,
        cast(f.value:dt   as timestamp) as forecast_dt,
        f.value:temp.max  as forecast_max_k,
        f.value:temp.min  as forecast_min_k,
        f.value           as forecast
 from snowflake_sample_data.weather.daily_16_total ow, lateral FLATTEN(input => V, path => 'data') f),

actual as
(select V:main.temp_max as temp_max_k,
        V:main.temp_min as temp_min_k,
        cast(V:time as timestamp)     as time_dt,
        V:city.name     as city,
        V:city.country  as country
 from snowflake_sample_data.weather.WEATHER_14_TOTAL)

select cast(forecast.prediction_dt as timestamp) prediction_dt,
       forecast.forecast_dt,
       forecast.forecast_max_k,
       forecast.forecast_min_k,
       actual.temp_max_k,
       actual.temp_min_k
from actual
left join forecast on actual.city = forecast.city and
                      actual.country = forecast.country and
                      date_trunc(day, actual.time_dt) = date_trunc(day, forecast.forecast_dt)
where actual.city = 'New York'
and   actual.country = 'US'
order by forecast_dt desc, prediction_dt desc;

Copy