AMC SQL Function Reference

DATE_TRUNC cuts a timestamp down to a unit such as week or month, so events can be grouped into periods.

EXTRACT reads exactly one time unit from a DATE or TIMESTAMP, for example the hour or the day of week.

CONVERT_TIME_ZONE_FROM_UTC converts a UTC timestamp to another time zone such as Europe/Berlin.

EXTEND_TIME_WINDOW widens the period of an input table beyond the reporting window of the query, backwards and forwards.

SECONDS_BETWEEN returns the seconds between two timestamps, computed as the second value minus the first.

APPROX_COUNT_DISTINCT counts the distinct values of a column approximately and is faster than COUNT(DISTINCT) on large data.

COLLECT gathers the values of a column per group into an array; with DISTINCT only the distinct values.

ARRAY_CONTAINS checks whether a value occurs in an array and returns true or false.

ARRAY_INTERSECT returns a new array with the elements that appear in both input arrays.

ARRAY_TO_STRING joins the elements of an array into one string, separated by a delimiter. It is the documented counterpart of ARRAY_JOIN.

EXPLODE turns an array into multiple rows, one row per element. It is the reverse of COLLECT.

CARDINALITY returns the number of elements in an array, for example how many values COLLECT gathered per group.

COALESCE returns the first value that is not NULL. If all values are NULL the result is NULL.

NULLIF returns NULL if both expressions are equal, otherwise the first expression.

LAG reads the value of an earlier row in the same partition without joining the table to itself.

ROW_NUMBER numbers the rows of a partition consecutively in the order of the OVER clause.

PERCENTILE returns the value at a given point of a distribution, such as the 90th percentile of click cost. APPROX_PERCENTILE computes it approximately.

NAMED_ROW groups several columns under one name, each with its own label, in a single result column.