機械学習とBIツールでデータ可視化してみた＜実践編＞～MotionBoard実装～

「BIツールから機械学習を実行し、可視化する」
――社内に眠っているデータを活用し、過去の分析だけにとどまらず、未来を予測する。そんなケースが増えてきました。

機械学習では、裏で膨大な計算をさせるためにチューニングに奮闘したり、デー タベースから機械学習用基盤にデータを送る仕組みを構築したり… メインの機械学習をさせるまでには様々な作業が発生します。
そんな作業を一気に解決してくれるのが「Oracle Autonomous Data Warehouse」 です！

そこで本ブログでは連載形式で、データベースにOracle Cloud の「Autonomous Data Warehouse（以下、ADWと表記）」、BIツールに”ADW対応”、かつ、”豊富な表現力でデータドリブンを可能とする”「MotionBoard」（MB）を使用した、機械学習を検証していきます。

今回は＜実践編＞～OracleMachineLearning実装～で作成したADW の機械学習をMotionBoard で動かしていきます。

-- 設定名："DBMS_DATA_MINING"."SYS"."DROP_MODEL"（モデル削除）	
	
BEGIN 	
    DBMS_DATA_MINING.DROP_MODEL('${MODEL_NAME}');	
END;	

SELECT SETTING_NAME, SETTING_VALUE FROM USER_MINING_MODEL_SETTINGS
WHERE MODEL_NAME = '${MODEL_NAME}'

SELECT ${CHART_ROW} AS SELECTION, SURVIVED FROM TITANIC_TRAIN

-- 設定名：VIEWの再作成

DECLARE
    VSQL VARCHAR2(1000);
BEGIN
    VSQL:='CREATE OR REPLACE VIEW V_TITANIC_TRAIN AS SELECT PASSENGERID ,SURVIVED ${SELECT_PCLASS} ${SELECT_NAME} ${SELECT_SEX} ${SELECT_AGE} ${SELECT_SIBSP} ${SELECT_PARCH} ${SELECT_FARE} ${SELECT_EMBARKED} FROM TITANIC_TRAIN';
    EXECUTE IMMEDIATE VSQL;
    
    VSQL:='CREATE OR REPLACE VIEW V_TITANIC_TEST AS SELECT PASSENGERID ${SELECT_PCLASS} ${SELECT_NAME} ${SELECT_SEX} ${SELECT_AGE} ${SELECT_SIBSP} ${SELECT_PARCH} ${SELECT_FARE} ${SELECT_EMBARKED} FROM TITANIC_TEST';
    EXECUTE IMMEDIATE VSQL;
END;

-- 設定名："DBMS_DATA_MINING"."SYS"."DROP_MODEL"（モデルの削除）

CALL DBMS_DATA_MINING.DROP_MODEL('${MODEL_NAME}')

-- 設定名："DBMS_DATA_MINING"."SYS"."CREATE_MODEL"（モデルの作成）

{CALL DBMS_DATA_MINING.CREATE_MODEL(
   MODEL_NAME          => '${MODEL_NAME}',
   MINING_FUNCTION     => DBMS_DATA_MINING.CLASSIFICATION,
   DATA_TABLE_NAME     => 'V_TITANIC_TRAIN',
   TARGET_COLUMN_NAME  => 'SURVIVED',
   CASE_ID_COLUMN_NAME => 'PASSENGERID',
   SETTINGS_TABLE_NAME => '${SETTINGS_TABLE_NAME}')
  }

・SETTINGS_TABLE

コンボボックス「ディシジョンツリー」「ベイズ」「ニューラルネットワーク」「ランダムフォレスト」それぞれに対応するテーブルを用意し、テーブルの情報を全行表示を行います。

ディシジョンツリー用：SELECT * FROM SETTING_DT

ベイズ用：SELECT * FROM SETTING_B

ニューラルネットワーク用：SELECT * FROM SETTING_NN

ランダムフォレスト：SELECT * FROM SETTING_RF

「モデルの設定情報」に表示させているSQL（カスタムビュー）は下記です。

SELECT SETTING_NAME,SETTING_VALUE FROM USER_MINING_MODEL_SETTINGS
WHERE MODEL_NAME = '${MODEL_NAME}'

④モデルにより作成されたビューの一覧

モデルを作成したときに Oracle が自動で作成するビューの一覧を表示します。

・「モデルにより作成されたビューの一覧」に表示させているSQL（カスタムビュー）

SELECT * FROM USER_MINING_MODEL_VIEWS WHERE MODEL_NAME = '${MODEL_NAME}'

VIEW_NAME カラムにある VIEW の中身は下記のような内容です。
ディシジョンツリーのノードの中身などが確認できます。

