Creating Queries Flashcards
What does the ‘GROUP BY’ function do?
*If your attribute has more than one category you can use the group by function to help see it in a stacked (eg. stacked bar chart) format
What 4 SiGNAL operators are available?
- arithmetic
- comparison
- logical
- mapping
Explain Arithmetic
+ - * / %(modulo i.e remainder)
eg. SELECT discount / price * 100
FROM THIS_PROCESS
Explain Comparison
> < >= <= = <>(not equal to)
Explain Logical
AND , IN, NOT, OR
Explain Mapping
IF (returning value based on condition)
eg. SELECT IF (condition, then, else)
Explain ‘Order by’ function
Function to sort the result set according to sorting criteria
Example of the order by function
‘ORDER BY column number ASC/DESC
Explain ‘Limit’ function
Function to limit the number of records in the result set
Example of the ‘Limit’ function
LIMIT no. of entries you want
What is Alias with and example
Aliases help to give data/column headers a temporary name to make them easier to read.
SELECT expression AS [“ALIAS”]
e.g SELECT “Case ID” as “Customer ID” (on the table produced it will be labelled as customer ID)
What is a subquery
a query that is nested inside a SELECT statement, or inside another sub query
when is subqueries used?
when retrieving data on an event-level
What are 3 date functions
DATE_TRUNC
NOW()
DURATION
Explain DATE_TRUNC
limits time stamps to certain units of time by removing detail
eg. SELECT….
DATE_TRUNC (‘Month’, (SELECT(end_time))).
as ‘Month in 2017’
[created row called ‘Month in 2017’, where it produced dates that stayed as the first day on every month in 2017, with the month only changing]
Explain NOW()
Helps calculate the duration between the current and given time
e.g SELECT…
(SELECT NOW() - LAST(end_time)) as “Last order”
[created row called ‘last order’, produced durations eg. 3y 4w]
Explain DURATION
Compares a duration of time with a threshold, and support strings (e.g “2 weeks”, “3days”)
eg. …
WHERE (SELECT LAST(end_time) - FIRST (End_time)) > DURATION “2weeks”
What is the FILTER function for?
allows you to filter an input for an aggregations function
Structure of the filter function
SELECT expressions
FILTER (WHERE conditions)
FROM table/process
What are matching operators?
Matches allow you to filter cases based on the indicated ‘MATCHES’ terms
[explain these matching operators]
A->B
A~>B
^A
[when it says directly followed you have to remember this is mapping a diagram so its the next task]
A->B : A direction followed by B
A~>B : A indirectly or directly followed by B
^A : sequence starts with A in any specific case
[explain these matching operators]
A NULL
NOT A
A NOT B
A NOT (B|C)
(A|B)
A NULL : A value is NULL
NOT A : All values that are not A
A NOT B : A directly followed by a value that is not B
A NOT (B|C) : A followed by a value that is not B or C
(A|B) : A or B
[explain these matching operators]
B$
A ANY
B$ : Sequence ends with B in any specific case
A ANY : A occurs anywhere in the case
What is BEHAVIOUR MATCHES operator with an example
Allows to filter MATCHES even further with expressions not related to task sequence
eg. (trying to obtain values where the end time is greater than a specific date for shipping goods)
WHERE BEHAVIOUR
(event_name = “Ship Goods Standard” and END_TIME > Date ‘2017-08-17’ ) AS behaviour_one
(event_name = “Receive Customer order” and END_TIME > Date ‘2017-08-17’ ) AS behaviour_two
MATCHES (^behaviour_one~>behaviour_two)
