All Druid SQL functions

:::info Apache Druid supports two query languages: Druid SQL and native queries. This document describes the SQL language. :::

This page provides a reference of Apache Druid® SQL functions in alphabetical order. For more details on a function, refer to the following:

• Aggregation functions
• Array functions
• JSON functions
• Multi-value string functions
• Scalar functions
• Window functions

Example data

The examples on this page use the following example datasources:

• array-example created with SQL-based ingestion
• flight-carriers using FlightCarrierOnTime (1 month) included with Druid
• kttm using KoalasToTheMax one day included with Druid
• mvd-example using SQL-based ingestion
• taxi-trips using NYC Taxi cabs (3 files) included with Druid

To load a datasource included with Druid, access the web console and go to Load data > Batch - SQL > Example data. Select Connect data, and parse using the default settings. On the page to configure the schema, select the datasource label and enter the name of the datasource listed above.

Use the following query to create the array-example datasource:

REPLACE INTO "array-example" OVERWRITE ALL
WITH "ext" AS (
  SELECT *
  FROM TABLE(
    EXTERN(
      '{"type":"inline","data":"{\"timestamp\": \"2023-01-01T00:00:00\", \"label\": \"row1\", \"arrayString\": [\"a\", \"b\"],  \"arrayLong\":[1, null,3], \"arrayDouble\":[1.1, 2.2, null]}\n{\"timestamp\": \"2023-01-01T00:00:00\", \"label\": \"row2\", \"arrayString\": [null, \"b\"], \"arrayLong\":null,        \"arrayDouble\":[999, null, 5.5]}\n{\"timestamp\": \"2023-01-01T00:00:00\", \"label\": \"row3\", \"arrayString\": [],          \"arrayLong\":[1, 2, 3],   \"arrayDouble\":[null, 2.2, 1.1]} \n{\"timestamp\": \"2023-01-01T00:00:00\", \"label\": \"row4\", \"arrayString\": [\"a\", \"b\"],  \"arrayLong\":[1, 2, 3],   \"arrayDouble\":[]}\n{\"timestamp\": \"2023-01-01T00:00:00\", \"label\": \"row5\", \"arrayString\": null,        \"arrayLong\":[],          \"arrayDouble\":null}"}',
      '{"type":"json"}'
    )
  ) EXTEND (
    "timestamp" VARCHAR,
    "label" VARCHAR,
    "arrayString" VARCHAR ARRAY,
    "arrayLong" BIGINT ARRAY,
    "arrayDouble" DOUBLE ARRAY
  )
)
SELECT
    TIME_PARSE("timestamp") AS "__time",
    "label",
    "arrayString",
    "arrayLong",
    "arrayDouble"
FROM "ext"
PARTITIONED BY DAY

Use the following query to create the mvd-example datasource:

REPLACE INTO "mvd-example" OVERWRITE ALL
WITH "ext" AS (
  SELECT *
  FROM TABLE(
    EXTERN(
      '{"type":"inline","data":"{\"timestamp\": \"2011-01-12T00:00:00.000Z\", \"label\": \"row1\", \"tags\": [\"t1\",\"t2\",\"t3\"]}\n{\"timestamp\": \"2011-01-13T00:00:00.000Z\", \"label\": \"row2\", \"tags\": [\"t3\",\"t4\",\"t5\"]}\n{\"timestamp\": \"2011-01-14T00:00:00.000Z\", \"label\": \"row3\", \"tags\": [\"t5\",\"t6\",\"t7\"]}\n{\"timestamp\": \"2011-01-14T00:00:00.000Z\", \"label\": \"row4\", \"tags\": []}"}',
      '{"type":"json"}',
      '[{"name":"timestamp", "type":"STRING"},{"name":"label", "type":"STRING"},{"name":"tags", "type":"ARRAY<STRING>"}]'
    )
  )
)
SELECT
  TIME_PARSE("timestamp") AS "__time",
  "label",
  ARRAY_TO_MV("tags") AS "tags"
FROM "ext"
PARTITIONED BY DAY

ABS

Calculates the absolute value of a numeric expression.

• Syntax: ABS(<NUMERIC>)
• Function type: Scalar, numeric

The following example applies the ABS function to the ArrDelay column from the flight-carriers datasource.

SELECT
  "ArrDelay" AS "arrival_delay",
  ABS("ArrDelay") AS "absolute_arrival_delay"
FROM "flight-carriers"
WHERE "ArrDelay" < 0
LIMIT 1

Returns the following:

Learn more

ACOS

Calculates the arc cosine (arccosine) of a numeric expression.

• Syntax: ACOS(expr)
• Function type: Scalar, numeric

The following example calculates the arc cosine of 0.

SELECT ACOS(0) AS "arc_cosine"

Returns the following:

Learn more

ANY_VALUE

Returns any value of the specified expression.

• Syntax: ANY_VALUE(expr, [maxBytesPerValue, [aggregateMultipleValues]])
• Function type: Aggregation

The following example returns the state abbreviation, state name, and average flight time grouped by each state in flight-carriers:

SELECT
  "OriginState",
  ANY_VALUE("OriginStateName") AS "OriginStateName",
  AVG("ActualElapsedTime") AS "AverageFlightTime"
FROM "flight-carriers"
GROUP BY 1
LIMIT 3

Returns the following:

Learn more

APPROX_COUNT_DISTINCT

Counts distinct values of a regular column or a prebuilt sketch column using an approximate algorithm.

• Syntax: APPROX_COUNT_DISTINCT(expr)
• Function type: Aggregation

The following example counts the number of distinct airlines reported in flight-carriers:

SELECT APPROX_COUNT_DISTINCT("Reporting_Airline") AS "num_airlines"
FROM "flight-carriers"

Returns the following:

Learn more

APPROX_COUNT_DISTINCT_BUILTIN

Counts distinct values of a string, numeric, or hyperUnique column using Druid's built-in cardinality or hyperUnique aggregators. Consider using APPROX_COUNT_DISTINCT_DS_HLL instead, which offers better accuracy in many cases.

• Syntax: APPROX_COUNT_DISTINCT_BUILTIN(expr)
• Function type: Aggregation

The following example counts the number of distinct airlines reported in flight-carriers:

SELECT APPROX_COUNT_DISTINCT_BUILTIN("Reporting_Airline") AS "num_airlines"
FROM "flight-carriers"

Returns the following:

Learn more

APPROX_COUNT_DISTINCT_DS_HLL

Returns the approximate number of distinct values in a HLL sketch column or a regular column. See DataSketches HLL Sketch module for a description of optional parameters.

• Syntax: APPROX_COUNT_DISTINCT_DS_HLL(expr, [lgK, tgtHllType])
• Function type: Aggregation

The following example returns the approximate number of distinct tail numbers in the flight-carriers datasource.

SELECT APPROX_COUNT_DISTINCT_DS_HLL("Tail_Number") AS "estimate"
FROM "flight-carriers"

Returns the following:

Learn more

APPROX_COUNT_DISTINCT_DS_THETA

Returns the approximate number of distinct values in a Theta sketch column or a regular column. See DataSketches Theta Sketch module for a description of optional parameters.

• Syntax: APPROX_COUNT_DISTINCT_DS_THETA(expr, [size])
• Function type: Aggregation

The following example returns the approximate number of distinct tail numbers in the Tail_Number column of the flight-carriers datasource.

SELECT APPROX_COUNT_DISTINCT_DS_THETA("Tail_Number") AS "estimate"
FROM "flight-carriers"

Returns the following:

Learn more

APPROX_QUANTILE

:::info Deprecated in favor of APPROX_QUANTILE_DS. :::

• Syntax: APPROX_QUANTILE(expr, probability, [k])
• Function type: Aggregation

Learn more

APPROX_QUANTILE_DS

Computes approximate quantiles on a Quantiles sketch column or a regular numeric column. See DataSketches Quantiles Sketch module for a description of parameters.

• Syntax: APPROX_QUANTILE_DS(expr, probability, [k])
• Function type: Aggregation

The following example approximates the median of the Distance column from the flight-carriers datasource. The query may return a different approximation on each execution.

SELECT APPROX_QUANTILE_DS("Distance", 0.5, 128)  AS "estimate_median"
FROM "flight-carriers"

Returns a result similar to the following:

Learn more

APPROX_QUANTILE_FIXED_BUCKETS

Computes approximate quantiles on fixed buckets histogram column or a regular numeric column. See Fixed buckets histogram for a description of parameters.

• Syntax: APPROX_QUANTILE_FIXED_BUCKETS(expr, probability, numBuckets, lowerLimit, upperLimit, [outlierHandlingMode])
• Function type: Aggregation

The following example approximates the median of a histogram on the Distance column from the flight-carriers datasource. The histogram has 10 buckets, a lower limit of zero, an upper limit of 2500, and ignores outlier values.

SELECT APPROX_QUANTILE_FIXED_BUCKETS("Distance", 0.5, 10, 0, 2500, 'ignore')  AS "estimate_median"
FROM "flight-carriers"

Returns the following:

Learn more

ARRAY

Constructs a SQL ARRAY literal from the provided expression arguments. All arguments must be of the same type.

• Syntax: ARRAY[expr1, expr2, ...]
• Function type: Array

The following example constructs arrays from the values of the agent_category, browser, and browser_version columns in the kttm datasource.

SELECT ARRAY["agent_category", "browser", "browser_version"] AS "user_agent_details"
FROM "kttm"
LIMIT 5

Returns the following:

Learn more

ARRAY_AGG

Returns an array of all values of the specified expression. To include only unique values, specify DISTINCT.

• Syntax: ARRAY_AGG([DISTINCT] expr, [size])
• Function type: Aggregation

The following example returns arrays of unique values from the OriginState column in the flight-carriers datasource, grouped by Reporting_Airline.

SELECT "Reporting_Airline", ARRAY_AGG(DISTINCT "OriginState", 50000) AS "Origin"
FROM "flight-carriers"
GROUP BY "Reporting_Airline"
LIMIT 5

Returns the following:

Learn more

ARRAY_APPEND

Appends the expression to the array. The source array type determines the resulting array type.

• Syntax: ARRAY_APPEND(arr, expr)
• Function type: Array

The following example appends c to the values in the arrayString column from the array-example datasource.

SELECT ARRAY_APPEND("arrayString",'c') AS "array_appended"
FROM "array-example"

Returns the following:

Learn more

ARRAY_CONCAT

Concatenates two arrays. The type of arr1 determines the resulting array type.

• Syntax: ARRAY_CONCAT(arr1, arr2)
• Function type: Array

The following example concatenates the arrays in the arrayLong and arrayDouble columns from the array-example datasource.

SELECT ARRAY_CONCAT("arrayLong", "arrayDouble") AS "arrayConcatenated" 
FROM "array-example"

Returns the following:

Learn more

ARRAY_CONCAT_AGG

Concatenates array inputs into a single array. To include only unique values, specify DISTINCT.

• Syntax: ARRAY_CONCAT_AGG([DISTINCT] expr, [size])
• Function type: Aggregation

The following example concatenates the array inputs from the arrayDouble column of the array-example datasource into a single array.

SELECT ARRAY_CONCAT_AGG( DISTINCT "arrayDouble") AS "array_concat_agg_distinct"
FROM "array-example"

