nPath Range-Matching Example | Teradata Vantage - nPath Range-Matching Example: Multiple Partitioned Input Tables and Dimension Input Table - Teradata® Database

An e-commerce store wants to count the advertising impressions that lead to a user clicking an online advertisement. The example counts the online advertisements that the user viewed and the television advertisements that the user might have viewed.

Input

SQL-MapReduce Call

The tables impressions and clicks have a user_id column, but the table tv_spots is only a record of television advertisements shown, which any user might have seen. Therefore, tv_spots must be a dimension table.

SELECT * FROM nPath (
  ON impressions PARTITION BY userid ORDER BY ts
  ON clicks2 PARTITION BY userid ORDER BY ts
  ON tv_spots DIMENSION ORDER BY ts
  USING
  Mode (NONOVERLAPPING)
  Symbols (TRUE AS imp, TRUE AS click, TRUE AS tv_imp)
  Pattern ('(imp|tv_imp)*.click')
  Result (
    COUNT(* of imp) AS imp_cnt,
    COUNT (* of tv_imp) AS tv_imp_cnt
  )
) AS dt ORDER BY dt.imp_cnt;

Output