一次元線形回帰、SQL、機械学習

みなさんこんにちは。 これはHabrに関する私の最初の記事です。批判とコメントを歓迎します。

この記事は、SQLに似た言語で特殊なタイプの予測子を構築するためのシンプルだが便利な方法に専念しています。 これらの予測子は、機械学習の問題を解決するために使用できるデータの線形傾向を表します。 アイデアは、トランザクションデータから線形トレンドを迅速かつ効率的に計算することです。

教師に教えることの課題-回帰または分類の課題を検討します。 このような問題の解決策は多くの段階に分けることができ、新しい予測変数を作成する段階に興味があります。 元の生データには、とりわけトランザクションデータがあると想定します。 詳細は重要ではないので、アクティビティを特徴付けるアクション（Amountフィールド）と予測したいアクション（ターゲットフィールド）をやがて実行するユーザーがいると想定します。

ユーザーID	ティミッド	金額	対象
101	1	20.0	0
101	2	30.0	1
101	3	50.0	0
102	2	80.0	0
102	4	50.0	0
102	5	50.0	0
102	7	30.0	1

時間は整数でインデックス付けされます。 このようなインデックスを作成するときは、インデックスを作成することが重要です $$$n$ そして $$$n + 1$ 本当に互いに続く一時的なユニットに対応しました（これから、これが重要である理由が明らかになります）。 たとえば、日を索引付けする必要がある場合、毎日、考慮されるすべての最初の日からの日数に等しい索引を関連付けることができます。 タブレットの最初の日が2017年1月1日である場合、1月1日はインデックス1、1月28日-インデックス27、および2月4日-インデックス34に対応します。量ゼロ。 たとえば、過去3か月間の特定の製品の消費に関するデータに関心がある場合があります。各ユーザーについて、テーブルには常に3つのエントリがあります。

この例の[ターゲット]フィールドには、対応する瞬間に発生したか発生しなかったバイナリイベントに関する情報が格納されます。

実際には、このような機会があれば、モデリングから予測子の作成を常に分離することに注意することが重要です。 通常、R、Python、または他の場所で実行できるほとんどの変換とトリックは、SQLでも実行できます。 そのため、可能な限り、データセットとすべての予測変数をSQLで常に作成します。 利点は、第1に、機械学習の各段階が互いに分離され、第2に、すべてのデータ操作が即座にデータベース内でのみ実行されることです。 さらに、SQLを使用すると、予測子変換がさらに簡単になる場合があります。

そのため、トランザクションデータを含むテーブルがあります：ユーザー、整数時点、およびこれらのユーザーのアクションの定量的説明。 私たちの目標は、ユーザーごとにこのデータの線形トレンドを見つけることです。 予測子は1つしかないため、 単純な線形回帰を使用します。 トレンドに興味があるので、勾配係数を計算します：

$$$\ alpha = \ frac {\ overline {xy}-\ bar {x} \ bar {y}} {\ overline {x ^ 2}-\ bar {x} ^ 2}$

式から明らかなように、勾配係数は平均とその積のみに依存します。 したがって、このような操作のために完全に投獄されるため、SQLで「オンザフライ」で計算することが可能になります。 また、この係数の変化を計算できることも明らかです。たとえば、5つの時間値を使用してトレンドが計算されることを修正し、5つの連続するレコードの各セットについてトレンドを計算します。 SQLに精通している人は、これを行う方法をすでに理解しています 。 ウィンドウ関数を使用する必要があります 。 ウィンドウ関数を使用すると、移動平均を計算できます。これはまさに必要なものです。 目標は、AmountフィールドとTimeIDフィールドの両方の移動平均を計算することです。 最初は従属変数の役割を果たし、2番目は独立変数の役割を果たします。

SELECT UserID , TimeID, Amount, , AVG(Amount) OVER(PARTITION BY UserID ORDER BY TimeID ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS [y_bar] , AVG(1.0*TimeID) OVER(PARTITION BY UserID ORDER BY TimeID ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS [x_bar] , AVG(Amount*TimeID) OVER(PARTITION BY UserID ORDER BY TimeID ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS [xy_bar] , AVG(1.0*TimeID*TimeID) OVER(PARTITION BY UserID ORDER BY TimeID ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS [x2_bar] INTO #tmp_LinearTrendAmount FROM MyTransTable
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この選択を完了すると、勾配係数の計算に必要なすべてのコンポーネントを自由に使用できます。 別の選択を使用すると、それらを目的のインジケータに収集し、必要なチェックを行うことができます（たとえば、ユーザーが1つのレコードしかない場合に発生するゼロ除算など）。 この例では、勾配はこの1つ前の5つのレコードから計算されます（使用するコードでは... AND 1 PRECEDING）。 事実は、[ターゲット]フィールドはその行からの特定の時点を参照しているため、レコードを取得することは「将来を見据える」ことです。

したがって、ウィンドウ関数を使用すると、スロープ係数をすばやく取得できます。 重要なのは、テーブル内のすべてのレコードについてこれらの係数を取得することです。SQLはテーブル全体を調べて移動平均を計算し、新しい列に書き込むからです（各ユーザーの最初の4つのレコードの勾配値が不正確になるように調整）。 その結果、各レコードは個別の観測と見なされるため、テーブル全体でモデルをトレーニングできます。 特に他の線形回帰の実装と比較した場合、ウィンドウ関数は十分に高速です。

注釈1.なぜこれがすべて必要なのかという質問について。 教師による指導のタスクでは、トランザクションデータがある場合、通常、さまざまなタイプの予測子を使用します。予測子の値の値を説明する平均、中央値、およびモードです。 標準偏差と変動係数を使用して、変動性と予測値の傾向を説明します。 一般に、これは適切なアプローチです。これは、通常、各種類の少なくとも1つの予測子がモデルに分類されるためです。

備考2.これで、TimeID時間インデックスを正しく計算することが重要だった理由が明らかになりました。 これは独立変数であり、正しく計算する必要があるためです。

備考3.悪魔は細部に宿る。 たとえば、一定の数の時間値を使用する場合、トレンドは異なる時間間隔で計算できます。1人のユーザーでは5つの時間値で1週間、もう1つのユーザーでは1か月でカバーできます。 もちろん、ロジックでこれを使用できるようにする必要があります。 たとえば、ユーザーごとに過去3か月の消費量を保存すると、この問題はなくなります。

結論の代わりに。 アプリケーションは常に個別です。多くは、どのデータ、どのトランザクション、どの期間、ターゲット変数の計算方法に依存します。 より多くの点で、私の考えは多くの問題を解決するために適応できるアプローチについて話すことでした-1次元の線形トレンドは平均のみに依存し、ウィンドウ関数を使用してSQLを含めて簡単に計算できます。