SiGNAL SELECT statement and clauses

In this section, it is explained how to use the SELECT statement and clauses in SiGNAL.

SELECT statement

The SELECT statement is used to select data from a process. The data returned is the result set.

Syntax:

SELECT expressions
FROM table
[WHERE condition]
[GROUP BY]
[FILL]
[LIMIT]
[OFFSET]
[ORDER BY index[ ASC | DESC ][NULLS FIRST | NULLS LAST]]  
        
Parameter Description
expressions The columns or calculations that you want to retrieve.
table The table from which you want to retrieve records.
WHERE condition The condition that must be met for the records to be selected. If no condition is provided, then all records are selected.
ORDER BY index

The index of a selected expression used to sort the records in the result set. If more than one expression is provided, separate the values with a comma.

ASC sorts the result set in ascending order by expression, DESC sorts it in descending order.

NULLS FIRST sorts the result set with null values first, NULLS LAST with null values last.

GROUP BY Collects data across multiple records and group the results by one or more columns.
FILL Function to fill results.
LIMIT

The number of records in the result set.

OFFSET The starting point to return rows from a result set.

Example:

SELECT column1, column2
FROM table
Parameter Description
column1, column2 The columns from which you want to retrieve records.
table The table from which you want to retrieve records.

This query returns the data of column1 and column2 from the specified table.

Only little effort is required to select multiple attributes and order and limit the result set.

Example:

SELECT case_id, Status, "Customer ID"
FROM table
ORDER BY 2 DESC
LIMIT 10

This query returns the case ID, status, and customer ID of the first 10 cases in the table. ORDER BY 2 DESC indicates to order the result set by the second column, which is Status, in descending order.

Subqueries

A subquery is a query that is nested inside a SELECT statement, or inside another subquery. You can use a subquery anywhere an expression is allowed to retrieve data on event-level.

Syntax:

SELECT ( 
SELECT(event_name))
FROM table
Parameter Description
event_name The column or expression from which you want to retrieve the first element.
table The table from which you want to retrieve records.

Example 1:

SELECT "City" AS "Sales Region",
AVG((SELECT LAST("end_time")-FIRST("end_time"))) AS "AVG Cycle Time"
FROM THIS_PROCESS
ORDER BY 2 ASC

This query returns the average cycle time per city.

Example 2:

SELECT SUM(
(SELECT SUM(IF("event_name"='T-shirt Printed',1,0))))
FROM THIS_PROCESS

This query maps a number to an event and sums the numbers up to count the number of occurrences of this event.

SELECT (DISTINCT)

The SELECT DISTINCT () function returns a table with the distinct values of the evaluated expression. If NULL values are present, they are included. This function is not supported in subqueries.

Syntax:

SELECT DISTINCT expression
FROM table
Parameter Description
expression

The column, expression, or event-attribute that is returned. If more than one expression is provided, separate the values with a comma.

table

The table of which you want to count the records.

Example 1 (case-level):

SELECT DISTINCT city 
FROM THIS_PROCESS
ORDER BY 1 ASC

This query returns a distinct list of cities in ascending order. Duplicate rows are removed from the result set so that every city only appears once.

Example 2 (event-level without FLATTEN):

SELECT DISTINCT event_name AS "Event Name:" 
FROM THIS_PROCESS

This query returns a table that is grouped by distinct event lists, that is, distinct sequences of events. Duplicate rows are removed from the result set so that every sequence of events only appears once.

Example 3 (event-level with flatten):

SELECT DISTINCT event_name
FROM FLATTEN (THIS_PROCESS)
ORDER BY 1 ASC

This query returns a distinct list of events in ascending order. Duplicate rows are removed from the result set so that every event only appears once.

FROM clause

The FROM clause is used to specify the process from which to retrieve the data.

Syntax:

FROM table
Parameter Description
table The table from which you want to retrieve records. You can reference the process by the expressionTHIS_PROCESSor the process ID. You find the process ID in the process settings, on theAPItab.

WHERE clause

The WHERE clause is used to filter the results and apply conditions to the SELECT statement.

Syntax:

WHERE condition
Parameter Description
condition The condition that must be met for records you want to select.

GROUP BY clause

The GROUP BY clause is used to collect data across multiple records and group the results by one or more columns. It's often used with aggregate functions, for example COUNT, MAX, MIN, and AVG. Read more in section Aggregate functions.

Syntax:

SELECT expression1, expression2, ... expression_n, 
aggregate_function (aggregate_expression)
FROM table
GROUP BY index 1...n
Parameter Description

index 1...n

The index of a selected expression that is not encapsulated within an aggregate function. The expression must be included in the GROUP BY clause at the end of the statement.

The expression relates either to a case-level attribute, for example, "city", or to an event-level attribute, for example, "event-name". In the first case, the table is grouped by cities. In the second case, the table is grouped by the list of identical sequences of events. This is useful to identify process variants.

aggregate_function An aggregate function such as the SUM, COUNT, MIN, MAX, or AVG function.
aggregate_expression The column or expression that the aggregate function is used on.
table The table from which you want to retrieve records.

Example 1 (case-level):

SELECT city, COUNT(case_id)
FROM THIS_PROCESS
GROUP BY 1

GROUP BY 1 refers to the first column in the SELECT statement, which is city. So, this query returns the amount of cases per city.

Example 2 (event-level):

SELECT event_name, COUNT(case_id)
FROM THIS_PROCESS
GROUP BY 1

GROUP BY 1 refers to the first column in the SELECT statement, which is event_name. So, this query returns the sequence of the events, that is, all variants, and the number of their occurrences.

ORDER BY clause

The ORDER BY clause is used to sort the result set in ascending or descending order, and add one or more sorting criteria.

Syntax:

SELECT expression 
FROM table
ORDER BY order_index [ASC | DESC] [NULLS FIRST | NULLS LAST]
Parameter Description
expression The columns or calculations that you want to retrieve.
table The table from which you want to retrieve records.
order_index The index of a selected expression used to sort the records in the result set. If more than one expression is provided, separate the values with comma.
[ ASC | DESC ] [NULLS FIRST | NULLS LAST]

ASC sorts the result set in ascending order by order_expression, DESC sorts it in descending order.

NULLS FIRST | NULLS LAST sorts the result set in order by null values first or last in list, default to first.

Example:

SELECT case_ID, discount, customer
FROM THIS_PROCESS
ORDER BY 2 DESC NULLS FIRST, 3 ASC

This query returns all discounts in this process and sorts them in descending order, starting the list with cases that don't have a discount. Additionally, the results are sorted by customer names in ascending order.

FILL clause

The FILL clause is used to fill results with values according to the fill specification.

Syntax:

SELECT expression
FROM table
GROUP BY index 1...n
FILL specification 	
Parameter Description
expression The columns or calculations that you want to retrieve.
table The table from which you want to retrieve records.
specification

The fill specification for each of the selected expressions in the same order that those expressions appear after SELECT.

TIMESERIES(date_part): Fills gaps in the result set for a timeseries with NULL according to the expression. The precision level (date_part) is given in single quotes for the timeseries gaps:

Available values:

  • year

  • quarter

  • month

  • week (ISO 8601-week numbering is applied)

  • day

  • hour

GROUP: Fills any grouping expressions (the ones referenced in the GROUP BY) by repeating the same value each time a timestamp is added inside this group.

NULL: Fills all results with NULL. No further expression required.

The TIMESERIES specification is required. Specifications can be omitted at the end and then default to NULL, but if the first expression is an aggregation for example, then NULL has to be specified.

Example:

SELECT DATE_TRUNC('month', (SELECT FIRST(end_time))), "City", COUNT(case_id)
FROM THIS_PROCESS
ORDER BY 2,1
FILL TIMESERIES('month'), GROUP, NULL

This query fills the gaps in the timeseries for the selected duration of time.

LIMIT clause

With the LIMIT clause, you can specify the number of rows to return. Normally, you use this clause together with the ORDER BY clause.

Without a LIMIT clause, the result set is limited to 500 rows by default.

Syntax:

SELECT expression
FROM table
LIMIT number
Parameter Description
expression The column or expression that you want to retrieve.
table The table from which you want to retrieve records.
number The number of rows in the result set.

Example:

SELECT discount 
FROM THIS_PROCESS
LIMIT 5

This query returns the first 5 rows with discounts in this process.

OFFSET clause

With the OFFSET clause, you can specify the starting point to return rows from a result set.

Syntax:

SELECT expression
FROM table
LIMIT number
OFFSET offset_number
Parameter Description
expression The column or expression that you want to retrieve.
table The table from which you want to retrieve records.
number The number of rows in the result set.
offset_number The amount or rows to skip from the top of the table.

Example:

SELECT discount 
FROM THIS_PROCESS
LIMIT 5
OFFSET 10

This query returns the discounts in row 11 to 16.

FLATTEN operator

The flatten operator flattens the nested table with the event attributes in a way that each event attribute becomes a top-level row. Case attributes are repeated accordingly.

Nested table before flattening:

Table after flattening:

The flattened table allows to perform aggregations based on event names or other event attributes.

Syntax:

SELECT expression
FROM FLATTEN (tablename)
Parameter Description
expression The column or expression that is queried, involving both case-level attributes and event-level attributes.
tablename

The nested table from which you want to retrieve records.

Example 1:

SELECT case_id,"Customer_ID", "Order Status", "City", "event_name", "end_time"
FROM FLATTEN (THIS_PROCESS)

This query creates a flattened table (see flattened table above) that allows further aggregations on event attribute-level.

Example 2:

SELECT COUNT (DISTINCT case_id), "event_name"
FROM FLATTEN (THIS_PROCESS)
GROUP BY 2	

This query returns in how many cases a particular event occurs.