Returns the following:

Learn more

ARRAY_CONTAINS

Checks if the array contains the specified expression.

Scalar

If the specified expression is a scalar value, returns true if the source array contains the value.

• Syntax: ARRAY_CONTAINS(arr, expr)
• Function type: Array

The following example returns true if the arraySring column from the array-example datasource contains 2.

SELECT "arrayLong", ARRAY_CONTAINS("arrayLong", 2) AS "arrayContains"
FROM "array-example"

Returns the following:

Learn more

Array

If the specified expression is an array, returns true if the source array contains all elements of the expression.

• Syntax: ARRAY_CONTAINS(arr, expr)
• Function type: Array

The following example returns true if the arrayLong column from the array-example datasource contains all elements of the provided expression.

SELECT "label", "arrayLong", ARRAY_CONTAINS("arrayLong", ARRAY[1,2,3]) AS "arrayContains"
FROM "array-example"

Returns the following:

Learn more

ARRAY_LENGTH

Returns the length of the array.

• Syntax: ARRAY_LENGTH(arr)
• Function type: Array

The following example returns the length of array expressions in the arrayDouble column from the array-example datasource.

SELECT "arrayDouble" AS "array", ARRAY_LENGTH("arrayDouble") AS "arrayLength"
FROM "array-example"

Returns the following:

Learn more

ARRAY_OFFSET

Returns the array element at the specified zero-based index. Returns null if the index is out of bounds.

• Syntax: ARRAY_OFFSET(arr, long)
• Function type: Array

The following example returns the element at the specified zero-based index from the arrays in the arrayLong column of the array-example datasource.

SELECT "arrayLong" as "array", ARRAY_OFFSET("arrayLong", 2) AS "elementAtIndex"
FROM "array-example"

Returns the following:

Learn more

ARRAY_OFFSET_OF

Returns the zero-based index of the first occurrence of the expression in the array. Returns null if the value isn't present.

• Syntax: ARRAY_OFFSET_OF(arr, expr)
• Function type: Array

The following example returns the zero-based index of the fist occurrence of 3 in the arrays in the arrayLong column of the array-example datasource.

SELECT "arrayLong" as "array", ARRAY_OFFSET_OF("arrayLong", 3) AS "offset"
FROM "array-example"

Returns the following:

Learn more

ARRAY_ORDINAL

Returns the array element at the specified one-based index. Returns null if the index is out of bounds.

• Syntax: ARRAY_ORDINAL(arr, long)
• Function type: Array

The following example returns the element at the specified one-based index from the arrays in the arrayLong column of the array-example datasource.

SELECT "arrayLong" as "array", ARRAY_ORDINAL("arrayLong", 2) AS "elementAtIndex"
FROM "array-example"

Returns the following:

Learn more

ARRAY_ORDINAL_OF

Returns the one-based index of the first occurrence of the expression in the array. Returns null if the value isn't present.

• Syntax: ARRAY_ORDINAL_OF(arr, expr)
• Function type: Array

The following example returns the one-based index of the fist occurrence of 3 in the arrays in the arrayLong column of the array-example datasource.

SELECT "arrayLong" as "array", ARRAY_ORDINAL_OF("arrayLong", 3) AS "ordinal"
FROM "array-example"

Returns the following:

Learn more

ARRAY_OVERLAP

Returns true if two arrays have any elements in common. Treats NULL values as known elements.

• Syntax: ARRAY_OVERLAP(arr1, arr2)
• Function type: Array

The following example returns true if columns arrayString and arrayDouble from the array-example datasource have common elements.

SELECT "arrayString", "arrayDouble",  ARRAY_OVERLAP("arrayString", "arrayDouble") AS "overlap"
FROM "array-example"

Returns the following:

Learn more

SCALAR_IN_ARRAY

Checks if the scalar value is present in the array. Returns false if the value is non-null, or UNKNOWN if the value is NULL. Returns UNKNOWN if the array is NULL.

• Syntax: SCALAR_IN_ARRAY(expr, arr)
• Function type: Array

The following example returns true if the value 36 is present in the array generated from the elements in the DestStateFips column from the flight-carriers datasource.

SELECT "Reporting_Airline", ARRAY_AGG(DISTINCT "DestStateFips") AS "StateFipsArray", SCALAR_IN_ARRAY(36, ARRAY_AGG(DISTINCT "DestStateFips")) AS "ValueInArray"
FROM "flight-carriers"
GROUP BY "Reporting_Airline"
LIMIT 5

Returns the following:

Learn more

ARRAY_PREPEND

Prepends the expression to the array. The source array type determines the resulting array type.

• Syntax: ARRAY_PREPEND(expr, arr)
• Function type: Array

The following example prepends c to the arrays in the arrayString column from the array-example datasource.

SELECT ARRAY_PREPEND('c', "arrayString") AS "arrayPrepended"
FROM "array-example"

Returns the following:

Learn more

ARRAY_SLICE

Returns a subset of the array from the zero-based index start (inclusive) to end (exclusive). Returns null if start is less than 0, greater than the length of the array, or greater than end.

• Syntax: ARRAY_SLICE(arr, start, end)
• Function type: Array

The following example constructs a new array from the elements of arrays in the arrayDouble column from the array-example datasource.

SELECT "arrayDouble", ARRAY_SLICE("arrayDouble", 0, 2) AS "arrayNew"
FROM "array-example"

Returns the following:

Learn more

ARRAY_TO_MV

Converts an array of any type into a multi-value string.

• Syntax: ARRAY_TO_MV(arr)
• Function type: Array

The following example converts the arrays in the arrayDouble column from the array-example datasource into multi-value strings.

SELECT ARRAY_TO_MV("arrayDouble") AS "multiValueString"
FROM "array-example"

Returns the following:

Learn more

ARRAY_TO_STRING

Joins all elements of the array into a string using the specified delimiter.

• Syntax: ARRAY_TO_STRING(arr, delimiter)
• Function type: Array

The following example converts the arrays in the arrayDouble column of the array-example datasource into concatenated strings.

SELECT ARRAY_TO_STRING("arrayDouble", '') AS "notSeparated"
FROM "array-example"

Returns the following:

Learn more

ASIN

Calculates the arc sine (arcsine) of a numeric expression.

• Syntax: ASIN(expr)
• Function type: Scalar, numeric

The following example calculates the arc sine of 1.

SELECT ASIN(1) AS "arc_sine"

Returns the following:

Learn more

ATAN

Calculates the arc tangent (arctangent) of a numeric expression.

• Syntax: ATAN(expr)
• Function type: Scalar, numeric

The following example calculates the arc tangent of 1.

SELECT ATAN(1) AS "arc_tangent"

Returns the following:

Learn more

ATAN2

Calculates the arc tangent (arctangent) of a specified x and y coordinate.

• Syntax: ATAN2(x, y)
• Function type: Scalar, numeric

The following example calculates the arc tangent of the coordinate (1, -1)

SELECT ATAN2(1,-1) AS "arc_tangent_2"

Returns the following:

Learn more

AVG

Calculates the average of a set of values.

• Syntax: AVG(<NUMERIC>)
• Function type: Aggregation

The following example calculates the average minutes of delay for a particular airlines in flight-carriers:

SELECT AVG("DepDelayMinutes") AS avg_delay
FROM "flight-carriers"
WHERE "Reporting_Airline" = 'AA'

Returns the following:

Learn more

BIT_AND

Performs a bitwise AND operation on all input values.

• Syntax: BIT_AND(expr)
• Function type: Aggregation

The following example returns the bitwise AND operation for all values in passenger-count from taxi-trips:

SELECT
  BIT_AND("passenger_count") AS "bit_and"
FROM "taxi-trips"

Returns the following:

Learn more

BIT_OR

Performs a bitwise OR operation on all input values.

• Syntax: BIT_OR(expr)
• Function type: Aggregation

The following example returns the bitwise OR operation for all values in passenger-count from taxi-trips:

SELECT
  BIT_OR("passenger_count") AS "bit_or"
FROM "taxi-trips"

Returns the following:

Learn more

BIT_XOR

Performs a bitwise XOR operation on all input values.

• Syntax: BIT_XOR(expr)
• Function type: Aggregation

The following example returns the bitwise XOR operation for all values in passenger-count from taxi-trips:

SELECT
  BIT_OR("passenger_count") AS "bit_xor"
FROM "taxi-trips"

Returns the following:

Learn more

BITWISE_AND

Returns the bitwise AND between two expressions: expr1 & expr2.

• Syntax: BITWISE_AND(expr1, expr2)
• Function type: Scalar, numeric

The following example performs the bitwise AND operation 12 & 10.

SELECT BITWISE_AND(12, 10) AS "bitwise_and"

Returns the following:

Learn more

BITWISE_COMPLEMENT

Returns the bitwise complement (bitwise not) for the expression: ~expr.

• Syntax: BITWISE_COMPLEMENT(expr)
• Function type: Scalar, numeric

The following example performs the bitwise complement operation ~12.

SELECT BITWISE_COMPLEMENT(12) AS "bitwise_complement"

Returns the following:

Learn more

BITWISE_CONVERT_DOUBLE_TO_LONG_BITS

Converts the bits of an IEEE 754 floating-point double value to long.

• Syntax:BITWISE_CONVERT_DOUBLE_TO_LONG_BITS(expr)
• Function type: Scalar, numeric

The following example returns the IEEE 754 floating-point double representation of 255 as a long.

SELECT BITWISE_CONVERT_DOUBLE_TO_LONG_BITS(255) AS "ieee_754_double_to_long"

Returns the following:

Learn more

BITWISE_CONVERT_LONG_BITS_TO_DOUBLE

Converts a long to the IEEE 754 floating-point double specified by the bits stored in the long.

• Syntax:BITWISE_CONVERT_LONG_BITS_TO_DOUBLE(expr)
• Function type: Scalar, numeric

The following example returns the long representation of 4643176031446892544 as an IEEE 754 floating-point double.

SELECT BITWISE_CONVERT_LONG_BITS_TO_DOUBLE(4643176031446892544) AS "long_to_ieee_754_double"

Returns the following:

Learn more

BITWISE_OR

Returns the bitwise OR between the two expressions: expr1 | expr2.

• Syntax: BITWISE_OR(expr1, expr2)
• Function type: Scalar, numeric

The following example performs the bitwise OR operation 12 | 10.

SELECT BITWISE_OR(12, 10) AS "bitwise_or"

Returns the following:

Learn more

BITWISE_SHIFT_LEFT

Returns the bitwise left shift by x positions of an expr: expr << x.

• Syntax: BITWISE_SHIFT_LEFT(expr, x)
• Function type: Scalar, numeric

The following example performs the bitwise SHIFT operation 2 << 3.

SELECT BITWISE_SHIFT_LEFT(2, 3) AS "bitwise_shift_left"

Returns the following:

Learn more

BITWISE_SHIFT_RIGHT

Returns the bitwise right shift by x positions of an expr: expr >> x.

• Syntax: BITWISE_SHIFT_RIGHT(expr, x)
• Function type: Scalar, numeric

The following example performs the bitwise SHIFT operation 16 >> 3.

SELECT BITWISE_SHIFT_RIGHT(16, 3) AS "bitwise_shift_right"

Returns the following:

Learn more

BITWISE_XOR

Returns the bitwise exclusive OR between the two expressions: expr1 ^ expr2.

• Syntax: BITWISE_XOR(expr1, expr2)
• Function type: Scalar, numeric

The following example performs the bitwise XOR operation 12 ^ 10.

SELECT BITWISE_XOR(12, 10) AS "bitwise_xor"

Returns the following:

Learn more

BLOOM_FILTER

Computes a Bloom filter from values provided in an expression.

• Syntax: BLOOM_FILTER(expr, numEntries)
numEntries specifies the maximum number of distinct values before the false positive rate increases.
• Function type: Aggregation

The following example returns a Base64-encoded Bloom filter representing the set of devices, agent_category, used in Albania:

SELECT "country",
  BLOOM_FILTER(agent_category, 10) as albanian_bloom
FROM "kttm"
WHERE "country" = 'Albania'
GROUP BY "country"

Returns the following:

Learn more

BLOOM_FILTER_TEST

Returns true if an expression is contained in a Base64-encoded Bloom filter string.

• Syntax: BLOOM_FILTER_TEST(expr, <STRING>)
• Function type: Scalar, other

The following example returns true when a device type, agent_category, exists in the Bloom filter representing the set of devices used in Albania:

SELECT agent_category,
BLOOM_FILTER_TEST("agent_category", 'BAAAAAgAAACAAEAAAAAAAAAAAEIAAAAAAAAAAAAAAAAAAAAAAAIIAAAAAAAAAAAAAAAAAAIAAAAAAQAAAAAAAAAAAAAA') AS bloom_test
FROM "kttm"
GROUP BY 1

Returns the following:

Learn more

BTRIM

Trims characters from both the leading and trailing ends of an expression. Defaults chars to a space if none is provided.

• Syntax: BTRIM(expr[, chars])
• Function type: Scalar, string

The following example trims the _ characters from both ends of the string expression.

SELECT 
  '___abc___' AS "original_string",
  BTRIM('___abc___', '_') AS "trim_both_ends"

Returns the following:

Learn more

CASE

Returns a result based on given conditions.

Simple CASE

Compares an expression to a set of values or expressions.

• Syntax: CASE expr WHEN value1 THEN result1 \[ WHEN value2 THEN result2 ... \] \[ ELSE resultN \] END
• Function type: Scalar, other

The following example returns a UI type based on the value of agent_category from the kttm datasource.

SELECT "agent_category" AS "device_type",
CASE "agent_category"
    WHEN 'Personal computer' THEN 'Large UI'
    WHEN 'Smartphone' THEN 'Mobile UI'
    ELSE 'other'
END AS "UI_type"
FROM "kttm"
LIMIT 2

Returns the following:

Lean more

Searched CASE

Evaluates a set of Boolean expressions.

• Syntax: CASE WHEN boolean_expr1 THEN result1 \[ WHEN boolean_expr2 THEN result2 ... \] \[ ELSE resultN \] END
• Function type: Scalar, other

The following example returns the departure location corresponding to the value of the OriginStateName column from the flight-carriers datasource.

SELECT "OriginStateName" AS "flight_origin",
CASE
    WHEN "OriginStateName" = 'Puerto Rico' THEN 'U.S. Territory'
    WHEN "OriginStateName" = 'U.S. Virgin Islands' THEN 'U.S. Territory'
    ELSE 'U.S. State'
END AS "state_status"
FROM "flight-carriers"
LIMIT 2

Returns the following:

Lean more

CAST

Converts a value into the specified data type.

• Syntax: CAST(value AS TYPE)
• Function type: Scalar, other

The following example converts the values in the Distance column from the flight-carriers datasource from DOUBLE to VARCHAR.

SELECT "Distance" AS "original_column",
      CAST("Distance" AS VARCHAR) "cast_to_string" 
FROM "flight-carriers"
LIMIT 1

Returns the following:

Learn more

CEIL

Date and time

Rounds up a timestamp by a given time unit.

• Syntax: CEIL(timestamp_expr TO unit>)
• Function type: Scalar, date and time

The following example rounds up the __time column from the taxi-trips datasource to the nearest year.

SELECT
  "__time" AS "original_time",
  CEIL("__time" TO YEAR) AS "ceiling"
FROM "taxi-trips"
LIMIT 1

Returns the following:

Learn more

Numeric

Calculates the smallest integer value greater than or equal to the numeric expression.

• Syntax: CEIL(<NUMERIC>)
• Function type: Scalar, numeric

The following example applies the CEIL function to the fare_amount column from the taxi-trips datasource.

SELECT
  "fare_amount" AS "fare_amount",
  CEIL("fare_amount") AS "ceiling_fare_amount"
FROM "taxi-trips"
LIMIT 1

Returns the following:

Learn more

CHAR_LENGTH

Alias for LENGTH.

• Syntax: CHAR_LENGTH(expr)
• Function type: Scalar, string

Learn more

CHARACTER_LENGTH

Alias for LENGTH.

• Syntax: CHARACTER_LENGTH(expr)
• Function type: Scalar, string

Learn more

COALESCE

Returns the first non-null value.

• Syntax: COALESCE(expr, expr, ...)
• Function type: Scalar, other

The following example returns the first non-null value from the list of parameters.

SELECT COALESCE(null, null, 5, 'abc') AS "first_non_null"

Returns the following:

Learn more

CONCAT

Concatenates a list of expressions.

• Syntax: CONCAT(expr[, expr,...])
• Function type: Scalar, string

The following example concatenates the OriginCityName column from flight-carriers, the string to, and the DestCityName column from flight-carriers.

SELECT
  "OriginCityName" AS "origin_city",
  "DestCityName" AS "destination_city",
  CONCAT("OriginCityName", ' to ', "DestCityName") AS "concatenate_flight_details"
FROM "flight-carriers"
LIMIT 1

Returns the following:

Learn more

CONTAINS_STRING

Returns true if str is a substring of expr, case-sensitive. Otherwise, returns false.

• Syntax: CONTAINS_STRING(expr, str)
• Function type: Scalar, string

The following example returns true if the OriginCityName column from the flight-carriers datasource contains the substring San.

SELECT
  "OriginCityName" AS "origin_city",
  CONTAINS_STRING("OriginCityName", 'San') AS "contains_string"
FROM "flight-carriers"
LIMIT 2

Returns the following:

Learn more

COS

Calculates the trigonometric cosine of an angle expressed in radians.

• Syntax: COS(expr)
• Function type: Scalar, numeric

The following example calculates the cosine of angle PI/3 radians.

SELECT COS(PI / 3) AS "cosine"

Returns the following:

Learn more

COT

Calculates the trigonometric cotangent of an angle expressed in radians.

• Syntax: COT(expr)
• Function type: Scalar, numeric

The following example calculates the cotangent of angle PI/3 radians.

SELECT COT(PI / 3) AS "cotangent"

Returns the following:

Learn more

COUNT

Counts the number of rows.

• Syntax: COUNT([DISTINCT] expr) COUNT(*)
  COUNT DISTINCT is an alias for APPROX_COUNT_DISTINCT.
• Function type: Aggregation

The following example counts the number of distinct flights per day after '2005-01-01 00:00:00' in flight-carriers:

SELECT
  TIME_FLOOR(__time, 'P1D') AS "flight_day",
  COUNT(*) AS "num_flights"
FROM "flight-carriers"
WHERE __time > '2005-01-01 00:00:00'
GROUP BY 1
LIMIT 3

Returns the following:

Learn more

CUME_DIST

Returns the cumulative distribution of the current row within the window calculated as number of window rows at the same rank or higher than current row / total window rows. The return value ranges between 1/number of rows and 1.

• Syntax: CUME_DIST()
• Function type: Window

The following example returns the cumulative distribution of number of flights by airline from two airports on a single day.

SELECT FLOOR("__time" TO DAY)  AS "flight_day",
    "Origin" AS "airport",
    "Reporting_Airline" as "airline",
    COUNT("Flight_Number_Reporting_Airline") as "num_flights",
    CUME_DIST() OVER (PARTITION BY "Origin" ORDER BY COUNT("Flight_Number_Reporting_Airline") DESC) AS "cume_dist"
FROM "flight-carriers"
WHERE FLOOR("__time" TO DAY) = '2005-11-01'
   AND "Origin" IN ('KOA', 'LIH')
GROUP BY 1, 2, 3

Returns the following:

Learn more

CURRENT_DATE

Returns the current date in UTC time, unless you specify a different timezone in the query context.

• Syntax: CURRENT_DATE
• Function type: Scalar, date and time

The following example returns the current date.

SELECT CURRENT_DATE AS "current_date"

Returns the following:

Learn more

CURRENT_TIMESTAMP

Returns the current timestamp in UTC time, unless you specify a different timezone in the query context.

• Syntax: CURRENT_TIMESTAMP
• Function type: Scalar, date and time

The following example returns the current timestamp.

SELECT CURRENT_TIMESTAMP AS "current_timestamp"

Returns the following:

Learn more

DATE_TRUNC

Rounds down a timestamp by a given time unit.

• Syntax: DATE_TRUNC(unit, timestamp_expr)
• Function type: Scalar, date and time

The following example truncates a timestamp from the __time column from the taxi-trips datasource to the most recent decade.

SELECT 
  "__time" AS "original_timestamp",
  DATE_TRUNC('decade', "__time") AS "truncate_timestamp"
FROM "taxi-trips"
LIMIT 1

Returns the following:

Learn more

DECODE_BASE64_COMPLEX

Decodes a Base64-encoded expression into a complex data type.

You can use the function to ingest data when a column contains an encoded data sketch such as Theta or HLL.

The function supports hyperUnique and serializablePairLongString data types by default. To enable support for a complex data type, load the corresponding extension:

• druid-bloom-filter: bloom
• druid-datasketches: arrayOfDoublesSketch, HLLSketch, KllDoublesSketch, KllFloatsSketch, quantilesDoublesSketch, thetaSketch
• druid-histogram: approximateHistogram, fixedBucketsHistogram
• druid-stats: variance
• druid-compressed-bigdecimal: compressedBigDecimal
• druid-momentsketch: momentSketch
• druid-tdigestsketch: tDigestSketch

• Syntax: DECODE_BASE64_COMPLEX(dataType, expr)
• Function type: Scalar, other

The following example returns a Theta sketch complex type from a Base64-encoded string representation of the sketch:

