Syntax Elements - Teradata® Database

Database Analytic Functions

Product
Teradata® Database
Release Number
16.10
15.10
Published
November 2017
Language
English (United States)
Last Update
2018-05-10
dita:mapPath
hoj1499019223447.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-1206
lifecycle
previous
Product Category
Teradata® Database

ON Clause

The function supports one ON clause.

The input table must have a timestamp column and columns by which to partition and order the data. Input data must be partitioned such that each partition contains all rows of an entity. No input column can have the name 'sessionid' or 'clicklag', because these are output column names.
If the input path to nPath is nondeterministic, then the results are nondeterministic.
ON table_name
Name of input table.
ON view_name
Name of input view.
ON query_expression
SELECT statement. See
AS alias_name
Alias name for the input table.
PARTITION BY column_name
Name of the column by which every partitioned input table is partitioned. The function requires at least one partitioned input table, and can have additional partitioned input tables.
PARTITION BY column_position
Position of the column by which every partitioned input table is partitioned. The function requires at least one partitioned input table, and you can specify additional partitioned input tables.
ORDER BY column_name
Name of the column by which every input table is ordered.
ORDER BY column_position
Position of the column to use for ordering the results.
ORDER BY sort_expression
Expression to use for ordering the results.
ASC
Sort results in ascending order.
DESC
Sort results in descending order.
NULLS FIRST
Sort nulls first in results.
NULLS LAST
Sort nulls last in results.

TIMECOLUMN

timestamp_column
Name of the input column that contains the click times. If the data type is INTEGER, BIGINT, or SMALLINT, then the function treats the values as milliseconds.
The timestamp_column must also be an order_column.
The timestamp_column can have any of the following data types:
  • TIME
  • TIMESTAMP
  • INTEGER
  • BIGINT
  • SMALLINT

TIMEOUT

session_timeout_value
Specifies the number of seconds at which the session times out. If session_timeout seconds elapse after a click, then the next click starts a new session. The data type of session_timeout is DOUBLE PRECISION.

CLICKLAG

min_human_click_lag
Specifies the minimum number of seconds between clicks for the session user to be considered human. If clicks are more frequent, indicating that the user is a “bot,” the function ignores the session. The min_human_click_lag must be less than session_timout. The data type of min_human_click_lag is DOUBLE PRECISION.

EMITNULL

Specifies whether to output rows that have NULL values in their session id and clicklag columns, even if their timestamp_column has a NULL value.

true_value
Output rows that have NULL values in their session id and clicklag column, even if TIMECOLUMN row has a NULL value.
Valid values for true_value are: true, t, yes, y, and 1, which must be enclosed in single quotation marks.
false_value
Do not output any rows with NULL values for the TIMECOLUMN. This is the default.
Valid values for false_value are: false, f, no, n, and 0. The default value is false.

AS alias_name

Name of output table.

other_select_conditions

You can specify other SELECT statement options. See SQL Data Manipulation Language , B035-1146

Output

Sessionize Output Table Schema
Column Name Data Type Description
input_column TIME,

TIMESTAMP,

INTEGER,

BIGINT, or

SMALLINT

Column copied from input table. The function copies every input table column to the output table.
sessionid INTEGER or BOOLEAN Contains the identifiers that the function assigned to the sessions.
clicklag BOOLEAN Contains 't' if the session exceeded min_human_click_lag, 'f' otherwise.