Expressions and functions writing guide

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:

  1. Constant values
  2. Referencing objects
  3. Naming expressions
  4. Operators
  5. Functions:

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 n2th 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)
Note: Adding two dates is not possible

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:

  • if format="day" then the result is the Date part of date (excluding the time)
  • if format="week" then the result is the first day of the week containing date
  • if format="month" then the result is the first day of the month containing date
  • if format="quarter" then the result is the first day of the quarter containing date
  • if format="year" then the result is the first day of the year containing date

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.
You can specify an average based on a group of rows:
Use the partition expression to partition the query result set into groups based on one or more value expression. If you omit this clause, then the function treats all rows of the query result set as a single group
Use the order by expression to specify how data is ordered within a partition

DISTINCT(expr)

Get distinct values returned by expr

MAX(value expr, [partition expr], [order by expr])

Maximum value within an expression
You can specify an average based on a group of rows:
Use the partition expression to partition the query result set into groups based on one or more value expression. If you omit this clause, then the function treats all rows of the query result set as a single group
Use the order by expression to specify how data is ordered within a partition

MEDIAN([DISTINCT] n)

Median of n, ignoring NULLs.

MIN(value expr, [partition expr], [order by expr])

Minimum value within an expression
You can specify an average based on a group of rows:
Use the partition expression to partition the query result set into groups based on one or more value expression. If you omit this clause, then the function treats all rows of the query result set as a single group
Use the order by expression to specify how data is ordered within a partition

SUM(value expr, [partition expr], [order by expr])

Sum an expression
You can specify sum based on a group of rows:
Use the partition expression to partition the query result set into groups based on one or more value expression. If you omit this clause, then the function treats all rows of the query result set as a single group
Use the order by expression to specify how data is ordered within a partition

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
The EXISTS condition is considered "to be met" if the subquery returns at least one row.

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

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.