The following common functions are supported in Luminesce SQL queries:
Most of the builtin scalar SQLite functions.
The SQLite date and time functions.
The mathematical and string functions exposed by the SQLite common extension library (C source file).
SQLite Window functions.
SQLite aggregate functions. Note aggregate queries can contain nonaggregate result columns that are not in a
GROUP BY
clause; read more on this.The functions exposed by the JSON1 extension library.
We have also implemented the following custom functions:
See the full list of valid keywords (both inherited from SQLite and proprietary to FINBOURNE).
General custom functions
General custom function  Explanation and examples 
 Performs an access control check on any application in the FINBOURNE platform, for example:

 Can be used conditionally to throw an error message, for example:
Append the following argument to throw without an error message:

 Can be used to debug a query, give feedback to an end user running the query, or enter text into the logs. Takes the following arguments:

 Converts a fractional day number to a more intuitive count of days, hours, minutes, seconds and so on.

 Performs an explicit date conversion from a string in a nonstandard format, for example:

 Returns 1 if a value is a date or datetime, otherwise 0. For example:

 Transforms a date and a TZ database name timezone to a UTC date, for example:
See also these examples. 
 Transforms a datetime to an ISOformatted string, for example:
See also these examples. 
 Transforms a UTC date to a given TZ database name timezone, for example:

 Transforms a date from one TZ database name timezone to another, for example:

 Returns 1 if a value is a long, int, double or decimal, otherwise 0. For example:

 Returns 1 if a value is a long or an int, otherwise 0. For example:

 Returns the Levenshtein distance between two strings, for example:

 Creates a hash string from one or more defined columns. The parameter syntax is as follows:
For example:

 Returns 1 if a string contains the specified token (that is, whole word), otherwise 0. For example:
This function is optimised for filtering data retrieved from providers supplied by FINBOURNE. For more information on filtering, see this article. 
 Returns 1 if a string contains a word starting with the specified token, otherwise 0. For example:
This function is optimised for filtering data retrieved from providers supplied by FINBOURNE. For more information on filtering, see this article. 
 Enables filtering by regular expression matches. Note this function is used as an operator and so behaves differently to the other regular expression functions below. For example:

 Returns the first portion of the search string that matches the regular expression, or

 Returns the portions of the search string that match the regular expression as a commaseparated list, or

 Returns the 1based index of the first portion of the search string that matches the regular expression, or

 Replaces the portions of the search string that match the regular expression with the replacement value. The parameter syntax is as follows:

Statistical custom functions
Explanation  Luminesce SQL syntax  Equivalent Lumipy method 

DescriptionThe coefficient of variation is the standard deviation scaled by the mean. It is a standardised measure of the dispersion of a random variable so distributions of different scale can be compared [1] Inputs



DescriptionCovariance is a statistical measure of the joint variability of two random variables [1] Inputs



DescriptionThe empirical CDF is the cumulative distribution function of a sample. It is a step function that jumps by 1/n at each of the n data points. This function returns the value of the empirical CDF at the given value [1] Inputs



DescriptionThe Shannon entropy measures the average amount of "surprise" in a sequence of values. It can be considered a measure of variability [1] It is calculated as
where p_i is the probability of the ith value occurring computed from the sample (n occurrences / sample size). This function is equivalent to scipy.stats.entropy called with a single series and with the natural base [2] Inputs



DescriptionThe geometric mean is the multiplicative equivalent of the normal arithmetic mean. It multiplies a set of nmany numbers together and then takes the nth root of the result [1] Inputs



DescriptionThe geometric standard deviation measures the variability of a set of numbers where the appropriate mean to use is the geometric one (they are more appropriately combined by multiplication rather than addition) [1] This is computed as the exponential of the standard deviation of the natural log of each element in the set
Inputs



DescriptionThe harmonic mean is the reciprocal of the mean of the individual reciprocals of the values in a set [1] Inputs



DescriptionThe interquantile range is the difference between two different quantiles. This is a generalisation of the interquartile range where q1=0.25 and q2=0.75. The upper quantile (q2) value must be greater than the lower quantile (q1) value. Inputs



