This page explains you how to use expressions and functions in Bouquet.
Requirements
Bouquet lets you write expressions to define metrics or dimensions to offer more flexibility. For instance, to define a new metric that COUNT the number of film the expression looks like the following:
COUNT(#'nr_events')
- COUNT: Function that count the number of rows returned
- #: The attribute prefix for a column
- 'film': The object name
In this documentation you will find the operators, functions and constant you can use to write expressions. You will also learn how to reference objects.
This guide is composed of five steps:
Constant values
Constant type | Example |
Numerical |
123, 123.4 |
Text | "Hello world" |
Date | DATE("DD/MM/YY") |
Referencing objects
Expression allows you to create references to a model object and to perform operations on their underlying values.
To create a reference, you can use two syntax:
- Shortand
- Explicit
Important: The resolution of an object identifier is case sensitive, and the identifier must be enclosed in simple quotes.
Object type | Shorthand | Explicit |
Column |
#'CUSTOMER_NAME' |
[col:'CUSTOMER_NAME'] |
Object by name (Domain, Dimension, Metric or Relation) |
‘Name' |
[attr:'Name'] |
Object by ID (of type association) |
@‘adf0a46e96aa3a6a76ee9e859' |
[id:'adf0a46e96aa3a6a76ee9e859'] |
Parameter |
$'M0' |
[param:'M0'] |
Naming expressions
Since version 4.2.2 (build #140), Bouquet server allows us to give a name to expressions. Just use the AS keyword in the expression editor:
'metric1' + 'metric2' AS 'sum_of_both_metrics'
This can be useful when using custom expressions to compute an analysis. The resulting column in the data-table will have the expression's name. Note however that if you define a dimension or a metric, the dimension or metric given name will take precedence over the expression's name.
Operators
Operator | Type | Description |
expr1 || expr2 | Logical | OR logical operator |
expr1 && expr2 | Logical | AND logical operator |
expr1 LIKE expr2 | Logical | Search for a specified pattern in a column |
expr1 = expr2 | Comparison | Checks if expr1 is equal to expr2 |
expr1 > expr2 | Comparison | Greater comparator |
expr1 >= expr2 | Comparison | Greater or equal comparator |
expr1 < expr2 | Comparison | Less comparator |
expr1 <= expr2 | Comparison | Less or equal comparator |
expr1 != expr2 | Comparison | Not equal comparator |
expr1 RLIKE expr2 | Comparison | Executes a "LIKE" comparison using a regexp expression |
n1 % n2 | Math | Modulo operator |
n1 + n2 | Math | Addition operator |
n1 - n2 | Math | Subtraction operator |
n1 *n2 | Math | Multiplication operator |
n1 / n2 | Math | Division operator |
n1 ** n2 | Math | Exponential operator (n1 to n2th power) |
Math functions
Function |
Definition |
ABS(n) |
Absolute value of number |
ACOS(n) |
Arc cosine of n |
ASIN(n) |
Arc sine of n |
ATAN(n) |
Arc tangent of n |
COS(n) |
Cosine of n |
CEIL(n) |
Smallest integer value that is greater than or equal to a number. |
COSH(n) |
Hyperbolic cosine of n |
DEGREES(n) |
Argument converted to degrees of n |
EXP(n1,n2) |
Exponential (n1 to n2^{th} power) |
FLOOR(n) |
Largest integer value that is less than or equal to a number. |
GREATEST(expr1, expr2, ...) |
Greatest value in a list of expressions |
LEAST(expr1, expr2, ...) |
Smallest value in a list of expressions |
LN(n) |
Natural Log of n, where n>0 |
LOG(n1,n2) |
Log of n1, base n2 |
MINUS(n1,n2) |
Difference between n1 and n2 |
PI() |
PI number of n |
POWER(n1,n2) |
n1 raised to the n2th power. |
RADIANS(n) |
Argument converted to radians of n |
RAND() |
Generate a random value |
ROUND(n,[p]) |
Number n rounded to a given (optionnal) precision p. p can be negative, to cause p digits left of the decimal point of the value n to become zero. |
SIGN(n) |
Sign of a number (-1 or 1) |
SIN(n) |
Sine of n |
SINH(n) |
Hyperbolic Sine of n |
SQRT(n) |
Square root of n |
SUBSTRACTION(n1,n2) |
Substract n2 from n1 |
TAN(n) |
Tangent of n |
TANH(n) |
Hyperbolic tangent of n |
TRUNCATE(n1,n2) |
n1 truncated to n2 decimal places. |
Date functions
Function |
Definition |
ADD_MONTHS(date,num_months) |
Returns date + num_months (can be negative) |
CURRENT_DATE() |
Get the current system date |
CURRENT_TIMESTAMP() |
Get the current system timestamp |
DATE_ADD(date1,number,period) |
Add to date1 the number of period (unit in SECOND,MINUTE,HOUR,DAY,MONTH,YEAR) |
DATE_INTERVAL(date1,date2,period) |
Compute the difference btw date1 and date2 in a particular period (unit in SECOND,MINUTE,HOUR,DAY) |
DATE_SUB(date1,date2) |
Remove date2 from date1 |
DATE_SUB(date1,number,period) |
Remove from date1 the number of period (unit in SECOND,MINUTE,HOUR,DAY,MONTH,YEAR) |
DATE_TRUNCATE(date,format) |
Truncate the date (it can be a Date or a Timestamp) depending on the format constant string:
Note: this is useful to perform weekly, monthly, quarterly or yearly analysis |
DAY(date) |
Extract the day of the month from date |
DAY_OF_WEEK(date) |
extract the day of the week date, between 1 and 7. Day 1 is sunday |
DAY_OF_YEAR(date) |
Extract the day of the year from the date |
FROM_EPOCH(n) |
Return a timestamp based on a numeric field representing the number of seconds since 01-01-1970. |
HOUR(date) |
Extract the hour from date |
MINUTE(date) |
Extract minutes from date |
MONTHS(date) |
Extract the month in the year from date |
MONTHS_BETWEEN(date1,date2) |
Number of months between d1 and d2 |
SECOND(date) |
Extract seconds from date |
TO_DATE(timestamp) |
Convert a timestamp to a date |
TO_DATE(text, format) |
Convert a text to a date with a specific format |
TO_EPOCH(timestamp) |
Return a numeric value based on a timestamp field representing the number of seconds since 01-01-1970. |
TO_TIMESTAMP(date) |
Convert a timestamp to a date |
TO_TIMESTAMP(text, format) |
Convert a text to a timestamp with a specific format |
YEAR(date) |
Extract the year from date |
Text functions
Function |
Definition |
TO_CHAR(date or timestamp, format) |
Convert a date or timestamp to a text with a specific format |
CONCAT(s1,s2) |
Concatenate Text1 and text2 |
LENGTH(s) |
Length of a text |
LOWER(s) |
Lower case text |
POSITION(s1, s2) |
Returns the position of s2 within s1 starting from index 1, 0 otherwise |
REPLACE(text, from, to) |
Replace within the first text from text with to text |
TRANSLATE(text, from, to) |
Replace within the first text each single char specified in from text withto the corresponding char specified in to |
SUBSTRING(text,n1 [,n2]) |
Substring of s, starting from index n1 optionally ending at index n2 |
TO_CHAR(number) |
Convert a number to a text |
UPPER(s) |
Uppercase text |
MD5(string) |
Calculates the MD5 hash of string, returning the result in hexadecimal |
REVERSE(string) |
Returns reversed string |
SPLIT_PART(string, delimiter, position) |
Splits string on delimiter and return the given field (counting from one) |
Regex functions
Function |
Definition |
REGEXP_COUNT(string, regexp) |
Counts the number of occurrence of the regexp within the string (Redshift only) |
REGEXP_INSTR(string, regexp) |
Returns the position of the first occurrence found of the searched regexp within the string (Redshift & Oracle) |
REGEXP_REPLACE(string, regexp, replace) |
Replace substring matching a POSIX regular expression (All DBs except MySQL) |
REGEXP_SUBSTR(string, regexp) |
Extract substring matching POSIX regular expression (All DBs except MySQL) |
JSON functions
These functions are only supported by the Redshift plugin.
Function |
Definition |
JSON_ARRAY_LENGTH ('json string') |
Returns the number of elements in the outer array of a JSON string |
JSON_EXTRACT_ARRAY_ELEMENT_TEXT('json string', pos) |
Returns a JSON array element in the outermost array of a JSON string, using a zero-based index. |
JSON_EXTRACT_PATH_TEXT(json string', 'path_elem' [,'path_elem'[, …]]) |
Returns the value for the key:value pair referenced by a series of path elements in a JSON string. The JSON path can be nested up to five levels deep. |
Aggregate functions
Function |
Definition |
COUNT(*) or COUNT([DISTINCT] expr) |
Count the no of rows returned or no of rows returned by expr |
AVG([DISTINCT] value expr, [partition expr], [order by expr]) |
Average value of ‘n' ignoring NULLs. |
DISTINCT(expr) |
Get distinct values returned by expr |
MAX(value expr, [partition expr], [order by expr]) |
Maximum value within an expression |
MEDIAN([DISTINCT] n) |
Median of n, ignoring NULLs. |
MIN(value expr, [partition expr], [order by expr]) |
Minimum value within an expression |
SUM(value expr, [partition expr], [order by expr]) |
Sum an expression |
VARIANCE([DISTINCT] n) |
Variance of n, ignoring NULLs |
Numeric functions
Function |
Definition |
TO_INTEGER(expr) |
Convert a text, or a numeric field to an integer |
TO_NUMBER(expr) |
Convert a text, an integer or a numeric field to a float |
TO_NUMBER(expr,size,precision) |
Convert a text, an integer or a numeric field to a specific numeric format |
Logical functions
Function |
Definition |
CASE(condition1,then1,...,[else]) |
Group the data into sub-sets |
ISNULL(expr) |
Expr is null logical function |
NOT(expr1, expr2) |
Expr1 not equal to expr2 logical operator |
NULLIF(expr1, expr2) |
Returns the first expression if the two expressions aren't equal or a null value if the two expressions are equal |
Miscellaneous functions
Function |
Definition |
EXISTS(expr) |
Checks that a sub condition is matched |
LPAD(text,length,text) |
Left pad with the text the text to specified length |
LTRIM(text,[character]) |
Remove leading characters from the text. By default, the character to remove is a whitespace |
RANK([partition expr], [order by expr]) |
Returns the rank of a value in apartition expression. Same values within a partition have the same rank and the partition can be ordered |
ROW_NUMBER([partition expr], [order by expr]) |
Returns a number to each row to which it is applied in a partition expression. The number returned is unique within its partition and the partition can be ordered |
RPAD(text,length,text) |
Right pad with the text the text to specified length |
RTRIM(text,[character]) |
Remove trailing characters from the text. By default, the character to remove is a whitespace |
TRIM(text, [character]) |
Remove leading and trailing characters from the text. By default, the character to remove is a whitespace |
0 Comments