Luminesce SQL keywords

The following table lists keywords valid in Luminesce SQL queries.

Most keywords are inherited from the SQLite dialect of SQL. Some keywords are proprietary extensions.

Keyword

Explanation

ALL

SQLite keywords defined at https://www.sqlite.org/lang_keywords.html

AND

AS

ASC

BETWEEN

NOTBETWEEN

BY

CASE

CAST

CHECK

COLLATE

COLLATENOACCENT

COLLATENOCASE

COLLATEEXACT

CROSS

CURRENT

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

DESC

DISTINCT

ELSE

END

EXCEPT

EXCLUDE

EXISTS

FALSE

FILTER

FIRST

FOLLOWING

FROM

FULL

GLOB

NOTGLOB

GROUP

GROUPS

HAVING

IN

NOTIN

INNER

INTERSECT

IS

ISNOT

ISNULL

ISNOTNULL

JOIN

LAST

LEFT

LIKE

NOTLIKE

LIMIT

NO

NOT

NULL

NULLS

OFFSET

ON

OR

ORDER

OTHERS

OUTER

OVER

PARTITION

PRAGMA

PRECEDING

RANGE

REGEXP

NOTREGEXP

REPLACE

ROW

ROWS

SELECT

THEN

TIES

TO

TRUE

TYPES

UNBOUNDED

UNION

USING

VALUES

WHEN

WHERE

WINDOW

WITH

USE

FINBOURNE keywords

ENDUSE

APPLY

WAIT

BIGINT

Types used by the TYPES keyword (more information)

INT

BOOLEAN

DOUBLE

DECIMAL

TEXT

ABS

SQLite scalar functions defined at https://www.sqlite.org/lang_corefunc.html

CHAR

COALESCE

HEX

IIF

IFNULL

INSTR

LENGTH

LIKELY

LOWER

LTRIM

MAX

MIN

NULLIF

PRINTF

QUOTE

RANDOM

RANDOMBLOB

ROUND

RTRIM

SOUNDEX

SUBSTR

TRIM

TYPEOF

UNICODE

UNLIKELY

UPPER

ZEROBLOB

DATE

SQLite datetime functions defined at https://www.sqlite.org/lang_datefunc.html

TIME

DATETIME

JULIANDAY

STRFTIME

AVG

SQLite aggregate functions defined at https://www.sqlite.org/lang_aggfunc.html

COUNT

GROUP_CONCAT

SUM

TOTAL

ROW_NUMBER

SQLite window functions defined at https://www.sqlite.org/windowfunctions.html

RANK

DENSE_RANK

PERCENT_RANK

CUME_DIST

NTILE

LAG

LEAD

FIRST_VALUE

LAST_VALUE

NTH_VALUE

STDEV

SQLite extension functions available from https://www.sqlite.org/contrib/download/extension-functions.c?get=25 

VARIANCE

MODE

MEDIAN

LOWER_QUARTILE

UPPER_QUARTILE

REPLICATE

LEFTSTR

RIGHTSTR

REVERSE

PROPER

PADL

PADR

PADC

STRFITLER

CHARINDEX

LOG

LOG10

EXP

DIFFERENCE

POWER

SIGN

SQRT

SQUARE

CEIL

FLOOR

JSON

SQL JSON functions defined at https://www.sqlite.org/json1.html

JSON_ARRAY

JSON_ARRAY_LENGTH

JSON_EXTRACT

JSON_INSERT

JSON_OBJECT

JSON_PATCH

JSON_QUOTE

JSON_REMOVE

JSON_TYPE

JSON_VALID

JSON_GROUP_ARRAY

JSON_GROUP_OBJECT

QUANTILE

FINBOURNE functions (general and statistical)

CUMEPROD

SKEWNESS

KURTOSIS

ROOT_MEAN_SQUARE

HARMONIC_MEAN

GEOMETRIC_MEAN

ENTROPY

EMPIRICAL_CUME_DIST_FUNCTION

INTERQUARTILE_RANGE

INTERQUANTILE_RANGE

PEARSON_CORRELATION

SPEARMAN_RANK_CORRELATION

MEAN_SQUARED_ERROR

MEAN_ABSOLUTE_ERROR

MEDIAN_ABSOLUTE_DEVIATION

MEAN_FRACTIONAL_ABSOLUTE_ERROR

GAIN_LOSS_RATIO

SEMI_DEVIATION

DRAWDOWN

MAX_DRAWDOWN

MEAN_DRAWDOWN

DRAWDOWN_LENGTH

MAX_DRAWDOWN_LENGTH

MEAN_DRAWDOWN_LENGTH

COVARIANCE

WINDOW_STDEV

LINEAR_REGRESSION_ALPHA

LINEAR_REGRESSION_BETA

LINEAR_REGRESSION_ALPHA_ERROR

LINEAR_REGRESSION_BETA_ERROR

PRICES_TO_RETURNS

RETURNS_TO_PRICES

MEAN_STDEV_RATIO

COEFFICIENT_OF_VARIATION

MINKOWSKI_DISTANCE

CHEBYSHEV_DISTANCE

MANHATTAN_DISTANCE

EUCLIDEAN_DISTANCE

CANBERRA_DISTANCE

BRAYCURTIS_DISTANCE

COSINE_DISTANCE

PRECISION_SCORE

RECALL_SCORE

FBETA_SCORE

R_SQUARED

ADJUSTED_R_SQUARED

TO_DATE

THROW

PRINT

REGEXP_MATCH

REGEXP_MATCHES

REGEXP_MATCH_LOCATION

REGEXP_REPLACE

DAY_COUNT_DISPLAY

NOOP_FOR_TRANSLATION

CHECK_ACCESS

CHECK_REGISTRATION_ACCESS

EDIT_DISTANCE

IS_DATE

IS_NUMERIC

IS_INTEGER

TO_UTC

TO_ISO

FROM_UTC

CONVERT_TIMEZONE

GENERATE_HASH

CONTAINS_TOKEN

CONTAINS_TOKEN_STARTING_WITH