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:
WHEN country = 'USA'
THEN 'North America'
WHEN country = 'Mexico'
THEN 'North America'
END AS 'West Region'
Returns the value of the field for the specified percentile rank.
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)
Opposite of Transpose, folds columns into rows.Â
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 *.Â
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.Â
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
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)
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.