DescriptionThe interquartile range is the difference between the upper and lower quartiles. It can be used as a robust measure of the variability of a random variable [1] Inputs



DescriptionKurtosis measures how much probability density is in the tails (extremes) of a sample's distribution [1] This function corresponds to the Pearson Kurtosis measure and currently only supports sample kurtosis. Inputs



DescriptionThe lower quartile is the value that bounds the lower quarter of a dataset [1] It is equivalent to quantile 0.25 or the 25th percentile. Inputs



DescriptionThis is a convenience function for computing the mean divided by the standard deviation. This is used in multiple financial statistics such as the Sharpe ratio and information ratio. Inputs



DescriptionThe median is the value that separates the top and bottom half of a dataset [1] It is equivalent to quantile 0.5, or the 50th percentile. Inputs



DescriptionThe median absolute deviation is a measure of the variability of a random variable. Unlike the standard deviation it is robust to the presence of outliers [1] Inputs



DescriptionPearson's r is a measure of the linear correlation between two random variables [1] Inputs



DescriptionThe quantile function of a given random variable and q value finds the value x where the probability of observing a value less than or equal to x is equal to q [1] Inputs



DescriptionThe Root Mean Square (RMS) is the square root of the mean of the squared values of a set of values. It is a statistical measure of the spead of a random variable [1] Inputs



DescriptionSkewness measures the degree of asymmetry of a random variable around its mean [1] This calculation currently only supports sample skewness. Inputs



DescriptionSpearman's rho measures how monotonic the relationship between two random variables is [1] Inputs



DescriptionAn implementation of standard deviation that can be used in a window. The standard deviation measures the dispersion of a set of values around the mean [1] This only calculates the sample standard deviation (delta degrees of freedom = 1) Inputs



DescriptionThe upper quartile is the value that bounds the upper quarter of a dataset [1] It is equivalent to quantile 0.75 or the 75th percentile. Inputs



DescriptionComputes the y intercept (alpha) of a regression line fitted to the given data [1] Inputs



DescriptionComputes the standard error of the y intercept (alpha) of a regression line fitted to the given data [1] This assumes the residuals are normally distributed and is calculated according to [2] Inputs



DescriptionComputes the gradient of a regression line (beta) fitted to the given data [1] Inputs



DescriptionComputes the standard error of the gradient (beta) of a regression line fitted to the given data [1] This assumes the residuals are normally distributed and is calculated according to [2] Inputs



DescriptionThe BrayCurtis distance is the elementwise sum of absolute differences between elements divided by the absolute value of their sum [1] Inputs



DescriptionThe Canberra distance is the elementwise sum of absolute differences between elements divided by the sum of their absolute values. It can be considered a weighted version of the Manhattan distance [1] Inputs



DescriptionThe Chebyshev distance is the greatest difference between dimension values of two vectors. It is equivalent to the Minkowski distance as p → ∞ [1] Inputs



DescriptionThe cosine distance is the cosine of the angle between two vectors subtracted from 1 [1] Inputs



DescriptionThe Euclidean distance is the familiar 'as the crow flies' distance. It is the square root of the sum of squared differences between the elements of two vectors [1] Inputs



DescriptionThe Fscore is a classifier performance metric which measures accuracy. It is defined as the weighted harmonic mean of precision and recall scores. The beta parameter controls the relative weighting of these two metrics. The most common value of beta is 1: this is the F_1 score (aka balanced Fscore). It weights precision and recall evenly. Values of beta greater than 1 weight recall higher than precision and less than 1 weights precision higher than recall [1] Inputs



DescriptionThe Manhattan distance (aka the taxicab distance) is the absolute sum of differences between the elements of two vectors. It is the distance traced out by a taxicab moving along a city grid like Manhattan where the distance travelled is the sum of the sides of the squares [1] Inputs



DescriptionThe mean absolute error is the mean absolute elementwise difference between two series of values [1] It is a common performance metric for regression models where one series is the predicted values and the other series is the observed values. Inputs



DescriptionThe mean fractional absolute error is the mean absolute elementwise fractional difference between two series of values. It is a scaleinvariant version of the mean absolute error [1] It is a common performance metric for regression models where one series is the predicted values and the other series is the observed values. Inputs



