時系列データの分析¶
この目的のために特別に設計された機能を使用して、Snowflakeで時系列データを分析できます。データベース管理者、データサイエンティスト、アプリケーション開発者は、ビジネスアナリストや他のコンシューマーがデータを利用できるようにする前に、時系列が効率的に保存され、読み込み中であること、多くの場合、完全で一貫性のある形式に要約されていることを確認する必要があります。
はじめに：時系列とは？¶
時系列 は、システム、プロセス、および行動が一定期間にわたってどのように変化するかを捉える連続した観察から構成されます。時系列データは、幅広い業界の各種デバイスから収集されます。一般的な例としては、金融アプリケーションのために収集された株式取引データ、気象観測、スマート工場のセンサーから収集された温度測定値、デジタル広告におけるユーザーのクリックログなどがあります。
時系列における1つの記録は通常、以下のような構成要素を持ちます。
一貫した粒度（ミリ秒、秒、分、時間など）を持つ日付、時刻、またはタイムスタンプ。
通常は数値（データの傾向や異常を明らかにする可能性のある事実）である1つ以上の測定値またはなんらかのメトリクス。
温度測定値の位置や取引銘柄のシンボルなど、測定に関連するディメンション。
例えば、以下の気象観測には、開始と終了のタイムスタンプ、降雨量の測定値（0.32）、および位置情報があります。
EVENTID | TYPE | SEVERITY | START_TIME | END_TIME | PRECIP | TIME_ZONE | CITY | COUNTY | STATE | ZIP
W100 | Rain | Moderate | 2020-12-20 16:35:00.000 | 2020-12-20 17:15:00.000 | 0.32 | US/Eastern | Southport | Brunswick | NC | 28461
工場デバイスから収集された以下のデータには、名前空間（IOT）、タグ（ID）またはセンサー（ID）（3000）、デバイス上の温度読み取り値のタイムスタンプ、温度読み取り値自体（21.1673）、およびデータがその後データブローカーに到着した時である「ブローカータイムスタンプ」があります。例えば、データブローカーはSnowflakeテーブルにデータを取り込むKafkaサーバーかもしれません。
DEVICE | LINE | DEVICE_TIMESTAMP | TEMP | BROKER_TIMESTAMP
IOT | 3000 | 2023-01-01 00:01:00.000 | 21.1673 | 2023-01-01 00:01:32.000
時系列で見ると、何らかの理由で測定値が急激に変化したときにスパイクが発生することがあります。例えば、以下の画像は15秒間隔で測定された一連の温度測定値を示しており、前日まで35℃台で安定していた値が40℃を超えるピークに達しています。
以下のセクションでは、SQL関数と結合を使用して、この種の大量のデータを分析および可視化し、高速かつ正確な結果を得る方法を紹介します。
時系列データの保存方法¶
次の 日次データ型 がサポートされています。
DATE
TIME
TIMESTAMP（およびTIMESTAMP_TZを含むバリエーション）
これらのデータ型を使用するデータのロード、管理、クエリについては、 日付と時刻の値の操作 をご参照ください。
時系列データの保存とクエリの両方に役立つ、よく使われる SQL 関数 が多数用意されています。例えば、 CONVERT_TIMEZONE を使ってタイムスタンプをあるタイムゾーンから別のタイムゾーンに変換することができます。また、 EXTRACT や TIMEADD などの関数を使えば、必要に応じて時間ベースのデータを操作することができます。
注釈
TIMESTAMP_TZ では、指定した値の作成時に、実際のタイムゾーンではなく指定したタイムゾーンのオフセットがSnowflakeに保存されます。
クエリのパフォーマンスを最適化するために、時系列分析に使用されるテーブルは、多くの場合、時間によってクラスタ化されます（場合によっては、センサーIDまたは同様のディメンションによってもクラスタ化されます）。 クラスタリングキーとクラスタ化されたテーブル をご参照ください。
時系列データの集計¶
時系列データの管理には、大量のきめ細かい記録をより要約された形に集計することが必要になる場合があります（このプロセスは「ダウンサンプリング」と呼ばれることもあります）。特定の時間ベースの粒度（ミリ秒、秒、分など）を持つレコードの大規模なセットが与えられた場合、これらのレコードをより粗い粒度にロールアップし、効果的に小さなサンプルを作成することができます。
ダウンサンプリングは、データセットのサイズとそのストレージ要件を減少させるので価値のあるものです。粒度を粗くすることで、クエリ実行時に必要な計算リソースも削減できます。ダウンサンプリングを行うもう1つの重要な理由は、分析者の視点から見ると、時系列中の多数の記録が冗長である可能性があることです。例えば、センサーが1秒に1回新しい値を発するが、この測定値が60秒の間隔でほとんど変化しない場合、データを分単位にロールアップして分析することができます。
ダウンサンプリングが必要なもう一つのケースは、2つの異なるデータセットを1つのデータとして分析する必要があるが、それらのデータセットの時間粒度が異なる場合です。例えば、工場のセンサーAは15秒ごとにデータを収集しますが、センサーBは30秒ごとに関連データを収集します。この場合、記録を1分のバケットに集計するのが良い解決策かもしれません。IDs各データセットの次元はそのまま保持されますが、数値測定値は共通の時間間隔で合計または平均されます。
ダウンサンプリングの例¶
TIME_SLICE 関数を使用すると、テーブルに格納されているデータセットをダウンサンプリングできます。この関数は、固定幅の「バケット」の開始時刻と終了時刻を計算し、SUMやAVGなどの標準的な集計関数を使用して、個々の記録をグループ化してまとめることができます。
同様に、 DATE_TRUNC 関数は、一連の日付やタイムスタンプの値の一部を切り捨て、その粒度を小さくします。以下のセクションでは、各関数の例を示します。
TIME_SLICEによるダウンサンプリング¶
次の例では、 sensor_data_ts という名前のテーブルをダウンサンプリングします。このテーブルには、2つの工場センサーからの読み取り値が含まれ、530万行が含まれます。これらの測定値は1秒ごとに取り込まれるため、530万行はわずか1ヶ月分のデータに相当し、1センサーあたり250万行強になります。TIME_SLICE関数を使用すると、1分ごと、1時間ごと、1日ごとなどに1行まで集計できます。
この例を実行するには、まず sensor_data_ts テーブルを作成し、ロードします。 sensor_data_tsテーブルの作成 をご参照ください。以下は表のデータの一部です。
+-----------+-------------------------+-------------+-----------+-----------+
| DEVICE_ID | TIMESTAMP | TEMPERATURE | VIBRATION | MOTOR_RPM |
|-----------+-------------------------+-------------+-----------+-----------|
| DEVICE1 | 2024-03-01 00:00:00.000 | 32.6908 | 0.3158 | 1492 |
| DEVICE2 | 2024-03-01 00:00:00.000 | 35.2086 | 0.3232 | 1461 |
| DEVICE1 | 2024-03-01 00:00:01.000 | 35.9578 | 0.3302 | 1452 |
| DEVICE2 | 2024-03-01 00:00:01.000 | 26.2468 | 0.3029 | 1455 |
+-----------+-------------------------+-------------+-----------+-----------+
このクエリで示されるように、このテーブルには、各デバイスについて1分間に60回の測定値が含まれています。
SELECT device_id, count(*) FROM sensor_data_ts
WHERE TIMESTAMP >= ('2024-03-01 00:01:00')
AND TIMESTAMP < ('2024-03-01 00:02:00')
GROUP BY device_id;
+-----------+----------+
| DEVICE_ID | COUNT(*) |
|-----------+----------|
| DEVICE2 | 60 |
| DEVICE1 | 60 |
+-----------+----------+
このダウンサンプリングクエリでは、TIME_SLICE関数は1分間のバケットを定義し、各バケットの開始時刻を返します。AVG関数は、デバイスごとにバケットごとの平均温度を計算します。COUNT (*) 関数は、各時間バケットにいくつの行があるかを示すためのものです。
vibration、 motor_rpm 列は含まれませんが、 temperature 列と同じ方法で集計するか、別の集計関数を使用して集計することができます。
重要
この例を自分で実行した場合、 sensor_data_ts テーブルにランダムに生成された値がロードされるため、出力は正確には一致しません。
SELECT
TIME_SLICE(TO_TIMESTAMP_NTZ(timestamp), 1, 'MINUTE') minute_slice,
device_id,
COUNT(*),
AVG(temperature) avg_temp
FROM sensor_data_ts
WHERE TIMESTAMP >= ('2024-03-01 00:01:00')
AND TIMESTAMP < ('2024-03-01 00:02:00')
GROUP BY 1,2
ORDER BY 1,2;
+-------------------------+-----------+----------+---------------+
| MINUTE_SLICE | DEVICE_ID | COUNT(*) | AVG_TEMP |
|-------------------------+-----------+----------+---------------|
| 2024-03-01 00:01:00.000 | DEVICE1 | 60 | 32.4315466667 |
| 2024-03-01 00:01:00.000 | DEVICE2 | 60 | 30.4967783333 |
+-------------------------+-----------+----------+---------------+
TIME_SLICE関数を使用すると、分析用に小さな集計表を作成でき、異なるレベル（時間、日、週など）でダウンサンプリングプロセスを適用できます。
DATE_TRUNCによるダウンサンプリング¶
次の例は、 Tasty Bytesサンプルデータベース の raw.pos スキーマの order_header というテーブルからデータを選択します。このテーブルには2億4800万行が含まれています。
order_header テーブルには、 order_ts という TIMESTAMP 列があります。このクエリは、DATE_TRUNC関数の第2引数としてこの列を使用することで、集計された時系列を作成します。最初の引数は、 day 間隔を指定します。つまり、時間/分/秒の粒度を持つ個々の記録が日ごとにロールアップされます。
このクエリは、 truck_id と location_id の2つの次元で記録をグループ化しています。 avg_amount 列は、記録された営業日ごとの、フードトラック1台あたりの、1ロケーションあたりの平均注文単価を返します。
このクエリでは、2022年1月1日の最初の25行に限定しています。この日付フィルタと LIMIT 句を削除すると、クエリは元の2億4800万行を約50万行にダウンサンプリングします。
SELECT DATE_TRUNC('day', order_ts)::date sliced_ts, truck_id, location_id, AVG(order_amount)::NUMBER(4,2) as avg_amount
FROM order_header
WHERE EXTRACT(YEAR FROM order_ts)='2022'
GROUP BY date_trunc('day', order_ts), truck_id, location_id
ORDER BY 1, 2, 3 LIMIT 25;
+------------+----------+-------------+------------+
| SLICED_TS | TRUCK_ID | LOCATION_ID | AVG_AMOUNT |
|------------+----------+-------------+------------|
| 2022-01-01 | 1 | 3223 | 19.23 |
| 2022-01-01 | 1 | 3869 | 20.15 |
| 2022-01-01 | 2 | 2401 | 39.29 |
| 2022-01-01 | 2 | 4199 | 34.29 |
| 2022-01-01 | 3 | 2883 | 35.01 |
| 2022-01-01 | 3 | 2961 | 39.15 |
| 2022-01-01 | 4 | 2614 | 35.95 |
| 2022-01-01 | 4 | 2899 | 40.29 |
| 2022-01-01 | 6 | 1946 | 26.58 |
| 2022-01-01 | 6 | 14960 | 18.59 |
| 2022-01-01 | 7 | 1427 | 26.91 |
| 2022-01-01 | 7 | 3224 | 28.88 |
| 2022-01-01 | 9 | 1557 | 35.52 |
| 2022-01-01 | 9 | 2612 | 43.80 |
| 2022-01-01 | 10 | 2217 | 32.35 |
| 2022-01-01 | 10 | 2694 | 32.23 |
| 2022-01-01 | 11 | 2656 | 44.23 |
| 2022-01-01 | 11 | 3327 | 52.00 |
| 2022-01-01 | 12 | 3181 | 52.84 |
| 2022-01-01 | 12 | 3622 | 49.59 |
| 2022-01-01 | 13 | 2516 | 31.13 |
| 2022-01-01 | 13 | 3876 | 28.13 |
| 2022-01-01 | 14 | 1359 | 72.04 |
| 2022-01-01 | 14 | 2505 | 68.75 |
| 2022-01-01 | 15 | 2901 | 41.90 |
+------------+----------+-------------+------------+
ローリング計算にウィンドウ集約を使用する¶
ウィンドウ集計関数を使用して、メトリックの経時変化を観察することで、時系列の傾向を分析することができます。ウィンドウ集計は、より大きなデータセットの定義されたサブセット（「ウィンドウ」）内のデータを分析するのに便利です。データセットの各行について、現在の行の前後または周辺の行のグループを考慮したローリング計算（移動平均や合計など）を行うことができます。この種の分析は、データセット全体を要約する通常の集計とは対照的です。
明示的なオフセットを持つ範囲ベースのウィンドウフレームを使用することにより、これらのローリング集計を計算するために非常に柔軟なアプローチを適用することができます。RANGE BETWEEN ウィンドウフレームは、タイムスタンプか数値のどちらかで並べられ、時系列データで発生する可能性のあるギャップによって中断されることはありません。例えば、次の図では、 Day 4 のデータが一連の記録で欠落していても、3日間の移動ウィンドウにおける集計関数の計算には影響しません。特にフレーム3、4、5は、 Day 4 のデータが未知であることを考慮に入れて、正しく計算されています。
次の例では、さまざまな都市や郡の毎時の降水量を記録した気象データの移動和を計算しています。この種のクエリを実行することで、センサーやその他の IoT デバイスなど、さまざまな時系列データセットの傾向を評価することができます。特に、これらのデータセットにギャップがあることが分かっている、または予想される場合です。
ウィンドウ関数は、現在の降水量の読み取り値と、 現在の読み取り値より前の指定された時間間隔内にあるすべての読み取り値をフレームに含めます。 ローリング計算は、正確な行 数 ではなく、この柔軟で論理的な行の 範囲 に基づいています。各都市の最初の行は、 precip と moving_sum_precip の値が一致しています。その後、フレーム内の後続の行ごとに合計が再計算されます。生の値は大きく変動しますが、移動和には強い平滑化効果があります。
この例を実行するには、まず以下の指示に従ってください。 heavy_weatherテーブルの作成とロード この非常に小さな表は、1時間ごとの散発的な気象観測を含み、欠落した日を含む多くのギャップがあります。このクエリは、 start_time 列で並べられた降水量の値の移動和を返します。ウィンドウフレームは、現在の行の12時間前から現在の行までの範囲を定義します。したがって、フレームは現在の行と、現在の行の ORDER BY タイムスタンプより12時間前までのタイムスタンプを持つ行のみで構成されます。
SELECT city, start_time, precip,
SUM(precip) OVER(
PARTITION BY city
ORDER BY start_time
RANGE BETWEEN INTERVAL '12 hours' PRECEDING AND CURRENT ROW) moving_sum_precip
FROM heavy_weather
WHERE city IN('South Lake Tahoe','Big Bear City')
GROUP BY city, precip, start_time
ORDER BY city;
+------------------+-------------------------+--------+-------------------+
| CITY | START_TIME | PRECIP | MOVING_SUM_PRECIP |
|------------------+-------------------------+--------+-------------------|
| Big Bear City | 2021-12-24 05:35:00.000 | 0.42 | 0.42 |
| Big Bear City | 2021-12-24 16:55:00.000 | 0.09 | 0.51 |
| Big Bear City | 2021-12-26 09:55:00.000 | 0.07 | 0.07 |
| South Lake Tahoe | 2021-12-23 16:23:00.000 | 0.56 | 0.56 |
| South Lake Tahoe | 2021-12-23 17:24:00.000 | 0.38 | 0.94 |
| South Lake Tahoe | 2021-12-23 18:30:00.000 | 0.28 | 1.22 |
| South Lake Tahoe | 2021-12-23 19:36:00.000 | 0.80 | 2.02 |
| South Lake Tahoe | 2021-12-24 06:49:00.000 | 0.17 | 0.97 |
| South Lake Tahoe | 2021-12-24 15:53:00.000 | 0.07 | 0.24 |
| South Lake Tahoe | 2021-12-26 05:43:00.000 | 0.16 | 0.16 |
| South Lake Tahoe | 2021-12-27 14:53:00.000 | 0.07 | 0.07 |
| South Lake Tahoe | 2021-12-27 17:53:00.000 | 0.07 | 0.14 |
+------------------+-------------------------+--------+-------------------+
Big Bear Cityの3つの moving_sum_precip 値は次のように計算されます。
0.42 = 0.42（前行なし）
0.42 + 0.09 = 0.51（最初の2行は12時間ウィンドウ内）
0.07 = 0.07（12時間ウィンドウ以内の前行なし）
例えば、South Lake Tahoeの行にはこのような計算が含まれています。
0.56 + 0.38 + 0.28 + 0.80 = 2.02（2024年12月23日の4行はすべて12時間以内）
0.80 + 0.17 = 0.97（1つ前の行は12時間ウィンドウ内）
LEAD、 LAG ランキング関数など、その他のウィンドウ関数も時系列分析でよく使われます。現在のデータポイントから相対的に、時系列の次のデータポイントを見つけるには、LEADウィンドウ関数を使用し、前のデータポイントを見つけるには、LAG関数を使用します。
Snowsightでクエリ結果を視覚化する¶
Snowsight を使って集計クエリの結果を視覚化し、スライディングウィンドウフレームを使った計算のスムージング効果をより実感することができます。クエリワークシートで、 Results の横にある Chart ボタンをクリックします。
例えば、次の棒グラフの黄色い線は、生の気温の青い線に対して、平均気温のはるかに滑らかな傾向を示しています。クエリそのものは次のようになります。
SELECT device_id, timestamp, temperature, AVG(temperature)
OVER (PARTITION BY device_id ORDER BY timestamp
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_temp
FROM sensor_data_ts
WHERE timestamp BETWEEN '2024-03-15 00:00:59.000' AND '2024-03-15 00:01:10.000'
ORDER BY 1, 2;
MIN_BYおよびMAX_BY集計関数の使用¶
同じ行の別の列の最小値または最大値に基づいて、ある列を選択する機能は、時系列データを扱う SQL 開発者にとって一般的な要件です。 MIN_BY と MAX_BY は、データがタイムスタンプなどの他の列でソートされている場合に、テーブルの開始値と終了値（または最高値と最低値、最初の値と最後の値）を返す便利な関数です。
最初の例は、単純にテーブル全体の最後の（最新の） precip 値を見つけます。MAX_BY 関数は、すべての行を start_time の値でソートし、次に「最大」開始時刻の precip の値を返します。
以下の例で使用するテーブルを作成し、ロードするには、 heavy_weatherテーブルの作成 をご参照ください。
SELECT MAX_BY(precip, start_time) most_recent_precip
FROM heavy_weather;
+--------------------+
| MOST_RECENT_PRECIP |
|--------------------|
| 0.07 |
+--------------------+
このクエリを実行することで、この結果を確認することができます。
SELECT * FROM heavy_weather WHERE start_time=
(SELECT MAX(start_time) FROM heavy_weather);
+-------------------------+--------+-------+-------------+
| START_TIME | PRECIP | CITY | COUNTY |
|-------------------------+--------+-------+-------------|
| 2021-12-30 20:53:00.000 | 0.07 | Lebec | Los Angeles |
+-------------------------+--------+-------+-------------+
GROUPBY句を追加して、このデータについてより興味深い質問をすることができます。例えば、次のクエリは、カリフォルニア州の各都市で観測された最後の降水量を precip の値（多い順から少ない順）で検索します。結果は city でグループ化され、各都市の最後の precip の値が返されます。
SELECT city, MAX_BY(precip, start_time) most_recent_precip
FROM heavy_weather
GROUP BY city
ORDER BY 2 DESC;
+------------------+--------------------+
| CITY | MOST_RECENT_PRECIP |
|------------------+--------------------|
| Alta | 0.89 |
| Bishop | 0.75 |
| Mammoth Lakes | 0.37 |
| Alturas | 0.23 |
| Mount Shasta | 0.09 |
| South Lake Tahoe | 0.07 |
| Big Bear City | 0.07 |
| Montague | 0.07 |
| Lebec | 0.07 |
+------------------+--------------------+
前回観測されたアルタ市の precip の値は 0.89 で、前回観測されたサウスレイクタホ市、ビッグベアシティ市、モンタギュー市、レベック市の precip の値は4か所とも 0.07 です。（クエリでは、これらの観測がいつ行われたかはわかりません。）
MIN_BY 関数を使用すると、「正反対」の結果セット（最も古い precip の記録と最新の記録）を返すことができます。
SELECT city, MIN_BY(precip, start_time) oldest_precip
FROM heavy_weather
GROUP BY city
ORDER BY 2 DESC;
+------------------+---------------+
| CITY | OLDEST_PRECIP |
|------------------+---------------|
| South Lake Tahoe | 0.56 |
| Big Bear City | 0.42 |
| Mammoth Lakes | 0.37 |
| Alta | 0.25 |
| Alturas | 0.23 |
| Bishop | 0.08 |
| Lebec | 0.08 |
| Mount Shasta | 0.08 |
| Montague | 0.07 |
+------------------+---------------+
時系列データの結合¶
時系列データを含むテーブルを結合するには、 ASOF JOIN 構文を使用します。ASOF JOIN クエリは複雑な SQL や他の型の結合、ウィンドウ関数を使用することでエミュレートできますが、 ASOF JOIN 構文を使用すると、これらのクエリの作成が簡単になります（最適化もされます）。
ASOFジョインの一般的な用途は、金融取引データの分析です。たとえば、取引コスト分析では、株式の購入を決定した時点での気配値と、取引が実行および記録された時点で実際に支払われた価格との差を測定する「スリッページ」計算が必要です。ASOF JOIN はこの種の分析を迅速化できます。この結合方法の主な機能は、ある時系列を別の時系列と比較して分析することであるため、ASOF JOINは、本質的に履歴的なデータセットを分析するのに役立つ可能性があります。これらのユースケースの多くでは、異なるデバイスから読み取られたタイムスタンプが完全に同じではない場合に、データを関連付けるためにASOF JOINが使用できます。
分析が必要な時系列データが2つのテーブルに存在し、各テーブルの各行にタイムスタンプがあることが前提となります。このタイムスタンプは、記録されたイベントの正確な「その時点」の日付と時刻を表します。最初（または左側）のテーブルの各行に対して、結合では、指定した比較演算子を持つ「一致条件」を使用して、タイムスタンプ値が次のいずれかである2番目（または右側）のテーブル内の単一の行を検索します。
左側のテーブルのタイムスタンプ値以下。
左側のテーブルのタイムスタンプ値以上。
左側のテーブルのタイムスタンプ値より小さい。
左側のテーブルのタイムスタンプ値より大きい。
右側の条件に該当する行はもっとも近い一致であり、指定された比較演算子に応じて、時間的に等しい、時間的に早い、または時間的に遅い可能性があります。
ASOF JOIN の結果のカーディナリティは常に左側のテーブルのカーディナリティと等しくなります。左側のテーブルに4,000万行が含まれている場合、 ASOF JOIN は4,000万行を返します。したがって、左側のテーブルは「保存」テーブル、右側のテーブルは「参照」テーブルと考えることができます。
2つのテーブルを最も近い一致で結合（アライメント）¶
たとえば、財務アプリケーションで、 quotes という名前のテーブルと trades という名前のテーブルがあるとします。1つのテーブルには株式購入の入札履歴が記録され、もう1つのテーブルには実際の取引履歴が記録されます。株式購入の入札は取引の前に行われます（記録された時間の粒度に応じて、「同時に」行われる場合もあります）。どちらのテーブルにもタイムスタンプがあり、比較対象として使用するその他の列も両方にあります。単純なASOF JOINクエリは、各取引の前にもっとも近い気配値（時間的に）を返します。言い換えると、クエリは次のように尋ねます。「取引を行った時点での株価はいくらでしたか」
trades テーブルには3つの行が含まれ、 quotes テーブルには7つの行が含まれるとします。セルの背景色は、一致する銘柄シンボルで行を結合し、そのタイムスタンプ列を比較したときに、 quotes のどの3行が ASOF JOIN の対象となるかを示しています。
TRADES テーブル（左側または「保存」テーブル）
QUOTESテーブル（右側または「参照」テーブル）
この概念的な例を具体的なクエリ（ASOFJOIN）にするのは簡単です。
SELECT t.stock_symbol, t.trade_time, t.quantity, q.quote_time, q.price
FROM trades t ASOF JOIN quotes q
MATCH_CONDITION(t.trade_time >= quote_time)
ON t.stock_symbol=q.stock_symbol
ORDER BY t.stock_symbol;
+--------------+-------------------------+----------+-------------------------+--------------+
| STOCK_SYMBOL | TRADE_TIME | QUANTITY | QUOTE_TIME | PRICE |
|--------------+-------------------------+----------+-------------------------+--------------|
| AAPL | 2023-10-01 09:00:05.000 | 2000 | 2023-10-01 09:00:03.000 | 139.00000000 |
| SNOW | 2023-10-01 09:00:05.000 | 1000 | 2023-10-01 09:00:02.000 | 163.00000000 |
| SNOW | 2023-10-01 09:00:10.000 | 1500 | 2023-10-01 09:00:08.000 | 165.00000000 |
+--------------+-------------------------+----------+-------------------------+--------------+
ON 条件は、一致した行を株式シンボルでグループ化します。
この例を実行するには、以下のようにテーブルを作成し、読み込みます。
CREATE OR REPLACE TABLE trades (
stock_symbol VARCHAR(4),
trade_time TIMESTAMP_NTZ(9),
quantity NUMBER(38,0)
);
CREATE OR REPLACE TABLE quotes (
stock_symbol VARCHAR(4),
quote_time TIMESTAMP_NTZ(9),
price NUMBER(12,8)
);
INSERT INTO trades VALUES
('SNOW','2023-10-01 09:00:05.000', 1000),
('AAPL','2023-10-01 09:00:05.000', 2000),
('SNOW','2023-10-01 09:00:10.000', 1500);
INSERT INTO quotes VALUES
('SNOW','2023-10-01 09:00:01.000', 166.00),
('SNOW','2023-10-01 09:00:02.000', 163.00),
('SNOW','2023-10-01 09:00:07.000', 166.00),
('SNOW','2023-10-01 09:00:08.000', 165.00),
('AAPL','2023-10-01 09:00:03.000', 139.00),
('AAPL','2023-10-01 09:00:07.000', 142.00),
('AAPL','2023-10-01 09:00:11.000', 142.00);
ASOF JOIN クエリの例については、 例 をご参照ください。
ASOFJOINでデータのギャップを埋める¶
ASOFJOINは、時間ベースの列の非正確な一致によって2つのテーブルのデータを整列させることに加え、生のデータテーブルに特定の日付やタイムスタンプの行がない場合に、時系列のギャップを埋めるのに便利です。このプロセスは「ギャップ埋め」または「補間」として知られています。機器の故障や停電のためにセンサーの読み取り値がスキップされ、行が欠落している場合は、ASOFJOINを使用して、生成された時系列からテーブルに値を補間することができます。欠落した行は、欠落している測定値の最後の既知の値で埋められます。この値は「前回の観察繰越」（LOCF）とも呼ばれます。ASOFJOINクエリは、時系列で連続した行の完全なセットを返します。
補間にASOFJOINを使用するには、以下の手順に従います。
簡単なクエリを実行して、テーブルのギャップを識別子します。
必要な期間について、適切な粒度で完全な時系列を作成します。例えば、時系列は特定の年の単純な日付の列かもしれませんし、ある日数の1秒あたりのタイムスタンプのもっと細かい列かもしれません。値のリストを生成するには、SQLまたは表計算アプリケーションを使用できます。
時系列には、後でASOFJOINON条件で指定する、各行に意味のあるIDまたはディメンションも必要です。
欠落している行に値を補間するASOFJOINクエリを記述します。生成された時系列は保存テーブルとなり、生データテーブルは参照テーブルとなります。
次の例では、 sensor_data_ts テーブルが必要です。まだ作成およびロードしていない場合は、 sensor_data_tsテーブルの作成 をご参照ください。ギャップを埋める作業の必要性をシミュレートするために、以下のようにテーブルからいくつかの行を削除します。
DELETE FROM sensor_data_ts
WHERE device_id='DEVICE2'
AND TIMESTAMP > ('2024-03-07 00:01:15')
AND TIMESTAMP <= ('2024-03-07 00:01:20');
その結果、3月7日の DEVICE2 （1:16～1:20）の5行が欠落した表が出来上がりました。
+------------------------+
| number of rows deleted |
|------------------------|
| 5 |
+------------------------+
では、以下の手順に従って、ギャップを埋める練習をしてください。
注釈
この例を自分で実行した場合、 sensor_data_ts テーブルにランダムに生成された値がロードされるため、出力は正確には一致しません。
ステップ1：テーブルにギャップがあることを確認します。¶
以下のクエリを実行して、ギャップを識別します。
SELECT * FROM sensor_data_ts
WHERE device_id='DEVICE2'
AND TIMESTAMP >= ('2024-03-07 00:01:15')
AND TIMESTAMP <= ('2024-03-07 00:01:21')
ORDER BY TIMESTAMP;
+-----------+-------------------------+-------------+-----------+-----------+
| DEVICE_ID | TIMESTAMP | TEMPERATURE | VIBRATION | MOTOR_RPM |
|-----------+-------------------------+-------------+-----------+-----------|
| DEVICE2 | 2024-03-07 00:01:15.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:21.000 | 28.0426 | 0.2944 | 1448 |
+-----------+-------------------------+-------------+-----------+-----------+
このクエリは、 DEVICE2 に対して2つの行を返します。ギャップ前の最後の行とギャップ後の最初の行です。
ステップ2：既知のギャップをカバーする完全な時系列の生成¶
sensor_data_ts テーブルのギャップに対して細かい粒度（1秒につき1行）で時系列を生成するには、生成されたタイムスタンプを含む以下のテーブルを作成します。
CREATE OR REPLACE TABLE continuous_timestamps AS
SELECT 'DEVICE2' as DEVICE_ID,
DATEADD('SECOND', ROW_NUMBER() OVER (ORDER BY SEQ8()), '2024-03-07 00:01:15')::TIMESTAMP_NTZ AS TS
FROM TABLE(GENERATOR(ROWCOUNT => 5));
この SQL ステートメントでは、 5 はギャップをカバーするために必要な秒数です。デバイスの ID 値（DEVICE2）は、生成された行に含まれることに注意してください。
以下のクエリは、生成された5つの行を返します。
SELECT * FROM continuous_timestamps ORDER BY ts;
+-----------+-------------------------+
| DEVICE_ID | TS |
|-----------+-------------------------|
| DEVICE2 | 2024-03-07 00:01:16.000 |
| DEVICE2 | 2024-03-07 00:01:17.000 |
| DEVICE2 | 2024-03-07 00:01:18.000 |
| DEVICE2 | 2024-03-07 00:01:19.000 |
| DEVICE2 | 2024-03-07 00:01:20.000 |
+-----------+-------------------------+
ステップ3：ASOFJOINを使って値を補間する¶
これで、 continuous_timestamps を sensor_data_ts に結合し、 DEVICE2 の欠損行の値を補間する、 ASOF JOIN クエリを実行できます。一致条件は、欠落している各行に対して時間的に最も近い行を見つけ、ON条件は、一致するデバイスIDsで補間が行われることを保証します。
この例で示されているように、 >= が一致条件に指定されている場合、欠落行に最も近い行は、 2024-03-07 00:01:16.000 のタイムスタンプを持つ行です。
INSERT INTO sensor_data_ts(device_id, timestamp, temperature, vibration, motor_rpm)
SELECT t.device_id, t.ts, s.temperature, s.vibration, s.motor_rpm
FROM continuous_timestamps t
ASOF JOIN sensor_data_ts s
MATCH_CONDITION(t.ts >= s.timestamp)
ON t.device_id = s.device_id
WHERE TIMESTAMP >= ('2024-03-07 00:01:15')
AND TIMESTAMP < ('2024-03-07 00:01:21');
この INSERT 文は、 ASOF JOIN 操作から5行を選択し、 sensor_data_ts テーブルに挿入します。
+-------------------------+
| number of rows inserted |
|-------------------------|
| 5 |
+-------------------------+
補間の結果を確認するには、 sensor_data_ts の表から、これらの5行と、その前後の2行を選択します。補間された5つの行は、 2024-03-07 00:01:15.000 の行で記録されたのと同じ値を、 temperature、 vibration、 motor_rpm の列で拾っていることに注意してください。補間は成功しました。
SELECT * FROM sensor_data_ts
WHERE device_id='DEVICE2'
AND TIMESTAMP >= ('2024-03-07 00:01:15')
AND TIMESTAMP <= ('2024-03-07 00:01:21')
ORDER BY TIMESTAMP;
+-----------+-------------------------+-------------+-----------+-----------+
| DEVICE_ID | TIMESTAMP | TEMPERATURE | VIBRATION | MOTOR_RPM |
|-----------+-------------------------+-------------+-----------+-----------|
| DEVICE2 | 2024-03-07 00:01:15.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:16.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:17.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:18.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:19.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:20.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:21.000 | 28.0426 | 0.2944 | 1448 |
+-----------+-------------------------+-------------+-----------+-----------+
時系列データへのMLベースの関数の適用¶
ML関数でモデルをトレーニングして、時系列データの予測分析を行うことができます。
予測は、過去の時系列データを使って将来のデータを予測します。過去の日時における実際の観測値が記録された時系列が与えられた場合、MLモデルは、将来の日時における観測値がどうなるかを予測します。
異常値検出は、予想される範囲から逸脱したデータポイントである異常値を識別します。時系列の文脈では、外れ値とは、同じような時間間隔の他の測定値よりもはるかに大きいか小さい測定値のことです。異常値を見つけるために、ML関数は異常値をチェックするのと同じ期間の予測を作成し、次に予測結果を実際のデータと比較します。
Top Insightsは、データセットの最も重要なディメンションを見つけ、それらのディメンションからセグメントを構築し、それらのセグメントのどれがメトリックに影響を与えたかを検出します。
注釈
機械学習のためには、時系列のタイムスタンプは一定の時間間隔を表す必要があります。必要であれば、TIMESTAMP列に対してDATE_TRUNCまたはTIME_SLICE関数を使用して、予測モデルのトレーニング時に不規則性を取り除くことができます。
時系列における異常検出の例¶
次の例では、30行しかない表示を使って異常検知モデルを学習します。まずデータをテーブルに生成し、そのテーブルに表示を作成します。表示オプションは必須ではありません（テーブルを使用してモデルをトレーニングすることができます）が、表示オプションを使用することで、ソースデータを更新することなく、行数を変えてモデルを繰り返しトレーニングする柔軟性が得られます。
注釈
この例を自分で実行した場合、 sensor_data_30_rows テーブルにランダムに生成された値がロードされるため、出力は正確には一致しません。
CREATE OR REPLACE TABLE sensor_data_30_rows (
device_id VARCHAR(10),
timestamp TIMESTAMP,
temperature DECIMAL(6,4),
vibration DECIMAL(6,4),
motor_rpm INT);
INSERT INTO sensor_data_30_rows (device_id, timestamp, temperature, vibration, motor_rpm)
SELECT 'DEVICE3', timestamp,
UNIFORM(30.2345, 36.3456, RANDOM()), --
UNIFORM(0.4000, 0.4718, RANDOM()), --
UNIFORM(1510, 1625, RANDOM()) --
FROM (
SELECT DATEADD(SECOND, SEQ4(), '2024-03-01') AS timestamp
FROM TABLE(GENERATOR(ROWCOUNT => 30))
);
CREATE OR REPLACE VIEW sensor_data_view AS SELECT * FROM sensor_data_30_rows;
次にモデルを作成します。
CREATE OR REPLACE SNOWFLAKE.ML.ANOMALY_DETECTION sensor_model(
INPUT_DATA => SYSTEM$REFERENCE('VIEW', 'sensor_data_view'),
TIMESTAMP_COLNAME => 'timestamp',
TARGET_COLNAME => 'temperature',
LABEL_COLNAME => '');
+---------------------------------------------+
| status |
|---------------------------------------------|
| Instance SENSOR_MODEL successfully created. |
+---------------------------------------------+
モデルが正常に構築されたら、 <モデル名>!DETECT_ANOMALIES メソッドを呼び出して、指定されたテストデータセットの外れ値を検出します。テストデータのタイムスタンプは、トレーニングデータのタイムスタンプに時系列的に追随していなければなりませんが、トレーニングデータとテストデータの間にあまり大きな時間のずれがあってはなりません。例えば、1秒ごとのタイムスタンプがある場合、トレーニングデータより何百万秒も先のテストデータは使用しないでください。
この例では、3行しかない別のテーブルをテストデータとして使用しています。これらの行は、トレーニングデータのタイムスタンプに近いタイムスタンプを持っています。
CREATE OR REPLACE TABLE sensor_data_device3 (
device_id VARCHAR(10),
timestamp TIMESTAMP,
temperature DECIMAL(6,4),
vibration DECIMAL(6,4),
motor_rpm INT);
INSERT INTO sensor_data_device3 VALUES
('DEVICE3','2024-03-01 00:00:30.000',36.0422,0.4226,1560),
('DEVICE3','2024-03-01 00:00:31.000',36.1519,0.4341,1515),
('DEVICE3','2024-03-01 00:00:32.000',36.1524,0.4321,1591);
CALL sensor_model!DETECT_ANOMALIES(
INPUT_DATA => SYSTEM$REFERENCE('TABLE', 'sensor_data_device3'),
TIMESTAMP_COLNAME => 'timestamp',
TARGET_COLNAME => 'temperature'
);
異常検知コールが終了すると、以下のような出力が返されます。
+-------------------------+---------+--------------+--------------+--------------+------------+--------------+-------------+
| TS | Y | FORECAST | LOWER_BOUND | UPPER_BOUND | IS_ANOMALY | PERCENTILE | DISTANCE |
|-------------------------+---------+--------------+--------------+--------------+------------+--------------+-------------|
| 2024-03-01 00:00:30.000 | 36.0422 | 30.809998241 | 25.583156942 | 36.036839539 | True | 0.9950380683 | 2.578470982 |
| 2024-03-01 00:00:31.000 | 36.1519 | 32.559470456 | 27.332629158 | 37.786311755 | False | 0.961667911 | 1.770378085 |
| 2024-03-01 00:00:32.000 | 36.1524 | 32.205610776 | 26.978769478 | 37.432452075 | False | 0.9741130751 | 1.945009377 |
+-------------------------+---------+--------------+--------------+--------------+------------+--------------+-------------+
TS と Y 列は、テストデータからタイムスタンプと温度値を返します。この非常に小さなテストケースで、この関数は異常（IS_ANOMALY=True）を発見しました。出力列の詳細については、 関数の説明 の「戻り値」セクションをご参照ください。
sensor_data_tsテーブルの作成¶
sensor_data_ts テーブルをクエリするこのセクションの例をテストしたい場合は、以下の SQL スクリプトを実行することで、このテーブルのコピーを作成し、ロードすることができます。このスクリプトは、UNIFORM、RANDOM、GENERATOR関数を呼び出すことによって、センサーの測定値の1ヶ月分の合成データを生成します。したがって、テーブルのコピーが同一の結果を返すわけではありません。測定値は同じ範囲になりますが、同じ値にはなりません。
CREATE OR REPLACE TABLE sensor_data_device1 (
device_id VARCHAR(10),
timestamp TIMESTAMP,
temperature DECIMAL(6,4),
vibration DECIMAL(6,4),
motor_rpm INT
);
INSERT INTO sensor_data_device1 (device_id, timestamp, temperature, vibration, motor_rpm)
SELECT 'DEVICE1', timestamp,
UNIFORM(25.1111, 40.2222, RANDOM()), -- Temperature range in °C
UNIFORM(0.2985, 0.3412, RANDOM()), -- Vibration range in mm/s
UNIFORM(1400, 1495, RANDOM()) -- Motor RPM range
FROM (
SELECT DATEADD(SECOND, SEQ4(), '2024-03-01') AS timestamp
FROM TABLE(GENERATOR(ROWCOUNT => 2678400)) -- seconds in 31 days
);
CREATE OR REPLACE TABLE sensor_data_device2 (
device_id VARCHAR(10),
timestamp TIMESTAMP,
temperature DECIMAL(6,4),
vibration DECIMAL(6,4),
motor_rpm INT
);
INSERT INTO sensor_data_device2 (device_id, timestamp, temperature, vibration, motor_rpm)
SELECT 'DEVICE2', timestamp,
UNIFORM(24.6642, 36.3107, RANDOM()), -- Temperature range in °C
UNIFORM(0.2876, 0.3333, RANDOM()), -- Vibration range in mm/s
UNIFORM(1425, 1505, RANDOM()) -- Motor RPM range
FROM (
SELECT DATEADD(SECOND, SEQ4(), '2024-03-01') AS timestamp
FROM TABLE(GENERATOR(ROWCOUNT => 2678400)) -- seconds in 31 days
);
INSERT INTO sensor_data_device1 SELECT * FROM sensor_data_device2;
DROP TABLE IF EXISTS sensor_data_ts;
ALTER TABLE sensor_data_device1 rename to sensor_data_ts;
DROP TABLE sensor_data_device2;
SELECT COUNT(*) FROM sensor_data_ts; -- verify row count = 5356800
heavy_weatherテーブルの作成¶
以下のスクリプトは、 MAX_BY 関数の例で使用される heavy_weather テーブルを作成し、ロードします。この表には、2021年最後の週のカリフォルニア州各都市の降雪降水量の記録が55行含まれています。
CREATE OR REPLACE TABLE heavy_weather
(start_time TIMESTAMP, precip NUMBER(3,2), city VARCHAR(20), county VARCHAR(20));
INSERT INTO heavy_weather VALUES
('2021-12-23 06:56:00.000',0.08,'Mount Shasta','Siskiyou'),
('2021-12-23 07:51:00.000',0.09,'Mount Shasta','Siskiyou'),
('2021-12-23 16:23:00.000',0.56,'South Lake Tahoe','El Dorado'),
('2021-12-23 17:24:00.000',0.38,'South Lake Tahoe','El Dorado'),
('2021-12-23 18:30:00.000',0.28,'South Lake Tahoe','El Dorado'),
('2021-12-23 19:35:00.000',0.37,'Mammoth Lakes','Mono'),
('2021-12-23 19:36:00.000',0.80,'South Lake Tahoe','El Dorado'),
('2021-12-24 04:43:00.000',0.25,'Alta','Placer'),
('2021-12-24 05:26:00.000',0.34,'Alta','Placer'),
('2021-12-24 05:35:00.000',0.42,'Big Bear City','San Bernardino'),
('2021-12-24 06:49:00.000',0.17,'South Lake Tahoe','El Dorado'),
('2021-12-24 07:40:00.000',0.07,'Alta','Placer'),
('2021-12-24 08:36:00.000',0.07,'Alta','Placer'),
('2021-12-24 11:52:00.000',0.08,'Alta','Placer'),
('2021-12-24 12:52:00.000',0.38,'Alta','Placer'),
('2021-12-24 15:44:00.000',0.13,'Alta','Placer'),
('2021-12-24 15:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-24 16:55:00.000',0.09,'Big Bear City','San Bernardino'),
('2021-12-24 21:53:00.000',0.07,'Montague','Siskiyou'),
('2021-12-25 02:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 07:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 08:52:00.000',0.08,'Alta','Placer'),
('2021-12-25 09:48:00.000',0.18,'Alta','Placer'),
('2021-12-25 12:52:00.000',0.10,'Alta','Placer'),
('2021-12-25 17:21:00.000',0.23,'Alturas','Modoc'),
('2021-12-25 17:52:00.000',1.54,'Alta','Placer'),
('2021-12-26 01:52:00.000',0.61,'Alta','Placer'),
('2021-12-26 05:43:00.000',0.16,'South Lake Tahoe','El Dorado'),
('2021-12-26 05:56:00.000',0.08,'Bishop','Inyo'),
('2021-12-26 06:52:00.000',0.75,'Bishop','Inyo'),
('2021-12-26 06:53:00.000',0.08,'Lebec','Los Angeles'),
('2021-12-26 07:52:00.000',0.65,'Alta','Placer'),
('2021-12-26 09:52:00.000',2.78,'Alta','Placer'),
('2021-12-26 09:55:00.000',0.07,'Big Bear City','San Bernardino'),
('2021-12-26 14:22:00.000',0.32,'Alta','Placer'),
('2021-12-26 14:52:00.000',0.34,'Alta','Placer'),
('2021-12-26 15:43:00.000',0.35,'Alta','Placer'),
('2021-12-26 17:31:00.000',5.24,'Alta','Placer'),
('2021-12-26 22:52:00.000',0.07,'Alta','Placer'),
('2021-12-26 23:15:00.000',0.52,'Alta','Placer'),
('2021-12-27 02:52:00.000',0.08,'Alta','Placer'),
('2021-12-27 03:52:00.000',0.14,'Alta','Placer'),
('2021-12-27 04:52:00.000',1.52,'Alta','Placer'),
('2021-12-27 14:37:00.000',0.89,'Alta','Placer'),
('2021-12-27 14:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-27 17:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-30 11:23:00.000',0.12,'Lebec','Los Angeles'),
('2021-12-30 11:43:00.000',0.98,'Lebec','Los Angeles'),
('2021-12-30 13:53:00.000',0.23,'Lebec','Los Angeles'),
('2021-12-30 14:53:00.000',0.13,'Lebec','Los Angeles'),
('2021-12-30 15:15:00.000',0.29,'Lebec','Los Angeles'),
('2021-12-30 17:53:00.000',0.10,'Lebec','Los Angeles'),
('2021-12-30 18:53:00.000',0.09,'Lebec','Los Angeles'),
('2021-12-30 19:53:00.000',0.07,'Lebec','Los Angeles'),
('2021-12-30 20:53:00.000',0.07,'Lebec','Los Angeles')
;