ポイント: Snowflakeは基本的な分析を統計関数を通じて提供し、共分散や平均のような集約メトリックに焦点を当てています。このチュートリアルは、気象データを使用してこれらの関数の使用法を示し、SQLクエリとウィンドウ関数を利用して効果的な分析を行います。
- Snowflakeは基本的な統計関数を提供しますが、高度な機械学習機能はありません。
- 主な例には、共分散の計算や平均やランクなどの基本統計が含まれます。
Snowflakeは機械学習を行いません。単純な線形回帰と基本的な統計関数のみを持っています。（Snowflakeで機械学習を行いたい場合は、データをSparkや他のサードパーティ製品に投入する必要があります。）
しかし、いくつかの数学の知識と集計関数、およびウィンドウ関数を駆使して、Snowflakeで分析を行うことができます。基本的な分析はほとんどの状況で必要なものであり、より詳細な分析への第一歩です。
このチュートリアルでは、Snowflakeの統計関数をいくつかの例を使って説明します。以下の関数をデモンストレーションします：
まず、作業環境を設定しましょう。
サンプルデータ
この例を通じて作業するためには、いくつかのデータが必要です。1,000の時間ごとの天気記録をこちらからダウンロードしてください。
（私たちは、キプロスのパフォスの20年間の天気データをOpenWeatherから購入しました。これは1,000件のレコードの小さなサブセットで、CSV形式に変換されています。他のチュートリアルでは、このデータを使用しており、例えばS3からSnowflakeへのCSVファイルのロードなどがあります。）
このデータを次のようにAmazon S3にアップロードします：
共分散
平均、最大、最小、標準偏差
相関
順位
aws s3 cp paphosWeather.csv s3://gluebmcwalkerrowe/paphosWeather.csv
Snowflakeにテーブルを作成
残念ながら、Snowflakeはヘッダーレコードを読み取って自動的にテーブルを作成しません。（おそらく、列のデータ型や数値の精度とサイズを自分で定義してほしいからでしょう。）
create table weather(
dt integer,
temp decimal(6,2),
temp_min decimal(6,2),
temp_max decimal(6,2),
pressure int,
humidity int,
speed decimal(6,2),
deg int,
main varchar(50),
description varchar(50))
データファイルをSnowflakeステージエリアにコピー
次に、次のようにSnowflakeステージエリアを作成します。
（Snowflakeの資格情報が何であれ、関係ありません：あなたのAmazon AWSの資格情報を入力してください。実際、あなたがSnowflakeのインスタンスで実行していても、Snowflakeの資格情報を見つける場所はありません。なぜなら、それらはあなたのAmazon IAMの資格情報と同じだからです。つまり、EC2サーバーにSnowflakeをローカルにインストールしていない場合です。）
create or replace stage paphosweather
url='s3://gluebmcwalkerrowe/paphosWeather.csv'
credentials=(aws_key_id='xxxxxxx' aws_secret_key='xxxxxxx')
では、上で作成したテーブルにデータをコピーします。
copy into weather
from 's3://gluebmcwalkerrowe/paphosWeather.csv'
credentials=(aws_key_id='xxxxxxx' aws_secret_key='xxxxxxxx')
file_format = (type = csv field_delimiter = ',' skip_header = 1);
変数xとyの共分散cは、xの増加がyの増加をもたらすときに> 0です。これは、二つの変数がどのように関連しているかを測定するものです。これをSnowflakeで示しましょう。
以下の各クエリは、内部クエリを外部クエリでラップしています。これは必要な理由です：
- 内部クエリは計算を行い、その計算結果を含む小さなセットを作成します。
- 次に外部クエリが実行されます。これは、内部クエリから渡された列を使用して、クエリ処理を二段階のプロセスにします。言い換えれば、ステップ1ではセットAを作成します。ステップ2では、別のセットBを生成するか、スカラー（単一の数値）を生成します。
私たちが持っているデータは、1か所の時間ごとの気象データです：キプロスのパフォス。気象学者によると、気圧の低下は風速の増加をもたらします。したがって、気圧の低下と風速の増加の共分散を計算して測定しましょう。
(以下のチュートリアルでは、20年間でキプロスのパフォスのためにOpenWeatherから117,000件の時間ごとの気象データレコードを使用しました。1,000件のレコードは41日分、こちらからダウンロードできます。あるいは、あなたの場所のデータを購入して、歴史的条件を調査してください。)
私たちは計算します：
- lag()ウィンドウ関数を使用して、2つの時点での気圧を見て、気圧の変化を計算します。ウィンドウ関数はクエリ結果の隣接する行に対して計算を実行します。したがって、lag(pressure,8)を使用して、前の8時間、つまり現在の行の8行前の気圧の変化を計算します。データベースの各レコードは1時間です。
- その後、同じ8時間にわたって風速の変化をlag()を使って計算します。
単純な減算を使用して変化を計算します。時間を扱っているので、データをdt列で並べ替えます。したがって、日付は順序通りである必要があります。
select to_timestamp(dt)
pressure, main,
lag(pressure, 1)over (order by dt) as pressure1,
lag(pressure, 8) over (order by dt) as pressure8,
pressure - pressure8 as pressurechange,
speed ,
lag(speed, 1) over (order by dt) as speed1,
lag(speed, 8) over (order by dt) as speed8,
speed8 - speed1 as windchange
from weather
order by dt desc
今、内部関数を外部関数内にラップします。注目すべき点：
- 内部関数が計算の名前pressurechangeとwindchangeを与えたので、私たちは単にcovar_pop(pressurechange, windchange)と書きます。
- 各気象条件に対する共分散を示すために、列mainを追加します。これは曇り、雨、雷雨などです。
select main, covar_pop(pressurechange, windchange) as covariance from
(select to_timestamp(dt)
pressure, main,
lag(pressure, 1)over (order by dt) as pressure1,
lag(pressure, 8) over (order by dt)as pressure8,
pressure - pressure8 as pressurechange,
speed ,
lag(speed, 1) over (order by dt) as speed1,
lag(speed, 8) over (order by dt) as speed8,
speed8 - speed1 as windchange
from weather
order by dt desc)
group by main
ここで、気圧の低下が風速の増加をもたらすことがわかります。これは予想通りです。その効果は雷雨の時に最も強くなります。
| メイン | 共分散 |
|---|---|
| 雲の | 2.682560523 |
| クリア | 2.578284619 |
| 雷雨 | 3.578449916 |
| 雨 | 3.328280913 |
| ほこり | 1.697657949 |
| 突風 | 2.921487603 |
| 霧 | 2.513627372 |
| 霧 | 2.9256 |
| かすみ | 3.565528153 |
| 竜巻 | -1.425 |
平均、最大、最小、標準偏差は集約関数です。これは、group by句を期待します。これらは最も基本的な統計です。これにより、いくつかのメトリックの平均と平均の変動のアイデアが得られます。注目すべき点：
- to_timestamp()はエポック時間（1970/01/01以降の秒）をyyyy-mm-dd hh:mm:ssに変換します。
- date_part()を使用して月と年を引き出します。
select round(avg(temp),2) as average, round(stddev(temp),2) as std, max(temp) as maxtemp,
min(temp) as mintemp, date_part(year, to_timestamp(dt)) as year, date_part(month,to_timestamp(dt)) as month
from weather
where to_timestamp(dt) > '2017-01-01'
group by date_part(year, to_timestamp(dt)), date_part(month,to_timestamp(dt))
order by date_part(year, to_timestamp(dt)), date_part(month,to_timestamp(dt))
ここに結果があります：
| 平均 | 標準偏差 | 最高気温 | 最低気温 | 年 | 月 |
|---|---|---|---|---|---|
| 53.4 | 5.57 | 64.08 | 37.42 | 2017 | 1 |
| 54.4 | 6.37 | 72.84 | 40.44 | 2017 | 2 |
| 59.17 | 5.1 | 70.84 | 45.91 | 2017 | 3 |
| 63.79 | 5.36 | 76.78 | 51.67 | 2017 | 4 |
| 79.28 | 0.1 | 79.39 | 79.21 | 2017 | 8 |
| 78.35 | 4.9 | 88.5 | 67.21 | 2017 | 9 |
| 71.86 | 4.86 | 82.2 | 62.13 | 2017 | 10 |
| 64.08 | 5.29 | 78.31 | 50.61 | 2017 | 11 |
| 59.55 | 5.69 | 69.87 | 45.84 | 2017 | 12 |
| 56.5 | 4.99 | 66.42 | 44.31 | 2018 | 1 |
| 58.81 | 4.47 | 68.04 | 47.88 | 2018 | 2 |
| 61.63 | 5.27 | 77.61 | 50.56 | 2018 | 3 |
| 65.91 | 6.07 | 78.75 | 51.94 | 2018 | 4 |
| 75.88 | 1.43 | 77.54 | 75.04 | 2018 | 8 |
| 79.21 | 5.01 | 88.81 | 66.67 | 2018 | 9 |
| 73.28 | 5.97 | 85.15 | 58.28 | 2018 | 10 |
結果を月ごとに最初に並べ替え、その後年で比較することができます：
select round(avg(temp),2) as average, round(stddev(temp),2) as std, max(temp) as maxtemp,
min(temp) as mintemp, date_part(year, to_timestamp(dt)) as year, date_part(month,to_timestamp(dt)) as month
from weather
group by date_part(year, to_timestamp(dt)), date_part(month,to_timestamp(dt))
order by date_part(month,to_timestamp(dt)) ,date_part(year, to_timestamp(dt))
明らかに、月は夏に暑く、冬に寒くなるため、温度と相関しています。その相関はどれくらい強いのでしょうか？それは100%に近いと思われるでしょう。ここでは、キプロスの穏やかな気候では70%に近いです。
これはスカラー統計値を返しますので、corr(month, average)のように1つの値を返します。
select corr(month, average) from
(select round(avg(temp),2) as average, round(stddev(temp),2) as std, max(temp) as maxtemp,
min(temp) as mintemp, date_part(year, to_timestamp(dt)) as year, date_part(month,to_timestamp(dt)) as month
from weather
where to_timestamp(dt) > '2017-01-01'
group by date_part(year, to_timestamp(dt)), date_part(month,to_timestamp(dt))
order by date_part(year, to_timestamp(dt)), date_part(month,to_timestamp(dt)))
このスカラー値の結果です。したがって、それらは正の相関がありますが、私たちが想像するほどではありません。
CORR(MONTH, AVERAGE)
0.6127760999
では、2017年以降の最も暑い平均月を計算してみましょう。通常のSQL関数でそれを行うこともできますが、rank()ウィンドウ関数を使用すれば、結果セット内の各行に番号を付けることができます。その番号が順位です。
これは二段階の関数でもあります。なぜなら、時間ごとの天気記録を月ごとに変換する必要があるからです。行数が多くなりすぎないようにするためです：
- 各月の平均温度を計算します。
- 順位付けしたいメトリクスを選択します。これは、(order by average desc [降順])ステートメントにaverage列を入れることで指定します。
select year, month, average, rank() over (order by average desc) as hottest from
(select round(avg(temp),2) as average, round(stddev(temp),2) as std, max(temp) as maxtemp,
min(temp) as mintemp, date_part(year, to_timestamp(dt)) as year, date_part(month,to_timestamp(dt)) as month
from weather
where to_timestamp(dt) > '2017-01-01'
group by date_part(year, to_timestamp(dt)), date_part(month,to_timestamp(dt)))
ここに結果があります（華氏で）。
| 年 | 月 | 平均 | 最も暑い |
|---|---|---|---|
| 2020 | 9 | 81.73 | 1 |
| 2019 | 8 | 79.36 | 2 |
| 2017 | 8 | 79.28 | 3 |
| 2018 | 9 | 79.21 | 4 |
| 2017 | 9 | 78.35 | 5 |
| 2019 | 9 | 77.74 | 6 |
| 2020 | 8 | 76.48 | 7 |
| 2018 | 8 | 75.88 | 8 |
| 2019 | 10 | 73.79 | 9 |
| 2018 | 10 | 73.28 | 10 |
| 2017 | 10 | 71.86 | 11 |
| 2019 | 11 | 68.13 | 12 |
| 2018 | 11 | 66.25 | 13 |
| 2018 | 4 | 65.91 | 14 |
追加リソース
このようなチュートリアルをもっと見たい場合は、以下のリソースを探ってください：
スノーフレークガイド
これらの投稿は私自身のものであり、必ずしもBMCの立場、戦略、または意見を代表するものではありません。