Views:

Related resources:

Explanation

Tutorials

How-to guides

The following common functions are supported in Luminesce SQL queries:

We have also implemented the following custom functions:

General custom functions

General custom function Explanation and examples
THROW Can be used conditionally to throw an error message, for example:
select iif(abc is not null, abc, throw('abc cannot be null'))
PRINT 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:
  • The text to print (mandatory). This may contain 'structured result formatters', standard .NET format strings with named placeholders (see examples below).
  • A severity, one of Progress (the default), Debug, Information, Warning, Error.
  • A sender (defaults to PRINT).
  • Up to 125 arguments that substitute into .NET format strings.
select print('*** Starting your process NOW!')
=>
[INF] LuminesceCli: >> Progress >> PRINT >> *** Starting your process NOW!
select print('*** Starting your process NOW! {X:00000}', 'error', 'START!!!', 42)
=>
[ERR] LuminesceCli: >> Progress >> START!!! >> *** Starting your process NOW! 00042
select print('*** Starting your process NOW! {X}', 'error', 'START!!!', 42)
=>
[ERR] LuminesceCli: >> Progress >> START!!! >> *** Starting your process NOW! 42
DAY_COUNT_DISPLAY Converts a fractional day number to a more intuitive count of days, hours, minutes, seconds and so on.
select
day_count_display(3.1234567) a,
day_count_display(0.00123) b,
day_count_display(0.00000123) c,
day_count_display(julianday('now', '+2 minutes') - julianday('now', '-1 hours', '+3 minutes', '-12 seconds')) d
=>
┌───────────┬────────┬───────┬─────────┐
│ a         │ b      │ c     | d       │
├───────────┼────────┼───────┼─────────┤
│ 3d 2h 57m │ 1m 46s │ 106ms │ 59m 12s │
└───────────┴────────┴───────┴─────────┘
TO_DATE Performs an explicit date conversion from a string in a non-standard format, for example:
select to_date('2022/29/03', 'yyyy/dd/MM') as aDate
REGEXP 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:
select * from x where abc REGEXP '([A-Z]*-[0-9]*)'
REGEXP_MATCH Returns the first portion of the search string that matches the regular expression, or null if there are no matches. The parameter syntax is as follows: (search-string, regex [, name-of-capture-group-to-return]). For example:
-- returns: ABC-123
select regexp_match('XXX: ABC-123 Something', '([A-Z]+-[0-9]*)')
select regexp_match('XXX: ABC-123 Something', '([a-zA-Z]+-[0-9]*)')
-- returns: null (due to not matching on case)
select regexp_match('XXX: ABC-123 Something', '([a-z]+-[0-9]*)')
-- returns: ABC
select regexp_match('XXX: ABC-123 Something', '(?<project>[A-Z]+)-(?<id>[0-9]*)', 'project')
REGEXP_MATCHES Returns the portions of the search string that match the regular expression as a comma-separated list, or null if there are no matches. The parameter syntax is as follows: (search-string, regex). For example:
-- returns: ABC-123,QQQ-456
select regexp_matches('XXX: ABC-123 something QQQ-456 Something', '([A-Z]+-[0-9]*)')
REGEXP_MATCH_LOCATION Returns the 1-based index of the first portion of the search string that matches the regular expression, or null if there are no matches. The parameter syntax is as follows: (search-string, regex [, name-of-capture-group-to-return]). For example:
-- returns: 6
select regexp_match_location('XXX: ABC-123 Something', '([A-Z]+-[0-9]*)')
select regexp_match_location('XXX: ABC-123 Something', '([a-zA-Z]+-[0-9]*)')
select regexp_match_location('XXX: ABC-123 Something', '(?<project>[A-Z]+)-(?<id>[0-9]*)')
-- returns: null (due to not matching on case)
select regexp_match_location('XXX: ABC-123 Something', '([a-z]+-[0-9]*)')
-- returns: 10
select
regexp_match_location('XXX: ABC-123 Something', '(?<project>[A-Z]+)-(?<id>[0-9]*)', 'id')
REGEXP_REPLACE Replaces the portions of the search string that match the regular expression with the replacement value. The parameter syntax is as follows: (search-string, regex, replacement-value). For example:
-- returns: XXX: ? something ? Something
select regexp_replace('XXX: ABC-123 something QQQ-456 Something', '([A-Z]+-[0-9]*)', '?')

Statistical custom functions

Explanation Luminesce SQL syntax Equivalent Lumipy method

Description

The 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

x (numeric): column of values to compute the coefficient of variation over.

coefficient_of_variation([x]) x.stats.coef_of_variation()

Description

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

Inputs

x (numeric): the first column of values to compute the coefficient of variation over.
y (numeric): the second column of values to compute the coefficient of variation over.
ddof (int): delta degrees of freedom (defaults to 1). Use ddof = 0 for the population covariance and
ddof = 1 for the sample covariance.:

covariance([x], [y], ddof) x.stats.covariance(y, ddof)

Description

The 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

