Teradata Package for R Function Reference | 17.00 - NPath - Teradata Package for R - Look here for syntax, methods and examples for the functions included in the Teradata Package for R.

Teradata® Package for R Function Reference

Product
Teradata Package for R
Release Number
17.00
Published
July 2021
Language
English (United States)
Last Update
2023-08-08
dita:id
B700-4007
NMT
no
Product Category
Teradata Vantage
Pattern Matching with nPath.

Description

The nPath function scans a set of rows, looking for patterns that you specify. For each set of input rows that matches the pattern, nPath produces a single output row. The function provides a flexible pattern-matching capability that lets you specify complex patterns in the input data and define the values that are output for each matched input set.

Usage

  td_npath_mle (
      data1 = NULL,
      mode = NULL,
      pattern = NULL,
      symbols = NULL,
      result = NULL,
      filter = NULL,
      data2 = NULL,
      data3 = NULL,
      data1.partition.column = NULL,
      data2.partition.column = NULL,
      data3.partition.column = NULL,
      data1.order.column = NULL,
      data2.order.column = NULL,
      data3.order.column = NULL
  )

Arguments

data1

Required Argument.
Specifies the input table.

data1.partition.column

Required Argument.
Specifies Partition By columns for "data1".
Values to this argument can be provided as a vector, if multiple columns are used for partition.
Types: character OR vector of Strings (character)

data1.order.column

Required Argument.
Specifies Order By columns for "data1".
Values to this argument can be provided as a vector, if multiple columns are used for ordering.
Types: character OR vector of Strings (character)

mode

Required Argument.
Specifies the pattern-matching mode:

  1. OVERLAPPING: The function finds every occurrence of the pattern in the partition, regardless of whether it is part of a previously found match. Therefore, one row can match multiple symbols in a given matched pattern.

  2. NONOVERLAPPING: The function begins the next pattern search at the row that follows the last pattern match. This is the default behavior of many commonly used pattern matching utilities, including the UNIX grep utility.

Permitted Values: OVERLAPPING, NONOVERLAPPING
Types: character

pattern

Required Argument.
Specifies the pattern for which the function searches. You compose pattern with the symbols that you define in the symbols argument, operators, and parentheses. tbl_teradata describes the simplest patterns, which you can combine to form more complex patterns. When patterns have multiple operators, the function applies them in order of precedence, and applies operators of equal precedence from left to right. To force the function to evaluate a subpattern first, enclose it in parentheses. To specify that a subpattern must appear a specific number of times, use the "Range-Matching Feature".

The basic pattern operators in decreasing order of precedence:
"pattern", "pattern.", "pattern?", "pattern*", "pattern+", "pattern1.pattern2", "pattern1|pattern2"
To force the function to evaluate a subpattern first, enclose it in parentheses.
Example: ^A.(B|C)+.D?.X*.A$
The preceding pattern definition matches any set of rows whose first row starts with the definition of symbol A, followed by a non-empty sequence of rows, each of which meets the definition of either symbol B or C, optionally followed by one row that meets the definition of symbol D, followed by any number of rows that meet the definition of symbol X, and ending with a row that ends with the definition of symbol A.
You can use parentheses to define precedence rules. Parentheses are recommended for clarity, even where not strictly required.
Types: character

symbols

Required Argument.
Specifies the symbols that appear in the values of the pattern and result arguments.
Example : col_expr = symbol_predicate AS symbol
The col_expr is an expression whose value is a column name, symbol is any valid identifier, and symbol_predicate is a SQL predicate (often a column name).
For example, the symbols argument for analyzing website visits might look like this:
symbols
( pagetype = "homepage" AS H,
pagetype <> "homepage" AND pagetype <> "checkout" AS PP,
pagetype = "checkout" AS CO
)
The symbol is case-insensitive, however a symbol of one or two uppercase letters is easy to identify in patterns.
If col_expr represents a column that appears in multiple input tbl_teradata, then you must qualify the ambiguous column name with its input tbl_teradata name.
For example:
symbols
( weblog.pagetype = "homepage" AS H,
weblog.pagetype = "thankyou" AS T,
ads.adname = "xmaspromo" AS X,
ads.adname = "realtorpromo" AS R
)
The mapping from teradataml DataFrame name to its corresponding SQL name is as shown below:
* data1: input
* data2: input1
* data3: input2
You can create symbol predicates that compare a row to a previous or subsequent row, using a LAG or LEAD operator.
LAG Expression Syntax:
current_expr operator LAG (previous_expr, lag_rows [, default]) | LAG (previous_expr, lag_rows [, default]) operator current_expr
LAG and LEAD Expression Rules:
A symbol definition can have multiple LAG and LEAD expressions, A symbol definition that has a LAG or LEAD expression cannot have an OR operator.
If a symbol definition has a LAG or LEAD expression and the input is not a table, you must create an alias of the input query.
Types: character OR vector of characters

result

