Transforming and Analyzing Data in s-Server

<< Click to Display Table of Contents >>

Navigation:  Understanding Streaming SQL Concepts > SQLstream Overview >

Transforming and Analyzing Data in s-Server

Previous pageReturn to chapter overviewNext page

s-Server provides a ranges of ways to transform and analyze data. These include scalar functions, aggregate functions, analytics functions, UDXes, UDFs, and operators.

Description

Available Features

Scalar functions return a single row for each input row to a query.

ABS, CAST, CEIL / CEILING, CHAR_LENGTH / CHARACTER_LENGTH, COALESCE, CURRENT_DATE, CURRENT_PATH, CURRENT_ROW_TIMESTAMP, CURRENT_TIMESTAMP, EXP, EXTRACT, FLOOR, INITCAP, LN, LOCALTIME, LOCALTIMESTAMP, LOG10, LOWER, MOD, NULLIF, OVERLAY, POSITION, POWER, SUBSTRING, SUBSTRING Regex, SUBSTRING with Escape, SYSTEM_USER, TRIM

 

An operator manipulates individual data items and returns a result. s-Server represents operators by special characters or by keywords. For example, s-Server represents the multiplication operator with an asterisk (*) and the operator that tests for nulls with the keywords IS NULL.

 

String Operators: ||, LIKE, SIMILAR TO

Date, Timestamp, and Interval Operators: Addition (+), Subtraction (-), Multiplication (*), Division (/)

Logical Operators: NOT, AND, OR, IS, IS NOT UNKNOWN, IS NULL, IS NOT NULL, =, !=, <>, >, >=, <=, BETWEEN, IS DISTINCT FROM, IS NOT DISTINCT FROM

 

Complex Event Processing/Temporal Predicate Operators let you analyze complicated, long-running events in streams of data.

CONTAINS, OVERLAPS, PRECEDES, SUCCEEDS, IMMEDIATELY PRECEDES, IMMEDIATELY SUCCEEDS, LEADS, LAGS, STRICTLY CONTAINS, STRICTLY OVERLAPS, STRICTLY PRECEDES, STRICTLY SUCCEEDS, STRICTLY LEADS, STRICTLY LAGS, IMMEDIATELY LEADS

 

An aggregate function returns a result aggregated from data contained in a set of rows, or from information about a set of rows. An aggregate function may appear in the <selection list> portion of a SELECT clause, an ORDER BY clause, or a HAVING clause.

 

AVG, COUNT, MAX, MIN, SUM, VAR_POP(expr), STDDEV_POP(expr), VAR_SAMP(expr), STDDEV_SAMP(expr)

Analytic functions calculate results from a set of rows defined in a window specification. The use of a finite set of rows is similar to aggregate functions. However, analytic functions must specify a window and analytic functions may only appear in the <selection list> portion of a SELECT clause or in the ORDER BY clause.

 

AVG, COUNT, FIRST_VALUE, LAST_VALUE, LAG, MAX, MIN, SUM, STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP

User Defined Transforms (UDXes) and User Defined Functions (UDFs) operate in the middle of processing a stream. UDXes return streaming results, whereas UDFs return scalar results.

SystemML Machine Learning Prediction UDX, DataRobot Machine Learning Prediction UDX, Kalman Filter UDX, Linear Interpolation UDX, Quadratic Interpolation UDX, Matched Filter UDX, Parser UDX, AesEncryptDecrypt UDX, Group Rank UDX, Table Lookup UDX, XML Parse UDX, URI Parse UDX, GeoIPFunctions UDF

 

Major statement operators let you select from, insert into, update, merge, delete, order, group, join, and otherwise manipulate streams of data.

SELECT, INSERT, UPDATE, MERGE, DELETE, ORDER BY, GROUP BY, WHERE, EXCEPT, INTERSECT, UNION, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN