Firebird Documentation Index → Firebird 3.0.6 Release Notes → Data Manipulation Language (DML) → Internal Functions |
Additions and enhancements to the internal functions set are:
A substring search can now use a regular expression.
Search Pattern
SUBSTRING(<string> [NOT] SIMILAR TO <pattern> ESCAPE <char>)
Discussion: TrackerCORE-2006
For more information about the use of SIMILAR TO expressions, refer to README.similar_to.txt
in the /doc/ subdirectory of your Firebird installation.
The regex used is the SQL one. A guide is available in the DML chapter of the v.2.5 release notes and also at the Firebird web site.
The six inverse hyperbolic trigonometric functions have been implemented internally. They are:
Returns the hyperbolic arc cosine of a number (expressed in radians). Format: ACOSH( <number> )
Returns the hyperbolic arc sine of a number (expressed in radians). Format: ASINH( <number> )
Returns the hyperbolic arc tangent of a number (expressed in radians). Format: ATANH( <number> )
Returns the hyperbolic cosine of an angle (expressed in radians). Format: COSH( <number> )
Returns the hyperbolic sine of an angle (expressed in radians). Format: SINH( <number> )
Returns the hyperbolic tangent of an angle (expressed in radians). Format: TANH( <number> )
A suite of SQL-standards-compliant statistical functions has been implemented.
Functions comprise Sample Variance, Population Variance, Sample Standard Deviation, Population Standard Deviation, Sample Population, Population Covariance and Coefficient of Correlation. See Tracker ticket CORE-4717.
Syntax
<single param statistical function> ::= <single param statistical function name>(<expr>) <single param statistical function name> := { VAR_POP | VAR_SAMP | STDDEV_POP | STDDEV_SAMP } <dual param statistical function> ::= <dual param statistical function name>(<expr1>, <expr>>) <dual param statistical function name> := { COVAR_POP | COVAR_SAMP | CORR }
NULL is returned from VAR_SAMP, STDDEV_SAMP or COVAR_SAMP if the result count is 0 or 1
NULL is returned from VAR_POP, STDDEV_POP, COVAR_POP or CORR if the result count is 0
Syntax
SELECT STDDEV_SAMP(salary) FROM employees;
SD/Variance Function Descriptions
Function | Format | Description |
---|---|---|
VAR_SAMP | VAR_SAMP( <expr> ) | Returns the Sample Variance, equivalent to |
(SUM(<expr> ^ 2) - SUM(<expr>) ^ 2 / COUNT(<expr>)) / (COUNT(<expr>) - 1) |
||
VAR_POP | VAR_POP( <expr> ) | Returns the the Population Variance, equivalent to |
(SUM(<expr> ^ 2) - SUM(<expr>) ^ 2 / COUNT(<expr>)) / COUNT(<expr>) |
||
STDDEV_SAMP | STDDEV_SAMP( <expr> ) | Returns the Sample Standard Deviation, equivalent to |
SQRT(VAR_SAMP(<expr>)) |
||
STDDEV_POP | STDDEV_POP( <expr> ) | Returns the Population Standard Deviation, equivalent to |
SQRT(VAR_POP(<expr>)) |
||
COVAR_SAMP | COVAR_SAMP( <expr1>, <expr2> ) | Returns the Sample Population, equivalent to |
(SUM(<expr1> * <expr2>) - SUM(<expr1>) * SUM(<expr2>) / COUNT(*)) / (COUNT(*) - 1) |
||
COVAR_POP | COVAR_POP( <expr1>, <expr2> ) | Returns the Population Covariance, equivalent to |
(SUM(<expr1> * <expr2>) - SUM(<expr1>) * SUM(<expr2>) / COUNT(*)) / COUNT(*) |
||
CORR | CORR( <expr1>, <expr2> ) | Returns the Coefficient of Correlation, equivalent to |
COVAR_POP(<expr1>, <expr2>) / (STDDEV_POP(<expr2>) * STDDEV_POP(<expr1>)) |
The suite of REGR_* functions analyses the relationships between two sets of numeric data, considering only sets that are not NULL in either expression. See Tracker ticket CORE-4722.
Syntax
<regr function> ::= <function name>(<expr1>, <expr2>) <function name> := { REGR_AVGX | REGR_AVGY | REGR_COUNT | REGR_INTERCEPT | REGR_R2 | REGR_SLOPE | REGR_SXX | REGR_SXY | REGR_SYY }
Formulae
The formulae use the following variables:
Y: <expr1> (<expr1> IS NOT NULL AND <expr2> IS NOT NULL) X: <expr2> (<expr1> IS NOT NULL AND <expr2> IS NOT NULL) N: COUNT of recordset unless <expr1> IS NULL OR <expr2> IS NULL
Y and X are DOUBLE PRECISION. N is SMALLINT, INTEGER or BIGINT.
All functions eliminate expression pairs where either expression in the pair is NULL. If no rows remain, the functions (except REGR_COUNT()) return NULL.
Linear Regression Function Descriptions
Function | Format | Description |
---|---|---|
REGR_AVGX | REGR_AVGX(Y, X) | Returns the average of the independent expression (Y) in the expression pair. The return value is of type DOUBLE PRECISION. |
Formula: REGR_AVGX(Y, X) = SUM(X) / N |
||
REGR_AVGY | REGR_AVGY(Y, X) | Returns the average of the dependent expression (X) in the expression pair. The return value is of type DOUBLE PRECISION. |
Formula: REGR_AVGY(Y, X) = SUM(Y) / N |
||
REGR_COUNT | REGR_COUNT(Y, X) | Returns the number of expression pairs (Y and X). The return value is of type SMALLINT, INTEGER or BIGINT. If no rows remain after elimination of pairs where either expression is NULL, the function returns 0. |
Formula: REGR_COUNT(Y, X) = N |
||
REGR_INTERCEPT | REGR_INTERCEPT(Y, X) | Returns the y-intercept of the regression line determined by a set of expression pairs (Y and X). |
Formula: REGR_INTERCEPT(Y, X) = REGR_AVGY(Y, X) - REGR_SLOPE(Y, X) * REGR_AVGX(Y, X) |
||
REGR_R2 | REGR_R2(Y, X) | Returns the square of the correlation coefficient of a set of expression pairs (Y and X). |
Formula: REGR_R2(Y, X) = POWER(CORR(Y, X),2) |
||
REGR_SLOPE | REGR_SLOPE(Y, X) | Returns the slope of the regression line, determined by a set of expression pairs (Y and X). |
Formula: REGR_SLOPE(Y, X) = COVAR_POP(Y, X) / VAR_POP(X) |
||
REGR_SXX | REGR_SXX(Y, X) | Returns the sum of squares of the independent expression (Y) in an expression pair (Y and X). |
Formula: REGR_SXX(Y, X) = N * VAR_POP(X) |
||
REGR_SXY | REGR_SXY(Y, X) | Returns the sum of products of the independent expression multiplied by the dependent expression in an expression pair (Y and X). |
Formula: REGR_SXY(Y, X) = N * COVAR_POP(Y, X) |
||
REGR_SYY | REGR_SYY(Y, X) | Returns the sum of squares of the dependent expression in an expression pair (Y and X). |
Formula: REGR_SYY(Y, X) = N * VAR_POP(Y) |
In prior versions, TRIM(substring from string) allowed BLOBs for both arguments, but the first argument had to be smaller than 32 KB. Now both arguments can take BLOBs of up to 4 GB.
The internal length of a string can, at some levels, be almost 64 KB. Tests demonstrated that it is safe to accept a string literal of up to that size for writing to a text BLOB. Accordingly,
The (32KB - 3) “safety limit” on literal string length for writing to text BLOBs has been raised to 65,533 bytes (64KB - 3);
A limit, in characters, is calculated in run-time for strings that are in multi-byte character sets, to avoid overrunning the bytes limit. For example, for a UTF8 string (max. 4 bytes/character) the run-time limit is likely to be about (floor (65533/4)) = 16383 characters.
See Tracker ticket CORE-4881.
Firebird Documentation Index → Firebird 3.0.6 Release Notes → Data Manipulation Language (DML) → Internal Functions |