NPath
Description
The td_npath_sqle()
function scans a set of rows, looking
for patterns that you specify.
For each set of input rows that matches the pattern, td_npath_sqle()
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,
data4 = NULL,
data5 = NULL,
data6 = NULL,
data7 = NULL,
data8 = NULL,
data9 = NULL,
data10 = NULL,
...
)
Arguments
data1 |
Required Argument. |
mode |
Required Argument.
Permitted Values: "OVERLAPPING", "NONOVERLAPPING" |
pattern |
Required Argument. |
symbols |
Required Argument.
You can create symbol predicates that compare a row to a previous
or subsequent row, using a LAG or LEAD operator. |
result |
Required Argument. |
filter |
Optional Argument. |
data2 |
Optional Arguments. |
data3 |
Optional Arguments. |
... |
Specifies the generic keyword arguments SQLE functions accept. volatile: Function allows the user to partition, hash, order or local order the input data. These generic arguments are available for each argument that accepts tbl_teradata as input and can be accessed as:
Note: |
Value
Function returns an object of class "td_npath_sqle"
which is a named list containing object of class "tbl_teradata".
Named list member(s) can be referenced directly with the "$" operator
using the name(s):result
Examples
# Get the current context/connection.
con <- td_get_context()$connection
# Load example data.
loadExampleData("npath_example2","impressions","clicks2", "tv_spots",
"clickstream")
# Create input tbl_teradata objects.
impressions <- tbl(con, "impressions")
clicks2 <- tbl(con, "clicks2")
tv_spots <- tbl(con, "tv_spots")
clickstream <- tbl(con, "clickstream")
# Check the list of available analytic functions.
display_analytic_functions()
# Example 1: Search for pattern '(imp|tv_imp)*.click' in the provided
# data(impressions, clicks2, tv_spots).
# Run td_npath_sqle function with the required patterns to get the rows
# which have specified pattern. Rows that matches the pattern.
obj <- td_npath_sqle(data1=impressions,
data1.partition.column='userid',
data1.order.column='ts',
data2=clicks2,
data2.partition.column='userid',
data2.order.column='ts',
data3=tv_spots,
data3.partition.column='ts',
data3.order.column='ts',
result=c('COUNT(* of imp) as imp_cnt',
'COUNT(* of tv_imp) as tv_imp_cnt'),
mode='nonoverlapping',
pattern='(imp|tv_imp)*.click',
symbols=c('true as imp','true as click',
'true as tv_imp'))
# Print the result.
print(obj$result)
# Example 2: Search for pattern 'home.clickview*.checkout' in the provided
# data set clickstream.
# Run td_npath_sqle function with the required patterns to get the rows which
# has specified pattern and filter the rows with the filter,
# where filter and result have ML Engine nPath sequence aggregate functions
# like 'FIRST', 'COUNT' and 'LAST'.
obj <- td_npath_sqle(data1=clickstream,
data1.partition.column='userid',
data1.order.column='clicktime',
result=c('FIRST(userid of ANY(home, checkout,
clickview)) AS userid',
'FIRST (sessionid of ANY(home, checkout,
clickview)) AS sessioinid',
'COUNT (* of any(home, checkout,
clickview)) AS cnt1',
'FIRST (clicktime of ANY(home)) AS firsthome',
'LAST (clicktime of ANY(checkout))
AS lastcheckout'),
mode='nonoverlapping',
pattern='home.clickview*.checkout',
symbols=c("cnt='home' AS home",
"cnt <> 'home' AND cnt <> 'checkout'
AS clickview",
"cnt='checkout' AS checkout"),
filter = "FIRST (clicktime OF ANY (home))
< FIRST (clicktime of any(checkout))")
# Print the result.
print(obj$result)