--- title: "Luminesce SQL keywords" slug: "luminesce-sql-keywords" tags: ["luminesce ", "function"] updated: 2026-10-20T13:09:00Z published: 2026-10-20T13:09:00Z canonical: "support.lusid.com/luminesce-sql-keywords" --- > ## Documentation Index > Fetch the complete documentation index at: https://support.lusid.com/llms.txt > Use this file to discover all available pages before exploring further. # Luminesce SQL keywords The following tables list every keyword valid in [Luminesce SQL queries](/v1/docs/understanding-the-luminesce-sql-query-syntax), with a short description and example of each. Most keywords are inherited from the SQLite dialect of SQL. Some are FINBOURNE extensions. Keywords are not case-sensitive. They are reserved, so to use one as a column or table name, wrap it in `[]`, for example `select [Date], [Text] from @x`. The exception is `FORCE`, which you can use as a name without brackets (new key words in the future will be like this whenever possible to avoid causing issues with existing queries that might have such words in them). ## Query clauses The building blocks of a `SELECT` statement. See [Understanding the Luminesce SQL query syntax](/v1/docs/understanding-the-luminesce-sql-query-syntax) for more. | **Keyword** | **Description** | **Example** | | --- | --- | --- | | `SELECT` | Starts a query and lists the columns to return. | `select Name, Age from @people` | | `FROM` | Names the provider, table variable or subquery to read from. | `select * from Lusid.Portfolio` | | `WHERE` | Keeps only the rows that match a condition. Also sets provider parameters. | `select * from @x where Age > 30` | | `GROUP` | With `BY`, groups rows for aggregate functions. Also forms `EXCLUDE GROUP` in a window frame. | `select Dept, count(*) from @x group by Dept` | | `BY` | Used in `GROUP BY`, `ORDER BY` and `PARTITION BY`. | `select * from @x order by Name` | | `HAVING` | Filters groups after `GROUP BY`. | `select Dept from @x group by Dept having count(*) > 5` | | `ORDER` | With `BY`, sorts the result. | `select * from @x order by Name` | | `ASC` | Sorts in ascending order (the default). | `order by Name asc` | | `DESC` | Sorts in descending order. | `order by Age desc` | | `NULLS` | With `FIRST` or `LAST`, puts null values at the start or end of a sort. | `order by Age nulls last` | | `FIRST` | Used in `NULLS FIRST`. | `order by Age desc nulls first` | | `LAST` | Used in `NULLS LAST`. | `order by Age nulls last` | | `LIMIT` | Returns at most the given number of rows. | `select * from @x limit 10` | | `OFFSET` | Skips the given number of rows before returning any. | `select * from @x limit 10 offset 20` | | `DISTINCT` | Removes duplicate rows, or duplicate values inside an aggregate function. | `select distinct Dept from @x` | | `ALL` | Keeps duplicate rows. Mostly used in `UNION ALL`. | `select a from @x union all select a from @y` | | `AS` | Names a column, table or window. Also sets the type in `CAST`. | `select Age as Years from @x` | | `VALUES` | Builds a table from literal rows. | `@x = values ('a', 1), ('b', 2);` | | `UNION` | Combines two results and removes duplicate rows. | `select a from @x union select a from @y` | | `INTERSECT` | Returns the rows that appear in both results. | `select a from @x intersect select a from @y` | | `EXCEPT` | Returns the rows in the first result that are not in the second. In `select * (except ...)`, leaves out the named columns. | `select * (except Age) from @x` | | `EXISTS` | True if a subquery returns at least one row. | `select * from @x where exists (select 1 from @y)` | | `PRAGMA` | Changes a setting for the current query. See [PRAGMAs](/v1/docs/understanding-the-luminesce-sql-query-syntax#pragmas). | `pragma TimeoutSec = 1800;` | | `EXPLAIN` | With `QUERY PLAN`, reports SQLite's plan for a statement as progress messages. The query still runs. | `explain query plan select * from @x` | | `QUERY` | Used in `EXPLAIN QUERY PLAN`. | `explain query plan select * from @x` | | `PLAN` | Used in `EXPLAIN QUERY PLAN`. | `explain query plan select * from @x` | ## Joins Luminesce supports inner, left, full and cross joins, but not right joins. See [JOIN clauses](/v1/docs/understanding-the-luminesce-sql-query-syntax#join-clauses). | **Keyword** | **Description** | **Example** | | --- | --- | --- | | `JOIN` | Combines rows from two sources. | `select * from @a a join @b b on a.Id = b.Id` | | `INNER` | Returns only the rows that match on both sides. This is the default join. | `@a a inner join @b b on a.Id = b.Id` | | `LEFT` | Returns every row from the left side, with nulls where the right side has no match. | `@a a left join @b b on a.Id = b.Id` | | `OUTER` | Optional in `LEFT OUTER JOIN` and `FULL OUTER JOIN`. Also forms `OUTER APPLY`. | `@a a left outer join @b b on a.Id = b.Id` | | `FULL` | Returns every row from both sides, with nulls where either side has no match. | `@a a full join @b b on a.Id = b.Id` | | `CROSS` | Pairs every row on the left with every row on the right. Also forms `CROSS APPLY`. | `select * from @a cross join @b` | | `ON` | Sets the join condition. | `@a a join @b b on a.Id = b.Id` | | `USING` | Joins on columns that have the same name on both sides. | `@a join @b using (Id)` | ## Operators and expressions Text comparisons in Luminesce ignore case and accents unless you use `COLLATE EXACT`. | **Keyword** | **Description** | **Example** | | --- | --- | --- | | `AND` | True if both conditions are true. | `where Age > 30 and Dept = 'IT'` | | `OR` | True if either condition is true. | `where Dept = 'IT' or Dept = 'HR'` | | `NOT` | Reverses a condition. | `where not Active` | | `IN` | True if a value is in a list, a subquery or a table variable. | `where Dept in ('IT', 'HR')` | | `NOT IN` | True if a value is not in a list, a subquery or a table variable. | `where Id not in @excluded` | | `IS` | Compares two values, treating two nulls as equal. | `where a is b` | | `IS NOT` | The opposite of `IS`. | `where a is not b` | | `IS NULL` | True if a value is null. The SQLite shorthand `x ISNULL` is not supported. | `where Nickname is null` | | `IS NOT NULL` | True if a value is not null. The SQLite shorthand `x NOTNULL` is not supported. | `where Nickname is not null` | | `BETWEEN` | True if a value is in a range, including both ends. | `where Age between 30 and 40` | | `NOT BETWEEN` | True if a value is outside a range. | `where Age not between 30 and 40` | | `LIKE` | Matches a pattern, where `%` is any run of characters and `_` is one character. | `where Name like 'Jo%'` | | `NOT LIKE` | True if a value does not match a `LIKE` pattern. | `where Name not like 'Jo%'` | | `ESCAPE` | Sets the character that makes `%` or `_` literal in a `LIKE` pattern. | `where Code like '10\%%' escape '\'` | | `GLOB` | Matches a Unix-style pattern, where `*` is any run of characters and `?` is one character. | `where FileName glob '*.csv'` | | `NOT GLOB` | True if a value does not match a `GLOB` pattern. | `where FileName not glob '*.tmp'` | | `REGEXP` | Matches a regular expression. See [REGEXP](/v1/docs/supported-functions-in-luminesce-sql-queries#regexp). | `where Code regexp '[A-Z]+-[0-9]+'` | | `NOT REGEXP` | True if a value does not match a regular expression. | `where Code not regexp '[0-9]'` | | `CASE` | Starts a conditional expression. | `case when Age < 30 then 'young' else 'old' end` | | `WHEN` | Gives a condition in a `CASE` expression. | `case when Age < 30 then 'young' end` | | `THEN` | Gives the result for a `WHEN` condition. | `case when Age < 30 then 'young' end` | | `ELSE` | Gives the result when no `WHEN` condition is true. | `case when Age < 30 then 'young' else 'old' end` | | `END` | Ends a `CASE` expression. | `case when Age < 30 then 'young' end` | | `CAST` | Converts a value to another type. | `cast(Age as text)` | | `COLLATE NOCASE` | Compares text ignoring case, but not accents. | `where Name = 'JOSE' collate nocase` | | `COLLATE NOACCENT` | Compares text ignoring both case and accents. This is the default. | `where Name = 'jose' collate noaccent` | | `COLLATE EXACT` | Compares text exactly, respecting case and accents. | `where Name = 'José' collate exact` | | `NULL` | A missing value. | `select null as Nothing` | | `TRUE` | The boolean value true. | `where Active = true` | | `FALSE` | The boolean value false. | `where Active = false` | | `CURRENT_DATE` | The current date, in UTC. | `select current_date` | | `CURRENT_TIME` | The current time, in UTC. | `select current_time` | | `CURRENT_TIMESTAMP` | The current date and time, in UTC. | `select current_timestamp` | ## Window definitions Used with `OVER` to define the rows that a window function works on. See the [SQLite window functions documentation](https://www.sqlite.org/windowfunctions.html). | **Keyword** | **Description** | **Example** | | --- | --- | --- | | `OVER` | Makes an aggregate or window function work over a window of rows. | `sum(Pay) over (order by StartDate)` | | `PARTITION` | With `BY`, splits the rows into separate windows. | `sum(Pay) over (partition by Dept)` | | `WINDOW` | Names a window definition so you can reuse it. | `select rank() over w from @x window w as (order by Age)` | | `FILTER` | Limits the rows an aggregate function uses. | `count(*) filter (where Age > 30)` | | `ROWS` | Defines the window frame as a number of rows. | `sum(x) over (rows 2 preceding)` | | `RANGE` | Defines the window frame by a range of `ORDER BY` values. | `sum(x) over (order by Day range 7 preceding)` | | `GROUPS` | Defines the window frame as a number of peer groups. | `sum(x) over (order by Day groups 1 preceding)` | | `UNBOUNDED` | Makes the frame start at the first row, or end at the last row. | `rows between unbounded preceding and current row` | | `PRECEDING` | Counts the frame start back from the current row. | `rows 3 preceding` | | `FOLLOWING` | Counts the frame end on from the current row. | `rows between current row and 1 following` | | `CURRENT` | Used in `CURRENT ROW`. | `rows between 1 preceding and current row` | | `ROW` | Used in `CURRENT ROW`. | `rows between 1 preceding and current row` | | `EXCLUDE` | Leaves some rows out of the frame. | `rows 2 preceding exclude current row` | | `NO` | Used in `EXCLUDE NO OTHERS`. | `rows 2 preceding exclude no others` | | `OTHERS` | Used in `EXCLUDE NO OTHERS`, which leaves nothing out (the default). | `rows 2 preceding exclude no others` | | `TIES` | Used in `EXCLUDE TIES`, which leaves out the current row's peers but keeps the current row. | `rows 2 preceding exclude ties` | ## FINBOURNE extensions Keywords that Luminesce adds to SQLite. | **Keyword** | **Description** | **Example** | | --- | --- | --- | | `USE` | Starts a block of input for a direct provider. The block ends with `ENDUSE`. | `@x = use Some.Provider--option=valueenduse;` | | `ENDUSE` | Ends a `USE` block. | `@x = use Some.Provider--option=valueenduse;` | | `WITH` | Passes table or scalar variables into a direct provider. Unlike SQLite, it does not define common table expressions; use table variables instead. | `use Some.Provider with @data, @@date enduse` | | `FORCE` | Makes sure the named columns exist in a direct provider's result, even if it returns no rows. `FORCE` can also use as a bare column or table name. | `use Some.Provider force ColA, ColB enduse` | | `APPLY` | With `CROSS` or `OUTER`, runs a subquery once for each row. See [FOR-EACH loops](/v1/docs/understanding-the-luminesce-sql-query-syntax#foreach-loops). | `from @ids i cross apply (select * from Some.Provider where Id = i.Id) r` | | `WAIT` | Waits for everything before it to finish, plus an optional number of seconds. See [WAIT](/v1/docs/understanding-the-luminesce-sql-query-syntax#pausing-query-execution-using-wait). | `select * from @view wait 5` | ## Types The types you can use with `TYPES` to set column types. `TYPES` also accepts `DATE`, `TIME` and `DATETIME`. See [Type conversion](/v1/docs/understanding-the-luminesce-sql-query-syntax#type-conversion). | **Keyword** | **Description** | **Example** | | --- | --- | --- | | `TYPES` | Sets the types of the columns returned by the next `SELECT`. | `types bigint, text; select 1, 2` | | `BIGINT` | A 64-bit integer. | `types bigint; select 1` | | `INT` | A 32-bit integer. | `types int; select 1` | | `BOOLEAN` | True or false. | `types boolean; select 1` | | `DOUBLE` | A double-precision floating-point number. | `types double; select 1` | | `DECIMAL` | A decimal number. | `types decimal; select 1.5` | | `TEXT` | A string. | `types text; select 1` | ## Reserved keywords These keywords are reserved but not currently used. To use one as a column or table name, wrap it in `[]`. | **Keyword** | **Description** | **Example** | | --- | --- | --- | | `CHECK` | Reserved. | `select [Check] from @x` | | `TO` | Reserved. | `select [To] from @x` | | `COLLATE` | Reserved on its own. Use `COLLATE NOCASE`, `COLLATE NOACCENT` or `COLLATE EXACT`. | `select [Collate] from @x` | ## SQLite scalar functions See the [SQLite core functions documentation](https://www.sqlite.org/lang_corefunc.html). | **Keyword** | **Description** | **Example** | | --- | --- | --- | | `ABS` | Returns the absolute value of a number. | `abs(-5)` | | `CHAR` | Reserved function. | | | `COALESCE` | Returns the first argument that is not null. | `coalesce(Nickname, Name)` | | `GLOB` | Function form of the `GLOB` operator: `glob(X,Y)` is the same as `Y GLOB X`. | `glob('*.csv', FileName)` | | `HEX` | Reserved function. | | | `IIF` | Returns the second argument if the first is true, otherwise the third. | `iif(Age > 30, 'old', 'young')` | | `IFNULL` | Returns the first argument if it is not null, otherwise the second. | `ifnull(Nickname, Name)` | | `INSTR` | Returns the position of the first occurrence of one string in another, or 0. | `instr('hello', 'l')` | | `LENGTH` | Returns the number of characters in a string. | `length('hello')` | | `LIKE` | Function form of the `LIKE` operator: `like(X,Y)` is the same as `Y LIKE X`. | `like('Jo%', Name)` | | `LIKELY` | Reserved function. | | | `LOWER` | Converts a string to lower case. | `lower('Hello')` | | `LTRIM` | Removes spaces, or the given characters, from the start of a string. | `ltrim('  hello')` | | `MAX` | With one argument, returns the largest value in a group. With several, returns the largest argument. | `max(1, 5, 3)` | | `MIN` | With one argument, returns the smallest value in a group. With several, returns the smallest argument. | `min(1, 5, 3)` | | `NULLIF` | Returns null if the two arguments are equal, otherwise the first. | `nullif(Qty, 0)` | | `PRINTF` | Formats values into a string, like the C `printf()` function. | `printf('%.2f', 3.14159)` | | `QUOTE` | Returns a value as an SQL literal. | `quote('it''s')` | | `RANDOM` | Returns a random 64-bit integer. | `random()` | | `RANDOMBLOB` | Returns a blob of the given number of random bytes. | `randomblob(16)` | | `REPLACE` | Replaces every occurrence of one string with another. | `replace('a-b-c', '-', '+')` | | `ROUND` | Rounds a number to the given number of decimal places. | `round(3.14159, 2)` | | `RTRIM` | Removes spaces, or the given characters, from the end of a string. | `rtrim('hello  ')` | | `SOUNDEX` | Returns the soundex code of a string. | `soundex('Robert')` | | `SUBSTR` | Returns part of a string, from a start position and an optional length. | `substr('hello', 2, 3)` | | `TRIM` | Removes spaces, or the given characters, from both ends of a string. | `trim('  hello  ')` | | `TYPEOF` | Returns the storage type of a value: `null`, `integer`, `real`, `text` or `blob`. | `typeof(1.5)` | | `UNICODE` | Returns the Unicode code point of the first character of a string. | `unicode('A')` | | `UNLIKELY` | Reserved function. | | | `UPPER` | Converts a string to upper case. | `upper('hello')` | | `ZEROBLOB` | Reserved function. | | ## SQLite date and time functions See the [SQLite date and time functions documentation](https://www.sqlite.org/lang_datefunc.html). `DATE`, `TIME` and `DATETIME` are also types you can use with `TYPES`. | **Keyword** | **Description** | **Example** | | --- | --- | --- | | `DATE` | Returns a date as `YYYY-MM-DD`. | `date('now', '-1 day')` | | `TIME` | Returns a time as `HH:MM:SS`. | `time('now')` | | `DATETIME` | Returns a date and time as `YYYY-MM-DD HH:MM:SS`. | `datetime('now', '+1 hour')` | | `JULIANDAY` | Returns the Julian day number of a date. | `julianday('now')` | | `STRFTIME` | Formats a date using a format string. | `strftime('%Y-%m', 'now')` | ## SQLite aggregate functions See the [SQLite aggregate functions documentation](https://www.sqlite.org/lang_aggfunc.html). `MAX` and `MIN` are listed under scalar functions. | **Keyword** | **Description** | **Example** | | --- | --- | --- | | `AVG` | Returns the average of the values that are not null. | `avg(Price)` | | `COUNT` | Counts rows, or the values that are not null. | `count(*)` | | `GROUP_CONCAT` | Joins values into one string, separated by commas or the given separator. | `group_concat(Name, '; ')` | | `SUM` | Returns the sum of the values, or null if there are none. | `sum(Qty)` | | `TOTAL` | Like `SUM`, but returns 0.0 rather than null when there are no values. | `total(Qty)` | ## SQLite window functions See the [SQLite window functions documentation](https://www.sqlite.org/windowfunctions.html). | **Keyword** | **Description** | **Example** | | --- | --- | --- | | `ROW_NUMBER` | Returns the number of the row in its partition, starting at 1. | `row_number() over (order by Age)` | | `RANK` | Returns the rank of the row, with gaps after ties. | `rank() over (order by Score desc)` | | `DENSE_RANK` | Returns the rank of the row, without gaps after ties. | `dense_rank() over (order by Score desc)` | | `PERCENT_RANK` | Returns the relative rank of the row, from 0.0 to 1.0. | `percent_rank() over (order by Score)` | | `CUME_DIST` | Returns the cumulative distribution of the row, from 0.0 to 1.0. | `cume_dist() over (order by Score)` | | `NTILE` | Splits the partition into N groups and returns the group number of the row. | `ntile(4) over (order by Score)` | | `LAG` | Returns a value from an earlier row. | `lag(Price) over (order by TradeDate)` | | `LEAD` | Returns a value from a later row. | `lead(Price) over (order by TradeDate)` | | `FIRST_VALUE` | Returns a value from the first row of the frame. | `first_value(Price) over (order by TradeDate)` | | `LAST_VALUE` | Returns a value from the last row of the frame. | `last_value(Price) over (order by TradeDate)` | | `NTH_VALUE` | Returns a value from the Nth row of the frame. | `nth_value(Price, 2) over (order by TradeDate)` | ## SQLite extension functions Maths, string and statistical functions from the [SQLite extension library](https://www.sqlite.org/contrib/download/extension-functions.c?get=25). | **Keyword** | **Description** | **Example** | | --- | --- | --- | | `STDEV` | Returns the standard deviation of the values. | `stdev(Price)` | | `VARIANCE` | Returns the variance of the values. | `variance(Price)` | | `MODE` | Returns the most common value. | `mode(Rating)` | | `MEDIAN` | Returns the median value. | `median(Price)` | | `LOWER_QUARTILE` | Returns the lower quartile (25th percentile). | `lower_quartile(Price)` | | `UPPER_QUARTILE` | Returns the upper quartile (75th percentile). | `upper_quartile(Price)` | | `REPLICATE` | Repeats a string the given number of times. | `replicate('ab', 3)` | | `LEFTSTR` | Returns the given number of characters from the start of a string. | `leftstr('hello', 2)` | | `RIGHTSTR` | Returns the given number of characters from the end of a string. | `rightstr('hello', 2)` | | `REVERSE` | Reverses a string. | `reverse('hello')` | | `PROPER` | Capitalises the first letter of each word. | `proper('hello world')` | | `PADL` | Pads a string on the left with spaces to the given length. | `padl('7', 3)` | | `PADR` | Pads a string on the right with spaces to the given length. | `padr('7', 3)` | | `PADC` | Pads a string on both sides with spaces to centre it in the given length. | `padc('7', 3)` | | `STRFILTER` | Keeps only the characters that appear in a second string (case-sensitive). | `strfilter('a1b2', '0123456789')` | | `CHARINDEX` | Returns the position of one string in another, optionally starting from a given position. | `charindex('de', 'abcdefdex')` | | `DIFFERENCE` | Compares the soundex codes of two strings and returns 0 (not alike) to 4 (very alike). | `difference('hello', 'hallo')` | | `LOG` | Returns the natural logarithm. | `log(10)` | | `LOG10` | Returns the base-10 logarithm. | `log10(1000)` | | `EXP` | Returns e raised to the given power. | `exp(1)` | | `POWER` | Raises a number to a power. | `power(2, 10)` | | `SIGN` | Returns 1, 0 or -1 for a positive, zero or negative number. | `sign(-5)` | | `SQRT` | Returns the square root. | `sqrt(16)` | | `SQUARE` | Returns the square. | `square(4)` | | `CEIL` | Rounds up to the next integer. | `ceil(1.2)` | | `FLOOR` | Rounds down to the next integer. | `floor(1.8)` | ## SQLite JSON functions See the [SQLite JSON functions documentation](https://www.sqlite.org/json1.html) and [Converting to and from JSON](/v1/docs/understanding-the-luminesce-sql-query-syntax#converting-to-and-from-json). | **Keyword** | **Description** | **Example** | | --- | --- | --- | | `JSON` | Checks that a string is valid JSON and returns it minified. | `json(' { "a" : 1 } ')` | | `JSON_ARRAY` | Builds a JSON array from its arguments. | `json_array(1, 2, 'x')` | | `JSON_ARRAY_LENGTH` | Returns the number of elements in a JSON array. | `json_array_length('[1,2,3]')` | | `JSON_EXTRACT` | Returns the value at a path in a JSON document. | `json_extract('{"a":{"b":2}}', '$.a.b')` | | `JSON_INSERT` | Adds a value at a path, if nothing is there already. | `json_insert('{"a":1}', '$.b', 2)` | | `JSON_OBJECT` | Builds a JSON object from name and value pairs. | `json_object('a', 1, 'b', 'x')` | | `JSON_PATCH` | Merges one JSON object into another. | `json_patch('{"a":1}', '{"b":2}')` | | `JSON_QUOTE` | Converts a value to its JSON form. | `json_quote('hello')` | | `JSON_REMOVE` | Removes the values at the given paths. | `json_remove('{"a":1,"b":2}', '$.a')` | | `JSON_TYPE` | Returns the type of a JSON value, such as `object`, `array` or `text`. | `json_type('[1,2]')` | | `JSON_VALID` | Returns 1 if a string is valid JSON, otherwise 0. | `json_valid('{"a":1}')` | | `JSON_GROUP_ARRAY` | Aggregates values into a JSON array. | `json_group_array(Name)` | | `JSON_GROUP_OBJECT` | Aggregates name and value pairs into a JSON object. | `json_group_object(Name, Age)` | ## FINBOURNE general functions See [General custom functions](/v1/docs/supported-functions-in-luminesce-sql-queries#general-custom-functions).

