SiGNAL functions

In this section, it is explained which functions you can use in SiGNAL queries.

Aggregate functions

AVG()

The AVG() function returns the average of an expression in a SELECT statement. NULL values are ignored.

Syntax:

SELECT AVG(aggregate_expression)
FROM table
Parameter Description
aggregate_expression The column or expression that is averaged.
table The table from which you want to retrieve records.

Example:

SELECT AVG("Order Amount")
FROM THIS_PROCESS

This query returns the average order amount of all cases.

SUM()

The SUM() function returns the sum of an expression in a SELECT statement. NULL values are ignored.

Syntax:

SELECT SUM(aggregate_expression)
FROM table
		
Parameter Description
aggregate_expression The column or expression that is summed up.
table The table from which you want to retrieve records.

Example:

SELECT SUM("ORDER AMOUNT")
FROM THIS_PROCESS
WHERE("City"='Boston')

This query returns the total order amount in Boston.

COUNT()

The COUNT() function returns the number of rows. NULL values aren't counted.

Syntax:

SELECT COUNT(count_expression)
Parameter Description
count_expression

The column or expression that is counted.

Example:

SELECT COUNT(case_id) 
FROM THIS_PROCESS

This query returns the number of cases.

COUNT (DISTINCT)

The COUNT DISTINCT() function returns the number of distinct values of the evaluated expression. If NULL values are present, they are included. Since event-level attributes are lists, this function counts the number of distinct lists. For example, COUNT (DISTINCT event_name) counts the number of distinct event sequences per case. These event sequences represent the process variants.

Syntax:

SELECT COUNT(DISTINCT count_expression)
FROM table
Parameter Description
count_expression

The column, event-attribute, or expression that is counted.

table

The table of which you want to count the records.

Example 1 (case-level):

SELECT COUNT(DISTINCT city) 
FROM THIS_PROCESS

This query returns the number of distinct cities.

Example 2 (event-level):

SELECT COUNT(DISTINCT event_name) 
FROM THIS_PROCESS

This query returns the number of process variants.

MIN()

The MIN() function returns the smallest value.

Syntax:

MIN(expression)
Parameter Description
expression

The column or expression that is counted.

Example:

SELECT MIN(discount)
FROM THIS_PROCESS

This query returns the smallest discount.

MAX()

The MAX() function returns the largest value.

Syntax:

MAX(expression)
Parameter Description
expression

The column or expression that is counted.

Example:

SELECT MAX(discount)
FROM THIS_PROCESS

This query returns the largest discount.

FIRST()

The FIRST() function returns the first element of an array.

Syntax:

SELECT FIRST(first_expression)
FROM table
Parameter Description
first_expression The column or expression of which you want the first element.
table

The table of which you want to retrieve the data.

Example:

SELECT 
SELECT FIRST(event_name))
FROM THIS_PROCESS

This query returns the name of the first event in this process.

LAST()

The LAST() function returns the first element of an array.

Syntax:

SELECT LAST(last_expression)
FROM table
Parameter Description
last_expression The column or expression of which you want the last element.
table

The table of which you want to retrieve the data.

Example:

SELECT 
SELECT LAST(event_name))
FROM THIS_PROCESS

This query returns the name of the lasst event in this process.

BOOL_OR()

The BOOL_OR function returns a value if any condition is true.

Syntax for simple condition:

SELECT BOOL_OR(condition)
Parameter Description
condition

The values to test.

Example with simple condition:

SELECT 
case_id,
SELECT 
BOOL_OR(event_name='event1')) AS "at least one event is event1"
FROM THIS_PROCESS

This query returns all cases for which at least one event is "event1".

BOOL_AND()

The BOOL_AND function returns a value if all conditions are true.

Syntax for simple condition:

SELECT BOOL_AND(condition)
Parameter Description
condition

The values to test.

Example with simple condition:

SELECT 
case_id,
SELECT 
BOOL_AND(event_name='event1')) AS "all events are event1"
FROM THIS_PROCESS

This query returns all cases for which all events are "event1".

IF()

The IF function returns a value if a condition is true, or another value if a condition is false. The values for then and else can be a literal, COL_EXPR, or CONDITIONAL_EXPR.

Syntax for simple condition:

SELECT IF(condition, then, else)

Syntax for nested conditions:

SELECT IF(condition, then, else if(condition, then, else if(condition, then, else)))
Parameter Description
condition

The value to test.

Nested conditions are allowed.

then The value to return if condition is true.
else The value to return if condition is false.

Example with simple condition:

SELECT IF(country='DE', 1.19, 1.0) * price
FROM THIS_PROCESS

For records with Germany as the country in this process, the price is multiplied with 1.19. If the country is not Germany, then the price is multiplied with 1.0.

OCCURRENCE()

The OCCURRENCE function counts the number of occurrences of events in a sequence, that is, identifies reworks. This function is always used as a nested subquery.

Syntax:

OCCURRENCE(event_name)AS alias_name
Parameter Description
event_name

The expression whose occurrence is to be counted in a sequence (case).

alias_name

The temporary name to assign.

Enclose the name in double quotes if it contains a character that isn't a letter or digit.

Example:

SELECT case_id AS "Case","event_name" AS "Events",
SELECT COUNT ("event_name")AS "No. Events"),
SELECT MAX (event_occurrence)
FROM(SELECT OCCURRENCE("event_name") AS event_occurrence)as sub)as "Max. Event Occurrence"	
FROM THIS_PROCESS

This query returns by case the total number of events and calculates a cumulative count per event. The maximum value of this calculation is returned as the maximum number of event occurrences, in the example "2". An occurrence of "2" indicates one rework (in the example: Ship Goods Standard).

Date functions

Use date functions to execute operations on date and time values.

DATE_TRUNC()

With DATE_TRUNC, you can remove details of timestamps and reduce them to these units of time:

  • year

  • quarter

  • month

  • week (ISO 8601-week numbering is applied)

  • day

  • hour

  • minute

  • second

  • millisecond

Syntax:

SELECT DATE_TRUNC(date_part, column)
Parameter Description
date_part

The precision level in single quotes for the timestamp that is returned.

Available values:

  • year

  • quarter

  • month

  • week (ISO 8601-week numbering is applied)

  • day

  • hour

  • minute

  • second

  • millisecond

column

The name of the column containing the timestamp you want to retrieve.

Example:

SELECT DATE_TRUNC('day', shipping_date)
FROM THIS_PROCESS

This query returns the shipping dates in this process in the format YYYY-MM-DD, without time information.