Miscellaneous Information

This section lists the supported aggregate functions, data types and reserved words in Sclera' SQL.

Aggregate Functions◄

The aggregate functions supported in Sclera can be order-insensitive, which apply to both ordered and unordered data, or order-sensitive, which apply only to ordered data.

Order-Insensitive Aggregate Functions◄

These functions can be used in standard SQL aggregates as well as in running aggregates on ordered data (table adapted from PostgreSQL documentation):

Aggregate	Argument Type	Return Type	Description
`avg(expression)`	any numeric type	`FLOAT`	the average (arithmetic mean) of all input values
`bool_and(expression)`	`BOOLEAN`	`BOOLEAN`	true if all input values are true, otherwise false
`bool_or(expression)`	`BOOLEAN`	`BOOLEAN`	true if at least one input value is true, otherwise false
`count(*)`		`BIGINT`	number of input rows
`count(expression)`	any type	`BIGINT`	number of input rows for which the value of expression is not null
`every(expression)`	`BOOLEAN`	`BOOLEAN`	equivalent to bool_and
`max(expression)`	any numeric, string, or date/time type	same as argument type	maximum value of expression across all input values
`min(expression)`	any numeric, string, or date/time type	same as argument type	minimum value of expression across all input values
`sum(expression)`	any numeric type	`BIGINT` for `SMALLINT` or `INTEGER` arguments, `FLOAT` for floating-point arguments	sum of expression across all input values
`corr(Y, X)`	any numeric type	`FLOAT`	correlation coefficient
`covar_pop(Y, X)`	any numeric type	`FLOAT`	population covariance
`covar_samp(Y, X)`	any numeric type	`FLOAT`	sample covariance
`regr_avgx(Y, X)`	any numeric type	`FLOAT`	average of the independent variable `(sum(X)/N)`
`regr_avgy(Y, X)`	any numeric type	`FLOAT`	average of the dependent variable `(sum(Y)/N)`
`regr_count(Y, X)`	any numeric type	`BIGINT`	number of input rows in which both expressions are nonnull
`regr_intercept(Y, X)`	any numeric type	`FLOAT`	y-intercept of the least-squares-fit linear equation determined by the `(X, Y)` pairs
`regr_r2(Y, X)`	any numeric type	`FLOAT`	the coefficient of determination (also called R-squared or goodness of fit)
`regr_slope(Y, X)`	any numeric type	`FLOAT`	slope of the least-squares-fit linear equation determined by the `(X, Y)` pairs
`regr_sxx(Y, X)`	any numeric type	`FLOAT`	`sum(X^2) - sum(X)^2/N` ("sum of squares" of the independent variable)
`regr_sxy(Y, X)`	any numeric type	`FLOAT`	`sum(XY) - sum(X)sum(Y)/N` ("sum of products" of independent times dependent variable)
`regr_syy(Y, X)`	any numeric type	`FLOAT`	`sum(Y^2) - sum(Y)^2/N` ("sum of squares" of the dependent variable)
`stddev(expression)`	any numeric type	`FLOAT`	historical alias for stddev_samp
`stddev_pop(expression)`	any numeric type	`FLOAT`	population standard deviation of the input values
`stddev_samp(expression)`	any numeric type	`FLOAT`	sample standard deviation of the input values
`variance(expression)`	any numeric type	`FLOAT`	historical alias for var_samp
`var_pop(expression)`	any numeric type	`FLOAT`	population variance of the input values (square of the population standard deviation)
`var_samp(expression)`	any numeric type	`FLOAT`	sample variance of the input values (square of the sample standard deviation)

Order-Sensitive Aggregate Functions◄

These functions can only be used in running aggregates on ordered data (table adapted from PostgreSQL documentation):

Function	Return Type	Description
`row_number()`	`BIGINT`	number of the current row within its partition, counting from 1
`rank()`	`BIGINT`	rank of the current row with gaps; same as row_number of its first peer
`dense_rank()`	`BIGINT`	rank of the current row without gaps; this function counts peer groups
`percent_rank()`	`FLOAT`	relative rank of the current row: `(rank - 1) / (total rows - 1)`
`cume_dist()`	`FLOAT`	relative rank of the current row: (number of rows preceding or peer with current row) / (total rows)
`ntile(num_buckets INTEGER)`	`INTEGER`	integer ranging from 1 to the argument value, dividing the partition as equally as possible
`lag(value ANY [, offset INTEGER [, default ANY ]])`	same type as value	returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null
`first_value(value ANY)`	same type as value	returns value evaluated at the row that is the first row of the window frame
`last_value(value ANY)`	same type as value	returns value evaluated at the row that is the last row of the window frame
`nth_value(value ANY, nth INTEGER)`	same type as value	returns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row
`string_agg(expression ANY, delimiter VARCHAR)`	`CHAR(n)`	input values concatenated into a string, separated by delimiter

Data Types◄

Sclera supports a subset of PostgreSQL data types:

BIGINT, BOOL or BOOLEAN, CHAR(n), CHAR, DATE, DECIMAL(prec), DECIMAL(prec, scale), DECIMAL, FLOAT(prec), FLOAT, INT or INTEGER, NUMERIC(prec), NUMERIC(prec, scale), NUMERIC, REAL, SMALLINT, TEXT, TIMESTAMP, TIME, VARCHAR(n), VARCHAR.

Note that ARRAY and other composite data types are not supported.

Reserved Keywords◄

The following cannot be used as a name or alias of any object within a SQL command:

ADD, ALL, ALTER, AND, ANY, ARG, AS, ASC, ASSOCIATOR, BETWEEN, BIGINT, BIT, BOOL, BOOLEAN, BPCHAR, BY, CASE, CAST, CHAR, CLASSIFIED, CLASSIFIER, CLUSTERED, CLUSTERER, COLUMN, CONNECTED, CREATE, CROSS, DATE, DAY, DECIMAL, DELETE, DESC, DISTINCT, DROP, ELSE, END, ESCAPE, EXCEPT, EXISTS, EXTERNAL, FALSE, FETCH, FIRST, FLAG, FLOAT, FOREIGN, FROM, FULL, GRAPH, GROUP, HAVING, HOUR, ILIKE, IMPUTED, IN, INNER, INSERT, INT, INTEGER, INTERSECT, INTERVAL, INTO, IS, ISNULL, JOIN, KEY, LABEL, LAST, LEFT, LIKE, LIMIT, LOCATION, MATCH, MINUTE, MONTH, NATURAL, NEXT, NOT, NOTNULL, NULL, NULLS, NUMERIC, OF, OFFSET, ON, ONLY, OR, ORDER, ORDERED, OUTER, OVER, PARTITION, PIVOT, PREDICTOR, PRIMARY, READONLY, REAL, REFERENCES, REMOVE, RIGHT, ROW, ROWS, SCHEMA, SECOND, SELECT, SET, SIMILAR, SMALLINT, SOME, SYMMETRIC, TABLE, TEMP, TEMPORARY, TEXT, THEN, TIME, TIMESTAMP, TO, TRUE, UNION, UNKNOWN, UPDATE, USING, VALUES, VARBIT, VARCHAR, VARCHAR2, VARYING, VIEW, WHEN, WHERE, WITH, WITHOUT, YEAR, ZONE.