nPath Resultの例: FIRSTおよびACCUMULATEの3つの形式 - Advanced SQL Engine - Teradata Database

入力

SQL呼び出し

SELECT * FROM nPath (
  ON clicks PARTITION BY sessionid ORDER BY clicktime
  USING
  Mode (NONOVERLAPPING)
  Symbols (
    pagetype='home' AS H,
    pagetype='checkout' AS C,
    pagetype <> 'home' AND pagetype <>'checkout' AS A
  )
  Pattern ('^H+.A*.C+$')
  Result (
    FIRST (sessionid OF ANY (H, A, C)) AS sessionid,
    FIRST (clicktime OF H) AS firsthome,
    FIRST (clicktime OF C) AS firstcheckout,
    ACCUMULATE (productname OF ANY (H,A,C) DELIMITER '*') AS products_accumulate,
    ACCUMULATE (CDISTINCT productname OF ANY (H,A,C) DELIMITER '$$') AS cde_dup_products,
    ACCUMULATE (DISTINCT productname OF ANY (H,A,C)) AS de_dup_products
  )
) ORDER BY sessionid;