ウィンドウ関数 / OVER

MachbaseのLAG()とLEAD()は、クエリ結果の各行から前または後の行の値を参照します。 例えばセンサー別の測定値を時刻順に比較し、直前の測定値との差を求められます。 GROUP BY集計と異なり、複数行を1行に集約しません。

構文

LAG(value_expression, offset) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
)

LEAD(value_expression, offset) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
)

OVERは必須ですが、括弧内のPARTITION BYとORDER BYは省略できます。時刻順の比較には ORDER BY timeなどの基準を明示してください。ソートキーが同じ行の順序は、この式だけでは区別できません。 最終結果の出力順序が必要な場合は、SELECT文の末尾にもORDER BYを指定します。

対応するウィンドウ関数

参照する行がなければNULLを返します。offsetは時間間隔ではなく行数です。測定間隔が不規則な場合、 直前の行が必ず1秒前や1分前の測定値とは限りません。

他のDBMSのウィンドウ構文との違い

次の構文をMachbaseのLAG/LEAD構文と混同しないでください。

• ROW_NUMBER()、RANK()、DENSE_RANK()、FIRST_VALUE()、LAST_VALUE()はサポートしません。
• SUM(...) OVER (...)やAVG(...) OVER (...)などの集約ウィンドウ関数はサポートしません。
• ROWS/RANGEフレームとUNBOUNDED PRECEDING、CURRENT ROWなどのフレーム境界は指定できません。
• OVER内のPARTITION BYとORDER BYには、それぞれ1つの式のみ指定できます。ORDER BYの後にASC/DESCを指定する構文もサポートしません。

結果行に番号を付けるROWNUM()と連続区間番号を求めるSERIESNUM()は、 ウィンドウ/系列関数で説明します。

例

LAG / LEAD: 前後の値の比較

次の例は、name、time、value列を持つsensor_tagテーブルを使用します。

SELECT name, time, value,
       LAG(value, 1) OVER (PARTITION BY name ORDER BY time) AS prev_value,
       LEAD(value, 1) OVER (PARTITION BY name ORDER BY time) AS next_value,
       value - LAG(value, 1) OVER (PARTITION BY name ORDER BY time) AS delta
  FROM sensor_tag
 WHERE name = 'TEMP-01'
   AND time >= TO_DATE('2024-01-01', 'YYYY-MM-DD')
 ORDER BY name, time;

prev_valueとnext_valueは参照範囲内の前・次の値です。先頭行のprev_valueと最終行のnext_valueは NULLです。WHEREで除外した過去の行は比較対象に含まれないため、先頭行の差も必要な場合は参照範囲を過去へ広げます。

集計結果の前の値との比較

タグ別の時間区間を先に集計し、集計値間の変化を比較できます。次の例は、時間単位のROLLUPを参照できる sensor_tagテーブルを前提とします。

SELECT name, bucket, avg_val,
       LAG(avg_val, 1) OVER (PARTITION BY name ORDER BY bucket) AS prev_avg
  FROM (
      SELECT name,
             rollup('hour', 1, time) AS bucket,
             AVG(value)              AS avg_val
        FROM sensor_tag
       WHERE time BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD')
                      AND TO_DATE('2024-07-01', 'YYYY-MM-DD')
       GROUP BY name, bucket
  ) t
 ORDER BY name, bucket;

このクエリは、区間別の平均と直前区間の平均を比較します。移動平均や累積合計を計算するクエリではありません。 データのない区間は自動補完しません。

性能上の注意

ウィンドウ計算にはグループ分割、ソート、前後の行値の保持が必要です。時間範囲とタグ条件で対象行を減らし、 長期傾向の比較は集計結果に適用すると処理行数を減らせます。

LAG/LEADはSELECTの結果式で使用します。WHERE、HAVING、GROUP BY、ORDER BY、JOINのON条件で 直接呼び出さないでください。計算値で絞り込むには、外側のSELECTからインラインビューの結果列を参照します。

関連ドキュメント

• ウィンドウ/系列関数 — ROWNUM()とSERIESNUM()
• PIVOT構文 — 行を列へ変換
• SERIES BY構文 — 連続区間の抽出