Absolute of a value

Signature

ABS(col1)

Usage Examples

select ABS(-12.1) AS value
from ignoreMe

select ABS(12.1) AS value
from ignoreMe

Return time as epoch millis before the given period (in ISO-8601 duration format).

Examples:

• "PT20.345S" -- parses as "20.345 seconds"

• "PT15M" -- parses as "15 minutes" (where a minute is 60 seconds)

• "PT10H" -- parses as "10 hours" (where an hour is 3600 seconds)

• "P2D" -- parses as "2 days" (where a day is 24 hours or 86400 seconds)

• "P2DT3H4M" -- parses as "2 days, 3 hours and 4 minutes"

• "P-6H3M" -- parses as "-6 hours and +3 minutes"

• "-P6H3M" -- parses as "-6 hours and -3 minutes"

• "-P-6H+3M" -- parses as "+6 hours and -3 minutes"

Signature

ago()

Usage Examples

This function is typically used in the predicate to filter on timestamps for recent data. e.g. filter data on recent 1 day.

Concatenates two arrays of doubles.

Signature

arrayConcatDouble('colName1', 'colName2')

Usage Examples

This example assumes the multiValueTable columns mvCol1 and mvCol2 are both of type DOUBLE with singleValueField in the table schema set to false.

Concatenates two arrays of floats.

Signature

arrayConcatFloat('colName1', 'colName2')

Usage Examples

This example assumes the multiValueTable columns mvCol1 and mvCol2 are both of type FLOAT with singleValueField in the table schema set to false.

This function scans the given dataset to identify the maximum and minimum values in the specified measuring columns. Once these extreme values (the maxima and minima) are found, the function locates the corresponding entries in the projection column. These entries are associated with the rows where the extreme values were found in the measuring columns. The function then returns these projection column values, providing a way to link the extreme measurements with their corresponding data in another part of the dataset.

Signature

EXPR_MIN (projectionCol, measuringCol1, measuringCol2, measuringCol3)

EXPR_MAX (projectionCol, measuringCol1, measuringCol2, measuringCol3)

Usage Examples

Find the user with maximum activity. If there are multiple users, break the tie with their last_activity_date. If still a tie, break with user_id. And project user_id.

More useful is that this multiple such aggregation function can be used with GROUP BY

Note:

1. In cases where multiple rows share the same extreme values in the measuring columns, all such rows will be returned by the function.

2. If the goal is to project multiple different columns that correspond to the same set of measuring columns, you can achieve this by invoking the function multiple times, each time specifying a different projection column.

3. This impl does not work with AS clause (e.g. SELECT exprmin(longCol, doubleCol) AS exprmin won't work)

4. Putting exprmin/exprmax column inside order by clause (e.g. SELECT intCol, exprmin(longCol, doubleCol) FROM table GROUP BY intCol ORDER BY exprmin(longCol, doubleCol)) is not supported as semantically ordering multi-column multi-row exprmin/exprmax results doesn't make sense

5. Currently projecting MV bytes column doesn't work for now due to an issue

This page contains reference documentation for functions in Apache Pinot.