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_sqle (
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.
Input table
|
data1.partition.column |
Partition By columns for data1.
Values to this argument can be provided as list, if multiple
columns are used for ordering.
|
data1.order.column |
Order By columns for data1.
Values to this argument can be provided as list, if multiple
columns are used for ordering.
|
mode |
Required Argument.
Specifies the pattern-matching mode:
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.
Permitted Values are: OVERLAPPING, NONOVERLAPPING
|
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. Table 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. Table also shows operator precedence. 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".
For pattern matching details, refer to "Pattern Matching".
|
symbols |
Required Argument.
Defines the symbols that appear in the values of the pattern and
result arguments. 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
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
)
For more information about symbols that appear in the Pattern
argument value, refer to "symbols". For more information about
symbols that appear in the Result argument value, refer to "result:
Applying Aggregate Functions".
|
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. For details, see "result: Applying 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).
|
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_operators can be <, >, <=, >=, =, or !=.
This argument can improve or degrade npath performance, depending on
several factors. For details, refer to "filters".
|
data2 |
Optional Argument.
Additional optional input table
|
data2.partition.column |
Partition By columns for data2.
Values to this argument can be provided as list, if multiple
columns are used for ordering.
|
data2.order.column |
Order By columns for data2.
Values to this argument can be provided as list, if multiple
columns are used for ordering.
|
data3 |
Optional Argument.
Additional optional input table
|
data3.partition.column |
Partition By columns for data3.
Values to this argument can be provided as list, if multiple
columns are used for ordering.
|
data3.order.column |
Order By columns for data3.
Values to this argument can be provided as list, if multiple
columns are used for ordering.
|
Value
Function returns an object of class "td_npath_sqle" 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")
# Create remote tibble objects.
tblQuery <- "SELECT customer_id, session_id, datestamp, page FROM bank_web_clicks2"
bank_web_clicks2 <- tbl(con, sql(tblQuery))
# Execute npath function.
npath_out <- td_npath_sqle(
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")
);