質問への回答

歴史的にデータエンジニアは、センサーの欠測読み取りを処理する際に、レコードを破棄するか、定数補完を使用するという課題に直面しましたが、どちらも分析結果を歪めるものでした。線形補間は、2つの既知のデータポイントの間の直線的な軌道を前提とし、連続した物理プロセスに対して統計的に好ましい方法として浮上しました。

この問題は、周囲の有効な測定値からの時間的距離に基づいて比例値を計算することによって、順序付けられたシーケンス内のNULL値を再構築することを必要とします。この作業は、手続きループやテーブルの自己結合を使用せずに、純粋なセットベースの論理を維持しながら実行しなければなりません。

解決策は、条件付きウィンドウ関数を利用して境界のアンカーを確立します。MAXを使用して、現在のギャップの前にある最も最近の非NULL値とタイムスタンプを取得します。一方、MINを使用して、ギャップの後にある次の非NULL値とタイムスタンプを取得します。その後、補間公式はこれらの境界の間の経過時間の比率に基づいて加重平均を計算します。

WITH boundaries AS (
  SELECT 
    device_id,
    reading_time,
    reading,
    MAX(CASE WHEN reading IS NOT NULL THEN reading_time END) 
      OVER (PARTITION BY device_id ORDER BY reading_time 
            ROWS UNBOUNDED PRECEDING) as prev_time,
    MAX(CASE WHEN reading IS NOT NULL THEN reading END) 
      OVER (PARTITION BY device_id ORDER BY reading_time 
            ROWS UNBOUNDED PRECEDING) as prev_val,
    MIN(CASE WHEN reading IS NOT NULL THEN reading_time END) 
      OVER (PARTITION BY device_id ORDER BY reading_time 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as next_time,
    MIN(CASE WHEN reading IS NOT NULL THEN reading END) 
      OVER (PARTITION BY device_id ORDER BY reading_time 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as next_val
  FROM sensor_readings
)
SELECT 
  device_id,
  reading_time,
  COALESCE(
    reading,
    prev_val + (next_val - prev_val) * 
      (EXTRACT(EPOCH FROM (reading_time - prev_time)) / 
       NULLIF(EXTRACT(EPOCH FROM (next_time - prev_time)), 0))
  ) as interpolated_reading
FROM boundaries;

実生活からの状況

製薬会社は、IoT温度センサーを使用してワクチンの冷却ストレージユニットを監視し、毎分報告を行っていました。ピーク稼働時のネットワークの混雑により、3~5分にわたって断続的にNULL読み取りが発生しました。規制のFDAのコンプライアンスにより、ギャップのない完全な温度履歴が必要とされましたが、単純な削除は継続的な監視プロトコルに違反していました。

NULLレコードの削除が最初に検討されました。このアプローチは、観測された値のみを保存することによって事実の整合性を維持しました。しかし、これは、クリティカル温度閾値を超える合計時間を計算するなどの期間ベースの集計を破壊する時間的な不連続性を生じさせ、データセットを監査基準に準拠させませんでした。

**最後の観察値を前に持ち越す（LOCF）**が、LAST_VALUEウィンドウ関数を使用した計算上簡単な代替手段として評価されました。パフォーマンスは良好でしたが、この方法は停止中の温度が一定であると仮定しており、これが冷却システムにおける熱的漂流の熱力学原理に違反し、分散と傾向分析において重要なバイアスを導入しました。

線形補間が最終的に選択されました。このアプローチにより、測定値間の現実的な熱慣性がモデル化され、一次傾向が保持され、ネットワークのブリップに典型的な短期間のギャップに対して数学的に防御可能な推定値が提供されました。単調線形変化を仮定しましたが、これはワクチン保管におけるコンプレッサーサイクル間の短い安定した期間において正確であることが証明されました。

この実装により、Q3のコンプライアンス監査中に99.2％の欠落値が再構築されました。補間されたデータセットは、物理的に記録された値から0.1℃以内の温度分散を維持し、FDAの要件を満たし、高価なハードウェアのアップグレードや冗長センサーネットワークを必要としませんでした。

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

補間のために境界値が1つだけ存在するパーティションの先頭または末尾で発生するNULL値はどのように処理しますか？

候補者はしばしば、prev_valまたはnext_valが未定義である場合に、補間式を適用することを考慮せずにギャップの計算を行い、前または後のギャップに対してNULLを返すことがあります。その解決策は、ロジックをCASE式にラップすることです：prev_time IS NULLの場合はnext_valを使用（後方外挿）、next_time IS NULLの場合はprev_valを使用（前方外挿またはLOCF）；さもなければ完全な補間式を適用します。これにより、クエリは内部範囲だけでなく、全データセットの結果を返すことが保証されます。

式(next_val - prev_val) / (next_time - prev_time)が厳密なANSI SQLで失敗する可能性がある理由と、数値的に正確さを確保する修正は何ですか？

ANSI SQLの日時計算はINTERVAL型を返し、数値スカラーではありません。間隔同士の算術除算や間隔と小数の混合を試みると、型不一致エラーが発生します。さらに、整数の除算は小数秒を切り捨て、精度を損ないます。候補者は、EXTRACT(EPOCH FROM (next_time - prev_time))を使用して数値の表現を取得し、同時にNULLIF(..., 0)で分母をラップして、バッチ挿入により連続する有効な読み取りが同一のタイムスタンプを持つ場合に発生するゼロ除算エラーを防ぐ必要があります。

このウィンドウ関数アプローチと相関サブクエリを使用した自己結合メソッドとの間の根本的な計算複雑性の違いは何ですか？このことが高頻度のストリーミングデータセットにとって重要なのはなぜですか？

ウィンドウ関数アプローチは、ソート操作によって支配されるO(n log n)時間で実行され、パーティションサイズに対して線形メモリ使用を維持します。相関サブクエリを使用した自己結合アプローチ（例：MIN(time) WHERE time > currentを使って次の非NULLを見つける）は、各行がその隣接行を問い合わせるため、O(n²)に悪化します。高頻度のテレメトリが数百万行を生成する場合、ウィンドウ関数メソッドは順序付けされたインデックススキャンと単独のパス実行を利用し、自己結合はデカルト積とメモリスピルを引き起こします。候補者は、UNBOUNDED FOLLOWINGフレームが大きなパーティションに対してディスクスプーリングを必要とする場合があることを見落としがちですが、それでも二次的な複雑さより漸近的に優れています。