質問への回答

重複を決定論的にタグ付けするために、CTE（共通テーブル式）とROW_NUMBER()ウィンドウ関数を組み合わせて使用します。ユニークな論理キー列（sensor_id、granularity）でデータセットをパーティション分割し、保持優先順位を反映したORDER BY句（signal_strength DESCおよびtimestamp ASC、最後に決定論を保証するためのPRIMARY KEY（例：log_id））を適用します。その後、外部クエリでは、割り当てられた行番号が1を超えるすべてのレコードを削除し、各グループごとに最高優先順位の行だけが残るようにします。

WITH RankedLogs AS (
  SELECT log_id,
         ROW_NUMBER() OVER (
           PARTITION BY sensor_id, granularity 
           ORDER BY signal_strength DESC, timestamp ASC, log_id ASC
         ) AS priority_rank
  FROM telemetry_logs
)
DELETE FROM telemetry_logs
WHERE log_id IN (
  SELECT log_id 
  FROM RankedLogs 
  WHERE priority_rank > 1
);

生活からの状況

産業用IoTプラットフォームは、製造ロボットから取得した高頻度の振動データをmachine_telemetryというテーブルに取り込みました。ネットワークパーティション中のMQTTブローカーの再試行により、テーブルの約40％が同じrobot_idとtime_bucketを共有する重複記録で構成されており、ペイロードチェックサムがわずかに異なっていました。報告ダッシュボードは稼働時間を二重にカウントしており、メンテナンススケジュールに歪みをもたらしていました。

ソリューション1：相関自己結合。 一つのアプローチは、robot_idとtime_bucketでテーブルを自己結合し、サロゲートキーがパートナーのキーよりも大きい行を削除することでした。この方法はウィンドウ関数を必要としませんでした。ただし、その時間計算量はO(N²)に近づき、3億行のデータセットで著しいパフォーマンス低下を引き起こし、複合キー内のNULL値を誤って処理しました。

ソリューション2：グループ化を伴うステージングテーブル。 エンジニアたちは、GROUP BYおよびMIN()集約を介して特定された生存するlog_idsのみを保持する一時テーブルを作成し、その後、元のテーブルをトランケートして再挿入することを検討しました。論理的には正しいですが、これはかなりの一時ストレージスペースを要し、制限された本番環境では利用できないDDL権限が必要であり、同時にデータが欠落しているように見える短いウィンドウが発生しました。

ソリューション3：ウィンドウ関数CTE。 チームは**ROW_NUMBER()**戦略を実装し、重複キー（robot_id、time_bucket）でパーティションを分割し、信号品質メトリックで並べました。このソリューションは、クリーンアップ中のデータの不整合を防ぐために、単一の原子的トランザクションとして実行されました。4分以内で全バックログを処理し、テーブルをオフラインにすることなくストレージコストを40％削減しました。

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

ビジネスロジックが一見して非ユニークなタイムスタンプによるソートのみを示唆している場合においても、なぜ真にユニークなプライマリキーが常にデデュプリケーションウィンドウ関数のORDER BY句の最終列として使用される必要があるのか？

ANSI SQLにおいては、すべての指定されたORDER BYキーに対して同一の値を持つ行の順序は非決定論的です。もし2つの重複記録が正確に同じtimestampとsignal_strengthを共有している場合、データベースエンジンはそれらを任意に配置する自由があります。その結果、削除ロジックを複数回実行すると、異なる行が保存のためにランダムに選択され、一貫性のない結果や重要なデータの喪失につながる可能性があります。PRIMARY KEYを追加することにより、完全な順序を保証し、副作用なく再現可能な削除を保証します。

ANSI SQLはPARTITION BY句内のNULL値を標準の等価述語とはどのように扱い、この違いがデデュプリケーションの精度を脅かすのか？

GROUP BYまたはPARTITION BY句内では、ANSI SQLはNULL値を区別できないものとして扱い、それらをまとめてグループ化します（実質的に、集約のためにNULLはNULLと等しい）。対照的に、WHERE句または結合述語（ON t1.x = t2.x）では、NULL = NULLの式はTRUEではなくUNKNOWNとして評価されます。したがって、自己結合を介してデデュプリケーションを行う場合、一致する列にNULL値がある行は決して重複として認識されず、不正に生き残ります。結合でNULLを正しく処理するには、IS NOT DISTINCT FROM構文を使用しなければなりません（ANSI SQL:1999）。

単一のトランザクションで何百万もの重複を削除する際、どのような特定の同時実行性およびリソースの危険が生産の安定性を脅かし、このリスクを軽減するためのANSI SQL技術は何か？

モノリシックなDELETE文は、影響を受けたすべての行にEXCLUSIVE LOCKSを取得し、潜在的にテーブルレベルのロックにエスカレートして、すべての同時挿入や読み取りをブロックする可能性があります。さらに、大規模なTRANSACTION LOGの増加を引き起こし、ディスクの枯渇や回復の失敗のリスクを高めます。これを軽減するためには、ANSI SQLに準拠しつつ、バッチで削除を処理する必要があります。これには、サブクエリ内でFETCH FIRST n ROWS ONLYにより特定された限定されたサブセットを反復的に削除したり、スクロール可能なカーソルを使用したりすることが含まれ、各小さなトランザクションを独立してコミットしてロックを解放し、ログセグメントを徐々に切り詰めます。