SELECT DECODE_BASE64_COMPLEX('thetaSketch','AgMDAAAazJNBAAAAAACAP+k/tkWGkSoFYWMAG0y+3gVabvKcIUNrBv0jAkGsw7sK5szX1k0ScwtMfCQmFP/rDhFK6yU7PPkObZ/Ugw5fcBQZ+GaO+Nt6FP+Whz6TmxkWyRJ+gaQLFhcts1+c0Q/vF9FLFfaVlOkb3/XpXaZ3JhyZ2dG8Di2/HO10sMs9C0AdM4FdHuye6SB+GYinIhTOITOHzB5SAfIiph3de9qIGSM89V+s/TkdI/WZVzK9wF0npfi4ZrmgBSnVjphCtQA5K2fp0x59UCwvMopZarsSkzEo81OIxjznNNXLr1BbQBo1Ei3OxJOoNzVs0x9xzsm4NfgAZSvZQvI1c2TmPsZvlzpW7tmIlizOOsr6pGWoh0U99/tV8RFwhz0SJoWyU1Z2P0hZ5d7KRnZBjlWC+e/FLEKrWsu14rlFRXhsOuxRId9FboEuH9PqMUixI2lB8MhLS803hJDoZ7tMy7Egl+YNU04QM11stXX4Tu96NHHcGiZRuCyciGiTGVQflMLmNt6lW6zIwJy0baNdbwjMCTjtUF7oZOtugWLYYJE9sJU3HuVijc0J10l6SmPslbfY6Fw0Za9w/Zdhn/5nIuKc1WMrYWnAJQJKXY73bHYWq7gI6dRvYdC2fLJyv3F8qwQcOJgFc0GaGXw8KRF3w3IVCwxsMntWhdTkaJ88e++5NFyM1Hd/D79wg0b9vH8=') AS "theta_sketch"

You can perform Theta sketch operations on the resulting COMPLEX<thetaSketch> value which resembles the input string.

Learn more

DECODE_BASE64_UTF8

Decodes a Base64-encoded expression into a UTF-8 encoded string.

• Syntax: DECODE_BASE64_UTF8(expr)
• Function type: Scalar, string

The following example decodes the Base64-encoded representation of "Hello, World!":

SELECT
  DECODE_BASE64_UTF8('SGVsbG8sIFdvcmxkIQ==') as decoded

Returns the following:

Learn more

DEGREES

Converts an angle from radians to degrees.

• Syntax: DEGREES(expr)
• Function type: Scalar, numeric

The following example converts an angle of PI radians to degrees

SELECT DEGREES(PI) AS "degrees"

Returns the following:

Learn more

DENSE_RANK

Returns the rank for a row within a window without gaps. For example, if two rows tie for a rank of 1, the subsequent row is ranked 2.

• Syntax: DENSE_RANK()
• Function type: Window

The following example returns the dense rank by airline for flights from two airports on a single day.

SELECT FLOOR("__time" TO DAY)  AS "flight_day",
    "Origin" AS "airport",
    "Reporting_Airline" as "airline",
    COUNT("Flight_Number_Reporting_Airline") as "num_flights",
    DENSE_RANK() OVER (PARTITION BY "Origin" ORDER BY COUNT("Flight_Number_Reporting_Airline") DESC) AS "dense_rank"
FROM "flight-carriers"
WHERE FLOOR("__time" TO DAY) = '2005-11-01'
    AND "Origin" IN ('KOA', 'LIH')
GROUP BY 1, 2, 3

Returns the following:

Learn more

DIV

Returns the result of integer division of x by y.

:::info The DIV function is not implemented in Druid versions 30.0.0 or earlier. Consider using SAFE_DIVIDE instead. :::

• Syntax: DIV(x, y)
• Function type: Scalar, numeric

The following calculates integer divisions of 78 by 10.

SELECT DIV(78, 10) as "division"

Returns the following:

Learn more

DS_CDF

Returns a string representing an approximation to the cumulative distribution function given a list of split points that define the edges of the bins from a Quantiles sketch.

• Syntax: DS_CDF(expr, splitPoint0, splitPoint1, ...)
• Function type: Scalar, sketch

The following example specifies three split points to return cumulative distribution function approximations on the Distance column from the flight-carriers datasource. The query may return a different approximation for each bin on each execution.

SELECT DS_CDF( DS_QUANTILES_SKETCH("Distance"), 750, 1500, 2250) AS "estimate_cdf"
FROM "flight-carriers"

Returns a result similar to the following:

Learn more

DS_GET_QUANTILE

Returns the quantile estimate corresponding to the fraction from a Quantiles sketch.

• Syntax: DS_GET_QUANTILE(expr, fraction)
• Function type: Scalar, sketch

The following example approximates the median of the Distance column from the flight-carriers datasource. The query may return a different approximation with each execution.

SELECT DS_GET_QUANTILE( DS_QUANTILES_SKETCH("Distance"), 0.5) AS "estimate_median"
FROM "flight-carriers"

Returns a result similar to the following:

Learn more

DS_GET_QUANTILES

Returns a string representing an array of quantile estimates corresponding to a list of fractions from a Quantiles sketch.

• Syntax: DS_GET_QUANTILES(expr, fraction0, fraction1, ...)
• Function type: Scalar, sketch

The following example approximates the 25th, 50th, and 75th percentiles of the Distance column from the flight-carriers datasource. The query may return a different approximation for each percentile on each execution.

SELECT DS_GET_QUANTILES( DS_QUANTILES_SKETCH("Distance"), 0.25, 0.5, 0.75) AS "estimate_fractions"
FROM "flight-carriers"

Returns a result similar to the following:

Learn more

DS_HISTOGRAM

Returns an approximation to the histogram from a Quantiles sketch. The split points define the histogram bins.

• Syntax: DS_HISTOGRAM(expr, splitPoint0, splitPoint1, ...)
• Function type: Scalar, sketch

The following example specifies three split points to approximate a histogram on the Distance column from the flight-carriers datasource. The query may return a different approximation for each bin on each execution.

SELECT DS_HISTOGRAM( DS_QUANTILES_SKETCH("Distance"), 750, 1500, 2250) AS "estimate_histogram"
FROM "flight-carriers"

Returns a result similar to the following:

Learn more

DS_HLL

Creates a HLL sketch on a column containing HLL sketches or a regular column. See DataSketches HLL Sketch module for a description of optional parameters.

• Syntax:DS_HLL(expr, [lgK, tgtHllType])
• Function type: Aggregation

The following example creates a HLL sketch on the Tail_number column of the flight-carriers datasource grouping by OriginState and DestState.

SELECT
  "OriginState" AS "origin_state",
  "DestState" AS "destination_state",
  DS_HLL("Tail_Number") AS "hll_tail_number"
FROM "flight-carriers"
GROUP BY 1,2
LIMIT 1

Returns the following:

Learn more

DS_QUANTILE_SUMMARY

Returns a string summary of a Quantiles sketch.

• Syntax: DS_QUANTILE_SUMMARY(expr)
• Function type: Scalar, sketch

The following example returns a summary of a Quantiles sketch on the Distance column from the flight-carriers datasource.

SELECT DS_QUANTILE_SUMMARY( DS_QUANTILES_SKETCH("Distance") ) AS "summary"
FROM "flight-carriers"

Returns the following:

### Quantiles DirectCompactDoublesSketch SUMMARY: 
   Empty                        : false
   Memory, Capacity bytes       : true, 6128
   Estimation Mode              : true
   K                            : 128
   N                            : 566,138
   Levels (Needed, Total, Valid): 12, 12, 5
   Level Bit Pattern            : 100010100011
   BaseBufferCount              : 122
   Combined Buffer Capacity     : 762
   Retained Items               : 762
   Compact Storage Bytes        : 6,128
   Updatable Storage Bytes      : 14,368
   Normalized Rank Error        : 1.406%
   Normalized Rank Error (PMF)  : 1.711%
   Min Item                     : 2.400000e+01
   Max Item                     : 4.962000e+03
### END SKETCH SUMMARY

Learn more

DS_QUANTILES_SKETCH

Creates a Quantiles sketch on a Quantiles sketch column or a regular column. See DataSketches Quantiles Sketch module for a description of parameters.

• Syntax: DS_QUANTILES_SKETCH(expr, [k])
• Function type: Aggregation

The following example creates a Quantile sketch on the Distance column from the flight-carriers datasource.

SELECT DS_QUANTILES_SKETCH("Distance") AS "quantile_sketch"
FROM "flight-carriers"

Returns the following:

Learn more

DS_RANK

Returns an approximate rank of a given value in a distribution. The rank represents the fraction of the distribution less than the given value.

• Syntax: DS_RANK(expr, value)
• Function type: Scalar, sketch

The following example estimates the fraction of records in the flight-carriers datasource where the value in the Distance column is less than 500. The query may return a different approximation on each execution.

SELECT DS_RANK( DS_QUANTILES_SKETCH("Distance"), 500) AS "estimate_rank"
FROM "flight-carriers"

Returns a result similar to the following:

Learn more

DS_THETA

Creates a Theta sketch on a column containing Theta sketches or a regular column. See DataSketches Theta Sketch module for a description of optional parameters.

• Syntax: DS_THETA(expr, [size])
• Function type: Aggregation

The following example creates a Theta sketch on the Tail_number column of the flight-carriers datasource grouping by OriginState and DestState.

SELECT
  "OriginState" AS "origin_state",
  "DestState" AS "destination_state",
  DS_THETA("Tail_Number") AS "theta_tail_number"
FROM "flight-carriers"
GROUP BY 1,2
LIMIT 1

Returns the following:

Learn more

DS_TUPLE_DOUBLES

Creates a Tuple sketch on raw data or a precomputed sketch column. See DataSketches Tuple Sketch module for a description of parameters.

• Syntax: DS_TUPLE_DOUBLES(expr[, nominalEntries])
DS_TUPLE_DOUBLES(dimensionColumnExpr, metricColumnExpr1[, metricColumnExpr2, ...], [nominalEntries])
• Function type: Aggregation

The following example creates a Tuples sketch column that stores the arrival and departure delay minutes for each airline in flight-carriers:

SELECT
  "Reporting_Airline",
  DS_TUPLE_DOUBLES("Reporting_Airline", "ArrDelayMinutes", "DepDelayMinutes") AS tuples_delay
FROM "flight-carriers"
GROUP BY 1
LIMIT 2

Returns the following:

Learn more

DS_TUPLE_DOUBLES_INTERSECT

Returns an intersection of Tuple sketches which each contain an array of double values as their Summary Objects. The values contained in the Summary Objects are summed when combined. If the last value of the array is a numeric literal, Druid assumes that the value is an override parameter for nominal entries.

• Syntax: DS_TUPLE_DOUBLES_INTERSECT(expr, ..., [nominalEntries])
• Function type: Scalar, sketch

The following example calculates the total minutes of arrival delay for airlines flying out of SFO or LAX. An airline that doesn't fly out of both airports returns a value of 0.

SELECT
  "Reporting_Airline",
  DS_TUPLE_DOUBLES_METRICS_SUM_ESTIMATE(
    DS_TUPLE_DOUBLES_INTERSECT(
      DS_TUPLE_DOUBLES("Reporting_Airline", "ArrDelayMinutes") FILTER(WHERE "Origin" = 'SFO'),
      DS_TUPLE_DOUBLES("Reporting_Airline", "ArrDelayMinutes") FILTER(WHERE "Origin" = 'LAX')
    )
  ) AS arrival_delay_sfo_lax
FROM "flight-carriers"
GROUP BY 1
LIMIT 5

Returns the following:

Learn more

DS_TUPLE_DOUBLES_METRICS_SUM_ESTIMATE

Computes approximate sums of the values contained within a Tuple sketch which contains an array of double values as the Summary Object.

• Syntax: DS_TUPLE_DOUBLES_METRICS_SUM_ESTIMATE(expr)
• Function type: Scalar, sketch

The following example calculates the sum of arrival and departure delay minutes for each airline in flight-carriers:

