Azureの資格試験であるDP-203を勉強する中で、公式ドキュメントを読んでも、Azure Stream Analyticsで使用される「LAG関数」が全く理解できませんでした。そこで、実際にAzure Stream Analyticsを作成し、データを用意して挙動を確かめます。
LAGについて
LAG関数は、時系列データなどにおいて、「1つ以上前の行の値を取得する」ときに使用します。それでは、1つ以上前の値を取得してきてどうするのでしょうか。それは、時間ごとの気温の変化を計算したいといった際に使用することができます。
基本的に以下のMSドキュメントに記載の通りですが、私はこれでもいまいち理解できなかったため以降の章でいろいろとLAG関数の動きを試していきます。
動作確認
まずは、10秒ごとの気温のデータを用意しました。実際にはこんなに短い間隔で気温は変化しませんが、あくまでLAG関数を理解するのが目的なので値は適当です（一番最後のデータだけあえて時間間隔を空けています）。
日時に関しては、Stream AnalyticsでBlobからcsvファイルを読み取る際に、自動的にdatetime型に変換されるようにISO 8601に従う形式で記載しています。
Stream AnalyticsでBlobに格納してあるcsvファイルを取得する詳細な手順は省略しますが、予めinputにblobからのstreamを作成しておきます。
また以下がLAG関数のシンタックスとなっています。
LAG(<scalar_expression >, [<offset >], [<default>]) OVER ([PARTITION BY <partition key>] LIMIT DURATION(<unit>, <length>) [WHEN boolean_expression])
LAG (Azure Stream Analytics) – Stream Analytics Query | Microsoft Learn
1つ前の値を読み取る
まずはシンプルに以下のSQLを実行します。
「lag()」には値を取得したいカラムを指定します。今回は気温を取得したいのでtemperatureを指定しています。「partition」にはdeviceカラムを指定しています。今回はtvしかありませんが、他のデバイスがあれば同じデバイスごとに以前の値を取得するようになります。最後の「duration」は、過去10秒以内に発生したデータを読み取ることを意味します。
from句でtimestampを指定しています。ここでは日時カラムを指定します。
select date,lag(temperature) over(partition by device limit duration(second,10)) from csv2 timestamp by date
実行結果は以下のようになりました。先頭行は過去のデータがないためnullとなっています。それ以外の列は1つ前の気温のデータを取得して表示していることが分かります。9:00:10では9:00:00の10を、9:00:20では9:00:10の11を取得しています。
オフセットを指定する
「lag()」にはオフセットを指定できます。デフォルトは1で、つまり1つ前のデータを取得します。ここでは3を指定して実行してみましょう。
すべてnullになってしまいました。それもそのはずです。過去10秒間に1つしかデータがないため3つ前のデータは当然取得できません。そこで、過去30秒間に変更して再度実行します。
30秒前の（3つ前）のデータを取得できました。
nullになる場合のデフォルト値を指定する
「lag()」の第三引数にはデフォルト値を指定できます。イベントがなくnullが返ってくるときに、デフォルト値が代わりに表示されます。
10秒前との気温差を計算する
以前のデータを表示するだけではあまり意味がありませんので、もう少し有用なクエリを実行してみます。今回は1つ前（10秒前）との気温差を表示します。以下のクエリでは、気温がstring型になっているのでキャストしています。
複数のデバイスがあるときの動作
次はもう少し複雑にします。デバイスにtvとpcが2つある状態で、デバイスごとに1つ前のデータとの差分を計算してくれるか確認してみたいと思います。
デバイスごとに1つ前のデータと正しく計算されていることを分かりやすくするために、予め計算した結果をdiffカラムに追加しています。
実行結果は以下の通りで、問題なくデバイスごとに計算されていました。デバイス関係なく1つ前のデータで計算してしまうと、4行目の9:00:10のtvのdiff2は2になってしまうはずですが、そうではなく1つ前のtvの気温（1行目で10）と計算しているため、diff2は5になっていることが分かります。
when句で計算する行を絞る
最後に、when句を使用してみます。when句はover()の最後の引数として指定します。すると以下のようにdeviceがtvの行だけ1つ前の気温を取得しています。lag関数のwhen句はlag関数にだけ影響します。そのため、表示する行もtvだけにしたい場合は、SQL自体のwhen句を使用します。
まとめ
LAG関数の「以前のデータを取得する」という説明について、実際に動かして理解したうえで読むとその通りじゃんって思います。しかし説明だけ読んでいてもなかなかイメージできなかったので、Stream Analyticsを作成していろいろなパターンで動作確認を行いました。
おそらくこれで試験でLAGについても問題が出れば、確実に答えられそうです。
コメント