不規則なセンサー読み取りの時間加重平均を計算するクエリを構築せよ。それぞれの値を次のサンプルまでの有効な期間で重み付けし、手続き的なロジックを使わずにANSI SQLのウィンドウ関数のみを使用せよ。

質問への回答

質問の歴史

時間加重平均は、産業用IoTや金融時系列分析において重要な指標として誕生しました。単純な算術平均は、センサー値が次の測定まで持続するため、現実を歪めます。ANSI SQL:2003標準以前は、これらの平均を計算するために手続き的なカーソルや高コストな自己結合が必要でした。これらの方法はO(n²)の時間計算量で動作しました。

LEADおよびLAGウィンドウ関数の導入により、この分野は革命を遂げました。これにより、O(n)の時間で実行される単一パスの集合ベースの区間計算が可能になりました。これにより、数十億行におけるリアルタイム解析がデータベース層内で実現可能になります。

問題

readingsというテーブルには、device_id、ts（タイムスタンプ）、およびvalueの列があります。目的は加重平均を計算することです。各行は次の読み取りまでの時間デルタに比例して貢献しなければなりません。数学的には、これは$\frac{\sum (value_i \times (ts_{i+1} - ts_i))}{\sum (ts_{i+1} - ts_i)}$として表されます。

最後の行は境界条件を表しています。その後のタイムスタンプがないため、その間隔はゼロとして定義するか、現在の時刻まで外挿するか、または知られた終了時刻で制限しなければなりません。解決策は、カーソルやユーザー定義関数、自己結合を避けて純粋に宣言的である必要があります。

解決策

LEADウィンドウ関数を使用して、次のタイムスタンプを現在の行に投影します。重みを導き出すためにエポックの差を計算します。次に、標準的な加重平均の公式を適用します。

WITH weighted AS (
  SELECT
    device_id,
    value,
    ts,
    COALESCE(
      EXTRACT(EPOCH FROM (LEAD(ts) OVER (PARTITION BY device_id ORDER BY ts) - ts)),
      0
    ) AS duration_seconds
  FROM readings
)
SELECT
  device_id,
  SUM(value * duration_seconds) / NULLIF(SUM(duration_seconds), 0) AS time_weighted_avg
FROM weighted
GROUP BY device_id;

このアプローチでは、PARTITION BYを使用してウィンドウがデバイスごとにリセットされることを保証します。これにより、異なるセンサーのタイムスタンプが干渉するのを防ぎます。COALESCEは終端行を扱い、ゼロの重みを割り当てて、実質的にそれを分母から除外します。

生活からの状況

製薬製造ラインは200のバイオリアクターを監視しています。それぞれが不規則な間隔で温度データを排出します—加熱フェーズ中は毎秒10回、アイドルホールド中は30分ごとにです。品質チームはコンプライアンスを確保するために、日々の時間加重平均を要求しました。単純な平均では、急速な加熱サンプルの影響が過大評価され、安定したホールドの影響が過小評価されるため、危険な温度の逸脱を見逃す可能性があります。

提案された解決策の一つは、すべてのデータをPythonのpandas DataFrameに抽出することでした。エンジニアはタイムスタンプのdiff()を計算し、加重平均を算出します。このアプローチは柔軟ですが、ギガバイトのデータをネットワーク経由で転送する必要がありました。また、90日間にわたる高頻度データの四半期末レポートを処理する際に解析ワークステーションがクラッシュしました。

別の選択肢は、各デバイスについて現在の行よりも大きいMIN(ts)を見つけるために相関サブクエリを使用しました。これは1,000行のテストセットで正しく実行されました。しかし、二次的な劣化を示し、単一のリアクターの完全な履歴を取得するのに45分かかりました。

チームはANSI SQLウィンドウ関数のアプローチを選択しました。計算をPostgreSQLクラスター内に保持することで、クエリは並列シーケンシャルスキャンを活用し、ネットワークオーバーヘッドを回避しました。最終的な実装は、すべてのリアクターにわたる5,000万行を12秒未満で処理しました。これにより、オペレーターは数分以内に熱ドリフトを見つけることができるリアルタイムのダッシュボード更新が可能になりました。

候補者が見落としがちな点

時間のない各パーティションの最終観測を、どのように処理しますか？次のタイムスタンプが存在しない場合、間隔の重みを定義できますか？

候補者はしばしば境界条件を省略します。これにより、終端行の間隔がNULLとして評価され、SQL集計はこれを無視します。結果として、最後の読み取りの寄与が除外され、平均が歪む可能性があります。正しいアプローチは、COALESCEを使用してゼロまたはEXTRACT(EPOCH FROM (boundary_time - ts))に置き換え、平均がCURRENT_TIMESTAMPのような既知の終了時間まで延長される必要がある場合です。

なぜSUM(value * duration) / SUM(duration)という式が数学的に時間加重平均を表し、AVG(value)を使った場合には何が起こるのか？

これは加重算術平均を計算し、期間が重み$w_i$として機能します。候補者はしばしばこれを幾何平均と混同するか、AVG(value * duration)を使用しようとしますが、これは正規化なしに積の合計を生成します。AVG(value)を使用すると、各行が均等に扱われ、均一な時間ステップが前提となり、長く持続する値の影響がより大きいという要件に違反します。

時間のギャップや同じパーティション内の重複タイムスタンプがLEAD関数の動作にどのように影響し、PARTITION BY device_idがなぜ重要なのか？

候補者は、LEADがウィンドウ指定内の行の物理的順序に基づいて動作することを忘れがちです。PARTITION BY device_idがないと、関数は異なるセンサーの間隔を計算し、意味のない負のまたは巨大な期間を生成します。さらに、重複したタイムスタンプが存在する場合、LEADは次の異なる行を返し、ゼロ秒の間隔を生じる可能性があります。候補者は、最初にDISTINCTまたは**ROW_NUMBER()**フィルタリングを使用して重複を削除し、ゼロ除算エラーを回避するかどうかを決定しなければなりません。