SELECT
  "Reporting_Airline",
  DS_TUPLE_DOUBLES_METRICS_SUM_ESTIMATE(DS_TUPLE_DOUBLES("Reporting_Airline", "ArrDelayMinutes", "DepDelayMinutes")) AS sum_delays
FROM "flight-carriers"
GROUP BY 1
LIMIT 2

Returns the following:

Compare this example with an analogous SQL statement that doesn't use approximations:

SELECT
  "Reporting_Airline",
  SUM("ArrDelayMinutes") AS sum_arrival_delay,
  SUM("DepDelayMinutes") AS sum_departure_delay
FROM "flight-carriers"
GROUP BY 1
LIMIT 2

Returns the following:

Learn more

DS_TUPLE_DOUBLES_NOT

Returns a set difference of Tuple sketches which each contain an array of double values as their Summary Objects. The values contained in the Summary Object are preserved as is. If the last value of the array is a numeric literal, Druid assumes that the value is an override parameter for nominal entries.

• Syntax: DS_TUPLE_DOUBLES_NOT(expr, ..., [nominalEntries])
• Function type: Scalar, sketch

The following example calculates the total minutes of arrival delay for airlines that fly out of SFO but not LAX.

SELECT
  "Reporting_Airline",
  DS_TUPLE_DOUBLES_METRICS_SUM_ESTIMATE(
    DS_TUPLE_DOUBLES_NOT(
      DS_TUPLE_DOUBLES("Reporting_Airline", "ArrDelayMinutes") FILTER(WHERE "Origin" = 'SFO'),
      DS_TUPLE_DOUBLES("Reporting_Airline", "ArrDelayMinutes") FILTER(WHERE "Origin" = 'LAX')
    )
  ) AS arrival_delay_sfo_lax
FROM "flight-carriers"
GROUP BY 1
LIMIT 5

Returns the following:

Learn more

DS_TUPLE_DOUBLES_UNION

Returns a union of Tuple sketches which each contain an array of double values as their Summary Objects. The values contained in the Summary Objects are summed when combined. If the last value of the array is a numeric literal, Druid assumes that the value is an override parameter for nominal entries.

• Syntax: DS_TUPLE_DOUBLES_UNION(expr, ..., [nominalEntries])
• Function type: Scalar, sketch

The following example calculates the total minutes of arrival delay for airlines flying out of either SFO or LAX.

SELECT
  "Reporting_Airline",
  DS_TUPLE_DOUBLES_METRICS_SUM_ESTIMATE(
    DS_TUPLE_DOUBLES_UNION(
      DS_TUPLE_DOUBLES("Reporting_Airline", "ArrDelayMinutes") FILTER(WHERE "Origin" = 'SFO'),
      DS_TUPLE_DOUBLES("Reporting_Airline", "ArrDelayMinutes") FILTER(WHERE "Origin" = 'LAX')
    )
  ) AS arrival_delay_sfo_lax
FROM "flight-carriers"
GROUP BY 1
LIMIT 5

Returns the following:

Learn more

EARLIEST

Returns the value of a numeric or string expression corresponding to the earliest __time value.

• Syntax: EARLIEST(expr, [maxBytesPerValue])
• Function type: Aggregation

The following example returns the origin airport code associated with the earliest departing flight daily after '2005-01-01 00:00:00' in flight-carriers:

SELECT
  TIME_FLOOR(__time, 'P1D') AS "departure_day",
  EARLIEST("Origin") AS "origin"
FROM "flight-carriers"
WHERE __time >= TIMESTAMP '2005-01-01 00:00:00'
GROUP BY 1
LIMIT 2

Returns the following:

Learn more

EARLIEST_BY

Returns the value of a numeric or string expression corresponding to the earliest time value from timestampExpr.

• Syntax: EARLIEST_BY(expr, timestampExpr, [maxBytesPerValue])
• Function type: Aggregation

The following example returns the destination airport code associated with the earliest arriving flight daily after '2005-01-01 00:00:00' in flight-carriers:

SELECT
  TIME_FLOOR(TIME_PARSE("arrivalime"), 'P1D') AS "arrival_day",
  EARLIEST_BY("Dest", TIME_PARSE("arrivalime")) AS "dest"
FROM "flight-carriers"
WHERE TIME_PARSE("arrivalime") >= TIMESTAMP '2005-01-01 00:00:00'
GROUP BY 1
LIMIT 2

Returns the following:

Learn more

EXP

Calculates e raised to the power of the numeric expression.

• Syntax: EXP(<NUMERIC>)
• Function type: Scalar, numeric

The following example calculates e to the power of 1.

SELECT EXP(1) AS "exponential" 

Returns the following:

Learn more

EXTRACT

Extracts the value of some unit from the timestamp.

• Syntax: EXTRACT(unit FROM timestamp_expr)
• Function type: Scalar, date and time

The following example extracts the year from the __time column from the taxi-trips datasource.

SELECT 
  "__time" AS "original_time",
  EXTRACT(YEAR FROM "__time" ) AS "year"
FROM "taxi-trips"
LIMIT 1

Returns the following:

Learn more

FIRST_VALUE

Returns the value evaluated for the expression for the first row within the window.

• Syntax: FIRST_VALUE(expr)
• Function type: Window

The following example returns the name of the first airline in the window of flights by airline for two airports on a single day.

SELECT FLOOR("__time" TO DAY)  AS "flight_day",
    "Origin" AS "airport",
    "Reporting_Airline" as "airline",
    COUNT("Flight_Number_Reporting_Airline") as "num_flights",
    FIRST_VALUE("Reporting_Airline") OVER (PARTITION BY "Origin" ORDER BY COUNT("Flight_Number_Reporting_Airline") DESC) AS "first_val"
FROM "flight-carriers"
WHERE FLOOR("__time" TO DAY) = '2005-11-01'
    AND "Origin" IN ('KOA', 'LIH')
GROUP BY 1, 2, 3

Returns the following:

Learn more

FLOOR

Date and time

Rounds down a timestamp by a given time unit.

• Syntax: FLOOR(timestamp_expr TO unit)
• Function type: Scalar, date and time

The following example rounds down the __time column from the taxi-trips datasource to the nearest year.

SELECT
  "__time" AS "original_time",
  FLOOR("__time" TO YEAR) AS "floor"
FROM "taxi-trips"
LIMIT 1

Returns the following:

Learn more

Numeric

Calculates the largest integer less than or equal to the numeric expression.

• Syntax: FLOOR(expr)
• Function type: Scalar, numeric

The following example applies the FLOOR function to the fare_amount column from the taxi-trips datasource.

SELECT
  "fare_amount" AS "fare_amount",
  FLOOR("fare_amount") AS "floor_fare_amount"
FROM "taxi-trips"
LIMIT 1

Returns the following:

Learn more

GREATEST

Returns the maximum value from the provided expressions. For information on how Druid interprets the arguments passed into the function, see Reduction functions.

• Syntax: GREATEST([expr1, ...])
• Function type: Scalar, reduction

The following example returns the greatest value between the numeric constant PI, the integer number 4, and the double -5.0. Druid interprets these arguments as DOUBLE data type.

SELECT GREATEST(PI, 4, -5.0) AS "greatest"

Returns the following:

Learn more

GROUPING

Returns a number for each output row of a groupBy query, indicating whether the specified dimension is included for that row.

• Syntax: GROUPING(expr, expr...)
• Function type: Aggregation

The following example returns the total minutes of flight delay for each day of the week in flight-carriers. The GROUP BY clause creates two grouping sets, one for the day of the week and one for the grand total.

For more information, refer to CASE and grouping sets with SQL GROUP BY.

SELECT
  CASE
     WHEN GROUPING("DayOfWeek") = 1 THEN 'Total'
     ELSE "DayOfWeek"
  END AS "DayOfWeek",
  GROUPING("DayOfWeek") AS Subgroup,
  SUM("DepDelayMinutes") AS "MinutesDelayed"
FROM "flight-carriers"
GROUP BY GROUPING SETS("DayOfWeek", ())

Returns the following:

Learn more

HLL_SKETCH_ESTIMATE

Returns the distinct count estimate from a HLL sketch. To round the distinct count estimate, set round to true. round defaults to false.

• Syntax: HLL_SKETCH_ESTIMATE(expr, [round])
• Function type: Scalar, sketch

The following example estimates the distinct number of unique tail numbers in the flight-carriers datasource.

SELECT
  HLL_SKETCH_ESTIMATE(DS_HLL("Tail_Number")) AS "estimate"
FROM "flight-carriers"

Returns the following:

Learn more

HLL_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS

Returns the distinct count estimate and error bounds from a HLL sketch. To specify the number of standard bound deviations, use numStdDev.

• Syntax: HLL_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS(expr, [numStdDev])
• Function type: Scalar, sketch

The following example estimates the number of unique tail numbers in the flight-carriers datasource with error bounds at plus or minus one standard deviation.

SELECT
  HLL_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS(DS_HLL("Tail_Number"), 1) AS "estimate_with_errors"
FROM "flight-carriers"

Returns the following:

Learn more

HLL_SKETCH_TO_STRING

Returns a human-readable string representation of a HLL sketch for debugging.

• Syntax: HLL_SKETCH_TO_STRING(expr)
• Function type: Scalar, sketch

The following example returns the HLL sketch on column Tail_Number from the flight-carriers datasource as a human-readable string.

SELECT
  HLL_SKETCH_TO_STRING( DS_HLL("Tail_Number") ) AS "summary"
FROM "flight-carriers"

Returns the following:

### HLL SKETCH SUMMARY: 
  Log Config K   : 12
  Hll Target     : HLL_4
  Current Mode   : HLL
  Memory         : false
  LB             : 4611.381540678335
  Estimate       : 4685.8815405960595
  UB             : 4762.978259800803
  OutOfOrder Flag: true
  CurMin         : 0
  NumAtCurMin    : 1316
  HipAccum       : 0.0
  KxQ0           : 2080.7755126953125
  KxQ1           : 0.0
  Rebuild KxQ Flg: false

Learn more

HLL_SKETCH_UNION

Returns a union of HLL sketches. See DataSketches HLL Sketch module for a description of optional parameters.

• Syntax: HLL_SKETCH_UNION([lgK, tgtHllType], expr0, expr1, ...)
• Function type: Scalar, sketch

The following example estimates the union of the HLL sketch of tail numbers that took off from CA and the HLL sketch of tail numbers that took off from TX. The example uses the Tail_Number and OriginState columns from the flight-carriers datasource.

SELECT
  HLL_SKETCH_ESTIMATE(
    HLL_SKETCH_UNION( 
      DS_HLL("Tail_Number") FILTER(WHERE "OriginState" = 'CA'),
      DS_HLL("Tail_Number") FILTER(WHERE "OriginState" = 'TX')
    )
  ) AS "estimate_union"
FROM "flight-carriers"

Returns the following:

Learn more

HUMAN_READABLE_BINARY_BYTE_FORMAT

Converts an integer byte size into human-readable IEC format.

• Syntax: HUMAN_READABLE_BINARY_BYTE_FORMAT(value[, precision])
• Function type: Scalar, numeric

The following example converts 1000000 into IEC format.

  SELECT HUMAN_READABLE_BINARY_BYTE_FORMAT(1000000, 2) AS "iec_format"

