7.00.02 - Use nPath to Generate Total 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_tot
  DISTRIBUTE BY hash(num_tot) COMPRESS low AS
  SELECT (IND_EMLOP || IND_CLKNI || IND_CLKIN || IND_CLKAC || IND_CLKCO) AS IND,
          num_tot 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(intractn_path) AS num_tot
             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)+')
               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)
               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,
               ACCUMULATE (mat_intractn_typ_cd OF ANY(B,C,D,E,F)) AS intractn_path)
  ) GROUP BY 1,2,3,4,5) a;