Other – Documentation and Support | Knowi

LAG

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

LAG(<field>[, offset[, default]])

select LAG(sales, 5)      -- Get sales from 5 rows behind
select LAG(sales, 3, 0)   -- Get sales from 3 rows behind,
                             default to 0 if none

Users can also group data in the LAG function to look behind within partitions.

LAG(<field>[, offset[, default[, <grouping field(s)>]]])

select LAG(sales, 1, NONE, customer)   -- Get sales from 1 row behind,
                                       default to NONE if none, group by
                                       customer

LEAD

Useful to access data from a subsequent row with an optional row offset (opposite of LAG).

select LEAD(<field>[, offset[, default]])

select LEAD(sales, 1)    -- Get next row's sales value
select LEAD(sales, 2, 0) -- Get sales from 2 rows ahead,
                            default to 0 if none

Users can also group data in the LEAD function to look ahead within partitions.

LEAD(<field>[, offset[, default[, <grouping field(s)>]]])

select LEAD(sales, 1, 0, customer) -- Get sales from 1 row ahead, 
                                      default to 0 if none, group by
                                      customer

Example combining LAG and LEAD:

select
   date,
   sales,
   product,
   LAG(sales, 1, 0, product) as prev_sales,
   LEAD(sales, 1, 0, product) as next_sales,
   LEAD(sales, 1, 0, product) - LAG(sales, 1, 0, product) as change_win
   ORDER BY product, date

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)

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