Validate Advanced SQL Engine Functions | Teradata Vantage - 2.2 - Validating Advanced SQL Engine Analytic Functions - Teradata Vantage

Configuring Teradata Vantageā„¢ After Installation

Product
Teradata Vantage
Release Number
2.2
Release Date
January 2021
Content Type
Configuration
Implementation
Publication ID
B700-4014-011K
Language
English (United States)

For information about Advanced SQL Engine analytic functions, see Teradata Vantageā„¢ - Advanced SQL Engine Analytic Functions, B035-1206.

  1. From a client application (such as BTEQ or Teradata Studio), log on to the Vantage system. For example:
    .logon systemname.your_company.com/hal

    where systemname is the primary database node (the name by which the Vantage system is known to the network, also called the TDPID).

  2. Create input tables:
    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. [Optional, for BTEQ users only] Set the page width for wider output:
    .WIDTH 1024
  4. Run the following Attribution query:
    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;
    
    The output is similar to the following:
    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                   ?                  ?