上記の情報を使って、ディシジョンツリーをMotionBoardで可視化することも可能です。

⑤モデルに登録された属性一覧

モデルに寄与している説明変数と目的変数の一覧を表示しています。

「EMBARKED」はモデル作成時に画面でチェックをつけましたが、ここに表示されていないので予測に使用しないと判断されたようです。
確かに④で確認した「DM$VPTITANIC_MODEL」や「DM$VITITANIC_MODEL」にも出てきていません。

・「モデルに登録された属性一覧」に表示させているSQL（カスタムビュー）

SELECT * FROM USER_MINING_MODEL_ATTRIBUTES WHERE MODEL_NAME = '${MODEL_NAME}'

⑥訓練データとの生存判定一致

訓練データと「SURVIVED」と機械学習で予測した「SURVIVED」の一致比率を表示しています。

・「訓練データとの生存判定一致」に表示させているSQL（カスタムビュー）

SELECT
    COUNT(*),
    CASE
        WHEN SURVIVED = PREDICTION THEN '一致'
        ELSE '不一致'
    END AS MATCH
FROM
    (
        SELECT
            TRAIN.*,
            PREDICTION(${MODEL_NAME} USING TRAIN.*) AS PREDICTION,
            PREDICTION_PROBABILITY(${MODEL_NAME} USING TRAIN.*) AS PREDICTION_PROBABILITY
        FROM
            V_TITANIC_TRAIN TRAIN
    ) SUB
GROUP BY
    CASE
        WHEN SURVIVED = PREDICTION THEN '一致'
        ELSE '不一致'
    END

【タブ３】訓練データとの照合

続いて、訓練データと予測した値を分析する画面です。

①訓練データとの生存判定一致率
　こちらは前画面に表示した棒グラフを円グラフにしたものです。

②PREDICTION_PROBABOLITY（予測に対する確立）
　③の明細の予測に対する確率ごとのデータ分布を棒グラフで表したものです。

③訓練データと予測値・予測に対する確率・詳細
　訓練データに下記のカラムを追加し、表示しています。

・上記明細に表示させているSQL（カスタムビュー）

SELECT
    SUB.PASSENGERID,
    SUB.SURVIVED,
    SUB.PCLASS,
    SUB.NAME,
    SUB.SEX,
    SUB.AGE,
    SUB.SIBSP,
    SUB.PARCH,
    SUB.TICKET,
    SUB.FARE,
    SUB.CABIN,
    SUB.EMBARKED,
    SUB.PREDICTION,
    SUB.PREDICTION_PROBABILITY,
    CASE
        WHEN SURVIVED = PREDICTION THEN '一致'
        ELSE '不一致'
    END AS MATCH,
    RTRIM(TRIM(SUBSTR(OUTPRED."Attribute1",17,100)),'rank="1"/>') FIRST_ATTRIBUTE,
    RTRIM(TRIM(SUBSTR(OUTPRED."Attribute2",17,100)),'rank="2"/>') SECOND_ATTRIBUTE,
    RTRIM(TRIM(SUBSTR(OUTPRED."Attribute3",17,100)),'rank="3"/>') THIRD_ATTRIBUTE,
    RTRIM(TRIM(SUBSTR(OUTPRED."Attribute4",17,100)),'rank="4"/>') FOURTH_ATTRIBUTE
FROM
    (
        SELECT
            ORG.*,
            PREDICTION(${MODEL_NAME} USING TRAIN.*) AS PREDICTION,
            ROUND(TO_NUMBER(PREDICTION_PROBABILITY(${MODEL_NAME} USING TRAIN.*)),3) AS PREDICTION_PROBABILITY,
            PREDICTION_DETAILS(${MODEL_NAME} USING TRAIN.*) AS PREDICTION_DETAILS
        FROM
            V_TITANIC_TRAIN TRAIN
        JOIN
            TITANIC_TRAIN ORG
        ON
            TRAIN.PASSENGERID = ORG.PASSENGERID
    ) SUB
  ,
    XMLTABLE('/Details'
        PASSING SUB.PREDICTION_DETAILS
            COLUMNS 
                "Attribute1" XMLType PATH 'Attribute[1]',
                "Attribute2" XMLType PATH 'Attribute[2]',
                "Attribute3" XMLType PATH 'Attribute[3]',
                "Attribute4" XMLType PATH 'Attribute[4]') OUTPRED
    

【タブ４】テストデータからの予測

最後に、Kaggle へ投稿する用に、テストデータを予測した結果を CSV ダウンロードする画面です。

・上記明細に表示させているSQL（カスタムビュー）

SELECT 
    PASSENGERID,
    PREDICTION(${MODEL_NAME} USING *) AS SURVIVED 
FROM V_TITANIC_TEST TEST