... |
Teiid supports the following aggregate functions: |
* COUNT\(*) -- count the number of values (including nulls and duplicates) in a group |
* COUNT\(*) -- count the number of values (including nulls and duplicates) in a group. Returns an integer - an exception will be thrown if a larger count is computed. |
|
* COUNT\(x) -- count the number of values (excluding nulls) in a group |
* COUNT\(x) -- count the number of values (excluding nulls) in a group. Returns an integer - an exception will be thrown if a larger count is computed. |
* SUM\(x) -- sum of the values (excluding nulls) in a group |
... |
* DENSE_RANK() -- Assigns a number to each unique ordering value within each partition starting at 1, such that the next rank is sequential. |
All values are integers - an exception will be thrown if a larger value is needed. |
h2. Processing |
... |
Identifiers, literals, and functions can be combined into expressions. Expressions can be used almost anywhere in a query -- SELECT, FROM (if specifying join criteria), WHERE, GROUP BY, HAVING, or ORDER BY.
Teiid supports the following types of expressions:
Column identifiers are used to specify the output columns in SELECT statements, the columns and their values for INSERT and UPDATE statements, and criteria used in WHERE and FROM clauses. They are also used in GROUP BY, HAVING, and ORDER BY clauses. The syntax for column identifiers was defined in the Identifiers section above.
Literal values represent fixed values. These can any of the 'standard' data types.
Syntax Rules:
Some examples of simple literal values are:
'abc'
'isn''t true'
5
-37.75e01
100.0
true
false
'\u0027'
X'0F0A'
Date/Time Literals can use either JDBC Escaped Literal Syntax:
{d'...'}
{t'...'}
{ts'...'}
Or the ANSI keyword syntax:
DATE '...'
TIME '...'
TIMESTAMP '...'
Either way the string literal value portion of the expression is expected to follow the defined format - "yyyy-MM-dd" for date, "hh:mm:ss" for time, and "yyyy-MM-dd[ hh:mm:ss[.fff...]]" for timestamp.
Aggregate functions take sets of values from a group produced by an explicit or implicit GROUP BY and return a single scalar value computed from the group.
Teiid supports the following aggregate functions:
TEXTAGG(col1, col2 as name DELIMITER '|' HEADER ORDER BY col1)
jsonArray_Agg(col1 order by col1 nulls first)
[null,null,1,2,3]
string_agg(col1, ',' ORDER BY col1 ASC)
'a,b,c'
Syntax Rules:
FILTER ( WHERE condition )
The condition may be any boolean value expression that does not contain a subquery or a correlated variable. The filter will logically be evaluated for each row prior to the grouping operation. If false the aggregate function will not accumulate a value for the given row.
For more information on aggregates, see the sections on GROUP BY or HAVING.
Teiid supports ANSI SQL 2003 window functions. A window function allows an aggregate function to be applied to a subset of the result set, without the need for a GROUP BY clause. A window function is similar to an aggregate function, but requires the use of an OVER clause or window specification.
Usage:
aggregate|ranking OVER ([PARTITION BY ...]] [ORDER BY ...])
aggregate can be any Aggregate Functions. Ranking can be one of ROW_NUMBER(), RANK(), DENSE_RANK().
Syntax Rules:
All values are integers - an exception will be thrown if a larger value is needed.
Window functions are logically processed just before creating the output from the SELECT clause. Window functions can use nested aggregates if a GROUP BY clause is present. The is no guaranteed affect on the output ordering from the presence of window functions. The SELECT statement must have an ORDER BY clause to have a predictable ordering.
Teiid will process all window functions with the same window specification together. In general a full pass over the row values coming into the SELECT clause will be required for each unique window specification. For each window specification the values will be grouped according to the PARTITION BY clause. If no PARTITION BY clause is specified, then the entire input is treated as a single partition. The output value is determined based upon the current row value, it's peers (that is rows that are the same with respect to their ordering), and all prior row values based upon ordering in the partition. The ROW_NUMBER function will assign a unique value to every row regardless of the number of peers.
SELECT name, salary, max(salary) over (partition by name) as max_sal, rank() over (order by salary) as rank, dense_rank() over (order by salary) as dense_rank, row_number() over (order by salary) as row_num FROM employees
name | salary | max_sal | rank | dense_rank | row_num |
---|---|---|---|---|---|
John | 100000 | 100000 | 2 | 2 | 2 |
Henry | 50000 | 50000 | 5 | 4 | 5 |
John | 60000 | 100000 | 3 | 3 | 3 |
Suzie | 60000 | 150000 | 3 | 3 | 4 |
Suzie | 150000 | 150000 | 1 | 1 | 1 |
Teiid supports two forms of the CASE expression which allows conditional logic in a scalar expression.
Supported forms:
Each form allows for an output based on conditional logic. The first form starts with an initial expression and evaluates WHEN expressions until the values match, and outputs the THEN expression. If no WHEN is matched, the ELSE expression is output. If no WHEN is matched and no ELSE is specified, a null literal value is output. The second form (the searched case expression) searches the WHEN clauses, which specify an arbitrary criteria to evaluate. If any criteria evaluates to true, the THEN expression is evaluated and output. If no WHEN is true, the ELSE is evaluated or NULL is output if none exists.
Subqueries can be used to produce a single scalar value in the SELECT, WHERE, or HAVING clauses only. A scalar subquery must have a single column in the SELECT clause and should return either 0 or 1 row. If no rows are returned, null will be returned as the scalar subquery value. For other types of subqueries, see the Subqueries section.
Parameters are specified using a '?' symbol. Parameters may only be used with PreparedStatement or CallableStatements in JDBC. Each parameter is linked to a value specified by 1-based index in the JDBC API.
Array values may be constructed using parenthesis around an expression list with an optional trailing comma.
() (,)
(expr,)
![]() | A trailing comma is required for the parser to recognize a single element expression as an array, rather than a simple nested expression. |
(expr, expr ... [,])
If all of the elements in the array have the same type, the array will have a matching base type. If the element types differ the array base type will be object.
An array element reference takes the form of:
array_expr[index_expr]
index_expr must resolve to an integer value. This syntax is effectively the same as the array_get system function and expects 1-based indexing.
Teiid parses and evaluates operators with higher precedence before those with lower precedence. Operator with equal precedence are left associative. Operator precedence listed from high to low:
Operator | Description |
---|---|
[] | array element reference |
+,- | positive/negative value expression |
*,/ | multiplication/division |
+,- | addition/subtraction |
|| | concat |
criteria | see Criteria |