質問に対する回答

質問の背景。 累積的一意数の必要性は、時間の経過に伴う累積的な一意の顧客獲得や一意のSKUの導入などの指標を追跡する分析作業から生まれました。ANSI SQL:2003ウィンドウ関数の拡張の前は、アナリストは自己結合や相関サブクエリに依存しており、現代のデータ量には不適切な二次時間計算を引き起こしていました。ウィンドウ関数の標準化は、手続きループなしで累積的なカーディナリティを維持するための線形時間、セットベースのメカニズムを提供しました。

問題。 ANSI SQLは、ウィンドウ集計関数内でのDISTINCTキーワードを明示的に禁止します（例： COUNT(DISTINCT col) OVER (...)）。この制限により、累積的またはスライディングフレーム内での一意の値の直接計算が妨げられます。主な課題は、各エンティティのソート順内での最初の出現を特定し、これらのバイナリフラグ（最初の出現 = 1、そうでない場合 = 0）を徐々に合計することにあります。

解決策。 標準的なアプローチは、最初の出現をフラグ付けするために**ROW_NUMBER()を組み合わせ、条件付きSUM()**ウィンドウ関数を使用します。**ROW_NUMBER()**をエンティティ識別子でパーティション分けすることで、時系列の最初の出現には値1が与えられ、以降の出現には増分の整数が与えられます。外部クエリは、行番号が1に等しい場合にのみ1を生成するケース式を合計します。

SELECT 
  event_date,
  region_id,
  user_id,
  SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) 
    OVER (PARTITION BY region_id 
          ORDER BY event_date 
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
    AS cumulative_unique_users
FROM (
  SELECT 
    event_date,
    region_id,
    user_id,
    ROW_NUMBER() OVER (
      PARTITION BY region_id, user_id 
      ORDER BY event_date, event_id  -- イベントIDをタイブレーカーとして使用
    ) AS rn
  FROM user_activity
) flagged;

生活からの状況

問題の説明。 フィンテックのスタートアップは、会計年度を通じて各販売地域ごとに搭載される累積一意の商人数を追跡することで規制遵守を監視する必要がありました。彼らのmerchant_signupsテーブルには、region_code、merchant_id、signup_timestampを持つ1億2000万行のデータが含まれていました。既存のPythonバッチジョブは、これらの指標を毎晩計算するのに35分を要し、報告の遅延や古いダッシュボードデータを引き起こしました。要求は、クラウドデータウェアハウス間での移植性のために、厳格なANSI SQLでリアルタイムの累積数を生成することでした。

解決策A: 自己結合アプローチ。 この方法は、テーブルを自己結合し、一致する地域と以前のタイムスタンプでの商人の数を数えます。利点: ウィンドウ関数のサポートが不要で、レガシーSQL-92エンジンで機能します。欠点: アルゴリズムはO(n²)の複雑性を示し、数百万行の場合、テラバイトの一時ストレージを消費する中間的なデカルト積を生成し、数時間以内に完了しないため、運用上の実現可能性がありません。

解決策B: 相関スカラサブクエリ。 ここでは、SELECT句がサブクエリを埋め込んでいます: (SELECT COUNT(DISTINCT merchant_id) FROM merchant_signups m2 WHERE m2.region_code = m1.region_code AND m2.signup_timestamp <= m1.signup_timestamp)。利点: 宣言的であり、論理的に読みやすいです。欠点: サブクエリは各行ごとに（1億2000万回）実行され、述語プッシュダウンが妨げられ、大規模なランダムI/Oを引き起こします。データベースの最適化プログラムは、異なる時間範囲にわたる一意の集計をデコリレートできず、推定実行時間は90分を超えます。

解決策C: ANSI SQLウィンドウ関数技術。 上記のコード例で示されるように、最初の出現を特定するために**ROW_NUMBER()を使用し、その後に実行中のSUM()**を使用します。利点: これは、一度のテーブルスキャンでソートを行い、O(n log n)の複雑性と制限されたメモリ使用を実現します。欠点: 時間的な ties を慎重に処理する必要があります。同じタイムスタンプを持つ2つのサインアップがある場合、非決定的な順序付けにより、重複カウントされる可能性があります。これを回避するためには、ORDER BY句に一意のタイブレーカー（event_idなど）を追加する必要があります。

選ばれた解決策と結果。 解決策Cが実装されました。最初の出現を決定するためにevent_idをORDER BYに含めることで、クエリは既存のクラスターで4分で実行され、9倍の改善が達成されました。この結果により、リアルタイムのコンプライアンスダッシュボードが可能となり、リスク担当者はETLの遅延なしにオンボーディングの多様性を監視できるようになり、クエリはPostgreSQL、Snowflake、およびBigQueryに対して変更なしに完全に移植可能でした。

候補者が見逃すことが多い点

なぜ COUNT(DISTINCT column) OVER (ORDER BY ...) は厳密なANSI SQLで構文エラーを引き起こしますか？

SQL標準は、ウィンドウ集計関数内でのDISTINCTキーワードを明示的に禁止します。例えば、COUNT、SUM、またはAVGのようなものです。特定のベンダー（例：PostgreSQL 16+、Oracle）がこれをプロプライエタリ拡張として提供する一方、ANSI SQL:2011および以前のバージョンは、ウィンドウ集計が定義されたフレーム内のすべての行で操作することを制限しています。この制限は、ストリーミング評価中に各可能なウィンドウフレームに対して一意セットハッシュテーブルを維持することが標準文法によって要求されていないため存在します。候補者は、DISTINCTがウィンドウ付きの一意のカウントでなく、OVER句のない標準集計関数内でのみ許可されていることを認識する必要があります。

タイムスタンプが重複している場合、エンティティの「最初の」出現をどのように処理しますか？

**ROW_NUMBER()**は、ORDER BY句が完全な順序付けを指定しない限り、 ties 間で任意の値を割り当てます。もし商人が同じタイムスタンプを持つ2つのエントリを持っている場合、オーダリングが非決定的なら、両方の行がrn = 1を受け取る可能性があり、累積的なカウントが誤って2回増加することになります。この解決策は、一意の主キーまたは自動インクリメントIDをORDER BY句に追加することです: ORDER BY signup_timestamp, merchant_signup_id。これにより、早く割り当てられたIDが最初の出現として考慮され、実行中の一意のカウントの数学的整合性が保たれます。

この技術を、非有界の前のウィンドウではなく、固定行数のウィンドウ（例：最後の100トランザクション）にわたる移動一意のカウントに適応できますか？

いいえ、純粋なANSI SQLでは効率的にできません。非有界の前のメソッドは成功しますが、一意性は単調であり、一度エンティティが出現すると、そのまま“一意にカウントされる”のです。スライディングウィンドウ（例：ROWS BETWEEN 100 PRECEDING AND CURRENT ROW）では、ウィンドウから出ていくエンティティがカウントを減少させる必要があり、その出ていく行が現在のフレーム内でそのエンティティの唯一のインスタンスであるかを把握する必要があります。ANSI SQLには、離脱を効率的に追跡するための配列集約や集合差演算子が欠如しているため、これを実装するには再帰的CTEが必要であり（このシナリオではO(n²)に低下します）、またはARRAY_AGGのようなプロプライエタリ拡張と集合演算の組み合わせが必要となり、どちらも厳格なANSI準拠に違反します。