Returns the following:

Learn more

HUMAN_READABLE_DECIMAL_BYTE_FORMAT

Converts a byte size into human-readable SI format.

• Syntax: HUMAN_READABLE_DECIMAL_BYTE_FORMAT(value[, precision])
• Function type: Scalar, numeric

The following example converts 1000000 into SI format.

SELECT HUMAN_READABLE_DECIMAL_BYTE_FORMAT(1000000, 2) AS "si_format"

Returns the following:

Learn more

HUMAN_READABLE_DECIMAL_FORMAT

Converts a byte size into human-readable SI format with single-character units.

• Syntax: HUMAN_READABLE_DECIMAL_FORMAT(value[, precision])
• Function type: Scalar, numeric

The following example converts 1000000 into single character SI format.

SELECT HUMAN_READABLE_DECIMAL_FORMAT(1000000, 2) AS "single_character_si_format"

Returns the following:

Learn more

ICONTAINS_STRING

Returns true if str is a substring of expr, case-insensitive. Otherwise, returns false.

• Syntax: ICONTAINS_STRING(expr, str)
• Function type: Scalar, string

The following example returns true if the OriginCityName column from the flight-carriers datasource contains the case-insensitive substring san.

SELECT
  "OriginCityName" AS "origin_city",
  ICONTAINS_STRING("OriginCityName", 'san') AS "contains_case_insensitive_string"
FROM "flight-carriers"
LIMIT 2

Returns the following:

Learn more

IPV4_MATCH

Returns true if the IPv4 address belongs to the subnet literal, otherwise returns false.

• Syntax: IPV4_MATCH(address, subnet)
• Function type: Scalar, IP address

The following example returns true if the IPv4 address in the forward_for column from the kttm datasource belongs to the subnet 181.13.41.0/24.

SELECT 
  "forwarded_for" AS "ipv4_address",
  IPV4_MATCH("forwarded_for", '181.13.41.0/24') AS "belongs_in_subnet"
FROM "kttm"
LIMIT 2

Returns the following:

Learn more

IPV4_PARSE

Parses an IPv4 address into its integer notation.

• Syntax: IPV4_PARSE(address)
• Function type: Scalar, IP address

The following example returns an integer that represents the IPv4 address 5.5.5.5.

SELECT 
  '5.5.5.5' AS "ipv4_address",
  IPV4_PARSE('5.5.5.5') AS "integer"

Returns the following:

Learn more

IPV4_STRINGIFY

Converts an IPv4 address in integer notation into dot-decimal notation.

• Syntax: IPV4_STRINGIFY(address)
• Function type: Scalar, IP address

The following example returns the integer 84215045 in IPv4 dot-decimal notation.

SELECT 
  '84215045' AS "integer",
  IPV4_STRINGIFY(84215045) AS "dot_decimal_notation"

Returns the following:

Learn more

IPV6_MATCH

Returns true if the IPv6 address belongs to the subnet literal. Otherwise, returns false.

• Syntax: IPV6_MATCH(address, subnet)
• Function type: Scalar, IP address

The following example returns true because 75e9:efa4:29c6:85f6::232c is in the subnet of 75e9:efa4:29c6:85f6::/64.

SELECT 
  '75e9:efa4:29c6:85f6::232c' AS "ipv6_address",
  IPV6_MATCH('75e9:efa4:29c6:85f6::232c', '75e9:efa4:29c6:85f6::/64') AS "belongs_in_subnet" 

Returns the following:

Learn more

JSON_KEYS

Returns an array of field names from an expression, at a specified path.

• Syntax: JSON_KEYS(expr, path)
• Function type: JSON

The following example returns an array of field names from the nested column agent:

SELECT
  JSON_KEYS(agent, '$.') AS agent_keys
FROM "kttm_nested"
LIMIT 1

Returns the following:

Learn more

JSON_MERGE

Merges two or more JSON STRING or COMPLEX<json> expressions into one, preserving the rightmost value when there are key overlaps. Returns NULL if any argument is NULL. The function always returns a COMPLEX<json> object.

• Syntax: JSON_MERGE(expr1, expr2[, expr3 ...])
• Function type: JSON

The following example merges the event object with a static string example_string:

SELECT 
  event,
  JSON_MERGE(event, '{"example_string": 123}') as event_with_string
FROM "kttm_nested"
LIMIT 1

Returns the following:

Learn more

JSON_OBJECT

Constructs a new COMPLEX<json> object from one or more expressions. The KEY expressions must evaluate to string types. The VALUE expressions can be composed of any input type, including other COMPLEX<json> objects. The function can accept colon-separated key-value pairs.

• Syntax: JSON_OBJECT(KEY expr1 VALUE expr2[, KEY expr3 VALUE expr4, ...])
  or
  JSON_OBJECT(expr1:expr2[, expr3:expr4, ...])
• Function type: JSON

The following example creates a new object combinedJSON from continent in geo_ip and type in event:

SELECT
  JSON_OBJECT(
     KEY 'geo_ip' VALUE JSON_QUERY(geo_ip, '$.continent'),
     KEY 'event' VALUE JSON_QUERY(event, '$.type')
     )
  as combined_JSON
FROM "kttm_nested"
LIMIT 1

Returns the following:

Learn more

JSON_PATHS

Returns an array of all paths which refer to primitive values in an expression, in JSONPath format.

• Syntax: JSON_PATHS(expr)
• Function type: JSON

The following example returns an array of distinct paths in the geo_ip nested column:

SELECT
  ARRAY_CONCAT_AGG(DISTINCT JSON_PATHS(geo_ip)) AS geo_ip_paths
from "kttm_nested"

Returns the following:

Learn more

JSON_QUERY

Extracts a COMPLEX<json> value from an expression at a specified path.

• Syntax: JSON_QUERY(expr, path)
• Function type: JSON

The following example returns the values of percentage in the event nested column:

SELECT
   "event",
   JSON_QUERY("event", '$.percentage')
FROM "kttm_nested"
LIMIT 2

Returns the following:

Learn more

JSON_QUERY_ARRAY

Extracts an ARRAY<COMPLEX<json>> value from an expression at a specified path.

If the value isn't an array, the function translates it into a single element ARRAY containing the value at path. This function is mainly used to extract arrays of objects to use as inputs to other array functions.

• Syntax: JSON_QUERY_ARRAY(expr, path)
• Function type: JSON

The following example returns an array of percentage values in the event nested column:

SELECT
   "event",
   JSON_QUERY_ARRAY("event", '$.percentage')
FROM "kttm_nested"
LIMIT 2

Returns the following:

Learn more

JSON_VALUE

Extracts a primitive value from an expression at a specified path.

If you include RETURNING and specify a SQL type (such as VARCHAR, BIGINT, DOUBLE) the function plans the query using the suggested type. If RETURNING isn't included, the function attempts to infer the type based on the context. If the function can't infer the type, it defaults to VARCHAR. Primitive arrays can also be returned, but only if RETURNING is specified as an ARRAY type, e.g. RETURNING VARCHAR ARRAY.

• Syntax: JSON_VALUE(expr, path [RETURNING sqlType])
• Function type: JSON

The following example returns the value of city in the geo_ip nested column:

SELECT
  geo_ip,
  JSON_VALUE(geo_ip, '$.city' RETURNING VARCHAR) as city
FROM "kttm_nested"
WHERE JSON_VALUE(geo_ip, '$.continent') = 'Asia'
LIMIT 2

Returns the following:

Learn more

LAG

If you do not supply an offset, returns the value evaluated at the row preceding the current row. Specify an offset number n to return the value evaluated at n rows preceding the current one.

• Syntax: LAG(expr[, offset])
• Function type: Window

The following example returns the preceding airline in the window for flights by airline from two airports on a single day.

SELECT FLOOR("__time" TO DAY)  AS "flight_day",
    "Origin" AS "airport",
    "Reporting_Airline" as "airline",
    COUNT("Flight_Number_Reporting_Airline") as "num_flights",
    LAG("Reporting_Airline") OVER (PARTITION BY "Origin" ORDER BY COUNT("Flight_Number_Reporting_Airline") DESC) AS "lag"
FROM "flight-carriers"
WHERE FLOOR("__time" TO DAY) = '2005-11-01'
    AND "Origin" IN ('KOA', 'LIH')
GROUP BY 1, 2, 3

Returns the following:

Learn more

LAST_VALUE

Returns the value evaluated for the expression for the last row within the window.

• Syntax: LAST_VALUE(expr)
• Function type: Window

The following example returns the last airline name in the window for flights for two airports on a single day. Note that the RANGE BETWEEN clause defines the window frame between the current row and the final row in the window instead of the default of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when using ORDER BY.

