Other

CONVERT_TYPE

Re-evaluates and converts the data types of the result set's columns. By default (or with TRUE), types are auto-detected from the values, so numeric strings become numbers and date-like strings become dates.

CONVERT_TYPE([<Detect Type>])

select convert_type()
select convert_type(true)

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

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

NOOP

Pass-through operation that returns the value unchanged. Useful for carrying a field through a step without applying any transformation or aggregation to it.

NOOP(<field>)

select noop(Customer)

NOT_EMPTY

Aggregate that returns the first non-empty value (non-null and non-blank) within each group. Useful when collapsing rows where only some contain a value.

NOT_EMPTY(<field>)

select Customer, NOT_EMPTY(Notes) as Notes group by Customer

PERCENTILE

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

PERCENTILE(<field>, <percentile>)

select percentile(sent,75)

TO_CSV

Converts the entire result set into a single CSV-formatted value (returned in a column named "csv"). A header row is included by default; pass FALSE to omit it. You may also list one or more columns to exclude from the output.

TO_CSV([<Include Headers>[, <Column to exclude>, ...]])

select to_csv()
select to_csv(false)
select to_csv(true, InternalId)