計算は最小二乗法に基づいています。傾き（β）は、独立変数Xと従属変数Yの共分散をXの分散で割ったものとして定義されます。切片（α）は、Yの平均から傾きとXの平均の積を引いたものとして導出されます。ANSI SQLでは、これらの代数定義をSUM、AVG、およびCOUNT集約関数を使用して実装します。通常はGROUP BY句内、またはOVER句を持つウィンドウ関数として使用します。クエリは、最終的な係数を解決するために、クロスプロダクトの合計（Σ(X - X̄)(Y - Ȳ)）とXの平方偏差の合計（Σ(X - X̄)²）を明示的に計算する必要があります。
小売分析チームは、動的価格戦略を最適化するために各製品カテゴリの価格弾力性を決定する必要がありました。彼らはunit_priceとquantity_soldを含む取引テーブルを持っていて、価格が各category_idに対して販売数量にどのように変化するかを定量化するトレンドラインを必要としていました。
提案された解決策の1つは、日次の集約データを外部のPythonスクリプトにエクスポートし、scikit-learnを使用して回帰モデルをフィットさせることでした。このアプローチは実装が簡便で、豊富な統計診断にアクセスできる利点がありました。しかし、これによりデータのレイテンシが大きくなり、敏感な販売データの外部コピーを作成することで厳格なデータガバナンスポリシーに違反し、自動化された価格アルゴリズムに必要なリアルタイムダッシュボードの更新を妨げることになりました。
別のオプションは、データベースエンジン内でユーザー定義集約関数（UDAF）を作成することであり、REGRESS_SLOPE(price, quantity)のような構文を可能にします。これはエレガントで再利用可能でしたが、異なるデータベースシステム間のポータビリティを犠牲にし、展開するのに高い管理者権限を必要としたため、ロックダウンされたマルチテナントのクラウドデータウェアハウス環境には適していませんでした。
選ばれた解決策は、標準集約を使用してANSI SQL内に代数式を直接実装しました。チームはcategory_idでパーティション化されたSUMとAVGウィンドウ関数を活用して、データの1パスで必要な共分散と分散項を計算しました。このアプローチは、計算をデータと共に配置し、抽出・変換・読み込み（ETL）の遅延を排除し、プロプライエタリ拡張なしで完全にポータブルなANSI SQL標準に厳格に従いました。その結果、新しい取引が到着するたびに自動的に更新されるサブ秒のレイテンシの価格弾力性ダッシュボードが実現され、自動化された価格アルゴリズムがリアルタイムでマージンを調整できるようになりました。
XまたはYでNULL値をどのように処理しますか？グループ全体の計算が無効にならないように。
候補者は、ANSI SQLの集約関数はNULLを無視するが、NULLを含む算術演算はNULLを返すことを忘れがちです。共分散項を計算する際にSUM((x - avg_x) * (y - avg_y))を使用する場合、特定の行でxまたはyがNULLであれば、その積はNULLになり、その行は合計から除外されます。これは効果的にペアワイズ削除を行い、通常は望ましいですが、分散計算の自由度のために使用されるCOUNTが非NULLペアのカウントを反映していることを確認する必要があります。解決策は、サブクエリでWHERE x IS NOT NULL AND y IS NOT NULLでフィルタリングすること、またはフィルタリング後に等しいCOUNT(x)（これはCOUNT(y)と同じ）を使用することです。すべての集約項の一貫した分母を確保します。
母集団とサンプルの間で回帰を計算することの違いは何ですか？SQLクエリにどのように影響しますか？
多くの候補者は、共分散の公式と一貫していないサンプル分散の公式（n - 1で割る）を適用します。ANSI SQLの組み込み関数のようにVAR_POPやVAR_SAMPがこの違いを処理しますが、もし手動で分散をSUM(POWER(x - avg_x, 2)) / COUNT(*)として計算する場合は、分母を意識的に選ぶ必要があります。傾きの計算において、もし分母でXの分散を手動で計算する場合、その共分散計算の分母とも一致させなければなりません。両者を混同すると（例えば、サンプル共分散を母集団分散で割るなど）、偏った傾きが得られます。修正されたアプローチは、統計的フレーム（母集団対サンプル）を決定し、共分散の分子と分散の分母で同じ分母の論理を適用することです（nまたはn-1のいずれか）にします。
同じクエリ内で決定係数（R²）を計算して適合度を測定するにはどうしますか？
候補者は検証指標をしばしば省略します。R²は1 - (SS_res / SS_tot)として計算され、ここでSS_resは平方残差の合計（Σ(y - ŷ)²）、SS_totは平方の合計（Σ(y - ȳ)²）です。ŷ（予測y）を計算するには、以前のステップで計算された傾きと切片が必要です。ANSI SQLでは、スタックされた共通テーブル式（CTEs）を使用してこれを計算できます。まず平均値を計算し、次に2番目のCTEで傾きと切片を計算し、最後に外部クエリで実際の値と予測値の間の平方の差を計算します。一般的なエラーは、計算された傾きをそれが計算された同じ集約レベル内で参照しようとすることで、これは論理的な処理順序に違反します。解決策は、計算された係数を最終的なR²の集約で定数として再利用できるように、ロジックを順次のCTEsに分離することです。