SELECT FLOOR("__time" TO DAY)  AS "flight_day",
    "Origin" AS "airport",
    "Reporting_Airline" as "airline",
    COUNT("Flight_Number_Reporting_Airline") as "num_flights",
    LAST_VALUE("Reporting_Airline") OVER (PARTITION BY "Origin" ORDER BY COUNT("Flight_Number_Reporting_Airline") DESC
      RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS "last_value"
FROM "flight-carriers"
WHERE FLOOR("__time" TO DAY) = '2005-11-01'
    AND "Origin" IN ('KOA', 'LIH')
GROUP BY 1, 2, 3

Returns the following:

Learn more

LATEST

Returns the value of a numeric or string expression corresponding to the latest __time value.

• Syntax: LATEST(expr, [maxBytesPerValue])
• Function type: Aggregation

The following example returns the origin airport code associated with the latest departing flight daily after '2005-01-01 00:00:00' in flight-carriers:

SELECT
  TIME_FLOOR(__time, 'P1D') AS "departure_day",
  LATEST("Origin") AS "origin"
FROM "flight-carriers"
WHERE __time >= TIMESTAMP '2005-01-01 00:00:00'
GROUP BY 1
LIMIT 2

Returns the following:

Learn more

LATEST_BY

Returns the value of a numeric or string expression corresponding to the latest time value from timestampExpr.

• Syntax: LATEST_BY(expr, timestampExpr, [maxBytesPerValue])
• Function type: Aggregation

The following example returns the destination airport code associated with the latest arriving flight daily after '2005-01-01 00:00:00' in flight-carriers:

SELECT
  TIME_FLOOR(TIME_PARSE("arrivalime"), 'P1D') AS "arrival_day",
  LATEST_BY("Dest", TIME_PARSE("arrivalime")) AS "dest"
FROM "flight-carriers"
WHERE TIME_PARSE("arrivalime") >= TIMESTAMP '2005-01-01 00:00:00'
GROUP BY 1
LIMIT 2

Returns the following:

Learn more

LEAD

If you do not supply an offset, returns the value evaluated at the row following the current row. Specify an offset number n to return the value evaluated at n rows following the current one; if there is no such row, returns the given default value.

• Syntax: LEAD(expr[, offset])
• Function type: Window

The following example returns the subsequent value for an airline in the window for flights from two airports on a single day.

SELECT FLOOR("__time" TO DAY)  AS "flight_day",
    "Origin" AS "airport",
    "Reporting_Airline" as "airline",
    COUNT("Flight_Number_Reporting_Airline") as "num_flights",
    LEAD("Reporting_Airline") OVER (PARTITION BY "Origin" ORDER BY COUNT("Flight_Number_Reporting_Airline") DESC) AS "lead"
FROM "flight-carriers"
WHERE FLOOR("__time" TO DAY) = '2005-11-01'
    AND "Origin" IN ('KOA', 'LIH')
GROUP BY 1, 2, 3

Returns the following:

Learn more

LEAST

Returns the minimum value from the provided expressions. For information on how Druid interprets the arguments passed into the function, see Reduction functions.

• Syntax: LEAST([expr1, ...])
• Function type: Scalar, reduction

The following example returns the minimum value between the strings apple, orange, and pear. Druid interprets these arguments as STRING data type.

SELECT LEAST( 'apple', 'orange', 'pear') AS "least"

Returns the following:

Learn more

LEFT

Returns the N leftmost characters of an expression, where N is an integer value.

• Syntax: LEFT(expr, N)
• Function type: Scalar, string

The following example returns the 3 leftmost characters of the expression ABCDEFG.

SELECT
  'ABCDEFG' AS "expression",
  LEFT('ABCDEFG', 3) AS "leftmost_characters"

Returns the following:

Learn more

LENGTH

Returns the length of the expression in UTF-16 code units.

• Syntax: LENGTH(expr)
• Function type: Scalar, string

The following example returns the character length of the OriginCityName column from the flight-carriers datasource.

SELECT 
  "OriginCityName" AS "origin_city_name",
  LENGTH("OriginCityName") AS "city_name_length"
FROM "flight-carriers"
LIMIT 1

Returns the following:

Learn more

LISTAGG

Alias for STRING_AGG.

• Syntax: LISTAGG([DISTINCT] expr, [separator, [size]])
• Function type: Aggregation

Learn more

LN

Calculates the natural logarithm of the numeric expression.

• Syntax: LN(<NUMERIC>)
• Function type: Scalar, numeric

The following example applies the LN function to the max_temperature column from the taxi-trips datasource.

SELECT
  "max_temperature" AS "max_temperature",
  LN("max_temperature") AS "natural_log_max_temp"
FROM "taxi-trips"
LIMIT 1

Returns the following:

Learn more

LOG10

Calculates the base-10 logarithm of the numeric expression.

• Syntax: LOG10(<NUMERIC>)
• Function type: Scalar, numeric

The following example applies the LOG10 function to the max_temperature column from the taxi-trips datasource.

SELECT
  "max_temperature" AS "max_temperature",
  LOG10("max_temperature") AS "log10_max_temp"
FROM "taxi-trips"
LIMIT 1

Returns the following:

Learn more

LOOKUP

Searches for expr in a registered query-time lookup table named lookupName and returns the mapped value. If expr is null or not contained in the lookup, returns defaultValue if supplied, otherwise returns null.

• Syntax: LOOKUP(expr, lookupName[, defaultValue])
• Function type: Scalar, string

The following example uses a map type lookup table named code_to_name, which contains the following key-value pairs:

{
  "SJU": "Luis Munoz Marin International Airport",
  "IAD": "Dulles International Airport"
}

The example uses code_to_name to map the Origin column from the flight-carriers datasource to the corresponding full airport name. Returns key not found if no matching key exists in the lookup table.

SELECT 
  "Origin" AS "origin_airport",
  LOOKUP("Origin", 'code_to_name','key not found') AS "full_airport_name"
FROM "flight-carriers"
LIMIT 2

Returns the following:

Learn more

LOWER

Returns the expression in lowercase.

• Syntax: LOWER(expr)
• Function type: Scalar, string

The following example converts the OriginCityName column from the flight-carriers datasource to lowercase.

SELECT 
  "OriginCityName" AS "origin_city",
  LOWER("OriginCityName") AS "lowercase"
FROM "flight-carriers"
LIMIT 1

Returns the following:

Learn more

LPAD

Returns a string of size length from expr. When the length of expr is less than length, left pads expr with chars, which defaults to the space character. Truncates expr to length if length is shorter than the length of expr.

• Syntax: LPAD(expr, length[, chars])
• Function type: Scalar, string

The following example left pads the value of OriginStateName from the flight-carriers datasource to return a total of 11 characters.

SELECT 
  "OriginStateName" AS "origin_state",
  LPAD("OriginStateName", 11, '+') AS "add_left_padding"
FROM "flight-carriers"
LIMIT 3

Returns the following:

Learn more

LTRIM

Trims characters from the leading end of an expression. Defaults chars to a space if none is provided.

• Syntax: LTRIM(expr[, chars])
• Function type: Scalar, string

The following example trims the _ characters from the leading end of the string expression.

SELECT 
  '___abc___' AS "original_string",
  LTRIM('___abc___', '_') AS "trim_leading_end_of_expression"

Returns the following:

Learn more

MAX

Returns the maximum value of a set of values.

• Syntax: MAX(expr)
• Function type: Aggregation

The following example calculates the maximum delay in minutes for an airline in flight-carriers:

SELECT MAX("DepDelayMinutes") AS max_delay
FROM "flight-carriers"
WHERE "Reporting_Airline" = 'AA'

Returns the following:

Learn more

MILLIS_TO_TIMESTAMP

Converts a number of milliseconds since epoch into a timestamp.

• Syntax: MILLIS_TO_TIMESTAMP(millis_expr)
• Function type: Scalar, date and time

The following example converts 1375344877000 milliseconds from epoch into a timestamp.

SELECT MILLIS_TO_TIMESTAMP(1375344877000) AS "timestamp"

Returns the following:

Learn more

MIN

Returns the minimum value of a set of values.

• Syntax: MIN(expr)
• Function type: Aggregation

The following example calculates the minimum delay in minutes for an airline in flight-carriers:

SELECT MIN("DepDelayMinutes") AS min_delay
FROM "flight-carriers"
WHERE "Reporting_Airline" = 'AA'

Returns the following:

Learn more

MOD

Calculates x modulo y, or the remainder of x divided by y. Where x and y are numeric expressions.

• Syntax: MOD(x, y)
• Function type: Scalar, numeric

The following calculates 78 MOD 10.

SELECT MOD(78, 10) as "modulo"

Returns the following:

Learn more

MV_APPEND

Adds the expression to the end of the array.

• Syntax: MV_APPEND(arr1, expr)
• Function type: Multi-value string

The following example appends the string label to the multi-value string tags from mvd-example:

SELECT MV_APPEND("tags", "label") AS append
FROM "mvd-example"
LIMIT 1

Returns the following:

Learn more

MV_CONCAT

Concatenates two arrays.

• Syntax: MV_CONCAT(arr1, arr2)
• Function type: Multi-value string

The following example concatenates tags from mvd-example to itself:

SELECT MV_CONCAT("tags", "tags") AS cat
FROM "mvd-example"
LIMIT 1

Returns the following:

Learn more

MV_CONTAINS

Returns true if the expression is in the array, false otherwise.

• Syntax: MV_CONTAINS(arr, expr)
• Function type: Multi-value string

The following example checks if the string t3 exists within tags from mvd-example:

SELECT "tags", MV_CONTAINS("tags", 't3') AS contained
FROM "mvd-example"

Returns the following:

Learn more

MV_FILTER_NONE

Filters a multi-value expression to exclude values from an array.

• Syntax: MV_FILTER_NONE(expr, arr)
• Function type: Multi-value string

The following example filters tags from mvd-example to remove values t1 or t3, if present:

SELECT MV_FILTER_NONE("tags", ARRAY['t1', 't3']) AS exclude
FROM "mvd-example"
LIMIT 3

Returns the following:

Learn more

MV_FILTER_ONLY

Filters a multi-value expression to include only values contained in the array.

• Syntax: MV_FILTER_ONLY(expr, arr)
• Function type: Multi-value string

The following example filters tags from mvd-example to only contain the values t1 or t3:

SELECT MV_FILTER_ONLY("tags", ARRAY['t1', 't3']) AS filt
FROM "mvd-example"
LIMIT 3

Returns the following:

Learn more

MV_FILTER_REGEX

Filters a multi-value expression to include only values matching the specified regular expression pattern.

• Syntax: MV_FILTER_REGEX(expr, pattern)
• Function type: Multi-value string

The following example filters the tags multi-value string from the mvd-example datasource to include only values starting with the letter t:

SELECT MV_FILTER_REGEX("tags", '^t.*') AS regex_filtered
FROM "mvd-example"
LIMIT 4

Returns the following:

Filters multi-value expr to include values that match pattern.

MV_FILTER_PREFIX

Filters a multi-value expression to include only values that start with the specified prefix.

• Syntax: MV_FILTER_PREFIX(expr, prefix)
• Function type: Multi-value string

The following example filters the tags multi-value string from the mvd-example datasource to include only values starting with t3:

SELECT MV_FILTER_PREFIX("tags", 't3') AS prefix_filtered
FROM "mvd-example"
LIMIT 4

Returns the following:

Filters multi-value expr to include values that have prefix prefix.

MV_LENGTH

Returns the length of an array expression.

• Syntax: MV_LENGTH(arr)
• Function type: Multi-value string

The following example returns the length of the tags multi-value strings from mvd-example:

SELECT MV_LENGTH("tags") AS len
FROM "mvd-example"
LIMIT 1

Returns the following:

Learn more

MV_OFFSET

Returns the array element at the given zero-based index.

• Syntax: MV_OFFSET(arr, long)
• Function type: Multi-value string

The following example returns tags and the element at the third position of tags in mvd-example:

SELECT "tags", MV_OFFSET("tags", 2) AS elem
FROM "mvd-example"

Returns the following:

Learn more

MV_OFFSET_OF

Returns the zero-based index of the first occurrence of a given expression in the array.

• Syntax: MV_OFFSET_OF(arr, expr)
• Function type: Multi-value string

The following example returns tags and the zero-based index of the string t3 from tags in mvd-example:

SELECT "tags", MV_OFFSET_OF("tags", 't3') AS index
FROM "mvd-example"

Returns the following:

Learn more

MV_ORDINAL

Returns the array element at the given one-based index.

• Syntax: MV_ORDINAL(arr, long)
• Function type: Multi-value string

The following example returns tags and the element at the third position of tags in mvd-example:

SELECT "tags", MV_ORDINAL("tags", 3) AS elem
FROM "mvd-example"

Returns the following:

Learn more

MV_ORDINAL_OF

Returns the one-based index of the first occurrence of a given expression.

• Syntax: MV_ORDINAL_OF(arr, expr)
• Function type: Multi-value string

The following example returns tags and the one-based index of the string t3 from tags in mvd-example:

SELECT "tags", MV_ORDINAL_OF("tags", 't3') AS index
FROM "mvd-example"

Returns the following:

Learn more

MV_OVERLAP

Returns true if the two arrays have any elements in common, false otherwise.

• Syntax: MV_OVERLAP(arr1, arr2)
• Function type: Multi-value string

The following example identifies rows that contain t1 or t3 in tags from mvd-example:

SELECT "tags", MV_OVERLAP("tags", ARRAY['t1', 't3']) AS overlap
FROM "mvd_example"

Returns the following:

Learn more

MV_PREPEND

Adds the expression to the beginning of the array.

• Syntax: MV_PREPEND(expr, arr)
• Function type: Multi-value string

The following example prepends the string dimension label to the multi-value string dimension tags from mvd-example:

SELECT MV_PREPEND("label", "tags") AS prepend
FROM "mvd-example"
LIMIT 1

Returns the following:

Learn more

MV_SLICE

Returns a slice of the array from the zero-based start and end indexes.

• Syntax: MV_SLICE(arr, start, end)
• Function type: Multi-value string

The following example returns tags and the second and third values of tags from mvd-example:

SELECT "tags", MV_SLICE(tags, 1, 3) AS slice
FROM "mvd-example"

Returns the following:

Learn more

MV_TO_ARRAY

Converts a multi-value string from a VARCHAR to a VARCHAR ARRAY.

• Syntax: MV_TO_ARRAY(str)
• Function type: Multi-value string

The following example transforms the tags column from mvd-example to arrays:

SELECT MV_TO_ARRAY(tags) AS arr
FROM "mvd-example"
LIMIT 1

Returns the following:

Learn more

MV_TO_STRING

Joins all elements of the array together by the given delimiter.

• Syntax: MV_TO_STRING(arr, str)
• Function type: Multi-value string

The following example transforms the tags column from mvd-example to strings delimited by a space character:

SELECT MV_TO_STRING("tags", ' ') AS str
FROM mvd-example
LIMIT 1

Returns the following:

Learn more

NTILE

Divides the rows within a window as evenly as possible into the number of tiles, also called buckets, and returns the value of the tile that the row falls into.

• Syntax: NTILE(tiles)
• Function type: Window

The following example returns the results for flights by airline from two airports on a single day divided into 3 tiles.

SELECT FLOOR("__time" TO DAY)  AS "flight_day",
    "Origin" AS "airport",
    "Reporting_Airline" as "airline",
    COUNT("Flight_Number_Reporting_Airline") as "num_flights",
    NTILE(3) OVER (PARTITION BY "Origin" ORDER BY COUNT("Flight_Number_Reporting_Airline") DESC) AS "ntile"
FROM "flight-carriers"
WHERE FLOOR("__time" TO DAY) = '2005-11-01'
    AND "Origin" IN ('KOA', 'LIH')
GROUP BY 1, 2, 3

Returns the following:

Learn more

NULLIF

Returns null if two values are equal, else returns the first value.

• Syntax: NULLIF(value1, value2)
• Function type: Scalar, other

The following example returns null if the OriginState column from the flight-carriers datasource is PR.

SELECT "OriginState" AS "origin_state",
  NULLIF("OriginState", 'PR') AS "remove_pr"
FROM "flight-carriers"
LIMIT 2

Returns the following:

Learn more

NVL

Returns value1 if value1 is not null, otherwise returns value2.

• Syntax: NVL(value1, value1)
• Function type: Scalar, other

The following example replaces each null value in the Tail_Number column of the flight-carriers datasource with the string "No tail number."

SELECT "Tail_Number" AS "original_column",
  NVL("Tail_Number", 'No tail number') AS "remove_null"
FROM "flight-carriers"
WHERE "OriginState" = 'CT'
LIMIT 2

Returns the following:

Learn more

PARSE_JSON

Parses an expression into a COMPLEX<json> object.

The function deserializes JSON values when processing them, translating stringified JSON into a nested structure. If the input is invalid JSON or not a VARCHAR, it returns an error.

• Syntax: PARSE_JSON(expr)
• Function type: JSON

The following example creates a COMPLEX<json> object gus from a string of fields:

SELECT
  PARSE_JSON('{"name":"Gus","email":"[email protected]","type":"Pet"}') as gus

Returns the following:

Learn more

PARSE_LONG

Converts a string into a long(BIGINT) with the given radix, or into DECIMAL(base 10) if a radix is not provided.

• Syntax:PARSE_LONG(string[, radix])
• Function type: Scalar, string

The following example converts the string representation of the binary, radix 2, number 1100 into its long (BIGINT) equivalent.

SELECT 
  '1100' AS "binary_as_string", 
  PARSE_LONG('1110', 2) AS "bigint_value"

Returns the following:

Learn more

PERCENT_RANK

Returns the relative rank of the row calculated as a percentage according to the formula: RANK() OVER (window) / COUNT(1) OVER (window).

• Syntax: PERCENT_RANK()
• Function type: Window

The following example returns the percent rank within the window for flights by airline from two airports on a single day.

SELECT FLOOR("__time" TO DAY)  AS "flight_day",
    "Origin" AS "airport",
    "Reporting_Airline" as "airline",
    COUNT("Flight_Number_Reporting_Airline") as "num_flights",
    PERCENT_RANK() OVER (PARTITION BY "Origin" ORDER BY COUNT("Flight_Number_Reporting_Airline") DESC) AS "pct_rank"
FROM "flight-carriers"
WHERE FLOOR("__time" TO DAY) = '2005-11-01'
    AND "Origin" IN ('KOA', 'LIH')
GROUP BY 1, 2, 3

Returns the following:

Learn more

POSITION

Returns the one-based index position of a substring within an expression, optionally starting from a given one-based index. If substring is not found, returns 0.

• Syntax: POSITION(substring IN expr [FROM startingIndex])
• Function type: Scalar, string

The following example returns the one-based index of the substring PR in the OriginCityName column from the flight-carriers datasource starting from index 5.

SELECT 
  "OriginCityName" AS "origin_city",
  POSITION('PR' IN "OriginCityName" FROM 5) AS "index"
FROM "flight-carriers"
LIMIT 2

Returns the following:

Learn more

POWER

Calculates a numerical expression raised to the specified power.

• Syntax: POWER(base, exponent)
• Function type: Scalar, numeric

The following example raises 5 to the power of 2.

SELECT POWER(5, 2) AS "power"

Returns the following:

Learn more

RADIANS

Converts an angle from degrees to radians.

• Syntax: RADIANS(expr)
• Function type: Scalar, numeric

The following example converts an angle of 180 degrees to radians

SELECT RADIANS(180) AS "radians"

Returns the following:

Learn more

RANK

Returns the rank with gaps for a row within a window. For example, if two rows tie for rank 1, the next rank is 3.

• Syntax: RANK()
• Function type: Window

The following example returns the rank within the window for flights by airline from two airports on a single day.

SELECT FLOOR("__time" TO DAY)  AS "flight_day",
    "Origin" AS "airport",
    "Reporting_Airline" as "airline",
    COUNT("Flight_Number_Reporting_Airline") as "num_flights",
    RANK() OVER (PARTITION BY "Origin" ORDER BY COUNT("Flight_Number_Reporting_Airline") DESC) AS "rank"
FROM "flight-carriers"
WHERE FLOOR("__time" TO DAY) = '2005-11-01'
    AND "Origin" IN ('KOA', 'LIH')
GROUP BY 1, 2, 3

Returns the following:

Learn more

REGEXP_EXTRACT

Apply regular expression pattern to expr and extract the Nth capture group. If N is unspecified or zero, returns the first substring that matches the pattern. Returns null if there is no matching pattern.

• Syntax: REGEXP_EXTRACT(expr, pattern[, N])
• Function type: Scalar, string

The following example uses regular expressions to find city names inside the OriginCityName column from the flight-carriers datasource by matching what comes before the comma.

SELECT 
  "OriginCityName" AS "origin_city",
  REGEXP_EXTRACT("OriginCityName", '([^,]+)', 0) AS "pattern_match"
FROM "flight-carriers"
LIMIT 1

Returns the following:

Learn more

REGEXP_LIKE

Returns true if the regular expression pattern finds a match in expr. Returns false otherwise.

• Syntax: REGEXP_LIKE(expr, pattern)
• Function type: Scalar, string

The following example returns true when the OriginCityName column from flight-carriers has a city name containing a space.

SELECT 
  "OriginCityName" AS "origin_city",
  REGEXP_LIKE("OriginCityName", '[A-Za-z]+\s[A-Za-z]+') AS "pattern_found"
FROM "flight-carriers"
LIMIT 2

Returns the following:

Learn more

REGEXP_REPLACE

Replaces all occurrences of a regular expression in a string expression with a replacement string. Refer to capture groups in the replacement string using $group syntax. For example: $1 or $2.

• Syntax: REGEXP_REPLACE(expr, pattern, replacement)
• Function type: Scalar, string

The following example matches three consecutive words, where each word is its own capture group, and replaces the matched words with the word in the second capture group punctuated with exclamation marks.

SELECT 
  'foo bar baz' AS "original_string",
  REGEXP_REPLACE('foo bar baz', '([A-Za-z]+) ([A-Za-z]+) ([A-Za-z]+)' , '$2!') AS "modified_string"

Returns the following:

Learn more

REPEAT

Repeats the string expression N times, where N is an integer.

• Syntax: REPEAT(expr, N)
• Function type: Scalar, string

The following example returns the string expression abc repeated 3 times.

SELECT 
  'abc' AS "original_string",
  REPEAT('abc', 3) AS "with_repetition"

Returns the following:

Learn more

REPLACE

Replaces instances of a substring with a replacement string in the given expression.

• Syntax: REPLACE(expr, substring, replacement)
• Function type: Scalar, string

The following example replaces instances of the substring abc with XYZ.

SELECT 
  'abc 123 abc 123' AS "original_string",
   REPLACE('abc 123 abc 123', 'abc', 'XYZ') AS "modified_string"

Returns the following:

Learn more

REVERSE

Reverses the given expression.

• Syntax: REVERSE(expr)
• Function type: Scalar, string

The following example reverses the string expression abc.

SELECT 
  'abc' AS "original_string",
  REVERSE('abc') AS "reversal"

Returns the following:

Learn more

RIGHT

Returns the N rightmost characters of an expression, where N is an integer value.

• Syntax: RIGHT(expr, N)
• Function type: Scalar, string

The following example returns the 3 rightmost characters of the expression ABCDEFG.

SELECT
  'ABCDEFG' AS "expression",
  RIGHT('ABCDEFG', 3) AS "rightmost_characters"

Returns the following:

Learn more

ROUND

Calculates the rounded value for a numerical expression.

• Syntax: ROUND(expr[, digits])
• Function type: Scalar, numeric

The following applies the ROUND function to 0 decimal points on the pickup_longitude column from the taxi-trips datasource.

SELECT
  "pickup_longitude" AS "pickup_longitude",
  ROUND("pickup_longitude", 0) as "rounded_pickup_longitude"
FROM "taxi-trips"
WHERE "pickup_longitude" IS NOT NULL
LIMIT 1

Returns the following:

Learn more

ROW_NUMBER

Returns the number of the row within the window starting from 1.

• Syntax: ROW_NUMBER()
• Function type: Window

The following example returns the row number within the window for flights by airline from two airports on a single day.

SELECT FLOOR("__time" TO DAY)  AS "flight_day",
    "Origin" AS "airport",
    "Reporting_Airline" as "airline",
    COUNT("Flight_Number_Reporting_Airline") as "num_flights",
    ROW_NUMBER() OVER (PARTITION BY "Origin" ORDER BY COUNT("Flight_Number_Reporting_Airline") DESC) AS "row_num"
FROM "flight-carriers"
WHERE FLOOR("__time" TO DAY) = '2005-11-01'
    AND "Origin" IN ('KOA', 'LIH')
GROUP BY 1, 2, 3

Returns the following: