So-net無料ブログ作成

SQL Server で線形回帰するためのSQL コード生打ち [BI]

メモです。

PostgreSQLあたりだとLinear regression (線形回帰) の関数があるのですが、SQL Serverにはそういう関数がありません。SSASだとできるらしいのですがSSRSで使いたいだけなのでSSASを設定するのもめんどくさい。ということで大学生の実験以来、線形回帰をSQLで手計算しました(-"-;;。

test というテーブルでx,yというコラム名があったときのa,b (slope, intercept)は以下になります。

SELECT
bunshi_a/bunbo_a AS a
,avg_y - bunshi_a/bunbo_a*avg_x AS b
FROM
(SELECT
SUM((x - avg_x)*(y - avg_y)) AS bunshi_a
,SUM((x - avg_x)*(x - avg_x)) AS bunbo_a
,avg_x ,avg_y
FROM
(SELECT
x,y
,(SELECT Avg(CAST(x AS REAL)) from test) AS avg_x
,(SELECT Avg(CAST(y AS REAL)) from test) AS avg_y
FROM test
) src1 /* シグマ内の必要な要素を一列に準備 */
GROUP BY avg_x,avg_y
) src2 /* 最後の計算の一歩手前 */

実際にはtestテーブルのx,yはエラー値を除去したビューかサブクエリにすると良いでしょう。
出てきたa,bから、またtestテーブルのx,yの横に回帰した値とか追加しようとすると6重サブクエリとかになりますな…。数百行なら大丈夫だろうけど。

Talend Open Studio (ETLツール) でデータ統合(7)~SCD Type2 の実装 [BI]

商品カテゴリをSCD Type2で実装したディメンジョンテーブルを使って集計する必要が出てきました。さて、集計の仕方ですが単純に時間スライスでJOINしてもいいのですが、商品カテゴリの世代毎の売り上げを比較したいという要件や、そもそもSCD Type2のテーブルは日付で条件をかけるというひと手間あるので、スタースキーマ前提のBIに突っ込むには適していないので、工夫が必要です。

そこで悩んだ末に「ファクトテーブルにカテゴリをつっこむ」という解決法をとることにしました。まあもともとちゃんとした階層構造ではないし商品マスタに持たせられないので、美しくは無いがパフォーマンスを考えると現実的な解かなとは思っています。

さて、上記のような背景をもとに以下のようにTalendで実装しました。

○テーブル
・ソースとなるファクトテーブル
 商品コード
 日付
・カテゴリマスタ
 商品コード
 カテゴリコード
 販売開始日
 販売終了日

○手順1
上記2つのテーブルをLEFT OUTER JOINします。カテゴリのカバレッジが100%ではない場合はCASE句などを使用して、カテゴリコード、販売開始日、販売終了日からNullを撲滅してください。

○手順2
上記データをtMapで処理します。tMapでポイントになるのは入力行を処理する際の条件式を追加するところです。出力項目のレンチのアイコンをクリックすると条件式が入力できます。
無題.png
ここでは以下のような式を入れました(項目は雰囲気で察してください)。

TalendDate.compareDate(row1.date, row1.startdate,"yyyy-MM-dd") >= 0 && TalendDate.compareDate(row1.date, row1.enddate,"yyyy-MM-dd") <= 0

要は販売の日付が販売開始日・終了日の間の場合は、、、という条件です。

○手順3
上記出力をDB側で受け取ります。

まあここまでやって思ったのは、SCD Type2 って大変ですね(泣)。業務側からは非常に要望の強い部分ではあるので、MDM含めてちゃんとできるとデータ分析のレベルを上げられる要素ではあります。
タグ:RDBMS TOS Talend SCD
メッセージを送る

この広告は前回の更新から一定期間経過したブログに表示されています。更新すると自動で解除されます。