7.00.02 - Use nPath to Generate Conversion Counts - Aster Analytics

Teradata Aster® Analytics Foundation User GuideUpdate 2

Product
Aster Analytics
Release Number
7.00.02
Release Date
September 2017
Content Type
Programming Reference
User Guide
Publication ID
B700-1022-700K
Language
English (United States)
CREATE TABLE atrbtn_old_dr_pth_noprsnt_conv
  DISTRIBUTE BY hash(num_conv) COMPRESS low AS
    SELECT (IND_EMLOP || IND_CLKNI || IND_CLKIN || IND_CLKAC || IND_CLKCO) AS IND,
            num_conv FROM
              (SELECT case when num_EMLOP > 0 then '1' ELSE '0' END AS IND_EMLOP
               , case when num_CLKNI > 0 then '1' ELSE '0' END AS IND_CLKNI
               , case when num_CLKIN > 0 then '1' ELSE '0' END AS IND_CLKIN 
               , case when num_CLKAC > 0 then '1' ELSE '0' END AS IND_CLKAC
               , case when num_CLKCO > 0 then '1' ELSE '0' END AS IND_CLKCO
               , count(conversion_evt) AS num_conv
               FROM NPATH (
                 ON atrbtn_table_old_direct_noprsnt
                 PARTITION BY indiv_prod_id
                 ORDER BY mat_intractn_dt_ts
                 MODE (nonoverlapping)
                 PATTERN ('(B|C|D|E|F)+.(act|ape|aps|apu|fun|rea){1}')
                 SYMBOLS (mat_intractn_typ_cd = 'EMLOP' AS B,
                          mat_intractn_typ_cd = 'CLKNI' AS C,
                          mat_intractn_typ_cd = 'CLKIN' AS D,
                          mat_intractn_typ_cd = 'CLKAC' AS E,
                          mat_intractn_typ_cd = 'CLKCO' AS F,
                          mat_intractn_typ_cd = 'ACTVD' AS act,
                          mat_intractn_typ_cd = 'APEAP' AS ape,
                          mat_intractn_typ_cd = 'APEST' AS aps,
                          mat_intractn_typ_cd = 'APESU' AS apu,
                          mat_intractn_typ_cd = 'FUNDD' AS fun,
                          mat_intractn_typ_cd = 'REACT' AS rea)
                 RESULT (first(indiv_prod_id of ANY(B,C,D,E,F)) AS indiv_prod_id,
                         count(mat_intractn_typ_cd of B) AS num_EMLOP,
                         count(mat_intractn_typ_cd of C) AS num_CLKNI,
                         count(mat_intractn_typ_cd of D) AS num_CLKIN,
                         count(mat_intractn_typ_cd of E) AS num_CLKAC,
                         count(mat_intractn_typ_cd of F) AS num_CLKCO,
                 LAST_NOTNULL(mat_intractn_typ_cd OF ANY(act,ape,aps,apu,fun,rea))
                   AS conversion_evt,
                 ACCUMULATE (mat_intractn_typ_cd OF ANY(B,C,D,E,F)) AS intractn_path)
    ) GROUP BY 1,2,3,4,5) a;