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

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

クライアントアプリケーション(BTEQ、Teradata Studioなど)からVantageシステムにログオンします。例:
```
.logon systemname.your_company.com/hal
```
systemnameはプライマリデータベースノード(そのネットワークでのVantageシステムの名前であり、TDPIDとも呼ばれる)です。

次のように入力テーブルを作成します。

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');

(オプション、BTEQユーザーの場合のみ)ページ幅を設定して出力幅を広くします。
```
.WIDTH 1024
```

次の属性クエリーを実行します。

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 ? ?

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

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