x (numeric): column of values to compute the coefficient of variation over.
value (numeric): location in the domain of x to evaluate the empirical CDF at.

empirical_cume_dist_function([x], value) x.stats.empirical_cdf(value)

Description

The 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

S = -sum(p_i * log(p_i))

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

x (numeric): column of values to compute the entropy over.

entropy([x]) x.stats.entropy()

Description

The geometric mean is the multiplicative equivalent of the normal arithmetic mean. It multiplies a set of n-many numbers together and then takes the n-th root of the result [1]

Inputs

x (numeric): column of values to compute the geometric mean over.

geometric_mean([x]) x.stats.geometric_mean()

Description

The 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

GSD = exp(stdev(log(x)))

Inputs

x (numeric): column of values to compute the geometric standard deviation over.

exp(window_stdev(log([x]))) x.stats.geometric_stdev()

Description

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

Inputs

x (numeric): column of values to compute the harmonic mean over.

harmonic_mean([x]) x.stats.harmonic_mean()

Description

The 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

x (numeric): column of values to compute the interquantile range over.
q1 (float): the lower quantile value.
q2 (float): the upper quantile value.

interquantile_range([x], [q1], [q2]) x.stats.interquantile_range()

Description

The 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

x (numeric): column of values to compute the interquartile range over.

interquartile_range([x]) x.stats.interquartile_range()

Description

Kurtosis 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

x (numeric): column of values to compute the kurtosis over.

kurtosis([x]) x.stats.kurtosis()

Description

The 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

x (numeric): column of values to compute the lower quartile over.

quantile([x], 0.25) x.stats.lower_quartile()

Description

This 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

x (numeric): column of values to compute the mean standard deviation ratio over.

mean_stdev_ratio([x]) x.stats.mean_stdev_ratio()

Description

The 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

x (numeric): column of values to compute the median over.

quantile([x], 0.5) x.stats.median()

Description

The 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

x (numeric): column of values to compute the median absolute deviation over.

median_absolute_deviation([x]) x.stats.median_abs_deviation()

Description

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

Inputs

x (numeric): the first series in the Pearson's r calculation.
y (numeric): the second series in the Pearson's r calculation.

pearson_correlation([x], [y]) x.stats.pearson_r(y)

Description

The 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

x (numeric): column of values to compute the quantile over.
q (float): the quantile value. Must be between 0 and 1.

quantile([x], q) x.stats.quantile(q)

Description

The 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

x (numeric): column of values to compute the root mean square over.

root_mean_square([x]) x.stats.root_mean_square(x)

Description

Skewness measures the degree of asymmetry of a random variable around its mean [1]

This calculation currently only supports sample skewness.

Inputs

x (numeric): column of values to compute the skewness mean over.

skewness([x]) x.stats.skewness(x)

Description

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

Inputs

x (numeric): the first series in the Spearman rank correlation calculation.
y (numeric): the second series in the Spearman rank correlation calculation.

spearman_rank_correlation([x], [y]) x.stats.spearman_r(y)

Description

An 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

x (numeric): column of values to compute the standard deviation over.

window_stdev([x]) x.stats.stdev()

Description

The 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

x (numeric): column of values to compute the upper quartile over.

quantile([x], 0.75) x.stats.upper_quartile()

Description

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

Inputs

x (numeric): an expression corresponding to the independent variable.
y (numeric): an expression corresponding to the dependent variable.

linear_regression_alpha([x], [y]) x.linreg.alpha(y)

Description

Computes 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

x (numeric): an expression corresponding to the independent variable.
y (numeric): an expression corresponding to the dependent variable.

linear_regression_alpha_error([x], [y]) x.linreg.alpha_std_err(y)

Description

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

Inputs

x (numeric): an expression corresponding to the independent variable.
y (numeric): an expression corresponding to the dependent variable.

linear_regression_beta([x], [y]) x.linreg.beta(y)

Description

Computes 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

x (numeric): the column corresponding to the independent variable.
y (numeric): the column corresponding to the dependent variable.

linear_regression_beta_error([x], [y]) x.linreg.beta_std_err(y)

Description

The Bray-Curtis distance is the elementwise sum of absolute differences between elements divided by the absolute value of their sum [1]

Inputs

x (numeric): the column corresponding to the first series.
y (numeric): the column corresponding to the second series.

braycurtis_distance([x], [y]) x.metric.braycurtis_distance(y)

Description

The 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

x (numeric): the column corresponding to the first series.
y (numeric): the column corresponding to the second series.

canberra_distance([x], [y]) x.metric.canberra_distance(y)

Description

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

Inputs

x (numeric): the column corresponding to the first series.
y (numeric): the column corresponding to the second series.

chebyshev_distance([x], [y]) x.metric.chebyshev_distance(y)

Description

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

Inputs

x (numeric): the column corresponding to the first series.
y (numeric): the column corresponding to the second series.

cosine_distance([x], [y]) x.metric.cosine_distance(y)

Description

The 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

x (numeric): the column corresponding to the first series.
y (numeric): the column corresponding to the second series.