DescriptionThe mean squared error between two series of values is the mean of the squared elementwise differences [1] It is a common performance metric for regression models. Inputs



DescriptionThe Minkowski distance is a generalisation of the Euclidean (p=2) or Manhattan (p=1) distance to other powers p [1] Inputs



DescriptionPrecision is a classification performance metric which measures the fraction of true positive events in a set of events that a classifier has predicted to be positive. It is calculated as follows
where tp is the number of true positives and fp is the number of false positives. Precision is a measure of the purity of the classifier's positive predictions. [1] It is also known as the positive predictive value and purity. Inputs



DescriptionRecall is a classification performance metric which measures the fraction of positive events that are successfully predicted by a classifier. It is calculated as follows
where tp is the number of true positives and fn is the number of false negatives. Recall is a measure of the efficiency of the classifier at retrieving positive events. [1] It is also known as sensitivity, hit rate, true positive rate (TPR) and efficiency. Inputs



DescriptionApply a R squared calculation between two value series in this window. This a measure of how well a regressor predicts true values. Returns a window column instance representing this calculation. Inputs



DescriptionApply an adjusted R squared calculation between two value series in this window. This a measure of how well a regressor predicts true values, but with a penalisation term for more predictors (inputs). Returns a window column instance representing this calculation. Inputs



DescriptionGainloss ratio is the mean positive return of the series divided by the mean negative return of the series. Inputs



DescriptionThe information ratio is the mean excess return between a return series and a benchmark series divided by the standard deviation of the excess return. Inputs



DescriptionDrawdown is calculated as
where x_h is high watermark (max) up to and including the point x_i [1] Drawdown must be in a window. You can change the limits in the
...whereas the following only looks back 90 rows:
In Lumipy this would be:
Inputs



DescriptionDrawdown length is calculated as the number of rows between the high watermark value and the current row. [1] Inputs



DescriptionDrawdown is calculated as
where x_h is high watermark (max) up to and including the point x_i [1] Max drawdown is then the maximum value of the drawdowns dd_i over the sequence of values. This aggregation assumes that the column is in time order. This calculation will be applied before ORDER BY in SQL syntax, so you should consider turning the table containing the data into a table variable, ordering that by the timeequivalent column and then applying the method to the corresponding column in a select statement on the table variable. Inputs



DescriptionDrawdown length is calculated as the number of rows between the high watermark value and the current row. [1] The max drawdown length is then the maximum value of the drawdown length in the time period. This aggregation assumes that the column is in time order. This calculation will be applied before ORDER BY in SQL syntax, so you should consider turning the table containing the data into a table variable, ordering that by the timeequivalent column and then applying the method to the corresponding column in a select statement on the table variable. Inputs



DescriptionDrawdown is calculated as
where x_h is high watermark (max) up to and including the point x_i [1] Mean drawdown is then the mean value of the drawdown over the sequence of values. This aggregation assumes that the column is in time order. This calculation will be applied before ORDER BY in SQL syntax, so you should consider turning the table containing the data into a table variable, ordering that by the timeequivalent column and then applying the method to the corresponding column in a .select() on the table variable. Inputs



DescriptionDrawdown length is calculated as the number of rows between the high watermark value and the current row. [1] The mean drawdown length is then the mean value of the drawdown length in the time period. This aggregation assumes that the column is in time order. This calculation will be applied before ORDER BY in SQL syntax, so you should consider turning the table containing the data into a table variable, ordering that by the timeequivalent column and then applying the method to the corresponding column in a .select() on the table variable. Inputs



DescriptionCompute a returns series from a prices series. Supports scaling by a simple scale factor or compounding. Inputs



DescriptionCompute a price series from a returns series and an initial value. Supports scaling by a simple scale factor or compounding. Inputs



DescriptionSemideviation is the standard deviation of values in a returns series below the mean return value. Inputs



DescriptionThe Sharpe ratio is calculated as the mean excess return over the riskfree rate divided by the standard deviation of the excess return. Inputs



DescriptionThe tracking error is the standard deviation of the difference between an index's return series and a benchmark. Inputs



DescriptionThe cumulative product is similar to cumulative sum except it multiplies rather than adds. Inputs