Keyword

Description

Example

CHECK_ACCESS

Checks whether you have access to a resource in the FINBOURNE platform.

check_access('Feature/Honeycomb/Execute', 'Honeycomb:stable-provider-sys-file')

CHECK_REGISTRATION_ACCESS

Reserved function.

THROW

Stops the query with an error message.

iif(x is null, throw('x is required'), x)

THROW_RETURN

Stops the query and returns the contents of a table variable as the result.

iif(x is null, throw_return(@fallback), x)

PRINT

Sends a message to the query progress log.

print('Starting')

DAY_COUNT_DISPLAY

Shows a fractional number of days as days, hours, minutes and seconds.

day_count_display(3.1234567)

TO_DATE

Converts a string in a non-standard format to a date.

to_date('2022/29/03', 'yyyy/dd/MM')

IS_DATE

Returns 1 if a value is a date, optionally in a given format, otherwise 0.

is_date('2024-01-19')

TO_UTC

Converts a date in the given timezone to UTC.

to_utc('2023/01/30 10:30:11', 'US/Eastern')

TO_ISO

Converts a date to an ISO 8601 string.

to_iso(#2023-03-25#)

FROM_UTC

Converts a UTC date to the given timezone.

from_utc('2023/01/30 10:30:11', 'US/Eastern')

CONVERT_TIMEZONE

Converts a date from one timezone to another.

convert_timezone('2023/01/30 10:30:11', 'US/Eastern', 'US/Pacific')

IS_NUMERIC

Returns 1 if a value is a number, otherwise 0.

is_numeric('200.99')

IS_INTEGER

Returns 1 if a value is an integer, otherwise 0.

is_integer('200')

EDIT_DISTANCE

Returns the Levenshtein distance between two strings.

edit_distance('kitten', 'sitting')

GENERATE_HASH

Creates a hash of one or more values. Uses SHA256 by default.

generate_hash('SHA256', Col1, Col2)

CONTAINS_TOKEN

Returns 1 if a string contains the given whole word, otherwise 0.

contains_token('the quick fox', 'quick')

CONTAINS_TOKEN_STARTING_WITH

Returns 1 if a string contains a word starting with the given text, otherwise 0.

contains_token_starting_with('the quick fox', 'qui')

ANY_EQUAL

Returns 1 if any item in a CSV string equals a value, otherwise 0.

any_equal('one,two', 'two')

ANY_IN

Returns 1 if any item in a CSV string is in a second CSV string, otherwise 0.

any_in('one,two', 'two,five')

ALL_EQUAL

Returns 1 if every item in a CSV string equals a value, otherwise 0.

all_equal('one,one', 'one')

ALL_IN

Returns 1 if every item in a CSV string is in a second CSV string, otherwise 0.

all_in('one,two', 'three,two,one')

REGEXP_MATCH

Returns the first part of a string that matches a regular expression.

regexp_match('XXX: ABC-123', '[A-Z]+-[0-9]+')

REGEXP_MATCHES

Returns every part of a string that matches a regular expression, as a CSV list.

regexp_matches('ABC-1 and QQ-2', '[A-Z]+-[0-9]+')

REGEXP_MATCH_LOCATION

Returns the position of the first match of a regular expression.

regexp_match_location('XXX: ABC-123', '[A-Z]+-[0-9]+')

REGEXP_REPLACE

Replaces every match of a regular expression.

regexp_replace('ABC-123', '[0-9]+', '?')

NOOP_FOR_TRANSLATION

Reserved function.

 ## FINBOURNE statistical functions See [Statistical custom functions](/v1/docs/supported-functions-in-luminesce-sql-queries#statistical-custom-functions).

Keyword

Description

Example

QUANTILE

Returns the value at a percentile, given from 0.0 to 1.0.

quantile(Price, 0.8)

CUMEPROD

Returns the cumulative product.

cumeprod(1 + Ret) over (rows unbounded preceding)

COVARIANCE

Returns the covariance of two columns.

covariance(x, y)

SKEWNESS

Measures how lopsided the distribution of values is.

skewness(Ret)

KURTOSIS

Measures how heavy the tails of the distribution are.

kurtosis(Ret)

ROOT_MEAN_SQUARE

Returns the square root of the mean of the squared values.

root_mean_square(x)

HARMONIC_MEAN

Returns the harmonic mean.

harmonic_mean(x)

GEOMETRIC_MEAN

Returns the geometric mean.

geometric_mean(x)

ENTROPY

Returns the Shannon entropy of the values.

entropy(Rating)

EMPIRICAL_CUME_DIST_FUNCTION

Returns the fraction of values less than or equal to the given value.

empirical_cume_dist_function(x, 10)

INTERQUARTILE_RANGE

Returns the upper quartile minus the lower quartile.

interquartile_range(x)

INTERQUANTILE_RANGE

Returns the difference between two quantiles.

interquantile_range(x, 0.1, 0.9)

PEARSON_CORRELATION

Returns the linear correlation of two columns.

pearson_correlation(x, y)

SPEARMAN_RANK_CORRELATION

Returns the rank correlation of two columns.

spearman_rank_correlation(x, y)

MEAN_SQUARED_ERROR

Returns the mean of the squared differences between two columns.

mean_squared_error(x, y)

MEAN_ABSOLUTE_ERROR

Returns the mean of the absolute differences between two columns.

mean_absolute_error(x, y)

MEDIAN_ABSOLUTE_DEVIATION

Returns the median of the absolute differences from the median.

median_absolute_deviation(x)

MEAN_FRACTIONAL_ABSOLUTE_ERROR

Returns the mean absolute difference between two columns, as a fraction.

mean_fractional_absolute_error(x, y)

GAIN_LOSS_RATIO

Returns the mean positive return divided by the mean negative return.

gain_loss_ratio(Ret)

SEMI_DEVIATION

Returns the standard deviation of the returns below the mean.

semi_deviation(Ret)

DRAWDOWN

Returns the fall from the highest value so far. Must be used in a window.

drawdown(Price) over (rows unbounded preceding)

MAX_DRAWDOWN

Returns the largest drawdown.

max_drawdown(Price)

MEAN_DRAWDOWN

Returns the mean drawdown.

mean_drawdown(Price)

DRAWDOWN_LENGTH

Returns the number of rows since the highest value so far. Must be used in a window.

drawdown_length(Price) over (rows unbounded preceding)

MAX_DRAWDOWN_LENGTH

Returns the longest drawdown length.

max_drawdown_length(Price)

MEAN_DRAWDOWN_LENGTH

Returns the mean drawdown length.

mean_drawdown_length(Price)

WINDOW_STDEV

Returns the standard deviation. Unlike STDEV, it can be used in a window.

window_stdev(x) over (rows 20 preceding)

LINEAR_REGRESSION_ALPHA

Returns the y intercept of a regression line fitted to two columns.

linear_regression_alpha(x, y)

LINEAR_REGRESSION_BETA

Returns the gradient of a regression line fitted to two columns.

linear_regression_beta(x, y)

LINEAR_REGRESSION_ALPHA_ERROR

Returns the standard error of the regression y intercept.

linear_regression_alpha_error(x, y)

LINEAR_REGRESSION_BETA_ERROR

Returns the standard error of the regression gradient.

linear_regression_beta_error(x, y)

PRICES_TO_RETURNS

Turns a series of prices into a series of returns.

prices_to_returns(Price, 1, 1.0, 0) over (rows unbounded preceding)

RETURNS_TO_PRICES

Turns a series of returns into a series of prices, from an initial price.

returns_to_prices(Ret, 400, 1.0, 0) over (rows unbounded preceding)

MEAN_STDEV_RATIO

Returns the mean divided by the standard deviation.

mean_stdev_ratio(Ret)

COEFFICIENT_OF_VARIATION

Returns the standard deviation divided by the mean.

coefficient_of_variation(x)

MINKOWSKI_DISTANCE

Returns the Minkowski distance between two columns, for a given power.

minkowski_distance(x, y, 3)

CHEBYSHEV_DISTANCE

Returns the largest difference between two columns.

chebyshev_distance(x, y)

MANHATTAN_DISTANCE

Returns the sum of the absolute differences between two columns.

manhattan_distance(x, y)

EUCLIDEAN_DISTANCE

Returns the straight-line distance between two columns.

euclidean_distance(x, y)

CANBERRA_DISTANCE

Returns the Canberra distance between two columns.

canberra_distance(x, y)

BRAYCURTIS_DISTANCE

Returns the Bray-Curtis distance between two columns.

braycurtis_distance(x, y)

COSINE_DISTANCE

Returns 1 minus the cosine of the angle between two columns.

cosine_distance(x, y)

PRECISION_SCORE

Returns the fraction of predicted positives that are true positives.

precision_score(x, y)

RECALL_SCORE

Returns the fraction of true positives that were predicted.

recall_score(x, y)

FBETA_SCORE

Returns the F-score, weighting recall against precision by beta.

fbeta_score(x, y, 1.0)

R_SQUARED

Returns how well one column predicts another.

r_squared(x, y)

ADJUSTED_R_SQUARED

Like R_SQUARED, adjusted for the number of predictors.

adjusted_r_squared(x, y, 3)

 ## Related - [Supported functions in Luminesce SQL queries](/supported-functions-in-luminesce-sql-queries.md) - [Understanding the Luminesce SQL query syntax](/understanding-the-luminesce-sql-query-syntax.md)