euclidean_distance([x], [y]) x.metric.euclidean_distance(y)

Description

The F-score 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 F-score). 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

x (boolean): the column corresponding to the first series.
y (boolean): the column corresponding to the second series.
beta (float): the value of beta to use in the calculation.

fbeta_score([x], [y], beta) x.metric.f_score(y, beta)

Description

The 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

x (numeric): the column corresponding to the first series.
y (numeric): the column corresponding to the second series.

manhattan_distance([x], [y]) x.metric.manhattan_distance(y)

Description

The 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

x (numeric): the column corresponding to the first series.
y (numeric): the column corresponding to the second series.

mean_absolute_error([x], [y]) x.metric.mean_absolute_error(y)

Description

The mean fractional absolute error is the mean absolute elementwise fractional difference between two series of values. It is a scale-invariant 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

x (numeric): the column corresponding to the first series.
y (numeric): the column corresponding to the second series.

mean_fractional_absolute_error([x], [y]) x.metric.mean_fractional_absolute_error(y)

Description

The 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

x (numeric): the column corresponding to the first series.
y (numeric): the column corresponding to the second series.

mean_squared_error([x], [y]) x.metric.mean_squared_error(y)

Description

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

Inputs

x (numeric): the column corresponding to the first series.
y (numeric): the column corresponding to the second series.
p (int): the order to use in the Minkowski distance calculation.

minkowski_distance([x], [y], p) x.metric.minkowski_distance(y, p)

Description

Precision 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

precision = tp / (tp + fp)

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

x (boolean): the column corresponding to the first series.
y (boolean): the column corresponding to the second series.

precision_score([x], [y]) x.metric.precision_score(y)

Description

Recall is a classification performance metric which measures the fraction of positive events that are successfully predicted by a classifier. It is calculated as follows

recall = tp / (tp + fn)

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

x (boolean): the column corresponding to the first series.
y (boolean): the column corresponding to the second series.

recall_score([x], [y]) x.metric.recall_score(y)

Description

Gain-loss ratio is the mean positive return of the series divided by the mean negative return of the series.

Inputs

x (numeric): column of return values to compute the gain-loss ratio over.

gain_loss_ratio([x]) x.finance.gain_loss_ratio()

Description

The 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

x (numeric): column of return values.
y (numeric): column of benchmark return values.

mean_stdev_ratio([x] - [y]) x.finance.information_ratio(y)

Description

Drawdown is calculated as

dd_i = (x_h - x_i)/(x_h)

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 time-equivalent column and then applying the method to the corresponding column in a select statement on the table variable.

Inputs

x (numeric): column of price values to compute the max drawdown over.

max_drawdown([x]) x.finance.max_drawdown()

Description

Drawdown 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 time-equivalent column and then applying the method to the corresponding column in a select statement on the table variable.

Inputs

x (numeric): column of price values to compute the max drawdown length over.

max_drawdown_length([x]) x.finance.max_drawdown_length()

Description

Drawdown is calculated as

dd_i = (x_h - x_i)/(x_h)

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 time-equivalent column and then applying the method to the corresponding column in a .select() on the table variable.

Inputs

x (numeric): column of price values to compute the mean drawdown over.

mean_drawdown([x]) x.finance.mean_drawdown()

Description

Drawdown 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 time-equivalent column and then applying the method to the corresponding column in a .select() on the table variable.

Inputs

x (numeric): column of price values to compute the mean drawdown length over.

mean_drawdown_length([x]) x.finance.mean_drawdown_length()

Description

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

Inputs

x (numeric): column of price values to compute the returns from.
interval (int): the number of time steps to use when scaling.
time_factor (float): a time scale factor for annualisation.
compound (bool): whether the time scale factor should be applied as a simple scale factor r*T or as a compounded calculation (r+1)**T

prices_to_returns([Price], 1, 1.0, 0) OVER( ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) x.finance.prices_to_returns(1, 1, False)

Description

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

Inputs

x (numeric): column of returns values to compute the prices from.
initial (float): the initial price to apply return factors to.
time_factor (float): a time scale factor for anualisation.
compound (bool): whether the time scale factor should be applied as a simple scale factor r*T or as a compounded calculation (r+1)**T

returns_to_prices([rets], 400, 1.0, 0) OVER( ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) x.finance.returns_to_prices(400, 1, False)

Description

Semi-deviation is the standard deviation of values in a returns series below the mean return value.

Inputs

x (numeric): column of return values to compute the semi-deviation over.

semi_deviation([x]) x.finance.semi_deviation()

Description

The Sharpe ratio is calculated as the mean excess return over the risk-free rate divided by the standard deviation of the excess return.

Inputs

x (numeric): column of return values to compute the Sharpe ratio for.
r (numeric): the risk-free rate of return. This can be a constant value (float) or a column.

mean_stdev_ratio([x] - [r]) x.finance.sharpe_ratio(r)

Description

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

Inputs

x (numeric): the index return series.
y (numeric): the benchmark return series.

window_stdev([x] - [y]) x.finance.tracking_error(y)