ML Engine 分析関数の検証 | Teradata Vantage - 2.2 - Machine Learning Engine分析関数の検証 - Teradata Vantage

インストール後のTeradata Vantage™の構成

Product
Teradata Vantage
Release Number
2.2
Published
2021年1月
Content Type
実装
構成
Publication ID
B700-4014-011K-JPN
Language
日本語 (日本)

Machine Learning Engine分析関数については、<Teradata Vantage™ Machine Learning Engine分析関数リファレンス, B700-4003>を参照してください。

  1. クライアント アプリケーション(BTEQTeradata Studioなど)から、Machine Learning Engineにアクセスするために必要なアクセス権があるユーザーとして、Vantageシステムにログオンします。例:
    .logon systemname.your_company.com/hal
  2. BTEQのコマンド ラインから次のコマンドを実行する場合は、出力幅を広げて読みやすくするために、SIDETITLESおよびFOLDLINEをONに設定します。
    .SET FOLDLINE ON ALL
    .SET SIDETITLES ON ALL
  3. 次の属性クエリーを実行します。このクエリーでは、Machine Learning Engineバージョンの属性が使用されていて、Attribution_MLEという構文で呼び出されています。
    Advanced SQL EngineMachine Learning Engineの両方に存在する関数を使用する場合、Machine Learning Engineバージョンを使用するには接尾辞_MLEを指定する必要があります(関数nPath®およびNTreeは例外です。これらの関数では、Machine Learning Engineバージョンを使用するには接尾辞@coprocessorを指定する必要があります)。
    SELECT * FROM Attribution_MLE (
    ON attribution_1 AS input1
    PARTITION BY user_id
    ORDER BY time_stamp
    ON attribution_2 AS input2
    PARTITION BY user_id
    ORDER BY time_stamp
    ON conversion_event_table AS ConversionEventTable DIMENSION
    ON excluding_event_table AS ExcludedEventTable DIMENSION
    ON optional_event_table AS OptionalEventTable DIMENSION
    ON model1_table AS FirstModelTable DIMENSION
    ON model2_table AS SecondModelTable DIMENSION
    USING
    EventColumn ('event')
    TimeColumn ('time_stamp')
    WindowSize ('rows:10&seconds:20')
    ) AS dt ;
    
    出力は次のようになります。
    user_id event time_stamp attribution time_to_conversion ------- ------------- -------------------------- ------------------- ------------------ 1 impression 2001-09-27 23:00:01.000000 0.28571428571428575 -19.0 1 impression 2001-09-27 23:00:05.000000 0.0 ? 1 impression 2001-09-27 23:00:19.000000 0.7142857142857143 -1.0 1 socialnetwork 2001-09-27 23:00:20.000000 ? ? 1 direct 2001-09-27 23:00:21.000000 0.5 -2.0 1 referral 2001-09-27 23:00:22.000000 0.5 -1.0 1 paidsearch 2001-09-27 23:00:23.000000 ? ?
  4. Advanced SQL Engineに同等の関数がないMachine Learning Engine関数を使用する場合は、接尾辞は不要です。次のように入力テーブルを作成します。
    create multiset table kmodes_input   (model varchar(30), mpg double PRECISION, cyl integer, disp double PRECISION, hp double PRECISION, drat double PRECISION, wt double PRECISION, qsec double PRECISION, vs varchar(30), am varchar(30), gear integer, carb integer);
    
    insert into kmodes_input values('mazda rx4',0.150884825,6,-0.570619819,-0.53509284,0.567513685,-0.610399567,-0.777165145,'s','manual',4,4);
    
    insert into kmodes_input values('mazda rx4 wag',0.150884825,6,-0.570619819,-0.53509284,0.567513685,-0.349785269,-0.46378082,'s','manual',4,4);
    
    insert into kmodes_input values('datsun 710',0.449543447,4,-0.990182091,-0.783040459,0.473999587,-0.917004624,0.426006817,'v','manual',4,1);
    
    insert into kmodes_input values('hornet 4 drive',0.217253407,6,0.220093694,-0.53509284,-0.96611753,-0.002299538,0.890487156,'v','automatic',3,1);
    
    insert into kmodes_input values('hornet sportabout',-0.230734526,8,1.043081228,0.412942174,-0.835197792,0.227654255,-0.46378082,'s','automatic',3,2);
    
    insert into kmodes_input values('valiant',-0.3302874,6,-0.046166978,-0.60801861,-1.564607761,0.248094592,1.326986752,'v','automatic',3,1);
    
    insert into kmodes_input values('duster 360',-0.960788935,8,1.043081228,1.433902959,-0.722980874,0.360516446,-1.124126363,'s','automatic',3,4);
    
    insert into kmodes_input values('merc 240d',0.715017777,4,-0.677930938,-1.235180235,0.174754472,-0.027849959,1.203871481,'v','automatic',4,2);
    
    insert into kmodes_input values('merc 230',0.449543447,4,-0.725535119,-0.753870151,0.604919325,-0.068730634,2.826754593,'v','automatic',4,2);
    
    insert into kmodes_input values('merc 280',-0.147773797,6,-0.509299179,-0.345485837,0.604919325,0.227654255,0.252526208,'v','automatic',4,4);
    
    insert into kmodes_input values('merc 280c',-0.380063837,6,-0.509299179,-0.345485837,0.604919325,0.227654255,0.588295128,'v','automatic',4,4);
    
    insert into kmodes_input values('merc 450se',-0.612353876,8,0.363713088,0.485867945,-0.98482035,0.871524874,-0.251127171,'s','automatic',3,3);
    
    insert into kmodes_input values('merc 450sl',-0.463024565,8,0.363713088,0.485867945,-0.98482035,0.524039143,-0.139204198,'s','automatic',3,3);
    
    insert into kmodes_input values('merc 450slc',-0.811459624,8,0.363713088,0.485867945,-0.98482035,0.575139986,0.084641749,'s','automatic',3,3);
    
    insert into kmodes_input values('cadillac fleetwood',-1.607882616,8,1.946753815,0.850496796,-1.246659826,2.077504765,0.073449451,'s','automatic',3,4);
    
    insert into kmodes_input values('lincoln continental',-1.607882616,8,1.849931752,0.996348337,-1.115740088,2.255335698,-0.016088927,'s','automatic',3,4);
    
    insert into kmodes_input values('chrysler imperial',-0.894420352,8,1.688561647,1.215125648,-0.685575235,2.174596366,-0.239934874,'s','automatic',3,4);
    
    insert into kmodes_input values('fiat 128',2.042389431,4,-1.226589294,-1.176839619,0.90416444,-1.039646647,0.907275602,'v','manual',4,1);
    
    insert into kmodes_input values('honda civic',1.710546517,4,-1.25079481,-1.381031775,2.493904115,-1.637526508,0.375641479,'v','manual',4,2);
    
    insert into kmodes_input values('toyota corolla',2.291271616,4,-1.287909934,-1.191424773,1.166003916,-1.4126828,1.147909994,'v','manual',4,1);
    
    insert into kmodes_input values('toyota corona',0.233845553,4,-0.892553178,-0.724699843,0.193457291,-0.76881218,1.20946763,'v','automatic',3,1);
    
    insert into kmodes_input values('dodge challenger',-0.761683187,8,0.704204008,0.048313323,-1.564607761,0.309415603,-0.54772305,'s','automatic',3,2);
    
    insert into kmodes_input values('amc javelin',-0.811459624,8,0.591244935,0.048313323,-0.835197792,0.22254417,-0.307088658,'s','automatic',3,2);
    
    insert into kmodes_input values('camaro z28',-1.126710392,8,0.962396176,1.433902959,0.24956575,0.636460997,-1.364760755,'s','automatic',3,4);
    
    insert into kmodes_input values('pontiac firebird',-0.147773797,8,1.365821438,0.412942174,-0.96611753,0.641571082,-0.446992374,'s','automatic',3,2);
    
    insert into kmodes_input values('fiat x1-9',1.196190002,4,-1.224168743,-1.176839619,0.90416444,-1.310481114,0.588295128,'v','manual',4,1);
    
    insert into kmodes_input values('porsche 914-2',0.980492108,4,-0.890939476,-0.812210767,1.55876313,-1.100967659,-0.642857578,'s','manual',5,2);
    
    insert into kmodes_input values('lotus europa',1.710546517,4,-1.094265808,-0.491337378,0.324377029,-1.741772228,-0.530934604,'v','manual',5,2);
    
    insert into kmodes_input values('ford pantera l',-0.71190675,8,0.970464681,1.711020886,1.166003916,-0.048290296,-1.874010283,'s','manual',5,4);
    
    insert into kmodes_input values('ferrari dino',-0.064813069,6,-0.691647397,0.412942174,0.043834734,-0.457097039,-1.314395417,'s','manual',5,6);
    
    insert into kmodes_input values('maserati bora',-0.844643915,8,0.567039419,2.746566825,-0.105787824,0.360516446,-1.818048797,'s','manual',5,8);
    
    insert into kmodes_input values('volvo 142e',0.217253407,4,-0.885291523,-0.549677994,0.960272899,-0.44687687,0.420410668,'v','manual',4,2);
    
    
  5. 例えば、次のKModesクエリーを実行します。
    SELECT * FROM KModes (
    ON kmodes_input AS InputTable
    OUT TABLE OutputTable (kmodes_clusters1)
    USING
    NumClusters (3)
    TargetColumns ('mpg:carb')
    NumericAsCategorical('cyl','gear','carb')
    ) AS dt ;
    出力は、次の表に示しているものと類似していますが、縦に並んで表示されます。
    KModesはランダムなアルゴリズムであるため、出力での数値はまったく同じにはなりません。
    set_id サマリー between_cluster_error total_within_cluster_error pseudo_f
    0

    クラスタの数: 3

    反復回数: 3

    モデルの収束: true

    データ ポイントの数: 32.0

    194.95312046325273 115.04687954956962 16.380686190908744
次のタスクテスト テーブルをクリーンアップします。
drop table kmodes_input;
drop table kmodes_clusters1;