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 vector, if multiple
columns are used for partition.
Default Value: 1
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 vector, if multiple
columns are used for ordering.
Types: character OR vector of Strings (character)
|
mode |
Required Argument.
Specifies the pattern-matching mode. Below values are permitted.
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.
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.
Types: character
|
pattern |
Required Argument.
Specifies the pattern for which the function searches.
You compose pattern with the symbols (which you define in the symbols argument),
operators, and parentheses. When patterns have multiple operators, the
function applies them in order of precedence, and applies operators of equal
precedence from left to right. 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.
Defines 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
tables, then you must qualify the ambiguous column name with its
input table name. For example:
symbols
(
weblog.pagetype = "homepage" AS H,
weblog.pagetype = "thankyou" AS T,
ads.adname = "xmaspromo" AS X,
ads.adname = "realtorpromo" AS R
)
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 be retrieved from the
matched rows. The function applies aggregate function to these
values. The Result argument defines the output columns, specifying
the values to retrieve from the matched rows and the aggregate function
to apply to these values. For each pattern, the nPath function can apply
one or more aggregate functions to the matched rows and output the aggregated
results. Supported aggregate functions:
SQL aggregate functions - AVG, COUNT, MAX, MIN, and SUM
ML Engine nPath sequence aggregate functions - COUNT, FIRST, LAST, NTH, FIRST_NOTNULL,
LAST_NOTNULL, MAX_CHOOSE, MIN_CHOOSE, DUPCOUNT, DUPCOUNTCUM, ACCUMULATE.
More details on each of these functions can be found in the Teradata Vantage Machine
Learning Engine Analytic Function Reference.
The function evaluates this argument once for every matched pattern
in the partition (that is, it outputs one row for each pattern match).
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 is either <, >, <=, >=, =, or !=.
Types: character OR vector of characters
|
data2 |
Optional Argument.
Specifies additional input table.
|
data2.partition.column |
Optional Argument.
Specifies Partition By columns for data2.
Values to this argument can be provided as vector, if multiple
columns are used for partition.
Default Value: 1
Types: character OR vector of Strings (character)
|
data2.order.column |
Required Argument.
Specifies Order By columns for data2.
Values to this argument can be provided as vector, if multiple
columns are used for ordering.
Types: character OR vector of Strings (character)
|
data3 |
Optional Argument.
Specifies additional input table.
|
data3.partition.column |
Optional Argument.
Specifies Partition By columns for data3.
Values to this argument can be provided as vector, if multiple
columns are used for partition.
Default Value: 1
Types: character OR vector of Strings (character)
|
data3.order.column |
Required Argument.
Specifies Order By columns for data3.
Values to this argument can be provided as 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 Teradata tbl object.
Named list member can be referenced directly with the "$" operator
using 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 remote tibble objects.
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")
)