Required Argument.
Defines the output columns. The col_expr is an expression whose value is a column name; it specifies the values to retrieve from the matched rows. The function applies aggregate function to these values.
Supported aggregate functions: SQL aggregate functions are [AVG, COUNT, MAX, MIN, SUM]. ML Engine nPath sequence aggregate functions. The function evaluates this argument once for every matched pattern in the partition (that is, it outputs one row for each pattern match).
Note: For col_expr representing a column that appears in multiple input tbl_teradata, you must qualify the ambiguous column name with the SQL name corresponding to its tbl_teradata. Please see the description of the "symbols" parameter for the mapping from tbl_teradata name to the SQL name.
Types: character OR vector of characters

filter

Optional Argument.
Specifies filters to impose on the matched rows. The function combines the filter expressions using the AND operator.
The filter_expression syntax is: symbol_expression comparison_operator symbol_expression The two symbol expressions must be type-compatible.
The symbol_expression syntax is:
FIRST | LAST (column_with_expression OF [ANY](symbol[,...]))
The column_with_expression cannot contain the operator AND or OR, and all its columns must come from the same input. If the function has multiple inputs, then column_with_expression and symbol must come from the same input.
The comparison_operator can be <, >, <=, >=, =, <>.
Note: For column_with_expression representing a column that appears in multiple input tbl_teradata, you must qualify the ambiguous column name with the SQL name corresponding to its tbl_teradata name. Please see the description of the "symbols" parameter for the mapping from tbl_teradata name to the SQL name. Types: character OR vector of characters

data2

Optional Argument.
Specifies additional input tbl_teradata.

data2.partition.column

Optional Argument.
Specifies Partition By columns for "data2".
Values to this argument can be provided as a vector, if multiple columns are used for partition.
Types: character OR vector of Strings (character)

data2.order.column

Optional Argument. Required if "data2" is specified.
Specifies Order By columns for "data2".
Values to this argument can be provided as a vector, if multiple columns are used for ordering.
Types: character OR vector of Strings (character)

data3

Optional Argument.
Specifies additional input tbl_teradata.

data3.partition.column

Optional Argument.
Specifies Partition By columns for "data3".
Values to this argument can be provided as a vector, if multiple columns are used for partition.
Types: character OR vector of Strings (character)

data3.order.column

Optional Argument. Required if "data3" is specified.
Specifies Order By columns for "data3".
Values to this argument can be provided as a vector, if multiple columns are used for ordering.
Types: character OR vector of Strings (character)

Value

Function returns an object of class "td_npath_mle" which is a named list containing object of class "tbl_teradata".
Named list member can be referenced directly with the "$" operator using the name: result.

Examples

  
    # Get the current context/connection
    con <- td_get_context()$connection

    # Load data
    loadExampleData("npath_example1", "bank_web_clicks2")
    loadExampleData("npath_example2", "aggregate_clicks","link2")

    # Create object(s) of class "tbl_teradata".
    aggregate_clicks <- tbl(con, "aggregate_clicks")
    tblQuery <- "SELECT customer_id, session_id, datestamp, page FROM bank_web_clicks2"
    bank_web_clicks2 <- tbl(con, sql(tblQuery))
    link2 <- tbl(con,"link2")

    # Example 1 - LAG Expression Rules.
    npath_out1 <- td_npath_mle(
                       data1=bank_web_clicks2,
                       data1.partition.column = c("customer_id", "session_id"),
                       data1.order.column = "datestamp",
                       mode = "nonoverlapping",
                       pattern = "(DUP|A)*",
                       symbols = c("true AS A",
                                   "page = LAG (page,1) AS DUP"),
                       result = c("FIRST (customer_id OF any (A)) AS customer_id",
                                  "FIRST (session_id OF A) AS session_id",
                                  "FIRST (datestamp OF A) AS first_date",
                                  "LAST (datestamp OF ANY(A,DUP)) AS last_date",
                                  "ACCUMULATE (page OF A) AS page_path",
                                  "ACCUMULATE (page of DUP) AS dup_path")
                       )

    # Example 2 - NPath Range-Matching Example.
    # Find Data for Sessions That Checked Out 3-6 Products.
    npath_out2 <- td_npath_mle(
             data1=aggregate_clicks,
             data1.partition.column = "sessionid",
             data1.order.column = "clicktime",
             mode = "nonoverlapping",
             pattern = "H+.D*.C{3,6}.D",
             symbols = c("'true' AS X",
                         "pagetype = 'home' AS H",
                         "pagetype='checkout' AS C",
                         "pagetype<>'home' AND pagetype<>'checkout' AS D"),
             result = c("FIRST (sessionid OF C) AS sessionid",
                        "max_choose (productprice, productname OF C) AS most_expensive_product",
                        "MAX (productprice OF C) AS max_price",
                        "min_choose (productprice, productname of C) AS least_expensive_product",
                        "MIN (productprice OF C) AS min_price")
              )

    # Example 3 - NPath Greedy Pattern Matching Example
    npath_out3 <- td_npath_mle(
                   data1=link2,
                   data1.partition.column = "userid",
                   data1.order.column = "startdate",
                   mode = "nonoverlapping",
                   pattern = "CEO.ENGR.OTHER*",
                   symbols = c("title1 like '%software eng%' AS ENGR",
                               "true AS OTHER",
                               "title1 like 'chief exec officer' AS CEO"),
                   result = c("accumulate(title1 OF ANY(ENGR,OTHER,CEO)) AS job_transition_path")
                   )