Advanced SQL Engine 関数の検証 | Teradata Vantage - 2.2 - Advanced SQL Engine分析関数の検証 - Teradata Vantage

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

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

Advanced SQL Engine分析関数については、<Teradata Vantage™ - Advanced SQL Engine分析関数, B035-1206>を参照してください。

  1. クライアント アプリケーション(BTEQTeradata Studioなど)からVantageシステムにログオンします。例:
    .logon systemname.your_company.com/hal

    systemnameはプライマリ データベース ノード(そのネットワークでのVantageシステムの名前であり、TDPIDとも呼ばれる)です。

  2. 次のように入力テーブルを作成します。
    create multiset table attribution_1 (user_id integer, event varchar(20), time_stamp timestamp);
    insert into attribution_1 values(1, 'impression', '2001-09-27 23:00:01');
    insert into attribution_1 values(1, 'impression', '2001-09-27 23:00:05');
    insert into attribution_1 values(1, 'email', '2001-09-27 23:00:15');
    insert into attribution_1 values(2, 'impression', '2001-09-27 23:00:31');
    insert into attribution_1 values(2, 'impression', '2001-09-27 23:00:51');
    
    create multiset table attribution_2 (user_id integer, event varchar(20), time_stamp timestamp);
    insert into attribution_2 values(1, 'impression', '2001-09-27 23:00:19');
    insert into attribution_2 values(1, 'socialnetwork', '2001-09-27 23:00:20');
    insert into attribution_2 values(1, 'direct', '2001-09-27 23:00:21');
    insert into attribution_2 values(1, 'referral', '2001-09-27 23:00:22');
    insert into attribution_2 values(1, 'paidsearch', '2001-09-27 23:00:23');
    insert into attribution_2 values(2, 'impression', '2001-09-27 23:00:29');
    insert into attribution_2 values(2, 'impression', '2001-09-27 23:00:31');
    insert into attribution_2 values(2, 'impression', '2001-09-27 23:00:33');
    insert into attribution_2 values(2, 'impression', '2001-09-27 23:00:36');
    insert into attribution_2 values(2, 'impression', '2001-09-27 23:00:38');
    
    create multiset table conversion_event_table (conversion_events varchar(20));
    insert into conversion_event_table values('socialnetwork');
    insert into conversion_event_table values('paidsearch');
    
    create multiset table excluding_event_table (excluding_events varchar(20));
    insert into excluding_event_table values('email');
    
    create multiset table optional_event_table (optional_events varchar(20));
    insert into optional_event_table values('organicsearch');
    insert into optional_event_table values('direct');
    insert into optional_event_table values('referral');
    
    create multiset table model1_table  (id integer, model varchar(30));
    insert into model1_table values('0', 'segment_rows');
    insert into model1_table values('1', '3:0.5:exponential:0.5,second');
    insert into model1_table values('2', '4:0.3:weighted:0.4,0.3,0.2,0.1');
    insert into model1_table values('3', '3:0.2:first_click:NA');
    
    create multiset table model2_table (id integer, model varchar(30));
    insert into model2_table values('0', 'segment_seconds');
    insert into model2_table values('1', '6:0.5:uniform:NA');
    insert into model2_table values('2', '8:0.3:last_click:NA');
    insert into model2_table values('3', '6:0.2:first_click:NA');
    
  3. (オプション、BTEQユーザーの場合のみ)ページ幅を設定して出力幅を広くします。
    .WIDTH 1024
  4. 次の属性クエリーを実行します。
    SELECT * FROM Attribution (
    ON attribution_1 AS InputTable1
    PARTITION BY user_id ORDER BY time_stamp
    ON attribution_2 AS InputTable2
    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 FirstModel DIMENSION
    ON model2_table AS SecondModel DIMENSION
    USING
    EventColumn ('event')
    TimeColumn ('time_stamp')
    WindowSize ('rows:10&seconds:20')
    ) AS dt ORDER BY user_id, time_stamp;
    
    出力は次のようになります。
    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 ? ?