Other – Documentation and Support | Knowi

LAG

Useful to access data from a previous row with an optional row offset;

LAG(F, null, Lag.class, “LAG(<field>[, offset[, default[, <grouping field(s)>]]])“),

select LAG([, offset[, default]])
select LAG(customer, 3)

CASE WHEN (IF..ELSE)

CASE WHEN statements provide great flexibility when dealing with buckets of results or when you need to find a way to filter out certain results. Another way to think of it is it's a conditional logic similar to IF-THEN statements in other programming languages.

When using a CASE WHEN statement, it's important to remember you need a condition, what to do when that condition is met, and an END clause. A simple example is below:

CASE 
WHEN condition
THEN result
ELSE other_result
END

For example,

SELECT
CASE
WHEN country = 'USA'
THEN 'North America'
WHEN country = 'Mexico'
THEN 'North America'
ELSE country
END AS 'West Region'

PERCENTILE

Returns the value of the field for the specified percentile rank.

PERCENTILE(<field>, <percentile>)

select percentile(sent,75)

TRANSPOSE

Pivots row values for a field to columns

TRANSPOSE(<field to transpose>, < current column name>)

select transpose(Message Type,Sent)

To collapse based on a key field, use:

TRANSPOSE(<field to transpose>, <current column name>, <key column name>)

select transpose(Message Type, Sent, Customer)

REVERSE_TRANSPOSE

Opposite of Transpose, folds columns into rows.Â

Syntax:

REVERSE_TRANSPOSE(<New ID column>, <New Value column>, <Value column 1>, ...., <Value column N>)(, , , ...., )

As you can see, I have initial data with 5 columns. After executing: select reversetranspose(NEWID, NEWV, V1, V2, V3) I get them value columns folded into the new column, where NEWID's value is the old value column names (V1, V2, V3 one for each new row) and the NEW_V contains the corresponding value.Â

When you have multiple columns that you want to fold in, specify all the columns you want to pin it by, specify the "pin" columns at the front, followed by new ID and the value column, followed by a *.Â

Syntax:

REVERSE_TRANSPOSE(Customer, Campaign, State, <New ID column>, <New Value column>, *)

This will fold in all the columns except for the columns in the first section.Â

INJECT

Injects last value records in for a date range when the values are not present for that date.

For example, if a sensor emits data point 100 for 01/01/2016 and and the next change of value is at 200 10 days later, you can use the inject function to inject 100 into all dates in between that range.

INJECT(<Date Field>, <Start Date for Injecting>, <End Date for Injecting>, <Injection Frequency> [, <Select Fields>])
[group by <Dimension 1>[, ..., <Dimension N>]]

The optional <Select Field> can either be * (for all fields) or a comma separated list of selected fields from input data.

select inject(date, start_range_date, end_range_date, 1d, Name, Division, Score)
group by Name, Division

APPEND

Combines the results of two queries into the same dataset

(select sum(sent) as Facebook where customer=Facebook) append (select sum(sent) as LinkedIn Sent where customer=Linkedin)

Nested

Use the dot notation to query nested elements and the array notation for selecting items in an array. The example below uses a JSON nested string and uses Cloud9QL to parse it.

LAG